(Reply destination) [DB2-L] Revert to DB2 z/OS V7 after Upgrade to DB2 z/OS V8 ENFM

Wayne Driscoll

(Reply destination) [DB2-L] Revert to DB2 z/OS V7 after Upgrade to DB2 z/OS V8 ENFM
Then fix your e-mail client so the reply-to is not your direct address.

Wayne Driscoll
Product Developer
NOTE: All opinions are strictly my own.



-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Ted MacNEIL
Sent: Thursday, March 13, 2008 2:40 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Revert to DB2 z/OS V7 after Upgrade to DB2 z/OS V8 ENFM

I know IBM does not support it.

Also, please don't send to both me and the list.
I have enough e-mail clutter as it is.

-
Too busy driving to stop for gas!

-----Original Message-----
From: Willie Favero <[login to unmask email]>

Date: Thu, 13 Mar 2008 13:24:59
To:[login to unmask email]
Cc:[login to unmask email]
Subject: Re: [DB2-L] Revert to DB2 z/OS V7 after Upgrade to DB2 z/OS V8 ENFM


It is possible, as Mark points out.  

However, it (falling back to V7 from V8 ENFM)  is HIGHLY discouraged and
not for the slight of heart.

Ted MacNEIL wrote: I thought that once you got past compatability mode
there was no going back? - Too busy driving to stop for gas!
______________________________________________________________________ *
IDUG 08 Warsaw, Poland * 13-17 October 2008 * http://IDUG.ORG/lsEU
< http://IDUG.ORG/lsEU > *
______________________________________________________________________ The
IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list
archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug
< http://www.idug.org/lsidug > under the Listserv tab. While at the site, you
can also access the IDUG Online Learning Center, Tech Library and Code
Place, see the latest IDUG conference information and much more. If you have
not yet signed up for Basic Membership in IDUG, available at no cost, click
on Member Services at http://www.idug.org/lsms < http://www.idug.org/lsms >
-- Willie My DB2 blog --> http://blogs.ittoolbox.com/database/db2zos
< http://blogs.ittoolbox.com/database/db2zos > Houston, TX, USA

______________________________________________________________________

* IDUG 08 Warsaw, Poland * 13-17 October 2008 * http://IDUG.ORG/lsEU *
______________________________________________________________________

The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L
list archives, FAQ, and delivery preferences are at
http://www.idug.org/lsidug under the Listserv tab. While at the site, you
can also access the IDUG Online Learning Center, Tech Library and Code
Place, see the latest IDUG conference information and much more. If you
have not yet signed up for Basic Membership in IDUG, available at no cost,
click on Member Services at http://www.idug.org/lsms

_____________________________________________________________________

* IDUG 08 Dallas, TX, USA * May 18-22, 2008 * http://IDUG.ORG/lsNA *
_____________________________________________________________________
The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Steve Runtsch

Re: Revert to DB2 z/OS V7 after Upgrade to DB2 z/OS V8 ENFM
(in response to Wayne Driscoll)
Thanks for all of the responses. After rereading my post I noticed that I
had mistakenly asked about returning to V7. I had intended to ask about
returning to V8 CM. I don't think that would have made much difference in
most responses (other than Avram's) since reverting back anything after
upgrading to ENFM would involve, as Phil Sevetson remarked, "Roll the
whole thing back to the last system wide image copy taken before the ENFM
implementation."

My main point was to determine if anyone became possessed, actually did
it, and why.

Thank you again.
Steve

Steve Runtsch | DBMS Technical Support | Assurant Inc.
Office: 651 361 5378 | Mobile: 651 260 9263 | Toll Free: 866 866 4488 |
Facsimile: 651 361 5625
[login to unmask email]




Avram Friedman <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
03/14/2008 08:59 AM
Please respond to
DB2 Database Discussion list at IDUG <[login to unmask email]>


To
[login to unmask email]
cc

Subject
Re: [DB2-L] Revert to DB2 z/OS V7 after Upgrade to DB2 z/OS V8 ENFM






Would like to make several points.
1. Fall back from V8 CM mode to V7 is not supported. What is supported
with
a catalog in V8 CM mode is tolerance of V7 executables.

....Regards
Avram Friedman





**************************************************************************************
This e-mail message and all attachments transmitted with it may contain legally privileged and/or confidential information intended solely for the use of the addressee(s). If the reader of this message is not the intended recipient, you are hereby notified that any reading, dissemination, distribution, copying, forwarding or other use of this message or its attachments is strictly prohibited. If you have received this message in error, please notify the sender immediately and delete this message and all copies and backups thereof.

Thank you.
**************************************************************************************

_____________________________________________________________________

* IDUG 08 Dallas, TX, USA * May 18-22, 2008 * http://IDUG.ORG/lsNA *
_____________________________________________________________________
The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Myron Miller

Re: Revert to DB2 z/OS V7 after Upgrade to DB2 z/OS V8 ENFM
(in response to Steve Runtsch)
Actually, we did revert back from V8 NFM to V8 CM with a complete restore copy
of all the system datasets.

We hit a major brand new problem in production that stopped the entire system
from running that we had to roll back from until we got fixes from IBM.
Actually this roll back happened TWICE for two separate problems.

Our conversion from CM to NFM was not a smooth conversion and definitely wasn't
straightforward or simple like lots of people.

Myron
--- [login to unmask email] wrote:

> Thanks for all of the responses. After rereading my post I noticed that I
> had mistakenly asked about returning to V7. I had intended to ask about
> returning to V8 CM. I don't think that would have made much difference in
> most responses (other than Avram's) since reverting back anything after
> upgrading to ENFM would involve, as Phil Sevetson remarked, "Roll the
> whole thing back to the last system wide image copy taken before the ENFM
> implementation."
>
> My main point was to determine if anyone became possessed, actually did
> it, and why.
>
> Thank you again.
> Steve
>
> Steve Runtsch | DBMS Technical Support | Assurant Inc.
> Office: 651 361 5378 | Mobile: 651 260 9263 | Toll Free: 866 866 4488 |
> Facsimile: 651 361 5625
> [login to unmask email]
>
>
>
>
> Avram Friedman <[login to unmask email]>
> Sent by: DB2 Data Base Discussion List <[login to unmask email]>
> 03/14/2008 08:59 AM
> Please respond to
> DB2 Database Discussion list at IDUG <[login to unmask email]>
>
>
> To
> [login to unmask email]
> cc
>
> Subject
> Re: [DB2-L] Revert to DB2 z/OS V7 after Upgrade to DB2 z/OS V8 ENFM
>
>
>
>
>
>
> Would like to make several points.
> 1. Fall back from V8 CM mode to V7 is not supported. What is supported
> with
> a catalog in V8 CM mode is tolerance of V7 executables.
>
> ....Regards
> Avram Friedman
>
>
>
>
>
>
**************************************************************************************
> This e-mail message and all attachments transmitted with it may contain
> legally privileged and/or confidential information intended solely for the
> use of the addressee(s). If the reader of this message is not the intended
> recipient, you are hereby notified that any reading, dissemination,
> distribution, copying, forwarding or other use of this message or its
> attachments is strictly prohibited. If you have received this message in
> error, please notify the sender immediately and delete this message and all
> copies and backups thereof.
>
> Thank you.
>
**************************************************************************************
>
> _____________________________________________________________________
>
> * IDUG 08 Dallas, TX, USA * May 18-22, 2008 * http://IDUG.ORG/lsNA *
> _____________________________________________________________________
> The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L
> list archives, FAQ, and delivery preferences are at
> http://www.idug.org/lsidug under the Listserv tab. While at the site, you
> can also access the IDUG Online Learning Center, Tech Library and Code Place,
> see the latest IDUG conference information and much more. If you have not
> yet signed up for Basic Membership in IDUG, available at no cost, click on
> Member Services at http://www.idug.org/lsms
>

_____________________________________________________________________

* IDUG 08 Dallas, TX, USA * May 18-22, 2008 * http://IDUG.ORG/lsNA *
_____________________________________________________________________
The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Steve Runtsch

Re: Revert to DB2 z/OS V7 after Upgrade to DB2 z/OS V8 ENFM
(in response to Myron Miller)
Thanks Myron.

By "system data sets" I'm guessing you referring to the DB2 catalog and
directory. May I ask, what APARs address the problems you had? Was the
subsystem up and in use when the problem was discovered, or was the issue
discovered during conversion?

Has anyone else reverted to CM after ENFM or NFM?

Steve

Steve Runtsch | DBMS Technical Support | Assurant Inc.
Office: 651 361 5378 | Mobile: 651 260 9263 | Toll Free: 866 866 4488 |
Facsimile: 651 361 5625
[login to unmask email]




Myron Miller <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
03/15/2008 08:36 AM
Please respond to
DB2 Database Discussion list at IDUG <[login to unmask email]>


To
[login to unmask email]
cc

Subject
Re: [DB2-L] Revert to DB2 z/OS V7 after Upgrade to DB2 z/OS V8 ENFM






Actually, we did revert back from V8 NFM to V8 CM with a complete restore
copy
of all the system datasets.

We hit a major brand new problem in production that stopped the entire
system
from running that we had to roll back from until we got fixes from IBM.
Actually this roll back happened TWICE for two separate problems.

Our conversion from CM to NFM was not a smooth conversion and definitely
wasn't
straightforward or simple like lots of people.

Myron
--- [login to unmask email] wrote:

> Thanks for all of the responses. After rereading my post I noticed that
I
> had mistakenly asked about returning to V7. I had intended to ask about

> returning to V8 CM. I don't think that would have made much difference
in
> most responses (other than Avram's) since reverting back anything after
> upgrading to ENFM would involve, as Phil Sevetson remarked, "Roll the
> whole thing back to the last system wide image copy taken before the
ENFM
> implementation."
>
> My main point was to determine if anyone became possessed, actually did
> it, and why.
>
> Thank you again.
> Steve
>
> Steve Runtsch | DBMS Technical Support | Assurant Inc.
> Office: 651 361 5378 | Mobile: 651 260 9263 | Toll Free: 866 866 4488 |
> Facsimile: 651 361 5625
> [login to unmask email]
>
>
>
>
> Avram Friedman <[login to unmask email]>
> Sent by: DB2 Data Base Discussion List <[login to unmask email]>
> 03/14/2008 08:59 AM
> Please respond to
> DB2 Database Discussion list at IDUG <[login to unmask email]>
>
>
> To
> [login to unmask email]
> cc
>
> Subject
> Re: [DB2-L] Revert to DB2 z/OS V7 after Upgrade to DB2 z/OS V8 ENFM
>
>
>
>
>
>
> Would like to make several points.
> 1. Fall back from V8 CM mode to V7 is not supported. What is supported
> with
> a catalog in V8 CM mode is tolerance of V7 executables.
>
> ....Regards
> Avram Friedman
>
>
>
>
>
>
**************************************************************************************
> This e-mail message and all attachments transmitted with it may contain
> legally privileged and/or confidential information intended solely for
the
> use of the addressee(s). If the reader of this message is not the
intended
> recipient, you are hereby notified that any reading, dissemination,
> distribution, copying, forwarding or other use of this message or its
> attachments is strictly prohibited. If you have received this message in
> error, please notify the sender immediately and delete this message and
all
> copies and backups thereof.
>
> Thank you.
>
**************************************************************************************
>
> _____________________________________________________________________
>
> * IDUG 08 Dallas, TX, USA * May 18-22, 2008 * http://IDUG.ORG/lsNA *
> _____________________________________________________________________
> The IDUG DB2-L Listserv is only part of your membership in IDUG. The
DB2-L
> list archives, FAQ, and delivery preferences are at
> http://www.idug.org/lsidug under the Listserv tab. While at the site,
you
> can also access the IDUG Online Learning Center, Tech Library and Code
Place,
> see the latest IDUG conference information and much more. If you have
not
> yet signed up for Basic Membership in IDUG, available at no cost, click
on
> Member Services at http://www.idug.org/lsms
>

_____________________________________________________________________

* IDUG 08 Dallas, TX, USA * May 18-22, 2008 * http://IDUG.ORG/lsNA *
_____________________________________________________________________
The IDUG DB2-L Listserv is only part of your membership in IDUG. The
DB2-L list archives, FAQ, and delivery preferences are at
http://www.idug.org/lsidug under the Listserv tab. While at the site, you
can also access the IDUG Online Learning Center, Tech Library and Code
Place, see the latest IDUG conference information and much more. If you
have not yet signed up for Basic Membership in IDUG, available at no cost,
click on Member Services at http://www.idug.org/lsms



**************************************************************************************
This e-mail message and all attachments transmitted with it may contain legally privileged and/or confidential information intended solely for the use of the addressee(s). If the reader of this message is not the intended recipient, you are hereby notified that any reading, dissemination, distribution, copying, forwarding or other use of this message or its attachments is strictly prohibited. If you have received this message in error, please notify the sender immediately and delete this message and all copies and backups thereof.

Thank you.
**************************************************************************************

______________________________________________________________________

* IDUG 08 Warsaw, Poland * 13-17 October 2008 * http://IDUG.ORG/lsEU *
______________________________________________________________________

The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Myron Miller

Re: Revert to DB2 z/OS V7 after Upgrade to DB2 z/OS V8 ENFM
(in response to Steve Runtsch)
I don't remember the APARs involved. It's been over a year now.

The system was up and running in production when it failed. I wish it had been
conversion. But it was real user programs that had problems.

One was some type of conversion error. We had a table created about version
2.1 that had the CCSID set to something or other and DB2 couldn't handle that.
IBM had never seen that problem before from a customer, just one time in their
simulations and it took a while to get a fix.

Another that bit us required a special usermod to turn off the feature of
allowing multiple open cursors from calling a stored procedure multiple times.
Basically, we have programs that call a single stored procedure maybe 500,000
to several million times and never closed the result cursor. Prior to V8, the
result cursor was automatically closed by DB2 on each subsequent call to the
Stored procedure. V8 added the capability to call a SP multiple times and have
the result set open with each call. This caused us to use up the EDM pool
rather quickly storing pointers to all the open cursors. And of course, once
the EDM pool fills up, it doesn't take long for DB2 to come down after that.

We have somewhat over 800+ programs that call SPs one or more times and never
found this in test. (Programmers obviously didn't test as thoroughly as we
hoped.) And this particular pecularity was not documented in any of the change
documentation for V8 or I would have caught it. At least it was not documented
in such a way that I recognized the issue of no longer closing cursors upon a
call.

Myron

Myron
--- [login to unmask email] wrote:

> Thanks Myron.
>
> By "system data sets" I'm guessing you referring to the DB2 catalog and
> directory. May I ask, what APARs address the problems you had? Was the
> subsystem up and in use when the problem was discovered, or was the issue
> discovered during conversion?
>
> Has anyone else reverted to CM after ENFM or NFM?
>
> Steve
>
> Steve Runtsch | DBMS Technical Support | Assurant Inc.
> Office: 651 361 5378 | Mobile: 651 260 9263 | Toll Free: 866 866 4488 |
> Facsimile: 651 361 5625
> [login to unmask email]
>
>
>
>
> Myron Miller <[login to unmask email]>
> Sent by: DB2 Data Base Discussion List <[login to unmask email]>
> 03/15/2008 08:36 AM
> Please respond to
> DB2 Database Discussion list at IDUG <[login to unmask email]>
>
>
> To
> [login to unmask email]
> cc
>
> Subject
> Re: [DB2-L] Revert to DB2 z/OS V7 after Upgrade to DB2 z/OS V8 ENFM
>
>
>
>
>
>
> Actually, we did revert back from V8 NFM to V8 CM with a complete restore
> copy
> of all the system datasets.
>
> We hit a major brand new problem in production that stopped the entire
> system
> from running that we had to roll back from until we got fixes from IBM.
> Actually this roll back happened TWICE for two separate problems.
>
> Our conversion from CM to NFM was not a smooth conversion and definitely
> wasn't
> straightforward or simple like lots of people.
>
> Myron
> --- [login to unmask email] wrote:
>
> > Thanks for all of the responses. After rereading my post I noticed that
> I
> > had mistakenly asked about returning to V7. I had intended to ask about
>
> > returning to V8 CM. I don't think that would have made much difference
> in
> > most responses (other than Avram's) since reverting back anything after
> > upgrading to ENFM would involve, as Phil Sevetson remarked, "Roll the
> > whole thing back to the last system wide image copy taken before the
> ENFM
> > implementation."
> >
> > My main point was to determine if anyone became possessed, actually did
> > it, and why.
> >
> > Thank you again.
> > Steve
> >
> > Steve Runtsch | DBMS Technical Support | Assurant Inc.
> > Office: 651 361 5378 | Mobile: 651 260 9263 | Toll Free: 866 866 4488 |
> > Facsimile: 651 361 5625
> > [login to unmask email]
> >
> >
> >
> >
> > Avram Friedman <[login to unmask email]>
> > Sent by: DB2 Data Base Discussion List <[login to unmask email]>
> > 03/14/2008 08:59 AM
> > Please respond to
> > DB2 Database Discussion list at IDUG <[login to unmask email]>
> >
> >
> > To
> > [login to unmask email]
> > cc
> >
> > Subject
> > Re: [DB2-L] Revert to DB2 z/OS V7 after Upgrade to DB2 z/OS V8 ENFM
> >
> >
> >
> >
> >
> >
> > Would like to make several points.
> > 1. Fall back from V8 CM mode to V7 is not supported. What is supported
> > with
> > a catalog in V8 CM mode is tolerance of V7 executables.
> >
> > ....Regards
> > Avram Friedman
> >
> >
> >
> >
> >
> >
>
**************************************************************************************
> > This e-mail message and all attachments transmitted with it may contain
> > legally privileged and/or confidential information intended solely for
> the
> > use of the addressee(s). If the reader of this message is not the
> intended
> > recipient, you are hereby notified that any reading, dissemination,
> > distribution, copying, forwarding or other use of this message or its
> > attachments is strictly prohibited. If you have received this message in
> > error, please notify the sender immediately and delete this message and
> all
> > copies and backups thereof.
> >
> > Thank you.
> >
>
**************************************************************************************
> >
> > _____________________________________________________________________
> >
> > * IDUG 08 Dallas, TX, USA * May 18-22, 2008 * http://IDUG.ORG/lsNA *
> > _____________________________________________________________________
> > The IDUG DB2-L Listserv is only part of your membership in IDUG. The
> DB2-L
> > list archives, FAQ, and delivery preferences are at
> > http://www.idug.org/lsidug under the Listserv tab. While at the site,
> you
> > can also access the IDUG Online Learning Center, Tech Library and Code
> Place,
> > see the latest IDUG conference information and much more. If you have
> not
> > yet signed up for Basic Membership in IDUG, available at no cost, click
> on
> > Member Services at http://www.idug.org/lsms
> >
>
> _____________________________________________________________________
>
> * IDUG 08 Dallas, TX, USA * May 18-22, 2008 * http://IDUG.ORG/lsNA *
> _____________________________________________________________________
> The IDUG DB2-L Listserv is only part of your membership in IDUG. The
> DB2-L list archives, FAQ, and delivery preferences are at
> http://www.idug.org/lsidug under the Listserv tab. While at the site, you
> can also access the IDUG Online Learning Center, Tech Library and Code
> Place, see the latest IDUG conference information and much more. If you
> have not yet signed up for Basic Membership in IDUG, available at no cost,
> click on Member Services at http://www.idug.org/lsms
>
>
>
>
**************************************************************************************
> This e-mail message and all attachments transmitted with it may contain
> legally privileged and/or confidential information intended solely for the
> use of the addressee(s). If the reader of this message is not the intended
> recipient, you are hereby notified that any reading, dissemination,
> distribution, copying, forwarding or other use of this message or its
> attachments is strictly prohibited. If you have received this message in
> error, please notify the sender immediately and delete this message and all
> copies and backups thereof.
>
> Thank you.
>
**************************************************************************************
>
> ______________________________________________________________________
>
> * IDUG 08 Warsaw, Poland * 13-17 October 2008 * http://IDUG.ORG/lsEU *
> ______________________________________________________________________
>
> The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L
> list archives, FAQ, and delivery preferences are at
> http://www.idug.org/lsidug under the Listserv tab. While at the site, you
> can also access the IDUG Online Learning Center, Tech Library and Code Place,
> see the latest IDUG conference information and much more. If you have not
> yet signed up for Basic Membership in IDUG, available at no cost, click on
> Member Services at http://www.idug.org/lsms
>

______________________________________________________________________

* IDUG 08 Warsaw, Poland * 13-17 October 2008 * http://IDUG.ORG/lsEU *
______________________________________________________________________

The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Roger Miller

Re: Revert to DB2 z/OS V7 after Upgrade to DB2 z/OS V8 ENFM
(in response to Myron Miller)
Sorry that one was missed. I checked the book and found this in the
incompatibilities section back as far as the -00 level. Is this a match? The
SQL standard semantics are those of V8, while many customers depended
upon the earlier flavor, except for some Java work that only functions with
standard behavior. What were you setting for MAX_ST_PROC MAX STORED
PROCS ===> Maximium active stored procs per thread?

Multiple calls to the same stored procedure
In previous versions of DB2, if a stored procedure was called twice from the
same program and at the same nesting level, DB2 closed the result set cursors
and released storage for the first instance of the stored procedure before
making the second call. In DB2 Version 8, if the requester and the server are
both DB2 Version 8 subsystems in new-function mode, when the second call is
made, both instances of the stored procedure can run at the same time. DB2
does not close the result sets from the first call or release storage for the first
instance of the stored procedure. If you make multiple calls to the same
stored procedure in the same application, be aware of the following
considerations:
A DESCRIBE PROCEDURE statement describes the last instance of the stored
procedure.
The ASSOCIATE LOCATOR statement works on the last instance of the stored
procedure. You should issue an ASSOCIATE LOCATOR statement after each
call to the stored procedure to provide a unique locator value for each result
set.
The ALLOCATE CURSOR statement must specify a unique cursor name for the
result set of each instance of the stored procedure. Otherwise, you will lose
the data from the result sets that are returned from prior instances or calls to
the stored procedure.

Roger Miller, DB2 for z/OS

On Mon, 17 Mar 2008 13:49:18 -0700, Myron Miller
<[login to unmask email]> wrote:

>I don't remember the APARs involved. It's been over a year now.
>
>The system was up and running in production when it failed. I wish it had
been
>conversion. But it was real user programs that had problems.
>
>One was some type of conversion error. We had a table created about
version
>2.1 that had the CCSID set to something or other and DB2 couldn't handle
that.
>IBM had never seen that problem before from a customer, just one time in
their
>simulations and it took a while to get a fix.
>
>Another that bit us required a special usermod to turn off the feature of
>allowing multiple open cursors from calling a stored procedure multiple times.
>Basically, we have programs that call a single stored procedure maybe
500,000
>to several million times and never closed the result cursor. Prior to V8, the
>result cursor was automatically closed by DB2 on each subsequent call to the
>Stored procedure. V8 added the capability to call a SP multiple times and
have
>the result set open with each call. This caused us to use up the EDM pool
>rather quickly storing pointers to all the open cursors. And of course, once
>the EDM pool fills up, it doesn't take long for DB2 to come down after that.
>
>We have somewhat over 800+ programs that call SPs one or more times and
never
>found this in test. (Programmers obviously didn't test as thoroughly as we
>hoped.) And this particular pecularity was not documented in any of the
change
>documentation for V8 or I would have caught it. At least it was not
documented
>in such a way that I recognized the issue of no longer closing cursors upon a
>call.
>
>Myron
>
>Myron
>--- [login to unmask email] wrote:
>
>> Thanks Myron.
>>
>> By "system data sets" I'm guessing you referring to the DB2 catalog and
>> directory. May I ask, what APARs address the problems you had? Was
the
>> subsystem up and in use when the problem was discovered, or was the
issue
>> discovered during conversion?
>>
>> Has anyone else reverted to CM after ENFM or NFM?
>>
>> Steve
>>
>> Steve Runtsch | DBMS Technical Support | Assurant Inc.
>> Office: 651 361 5378 | Mobile: 651 260 9263 | Toll Free: 866 866 4488 |
>> Facsimile: 651 361 5625
>> [login to unmask email]
>>
>>
>>
>>
>> Myron Miller <[login to unmask email]>
>> Sent by: DB2 Data Base Discussion List <[login to unmask email]>
>> 03/15/2008 08:36 AM
>> Please respond to
>> DB2 Database Discussion list at IDUG <[login to unmask email]>
>>
>>
>> To
>> [login to unmask email]
>> cc
>>
>> Subject
>> Re: [DB2-L] Revert to DB2 z/OS V7 after Upgrade to DB2 z/OS V8 ENFM
>>
>>
>>
>>
>>
>>
>> Actually, we did revert back from V8 NFM to V8 CM with a complete restore
>> copy
>> of all the system datasets.
>>
>> We hit a major brand new problem in production that stopped the entire
>> system
>> from running that we had to roll back from until we got fixes from IBM.
>> Actually this roll back happened TWICE for two separate problems.
>>
>> Our conversion from CM to NFM was not a smooth conversion and definitely
>> wasn't
>> straightforward or simple like lots of people.
>>
>> Myron
>> --- [login to unmask email] wrote:
>>
>> > Thanks for all of the responses. After rereading my post I noticed that
>> I
>> > had mistakenly asked about returning to V7. I had intended to ask about
>>
>> > returning to V8 CM. I don't think that would have made much difference
>> in
>> > most responses (other than Avram's) since reverting back anything after
>> > upgrading to ENFM would involve, as Phil Sevetson remarked, "Roll the
>> > whole thing back to the last system wide image copy taken before the
>> ENFM
>> > implementation."
>> >
>> > My main point was to determine if anyone became possessed, actually did
>> > it, and why.
>> >
>> > Thank you again.
>> > Steve
>> >
>> > Steve Runtsch | DBMS Technical Support | Assurant Inc.
>> > Office: 651 361 5378 | Mobile: 651 260 9263 | Toll Free: 866 866 4488 |
>> > Facsimile: 651 361 5625
>> > [login to unmask email]
>> >
>> >
>> >
>> >
>> > Avram Friedman <[login to unmask email]>
>> > Sent by: DB2 Data Base Discussion List <[login to unmask email]>
>> > 03/14/2008 08:59 AM
>> > Please respond to
>> > DB2 Database Discussion list at IDUG <[login to unmask email]>
>> >
>> >
>> > To
>> > [login to unmask email]
>> > cc
>> >
>> > Subject
>> > Re: [DB2-L] Revert to DB2 z/OS V7 after Upgrade to DB2 z/OS V8 ENFM
>> >
>> >
>> >
>> >
>> >
>> >
>> > Would like to make several points.
>> > 1. Fall back from V8 CM mode to V7 is not supported. What is supported
>> > with
>> > a catalog in V8 CM mode is tolerance of V7 executables.
>> >
>> > ....Regards
>> > Avram Friedman
>> >
>> >
>>
>*********************************************************
*****************************
>> > This e-mail message and all attachments transmitted with it may contain
>> > legally privileged and/or confidential information intended solely for
>> the
>> > use of the addressee(s). If the reader of this message is not the
>> intended
>> > recipient, you are hereby notified that any reading, dissemination,
>> > distribution, copying, forwarding or other use of this message or its
>> > attachments is strictly prohibited. If you have received this message in
>> > error, please notify the sender immediately and delete this message and
>> all
>> > copies and backups thereof.
>> >
>> > Thank you.
>> >
>>
>*********************************************************
*****************************
>> >
>> >
__________________________________________________________________
___

>>
__________________________________________________________________
___
>>
>> * IDUG 08 Dallas, TX, USA * May 18-22, 2008 * http://IDUG.ORG/lsNA *
>>
__________________________________________________________________
___
>> The IDUG DB2-L Listserv is only part of your membership in IDUG. The
>> DB2-L list archives, FAQ, and delivery preferences are at
>> http://www.idug.org/lsidug under the Listserv tab. While at the site, you
>> can also access the IDUG Online Learning Center, Tech Library and Code
>> Place, see the latest IDUG conference information and much more. If you
>> have not yet signed up for Basic Membership in IDUG, available at no cost,
>> click on Member Services at http://www.idug.org/lsms
>>
>>
>>
>>
>*********************************************************
*****************************
>> This e-mail message and all attachments transmitted with it may contain
>> legally privileged and/or confidential information intended solely for the
>> use of the addressee(s). If the reader of this message is not the intended
>> recipient, you are hereby notified that any reading, dissemination,
>> distribution, copying, forwarding or other use of this message or its
>> attachments is strictly prohibited. If you have received this message in
>> error, please notify the sender immediately and delete this message and all
>> copies and backups thereof.
>>
>> Thank you.
>>
>*********************************************************
*****************************
>>
>>
__________________________________________________________________
____
>>
>> * IDUG 08 Warsaw, Poland * 13-17 October 2008 * http://IDUG.ORG/lsEU *
>>
__________________________________________________________________
____
>>

_____________________________________________________________________

* IDUG 08 Dallas, TX, USA * May 18-22, 2008 * http://IDUG.ORG/lsNA *
_____________________________________________________________________
The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Myron Miller

Re: Revert to DB2 z/OS V7 after Upgrade to DB2 z/OS V8 ENFM
(in response to Roger Miller)
Roger,
It's a bit more serious about the multiple calls than just the associate
locator issue. Each call sets up an area in the EDM pool for the result set of
about 6k, give or take. Now do the calls about 500,000 times and figure the
resulting size of the EDM pool. It's slightly larger than 2g (by a large
bunch). And when you run out of EDM pool, you obviously know where DB2 is
going, DOWN!

We have MAX_ST_PROCS to something like 99,999 or something like that. But from
everything I've been able to tell, that number really doesn't make any
difference in this issue. It allows for the maximum number of different calls,
but didn't help or make any difference on when the same exact SP was called
repetitively. I tried a number of different values in testing this problem and
we saw no difference in the problem. Sometimes we went down with shortage of
EDM pool prior to hitting the 99,999 number and sometimes it was when 200,000
times the SP was called which is a much higher number than the theoretical
limit. So in our testing, this limit did not seem to be a factor at all.

As far as the incompatibilities issue, it was not in the books and was totally
different from what the books specified.. I went thru carefully all the
incompatibilities know prior to the install and ran all the check queries
multiple times. This one was wierd because it was isolated to only a few
tables out of a number that should have had the exact same issue.

I spent considerable time on the phone with the support center and they hadn't
seen it before but were able to figure out the problem from the dumps and
stuff. They were extremely helpful on the problem, helping significantly in
working thru all the issues, but it did take some time to resolve. And
Production was down for a considerable time because of it, so we had to roll
back before it was finally resolved. The fix was actually two-phased, a change
to the CCSID value in the DB2 Catalog and a PTF being applied to prevent the
issue from re-occuring.

Myron


--- Roger Miller <[login to unmask email]> wrote:

> Sorry that one was missed. I checked the book and found this in the
> incompatibilities section back as far as the -00 level. Is this a match?
> The
> SQL standard semantics are those of V8, while many customers depended
> upon the earlier flavor, except for some Java work that only functions with
> standard behavior. What were you setting for MAX_ST_PROC MAX STORED
> PROCS ===> Maximium active stored procs per thread?
>
> Multiple calls to the same stored procedure
> In previous versions of DB2, if a stored procedure was called twice from the
> same program and at the same nesting level, DB2 closed the result set cursors
>
> and released storage for the first instance of the stored procedure before
> making the second call. In DB2 Version 8, if the requester and the server are
>
> both DB2 Version 8 subsystems in new-function mode, when the second call is
> made, both instances of the stored procedure can run at the same time. DB2
> does not close the result sets from the first call or release storage for the
> first
> instance of the stored procedure. If you make multiple calls to the same
> stored procedure in the same application, be aware of the following
> considerations:
> A DESCRIBE PROCEDURE statement describes the last instance of the stored
> procedure.
> The ASSOCIATE LOCATOR statement works on the last instance of the stored
> procedure. You should issue an ASSOCIATE LOCATOR statement after each
> call to the stored procedure to provide a unique locator value for each
> result
> set.
> The ALLOCATE CURSOR statement must specify a unique cursor name for the
> result set of each instance of the stored procedure. Otherwise, you will lose
>
> the data from the result sets that are returned from prior instances or calls
> to
> the stored procedure.
>
> Roger Miller, DB2 for z/OS
>
> On Mon, 17 Mar 2008 13:49:18 -0700, Myron Miller
> <[login to unmask email]> wrote:
>
> >I don't remember the APARs involved. It's been over a year now.
> >
> >The system was up and running in production when it failed. I wish it had
> been
> >conversion. But it was real user programs that had problems.
> >
> >One was some type of conversion error. We had a table created about
> version
> >2.1 that had the CCSID set to something or other and DB2 couldn't handle
> that.
> >IBM had never seen that problem before from a customer, just one time in
> their
> >simulations and it took a while to get a fix.
> >
> >Another that bit us required a special usermod to turn off the feature of
> >allowing multiple open cursors from calling a stored procedure multiple
> times.
> >Basically, we have programs that call a single stored procedure maybe
> 500,000
> >to several million times and never closed the result cursor. Prior to V8,
> the
> >result cursor was automatically closed by DB2 on each subsequent call to the
> >Stored procedure. V8 added the capability to call a SP multiple times and
> have
> >the result set open with each call. This caused us to use up the EDM pool
> >rather quickly storing pointers to all the open cursors. And of course,
> once
> >the EDM pool fills up, it doesn't take long for DB2 to come down after that.
> >
> >We have somewhat over 800+ programs that call SPs one or more times and
> never
> >found this in test. (Programmers obviously didn't test as thoroughly as we
> >hoped.) And this particular pecularity was not documented in any of the
> change
> >documentation for V8 or I would have caught it. At least it was not
> documented
> >in such a way that I recognized the issue of no longer closing cursors upon
> a
> >call.
> >
> >Myron
> >
> >Myron
> >--- [login to unmask email] wrote:
> >
> >> Thanks Myron.
> >>
> >> By "system data sets" I'm guessing you referring to the DB2 catalog and
> >> directory. May I ask, what APARs address the problems you had? Was
> the
> >> subsystem up and in use when the problem was discovered, or was the
> issue
> >> discovered during conversion?
> >>
> >> Has anyone else reverted to CM after ENFM or NFM?
> >>
> >> Steve
> >>
> >> Steve Runtsch | DBMS Technical Support | Assurant Inc.
> >> Office: 651 361 5378 | Mobile: 651 260 9263 | Toll Free: 866 866 4488 |
> >> Facsimile: 651 361 5625
> >> [login to unmask email]
> >>
> >>
> >>
> >>
> >> Myron Miller <[login to unmask email]>
> >> Sent by: DB2 Data Base Discussion List <[login to unmask email]>
> >> 03/15/2008 08:36 AM
> >> Please respond to
> >> DB2 Database Discussion list at IDUG <[login to unmask email]>
> >>
> >>
> >> To
> >> [login to unmask email]
> >> cc
> >>
> >> Subject
> >> Re: [DB2-L] Revert to DB2 z/OS V7 after Upgrade to DB2 z/OS V8 ENFM
> >>
> >>
> >>
> >>
> >>
> >>
> >> Actually, we did revert back from V8 NFM to V8 CM with a complete restore
> >> copy
> >> of all the system datasets.
> >>
> >> We hit a major brand new problem in production that stopped the entire
> >> system
> >> from running that we had to roll back from until we got fixes from IBM.
> >> Actually this roll back happened TWICE for two separate problems.
> >>
> >> Our conversion from CM to NFM was not a smooth conversion and definitely
> >> wasn't
> >> straightforward or simple like lots of people.
> >>
> >> Myron
> >> --- [login to unmask email] wrote:
> >>
> >> > Thanks for all of the responses. After rereading my post I noticed that
> >> I
> >> > had mistakenly asked about returning to V7. I had intended to ask about
> >>
> >> > returning to V8 CM. I don't think that would have made much difference
> >> in
> >> > most responses (other than Avram's) since reverting back anything after
> >> > upgrading to ENFM would involve, as Phil Sevetson remarked, "Roll the
> >> > whole thing back to the last system wide image copy taken before the
> >> ENFM
> >> > implementation."
> >> >
> >> > My main point was to determine if anyone became possessed, actually did
> >> > it, and why.
> >> >
> >> > Thank you again.
> >> > Steve
> >> >
> >> > Steve Runtsch | DBMS Technical Support | Assurant Inc.
> >> > Office: 651 361 5378 | Mobile: 651 260 9263 | Toll Free: 866 866 4488 |
> >> > Facsimile: 651 361 5625
> >> > [login to unmask email]
> >> >
> >> >
> >> >
> >> >
> >> > Avram Friedman <[login to unmask email]>
> >> > Sent by: DB2 Data Base Discussion List <[login to unmask email]>
> >> > 03/14/2008 08:59 AM
> >> > Please respond to
> >> > DB2 Database Discussion list at IDUG <[login to unmask email]>
> >> >
> >> >
> >> > To
> >> > [login to unmask email]
> >> > cc
> >> >
> >> > Subject
> >> > Re: [DB2-L] Revert to DB2 z/OS V7 after Upgrade to DB2 z/OS V8 ENFM
> >> >
> >> >
> >> >
> >> >
> >> >
> >> >
> >> > Would like to make several points.
> >> > 1. Fall back from V8 CM mode to V7 is not supported. What is supported
> >> > with
> >> > a catalog in V8 CM mode is tolerance of V7 executables.
> >> >
> >> > ....Regards
> >> > Avram Friedman
> >> >
> >> >
> >>
> >*********************************************************
> *****************************
> >> > This e-mail message and all attachments transmitted with it may contain
> >> > legally privileged and/or confidential information intended solely for
>
=== message truncated ===

______________________________________________________________________

* IDUG 08 Bangalore, India * 21-23 August 2008 * http://IDUG.ORG/lsIN *
______________________________________________________________________

The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Roger Miller

Re: Revert to DB2 z/OS V7 after Upgrade to DB2 z/OS V8 ENFM
(in response to Myron Miller)
The first part of the statement is exactly the point.
> In previous versions of DB2, if a stored procedure was called twice from the
> same program and at the same nesting level, DB2 closed the result set
> cursors and released storage for the first instance of the stored procedure
> before making the second call. In DB2 Version 8, if the requester and the
> server are both DB2 Version 8 subsystems in new-function mode, when the
> second call is made, both instances of the stored procedure can run at the
> same time. DB2 does not close the result sets from the first call or release
> storage for the first instance of the stored procedure.

Your situation was fairly unique, I suspect, as you changed the default of
2000 to about 50 times more, the maximum. That change allows the storage
to be held, rather than limiting the application. Closing the result set cursors
and commiting have been used in other situations.

Roger Miller

On Wed, 26 Mar 2008 17:20:21 -0700, Myron Miller
<[login to unmask email]> wrote:

>Roger,
> It's a bit more serious about the multiple calls than just the associate
>locator issue. Each call sets up an area in the EDM pool for the result set of
>about 6k, give or take. Now do the calls about 500,000 times and figure the
>resulting size of the EDM pool. It's slightly larger than 2g (by a large
>bunch). And when you run out of EDM pool, you obviously know where DB2 is
>going, DOWN!
>
>We have MAX_ST_PROCS to something like 99,999 or something like that.
But from
>everything I've been able to tell, that number really doesn't make any
>difference in this issue. It allows for the maximum number of different calls,
>but didn't help or make any difference on when the same exact SP was called
>repetitively. I tried a number of different values in testing this problem and
>we saw no difference in the problem. Sometimes we went down with
shortage of
>EDM pool prior to hitting the 99,999 number and sometimes it was when
200,000
>times the SP was called which is a much higher number than the theoretical
>limit. So in our testing, this limit did not seem to be a factor at all.
>
>As far as the incompatibilities issue, it was not in the books and was totally
>different from what the books specified.. I went thru carefully all the
>incompatibilities know prior to the install and ran all the check queries
>multiple times. This one was wierd because it was isolated to only a few
>tables out of a number that should have had the exact same issue.
>
> I spent considerable time on the phone with the support center and they
hadn't
>seen it before but were able to figure out the problem from the dumps and
>stuff. They were extremely helpful on the problem, helping significantly in
>working thru all the issues, but it did take some time to resolve. And
>Production was down for a considerable time because of it, so we had to roll
>back before it was finally resolved. The fix was actually two-phased, a
change
>to the CCSID value in the DB2 Catalog and a PTF being applied to prevent the
>issue from re-occuring.
>
>Myron
>
>
>--- Roger Miller <[login to unmask email]> wrote:
>
>> Sorry that one was missed. I checked the book and found this in the
>> incompatibilities section back as far as the -00 level. Is this a match?
>> The
>> SQL standard semantics are those of V8, while many customers depended
>> upon the earlier flavor, except for some Java work that only functions with
>> standard behavior. What were you setting for MAX_ST_PROC MAX
STORED
>> PROCS ===> Maximium active stored procs per thread?
>>
>> Multiple calls to the same stored procedure
>> In previous versions of DB2, if a stored procedure was called twice from the
>> same program and at the same nesting level, DB2 closed the result set
cursors
>>
>> and released storage for the first instance of the stored procedure before
>> making the second call. In DB2 Version 8, if the requester and the server
are
>>
>> both DB2 Version 8 subsystems in new-function mode, when the second
call is
>> made, both instances of the stored procedure can run at the same time.
DB2
>> does not close the result sets from the first call or release storage for the
>> first
>> instance of the stored procedure. If you make multiple calls to the same
>> stored procedure in the same application, be aware of the following
>> considerations:
>> A DESCRIBE PROCEDURE statement describes the last instance of the
stored
>> procedure.
>> The ASSOCIATE LOCATOR statement works on the last instance of the
stored
>> procedure. You should issue an ASSOCIATE LOCATOR statement after each
>> call to the stored procedure to provide a unique locator value for each
>> result
>> set.
>> The ALLOCATE CURSOR statement must specify a unique cursor name for
the
>> result set of each instance of the stored procedure. Otherwise, you will
lose
>>
>> the data from the result sets that are returned from prior instances or calls
>> to
>> the stored procedure.
>>
>> Roger Miller, DB2 for z/OS
>>
>> On Mon, 17 Mar 2008 13:49:18 -0700, Myron Miller
>> <[login to unmask email]> wrote:
>>
>> >I don't remember the APARs involved. It's been over a year now.
>> >
>> >The system was up and running in production when it failed. I wish it had
>> been
>> >conversion. But it was real user programs that had problems.
>> >
>> >One was some type of conversion error. We had a table created about
>> version
>> >2.1 that had the CCSID set to something or other and DB2 couldn't handle
>> that.
>> >IBM had never seen that problem before from a customer, just one time in
>> their
>> >simulations and it took a while to get a fix.
>> >
>> >Another that bit us required a special usermod to turn off the feature of
>> >allowing multiple open cursors from calling a stored procedure multiple
>> times.
>> >Basically, we have programs that call a single stored procedure maybe
>> 500,000
>> >to several million times and never closed the result cursor. Prior to V8,
>> the
>> >result cursor was automatically closed by DB2 on each subsequent call to
the
>> >Stored procedure. V8 added the capability to call a SP multiple times and
>> have
>> >the result set open with each call. This caused us to use up the EDM pool
>> >rather quickly storing pointers to all the open cursors. And of course,
>> once
>> >the EDM pool fills up, it doesn't take long for DB2 to come down after
that.
>> >
>> >We have somewhat over 800+ programs that call SPs one or more times
and
>> never
>> >found this in test. (Programmers obviously didn't test as thoroughly as we
>> >hoped.) And this particular pecularity was not documented in any of the
>> change
>> >documentation for V8 or I would have caught it. At least it was not
>> documented
>> >in such a way that I recognized the issue of no longer closing cursors
upon
>> a
>> >call.
>> >
>> >Myron
>> >
>> >Myron
>> >--- [login to unmask email] wrote:
>> >
>> >> Thanks Myron.
>> >>
>> >> By "system data sets" I'm guessing you referring to the DB2 catalog and
>> >> directory. May I ask, what APARs address the problems you had? Was
>> the
>> >> subsystem up and in use when the problem was discovered, or was the
>> issue
>> >> discovered during conversion?
>> >>
>> >> Has anyone else reverted to CM after ENFM or NFM?
>> >>
>> >> Steve
>> >>
>> >> Steve Runtsch | DBMS Technical Support | Assurant Inc.
>> >> Office: 651 361 5378 | Mobile: 651 260 9263 | Toll Free: 866 866 4488
|
>> >> Facsimile: 651 361 5625
>> >> [login to unmask email]
>> >>
>> >>
>> >>
>> >>
>> >> Myron Miller <[login to unmask email]>
>> >> Sent by: DB2 Data Base Discussion List <[login to unmask email]>
>> >> 03/15/2008 08:36 AM
>> >> Please respond to
>> >> DB2 Database Discussion list at IDUG <[login to unmask email]>
>> >>
>> >>
>> >> To
>> >> [login to unmask email]
>> >> cc
>> >>
>> >> Subject
>> >> Re: [DB2-L] Revert to DB2 z/OS V7 after Upgrade to DB2 z/OS V8 ENFM
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >> Actually, we did revert back from V8 NFM to V8 CM with a complete
restore
>> >> copy
>> >> of all the system datasets.
>> >>
>> >> We hit a major brand new problem in production that stopped the entire
>> >> system
>> >> from running that we had to roll back from until we got fixes from IBM.
>> >> Actually this roll back happened TWICE for two separate problems.
>> >>
>> >> Our conversion from CM to NFM was not a smooth conversion and
definitely
>> >> wasn't
>> >> straightforward or simple like lots of people.
>> >>
>> >> Myron
>> >> --- [login to unmask email] wrote:
>> >>
>> >> > Thanks for all of the responses. After rereading my post I noticed
that
>> >> I
>> >> > had mistakenly asked about returning to V7. I had intended to ask
about
>> >>
>> >> > returning to V8 CM. I don't think that would have made much
difference
>> >> in
>> >> > most responses (other than Avram's) since reverting back anything
after
>> >> > upgrading to ENFM would involve, as Phil Sevetson remarked, "Roll the
>> >> > whole thing back to the last system wide image copy taken before the
>> >> ENFM
>> >> > implementation."
>> >> >
>> >> > My main point was to determine if anyone became possessed,
actually did
>> >> > it, and why.
>> >> >
>> >> > Thank you again.
>> >> > Steve
>> >> >
>> >> > Steve Runtsch | DBMS Technical Support | Assurant Inc.
>> >> > Office: 651 361 5378 | Mobile: 651 260 9263 | Toll Free: 866 866
4488 |
>> >> > Facsimile: 651 361 5625
>> >> > [login to unmask email]
>> >> >
>> >> >
>> >> >
>> >> >
>> >> > Avram Friedman <[login to unmask email]>
>> >> > Sent by: DB2 Data Base Discussion List <[login to unmask email]>
>> >> > 03/14/2008 08:59 AM
>> >> > Please respond to
>> >> > DB2 Database Discussion list at IDUG <[login to unmask email]>
>> >> >
>> >> >
>> >> > To
>> >> > [login to unmask email]
>> >> > cc
>> >> >
>> >> > Subject
>> >> > Re: [DB2-L] Revert to DB2 z/OS V7 after Upgrade to DB2 z/OS V8
ENFM
>> >> >
>> >> >
>> >> >
>> >> >
>> >> >
>> >> >
>> >> > Would like to make several points.
>> >> > 1. Fall back from V8 CM mode to V7 is not supported. What is
supported
>> >> > with
>> >> > a catalog in V8 CM mode is tolerance of V7 executables.
>> >> >
>> >> > ....Regards
>> >> > Avram Friedman
>> >> >
>> >> >
>> >>
>>
>*********************************************************
>> *****************************
>> >> > This e-mail message and all attachments transmitted with it may
contain
>> >> > legally privileged and/or confidential information intended solely for
>>
>=== message truncated ===
>
>_________________________________________________________________
_____
>
>* IDUG 08 Bangalore, India * 21-23 August 2008 * http://IDUG.ORG/lsIN *
>_________________________________________________________________
_____
>
>The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L
list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug
under the Listserv tab. While at the site, you can also access the IDUG
Online Learning Center, Tech Library and Code Place, see the latest IDUG
conference information and much more. If you have not yet signed up for
Basic Membership in IDUG, available at no cost, click on Member Services at
http://www.idug.org/lsms

______________________________________________________________________

* IDUG 08 Bangalore, India * 21-23 August 2008 * http://IDUG.ORG/lsIN *
______________________________________________________________________

The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Myron Miller

Re: Revert to DB2 z/OS V7 after Upgrade to DB2 z/OS V8 ENFM
(in response to Roger Miller)
No question that the programmers should have been closing the result set cursor
before the next call. No commit was needed nor should be taken that often,
think of committing after every single read, over 500,000 of them, that seems
excessive and unnecessary. Most programmers never commit on READ-only type
processing and I can't say that I blame them.

But, yes, not closing the result set was an issue. And the code will be
changed to do this. Some has been already, but other programs have not. It
was almost a 1000 different programs involved, and with the other critical
projects going on, this wasn't the priority that maybe it should be.

But, to be honest, it would have been nice if somewhere in one of the
transition documents, this real effect was documented. I'll agree that the
change from one result set to multiple allowances was documented. But it
really wasn't clear the impact on the EDMPOOL of keeping multiple results open
nor what would happen to existing applications that did not close the cursors
properly. I know, I searched and searched after the fact for this and couldn't
find it in the documentation. It might be there, but it was not that clearly
stated.

This behaviour is a lot similar to the old days where in COBOL if you left a
file open when you terminated the program you received a S0C4. After a while
IBM changed the compiler such that it automatically closed the files upon
close. Many of our programmers came from that environment and thought honestly
that everything was closed when the SP was called a second time. Deluded, yes.
Wrong for V8, NO QUESTION. Problem, you betcha.

Myron
--- Roger Miller <[login to unmask email]> wrote:

> The first part of the statement is exactly the point.
> > In previous versions of DB2, if a stored procedure was called twice from
> the
> > same program and at the same nesting level, DB2 closed the result set
>
> > cursors and released storage for the first instance of the stored procedure
>
> > before making the second call. In DB2 Version 8, if the requester and the
>
> > server are both DB2 Version 8 subsystems in new-function mode, when the
> > second call is made, both instances of the stored procedure can run at the
>
> > same time. DB2 does not close the result sets from the first call or
> release
> > storage for the first instance of the stored procedure.
>
> Your situation was fairly unique, I suspect, as you changed the default of
> 2000 to about 50 times more, the maximum. That change allows the storage
> to be held, rather than limiting the application. Closing the result set
> cursors
> and commiting have been used in other situations.
>
> Roger Miller
>
> On Wed, 26 Mar 2008 17:20:21 -0700, Myron Miller
> <[login to unmask email]> wrote:
>
> >Roger,
> > It's a bit more serious about the multiple calls than just the associate
> >locator issue. Each call sets up an area in the EDM pool for the result set
> of
> >about 6k, give or take. Now do the calls about 500,000 times and figure the
> >resulting size of the EDM pool. It's slightly larger than 2g (by a large
> >bunch). And when you run out of EDM pool, you obviously know where DB2 is
> >going, DOWN!
> >
> >We have MAX_ST_PROCS to something like 99,999 or something like that.
> But from
> >everything I've been able to tell, that number really doesn't make any
> >difference in this issue. It allows for the maximum number of different
> calls,
> >but didn't help or make any difference on when the same exact SP was called
> >repetitively. I tried a number of different values in testing this problem
> and
> >we saw no difference in the problem. Sometimes we went down with
> shortage of
> >EDM pool prior to hitting the 99,999 number and sometimes it was when
> 200,000
> >times the SP was called which is a much higher number than the theoretical
> >limit. So in our testing, this limit did not seem to be a factor at all.
> >
> >As far as the incompatibilities issue, it was not in the books and was
> totally
> >different from what the books specified.. I went thru carefully all the
> >incompatibilities know prior to the install and ran all the check queries
> >multiple times. This one was wierd because it was isolated to only a few
> >tables out of a number that should have had the exact same issue.
> >
> > I spent considerable time on the phone with the support center and they
> hadn't
> >seen it before but were able to figure out the problem from the dumps and
> >stuff. They were extremely helpful on the problem, helping significantly in
> >working thru all the issues, but it did take some time to resolve. And
> >Production was down for a considerable time because of it, so we had to roll
> >back before it was finally resolved. The fix was actually two-phased, a
> change
> >to the CCSID value in the DB2 Catalog and a PTF being applied to prevent the
> >issue from re-occuring.
> >
> >Myron
> >
> >
> >--- Roger Miller <[login to unmask email]> wrote:
> >
> >> Sorry that one was missed. I checked the book and found this in the
> >> incompatibilities section back as far as the -00 level. Is this a match?
> >> The
> >> SQL standard semantics are those of V8, while many customers depended
> >> upon the earlier flavor, except for some Java work that only functions
> with
> >> standard behavior. What were you setting for MAX_ST_PROC MAX
> STORED
> >> PROCS ===> Maximium active stored procs per thread?
> >>
> >> Multiple calls to the same stored procedure
> >> In previous versions of DB2, if a stored procedure was called twice from
> the
> >> same program and at the same nesting level, DB2 closed the result set
> cursors
> >>
> >> and released storage for the first instance of the stored procedure before
> >> making the second call. In DB2 Version 8, if the requester and the server
> are
> >>
> >> both DB2 Version 8 subsystems in new-function mode, when the second
> call is
> >> made, both instances of the stored procedure can run at the same time.
> DB2
> >> does not close the result sets from the first call or release storage for
> the
> >> first
> >> instance of the stored procedure. If you make multiple calls to the same
> >> stored procedure in the same application, be aware of the following
> >> considerations:
> >> A DESCRIBE PROCEDURE statement describes the last instance of the
> stored
> >> procedure.
> >> The ASSOCIATE LOCATOR statement works on the last instance of the
> stored
> >> procedure. You should issue an ASSOCIATE LOCATOR statement after each
> >> call to the stored procedure to provide a unique locator value for each
> >> result
> >> set.
> >> The ALLOCATE CURSOR statement must specify a unique cursor name for
> the
> >> result set of each instance of the stored procedure. Otherwise, you will
> lose
> >>
> >> the data from the result sets that are returned from prior instances or
> calls
> >> to
> >> the stored procedure.
> >>
> >> Roger Miller, DB2 for z/OS
> >>
> >> On Mon, 17 Mar 2008 13:49:18 -0700, Myron Miller
> >> <[login to unmask email]> wrote:
> >>
> >> >I don't remember the APARs involved. It's been over a year now.
> >> >
> >> >The system was up and running in production when it failed. I wish it
> had
> >> been
> >> >conversion. But it was real user programs that had problems.
> >> >
> >> >One was some type of conversion error. We had a table created about
> >> version
> >> >2.1 that had the CCSID set to something or other and DB2 couldn't handle
> >> that.
> >> >IBM had never seen that problem before from a customer, just one time in
> >> their
> >> >simulations and it took a while to get a fix.
> >> >
> >> >Another that bit us required a special usermod to turn off the feature of
> >> >allowing multiple open cursors from calling a stored procedure multiple
> >> times.
> >> >Basically, we have programs that call a single stored procedure maybe
> >> 500,000
> >> >to several million times and never closed the result cursor. Prior to
> V8,
> >> the
> >> >result cursor was automatically closed by DB2 on each subsequent call to
> the
> >> >Stored procedure. V8 added the capability to call a SP multiple times
> and
> >> have
> >> >the result set open with each call. This caused us to use up the EDM
> pool
> >> >rather quickly storing pointers to all the open cursors. And of course,
> >> once
> >> >the EDM pool fills up, it doesn't take long for DB2 to come down after
> that.
> >> >
> >> >We have somewhat over 800+ programs that call SPs one or more times
> and
> >> never
> >> >found this in test. (Programmers obviously didn't test as thoroughly as
> we
> >> >hoped.) And this particular pecularity was not documented in any of the
> >> change
> >> >documentation for V8 or I would have caught it. At least it was not
> >> documented
> >> >in such a way that I recognized the issue of no longer closing cursors
> upon
> >> a
> >> >call.
> >> >
> >> >Myron
> >> >
> >> >Myron
> >> >--- [login to unmask email] wrote:
> >> >
> >> >> Thanks Myron.
> >> >>
> >> >> By "system data sets" I'm guessing you referring to the DB2 catalog and
> >> >> directory. May I ask, what APARs address the problems you had? Was
> >> the
> >> >> subsystem up and in use when the problem was discovered, or was the
> >> issue
> >> >> discovered during conversion?
> >> >>
> >> >> Has anyone else reverted to CM after ENFM or NFM?
> >> >>
> >> >> Steve
> >> >>
> >> >> Steve Runtsch | DBMS Technical Support | Assurant Inc.
> >> >> Office: 651 361 5378 | Mobile: 651 260 9263 | Toll Free: 866 866 4488
> |
> >> >> Facsimile: 651 361 5625
> >> >> [login to unmask email]
> >> >>
>
=== message truncated ===

______________________________________________________________________

* IDUG 08 Bangalore, India * 21-23 August 2008 * http://IDUG.ORG/lsIN *
______________________________________________________________________

The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Ted MacNEIL

Re: Revert to DB2 z/OS V7 after Upgrade to DB2 z/OS V8 ENFM
(in response to Myron Miller)
>This behaviour is a lot similar to the old days where in COBOL if you left a file open when you terminated the program you received a S0C4. After a while
IBM changed the compiler such that it automatically closed the files upon close.

Not always.
Now, you can get an SC03 if you leave a file open in a DB2/COBOL Batch environment.
Especially, if it's SYSOUT.
But, it's because you called an assembler programme in a non-LE mode.

-
Too busy driving to stop for gas!

______________________________________________________________________

* IDUG 08 Bangalore, India * 21-23 August 2008 * http://IDUG.ORG/lsIN *
______________________________________________________________________

The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Richard Humphris

Re: Revert to DB2 z/OS V7 after Upgrade to DB2 z/OS V8 ENFM
(in response to Ted MacNEIL)
Hi Roger,

The parm MAX_ST_PROC says it's the max for the thread. Is it the max
for the thread or the max for a uow?

We have triggers that call stored procedures. And when the table is
originally loaded, the triggers might get called a few million times;
but if commits controls the usage count an application program can
control the commit count. But won't this become a problem if this table
is ever re-loaded via the load utility as well?

Thanks,
Richard Humphris

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Roger Miller
Sent: Wednesday, March 26, 2008 9:39 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Revert to DB2 z/OS V7 after Upgrade to DB2 z/OS V8
ENFM

The first part of the statement is exactly the point.
> In previous versions of DB2, if a stored procedure was called twice
from the
> same program and at the same nesting level, DB2 closed the result set

> cursors and released storage for the first instance of the stored
procedure
> before making the second call. In DB2 Version 8, if the requester and
the
> server are both DB2 Version 8 subsystems in new-function mode, when
the
> second call is made, both instances of the stored procedure can run at
the
> same time. DB2 does not close the result sets from the first call or
release
> storage for the first instance of the stored procedure.

Your situation was fairly unique, I suspect, as you changed the default
of
2000 to about 50 times more, the maximum. That change allows the
storage
to be held, rather than limiting the application. Closing the result
set cursors
and commiting have been used in other situations.

Roger Miller

On Wed, 26 Mar 2008 17:20:21 -0700, Myron Miller
<[login to unmask email]> wrote:

>Roger,
> It's a bit more serious about the multiple calls than just the
associate
>locator issue. Each call sets up an area in the EDM pool for the
result set of
>about 6k, give or take. Now do the calls about 500,000 times and
figure the
>resulting size of the EDM pool. It's slightly larger than 2g (by a
large
>bunch). And when you run out of EDM pool, you obviously know where DB2
is
>going, DOWN!
>
>We have MAX_ST_PROCS to something like 99,999 or something like that.
But from
>everything I've been able to tell, that number really doesn't make any
>difference in this issue. It allows for the maximum number of
different calls,
>but didn't help or make any difference on when the same exact SP was
called
>repetitively. I tried a number of different values in testing this
problem and
>we saw no difference in the problem. Sometimes we went down with
shortage of
>EDM pool prior to hitting the 99,999 number and sometimes it was when
200,000
>times the SP was called which is a much higher number than the
theoretical
>limit. So in our testing, this limit did not seem to be a factor at
all.
>
>As far as the incompatibilities issue, it was not in the books and was
totally
>different from what the books specified.. I went thru carefully all
the
>incompatibilities know prior to the install and ran all the check
queries
>multiple times. This one was wierd because it was isolated to only a
few
>tables out of a number that should have had the exact same issue.
>
> I spent considerable time on the phone with the support center and
they
hadn't
>seen it before but were able to figure out the problem from the dumps
and
>stuff. They were extremely helpful on the problem, helping
significantly in
>working thru all the issues, but it did take some time to resolve. And
>Production was down for a considerable time because of it, so we had to
roll
>back before it was finally resolved. The fix was actually two-phased,
a
change
>to the CCSID value in the DB2 Catalog and a PTF being applied to
prevent the
>issue from re-occuring.
>
>Myron
>
>
>--- Roger Miller <[login to unmask email]> wrote:
>
>> Sorry that one was missed. I checked the book and found this in the
>> incompatibilities section back as far as the -00 level. Is this a
match?
>> The
>> SQL standard semantics are those of V8, while many customers depended
>> upon the earlier flavor, except for some Java work that only
functions with
>> standard behavior. What were you setting for MAX_ST_PROC MAX
STORED
>> PROCS ===> Maximium active stored procs per thread?
>>
>> Multiple calls to the same stored procedure
>> In previous versions of DB2, if a stored procedure was called twice
from the
>> same program and at the same nesting level, DB2 closed the result set

cursors
>>
>> and released storage for the first instance of the stored procedure
before
>> making the second call. In DB2 Version 8, if the requester and the
server
are
>>
>> both DB2 Version 8 subsystems in new-function mode, when the second
call is
>> made, both instances of the stored procedure can run at the same
time.
DB2
>> does not close the result sets from the first call or release storage
for the
>> first
>> instance of the stored procedure. If you make multiple calls to the
same
>> stored procedure in the same application, be aware of the following
>> considerations:
>> A DESCRIBE PROCEDURE statement describes the last instance of the
stored
>> procedure.
>> The ASSOCIATE LOCATOR statement works on the last instance of the
stored
>> procedure. You should issue an ASSOCIATE LOCATOR statement after each
>> call to the stored procedure to provide a unique locator value for
each
>> result
>> set.
>> The ALLOCATE CURSOR statement must specify a unique cursor name for
the
>> result set of each instance of the stored procedure. Otherwise, you
will
lose
>>
>> the data from the result sets that are returned from prior instances
or calls
>> to
>> the stored procedure.
>>
>> Roger Miller, DB2 for z/OS
>>
>> On Mon, 17 Mar 2008 13:49:18 -0700, Myron Miller
>> <[login to unmask email]> wrote:
>>
>> >I don't remember the APARs involved. It's been over a year now.
>> >
>> >The system was up and running in production when it failed. I wish
it had
>> been
>> >conversion. But it was real user programs that had problems.
>> >
>> >One was some type of conversion error. We had a table created about
>> version
>> >2.1 that had the CCSID set to something or other and DB2 couldn't
handle
>> that.
>> >IBM had never seen that problem before from a customer, just one
time in
>> their
>> >simulations and it took a while to get a fix.
>> >
>> >Another that bit us required a special usermod to turn off the
feature of
>> >allowing multiple open cursors from calling a stored procedure
multiple
>> times.
>> >Basically, we have programs that call a single stored procedure
maybe
>> 500,000
>> >to several million times and never closed the result cursor. Prior
to V8,
>> the
>> >result cursor was automatically closed by DB2 on each subsequent
call to
the
>> >Stored procedure. V8 added the capability to call a SP multiple
times and
>> have
>> >the result set open with each call. This caused us to use up the
EDM pool
>> >rather quickly storing pointers to all the open cursors. And of
course,
>> once
>> >the EDM pool fills up, it doesn't take long for DB2 to come down
after
that.
>> >
>> >We have somewhat over 800+ programs that call SPs one or more times
and
>> never
>> >found this in test. (Programmers obviously didn't test as
thoroughly as we
>> >hoped.) And this particular pecularity was not documented in any of
the
>> change
>> >documentation for V8 or I would have caught it. At least it was not
>> documented
>> >in such a way that I recognized the issue of no longer closing
cursors
upon
>> a
>> >call.
>> >
>> >Myron
>> >
>> >Myron
>> >--- [login to unmask email] wrote:
>> >
>> >> Thanks Myron.
>> >>
>> >> By "system data sets" I'm guessing you referring to the DB2
catalog and
>> >> directory. May I ask, what APARs address the problems you had?
Was
>> the
>> >> subsystem up and in use when the problem was discovered, or was
the
>> issue
>> >> discovered during conversion?
>> >>
>> >> Has anyone else reverted to CM after ENFM or NFM?
>> >>
>> >> Steve
>> >>
>> >> Steve Runtsch | DBMS Technical Support | Assurant Inc.
>> >> Office: 651 361 5378 | Mobile: 651 260 9263 | Toll Free: 866 866
4488
|
>> >> Facsimile: 651 361 5625
>> >> [login to unmask email]
>> >>
>> >>
>> >>
>> >>
>> >> Myron Miller <[login to unmask email]>
>> >> Sent by: DB2 Data Base Discussion List <[login to unmask email]>
>> >> 03/15/2008 08:36 AM
>> >> Please respond to
>> >> DB2 Database Discussion list at IDUG <[login to unmask email]>
>> >>
>> >>
>> >> To
>> >> [login to unmask email]
>> >> cc
>> >>
>> >> Subject
>> >> Re: [DB2-L] Revert to DB2 z/OS V7 after Upgrade to DB2 z/OS V8
ENFM
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >> Actually, we did revert back from V8 NFM to V8 CM with a complete
restore
>> >> copy
>> >> of all the system datasets.
>> >>
>> >> We hit a major brand new problem in production that stopped the
entire
>> >> system
>> >> from running that we had to roll back from until we got fixes from
IBM.
>> >> Actually this roll back happened TWICE for two separate problems.
>> >>
>> >> Our conversion from CM to NFM was not a smooth conversion and
definitely
>> >> wasn't
>> >> straightforward or simple like lots of people.
>> >>
>> >> Myron
>> >> --- [login to unmask email] wrote:
>> >>
>> >> > Thanks for all of the responses. After rereading my post I
noticed
that
>> >> I
>> >> > had mistakenly asked about returning to V7. I had intended to
ask
about
>> >>
>> >> > returning to V8 CM. I don't think that would have made much
difference
>> >> in
>> >> > most responses (other than Avram's) since reverting back
anything
after
>> >> > upgrading to ENFM would involve, as Phil Sevetson remarked,
"Roll the
>> >> > whole thing back to the last system wide image copy taken before
the
>> >> ENFM
>> >> > implementation."
>> >> >
>> >> > My main point was to determine if anyone became possessed,
actually did
>> >> > it, and why.
>> >> >
>> >> > Thank you again.
>> >> > Steve
>> >> >
>> >> > Steve Runtsch | DBMS Technical Support | Assurant Inc.
>> >> > Office: 651 361 5378 | Mobile: 651 260 9263 | Toll Free: 866 866

4488 |
>> >> > Facsimile: 651 361 5625
>> >> > [login to unmask email]
>> >> >
>> >> >
>> >> >
>> >> >
>> >> > Avram Friedman <[login to unmask email]>
>> >> > Sent by: DB2 Data Base Discussion List <[login to unmask email]>
>> >> > 03/14/2008 08:59 AM
>> >> > Please respond to
>> >> > DB2 Database Discussion list at IDUG <[login to unmask email]>
>> >> >
>> >> >
>> >> > To
>> >> > [login to unmask email]
>> >> > cc
>> >> >
>> >> > Subject
>> >> > Re: [DB2-L] Revert to DB2 z/OS V7 after Upgrade to DB2 z/OS V8
ENFM
>> >> >
>> >> >
>> >> >
>> >> >
>> >> >
>> >> >
>> >> > Would like to make several points.
>> >> > 1. Fall back from V8 CM mode to V7 is not supported. What is
supported
>> >> > with
>> >> > a catalog in V8 CM mode is tolerance of V7 executables.
>> >> >
>> >> > ....Regards
>> >> > Avram Friedman
>> >> >
>> >> >
>> >>
>>
>*********************************************************
>> *****************************
>> >> > This e-mail message and all attachments transmitted with it may
contain
>> >> > legally privileged and/or confidential information intended
solely for
>>
>=== message truncated ===
>
>_________________________________________________________________
_____
>
>* IDUG 08 Bangalore, India * 21-23 August 2008 * http://IDUG.ORG/lsIN *
>_________________________________________________________________
_____
>
>The IDUG DB2-L Listserv is only part of your membership in IDUG. The
DB2-L
list archives, FAQ, and delivery preferences are at
http://www.idug.org/lsidug
under the Listserv tab. While at the site, you can also access the IDUG

Online Learning Center, Tech Library and Code Place, see the latest IDUG

conference information and much more. If you have not yet signed up for

Basic Membership in IDUG, available at no cost, click on Member Services
at
http://www.idug.org/lsms

______________________________________________________________________

* IDUG 08 Bangalore, India * 21-23 August 2008 * http://IDUG.ORG/lsIN *
______________________________________________________________________

The IDUG DB2-L Listserv is only part of your membership in IDUG. The
DB2-L list archives, FAQ, and delivery preferences are at
http://www.idug.org/lsidug under the Listserv tab. While at the site,
you can also access the IDUG Online Learning Center, Tech Library and
Code Place, see the latest IDUG conference information and much more.
If you have not yet signed up for Basic Membership in IDUG, available at
no cost, click on Member Services at http://www.idug.org/lsms

E-MAIL CONFIDENTIALITY NOTICE: The contents of this e-mail message and any attachments are intended solely for the
addressee(s) and may contain confidential and/or legally privileged information. If you are not the
intended recipient of this message or if this message has been addressed to you in error, please
immediately alert the sender by reply e-mail and then delete this message and any attachments. If you
are not the intended recipient, you are notified that any use, dissemination, distribution, copying, or
storage of this message or any attachment is strictly prohibited.

_____________________________________________________________________

* IDUG NA08 On Site registration opens May 17th * http://IDUG.ORG/lsNA *
_____________________________________________________________________

The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Ted MacNEIL

Re: Revert to DB2 z/OS V7 after Upgrade to DB2 z/OS V8 ENFM
(in response to Richard Humphris)
>The parm MAX_ST_PROC says it's the max for the thread. Is it the max for the thread or the max for a uow?

I don't understand the question.
It says it's the max for the thread.
Therefore, it's the max for ... [answer left as an exercise for the student] ...

-
Too busy driving to stop for gas!

_____________________________________________________________________

* IDUG NA08 On Site registration opens May 17th * http://IDUG.ORG/lsNA *
_____________________________________________________________________

The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Mike Vaughan

Re: Revert to DB2 z/OS V7 after Upgrade to DB2 z/OS V8 ENFM
(in response to Ted MacNEIL)
But I think there's more to the story here when you dig into what is included in this count... when this originally was introduced in v8 the redbook (the "everything and more" one) mentioned that this was limiting "active" stored procedures per thread, so one question to come out of that is what is considered as "active". From some reading between the lines (this may have even been explicitly mentioned somewhere, but I can't find a reference to it at the moment so we'll just assume I'm interpreting here) I believe that unless a stored procedure has some reason to stick around for awhile (result set cursors left open, for example) then it is not counted against this "active" limit. I'll also note that a quick search of IBMLink shows some maintenance that came out regarding this limit since it was initially introduced that reduced the instances where it was painful. Some of that maintenance - PK08328 for example - does make reference to a commit, which would seem to imply UOW scope, but I have a hunch that a resultset cursor that was opened "with hold" would invalidate that, which would in essence mean this is a thread-limit (but not neccessarily just counting calls).

________________________________

From: DB2 Data Base Discussion List on behalf of Ted MacNEIL
Sent: Wed 5/21/2008 5:31 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Revert to DB2 z/OS V7 after Upgrade to DB2 z/OS V8 ENFM



>The parm MAX_ST_PROC says it's the max for the thread. Is it the max for the thread or the max for a uow?

I don't understand the question.
It says it's the max for the thread.
Therefore, it's the max for ... [answer left as an exercise for the student] ...

-
Too busy driving to stop for gas!

_____________________________________________________________________

* IDUG NA08 On Site registration opens May 17th * http://IDUG.ORG/lsNA *
_____________________________________________________________________

The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms




-----Message Disclaimer-----

This e-mail message is intended only for the use of the individual or
entity to which it is addressed, and may contain information that is
privileged, confidential and exempt from disclosure under applicable law.
If you are not the intended recipient, any dissemination, distribution or
copying of this communication is strictly prohibited. If you have
received this communication in error, please notify us immediately by
reply email to [login to unmask email] and delete or destroy all copies of
the original message and attachments thereto. Email sent to or from the
Principal Financial Group or any of its member companies may be retained
as required by law or regulation.

Nothing in this message is intended to constitute an Electronic signature
for purposes of the Uniform Electronic Transactions Act (UETA) or the
Electronic Signatures in Global and National Commerce Act ("E-Sign")
unless a specific statement to the contrary is included in this message.

While this communication may be used to promote or market a transaction
or an idea that is discussed in the publication, it is intended to provide
general information about the subject matter covered and is provided with
the understanding that The Principal is not rendering legal, accounting,
or tax advice. It is not a marketed opinion and may not be used to avoid
penalties under the Internal Revenue Code. You should consult with
appropriate counsel or other advisors on all matters pertaining to legal,
tax, or accounting obligations and requirements.

_____________________________________________________________________

* IDUG NA08 On Site registration opens May 17th * http://IDUG.ORG/lsNA *
_____________________________________________________________________

The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Richard Humphris

Re: Revert to DB2 z/OS V7 after Upgrade to DB2 z/OS V8 ENFM
(in response to Mike Vaughan)
I agree, that's what the manual says; but based on discussions here the
manual appears to be wrong:

--- Roger Miller implied that the limit wasn't a thread limit.
--- And another IBM customer (maybe in this thread or so other thread)
implied he did a few hundred thousand calls in a single thread which is
obviously above 99,999.
--- So I'm trying to find out what the actual restriction is.

And I'm still interested in how this would effect (or not effect) using
the load or reorg utility with a trigger that calls stored procedures.

Btw, since a 99k restriction is over 65k, it sounds like this value is
stored in a fullword anyway. So probably the 99k restriction is only
enforced by the macro expansion (and probably the real (internal) max is
2g-1 and/or 4g-1).

Of course if you don't close result sets, DB2 will probably crash before
you hit the 99k restriction anyway.

Richard Humphris


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Ted MacNEIL
Sent: Wednesday, May 21, 2008 5:32 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Revert to DB2 z/OS V7 after Upgrade to DB2 z/OS V8
ENFM

>The parm MAX_ST_PROC says it's the max for the thread. Is it the max
for the thread or the max for a uow?

I don't understand the question.
It says it's the max for the thread.
Therefore, it's the max for ... [answer left as an exercise for the
student] ...

-
Too busy driving to stop for gas!

_____________________________________________________________________

* IDUG NA08 On Site registration opens May 17th * http://IDUG.ORG/lsNA
*
_____________________________________________________________________

The IDUG DB2-L Listserv is only part of your membership in IDUG. The
DB2-L list archives, FAQ, and delivery preferences are at
http://www.idug.org/lsidug under the Listserv tab. While at the site,
you can also access the IDUG Online Learning Center, Tech Library and
Code Place, see the latest IDUG conference information and much more.
If you have not yet signed up for Basic Membership in IDUG, available at
no cost, click on Member Services at http://www.idug.org/lsms

E-MAIL CONFIDENTIALITY NOTICE: The contents of this e-mail message and any attachments are intended solely for the
addressee(s) and may contain confidential and/or legally privileged information. If you are not the
intended recipient of this message or if this message has been addressed to you in error, please
immediately alert the sender by reply e-mail and then delete this message and any attachments. If you
are not the intended recipient, you are notified that any use, dissemination, distribution, copying, or
storage of this message or any attachment is strictly prohibited.

_____________________________________________________________________

* IDUG NA08 On Site registration opens May 17th * http://IDUG.ORG/lsNA *
_____________________________________________________________________

The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms