Is there anything Written on the use and/or misuse of Collectin-ids ?

Daniel Lapp

Is there anything Written on the use and/or misuse of Collectin-ids ?
Is there anything Written on the use and/or misuse of Collectin-ids ? I can
understand using collection ids to represent a collection of packages in a
line of business but what if one was to over use collection ids to represent a
much finer granularity ? For instance, the same package bound a hundred
different time each with a different collection id, and each collection id
utilizing different load and DBRM libs ? This is the sitaution we appear to
have and I believe it contributes to the large size of our EDMPOOL, the large
size of bufferpool BP0, the umanageable size of our SPT01, and elongated
bind times because the wildcarding that is necessary in the PKLIST of the
bind statements. It is very difficult to do any type of cleanup.

Does anyone have any experience similiar to this and can suggest a better
way of doing things ?

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Robert Catterall

Re: Is there anything Written on the use and/or misuse of Collectin-ids ?
(in response to Daniel Lapp)
Regarding stuff written on package management, you might want to check out
(if you haven't already) the IBM red book titled "DB2 9 for z/OS: Packages
Revisited." You can download a copy in PDF format at this url:

http://www.redbooks.ibm.com/abstracts/sg247688.html?Open

Yeah, binding a given package into a whole lot of collections versus very
few collections would tend to increase the amount of virtual storage you'd
need for your EDM pool, particularly the part that holds the skeleton
package table. And, it would mean a larger SPT01 directory tablespace.

What about your DB2 plans? Do you have lots of plans to go with the large
number of collections, with each plan having a PKLIST that points to only 1
or 2 of the many collections, or do you have a few plans, each with a lot of
collections in the PKLIST?

Robert


On Mon, Dec 14, 2009 at 9:31 PM, Daniel Lapp <[login to unmask email]> wrote:

> Is there anything Written on the use and/or misuse of Collectin-ids ? I
> can
> understand using collection ids to represent a collection of packages in a
> line of business but what if one was to over use collection ids to
> represent a
> much finer granularity ? For instance, the same package bound a hundred
> different time each with a different collection id, and each collection id
> utilizing different load and DBRM libs ? This is the sitaution we appear
> to
> have and I believe it contributes to the large size of our EDMPOOL, the
> large
> size of bufferpool BP0, the umanageable size of our SPT01, and elongated
> bind times because the wildcarding that is necessary in the PKLIST of the
> bind statements. It is very difficult to do any type of cleanup.
>
> Does anyone have any experience similiar to this and can suggest a better
> way of doing things ?
>
> _____________________________________________________________________
>
> * IDUG North America * Tampa, Florida, * May 10-14 2010 *
> http://IDUG.ORG/NA *
> _____________________________________________________________________
>
> http://www.idug.org/db2-content/index.html has THOUSANDS of free technical
> presentations!
> DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI,
> Warehouses, - among
> many more categories of help waiting for you!
> Whether you are an old hand or a DB2 newbie, we have presentations for
> every level.
> _____________________________________________________________________
>
> If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is
> the home of IDUG's DB2-L
>



--
Robert Catterall
Catterall Consulting
www.catterallconsulting.com

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Jorg Lueke

Re: Is there anything Written on the use and/or misuse of Collectin-ids ?
(in response to Robert Catterall)
Are you talking literally hundreds of times? That would really seem
excessive. We have 20 test environments here, which I think is excessive,
and each of those has it;s own plan/collection due to using different
CICS/IMS regions and the fact that changes are rolled to different regions at
different times. I can't imagine the reason behind hundreds of collections per
package.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/solutions-journal.html - home of the IDUG Solutions Journal
Technical atricles from world famous authors in DB2's most prestigious, peer reviewed
magazine now on-line!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Daniel Lapp

Re: Is there anything Written on the use and/or misuse of Collectin-ids ?
(in response to Jorg Lueke)
Robert, thanks for responding ! We seem to have few plans with lots of
wildcarding in the pklist which I am sure causes some overhead as well. Am I
correct in thinking that DB2 then has to keep a copy of each package in the
EDMPOOL and search looking for the one with the correct CONTOKEN to
execute since there appears to be load libraries aligned with each of the
different collection ids. It's an assumption on my part but I would guess each
load library has a different version of each corresponding program which
would mean each load had a different CONTOKEN that would align to one
and only one package. In other words, even if a program is exactly the same
but has been precompled and compiled into 30 different load libraries that
would represent 30 different contokens for a program that was exactly the
same but each one would have to have it's own corresponding package. I
think they do this by the use of different libraries and collection ids.

Then, all the developers have to do is issue the set current packageset sql
command, change the JCL for the load libraries they want to execute from
and they can bounce from silo to silo (their terminology). Or you can have parallel development cycles running simultaneously.

Maintaining and cleaning up the environment before our SPT01 runs out of
space is a real challenge.

Any comments

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/solutions-journal.html - home of the IDUG Solutions Journal
Technical atricles from world famous authors in DB2's most prestigious, peer reviewed
magazine now on-line!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Suresh Sane

Re: Is there anything Written on the use and/or misuse of Collectin-ids ?
(in response to Daniel Lapp)

Daniel,



The scenario you describe is correct if you precompile/compile/link/bind 30 different times.



A few things to add:



1. In the Redbook, see chap 3, especially diagrams 3-6 and 3-7, which are an old-style flowchart of how the package is searched and loaded.



2. If the program issues a SET CURRENT PACKAGESET, only that collection is searched (29 others are irrelevant).



3. If you are concerned about the size of SPT01, a new PTF to compress it is now available. IBM reports savings of about 60% (I have not tried it).



I am one of the co-authors of the Redbook Robert pointed you to (SG24-7688) and will be happy respond offline with any follow up questions.



Thx

Suresh



> Date: Tue, 22 Dec 2009 13:00:33 +0000
> From: [login to unmask email]
> To: [login to unmask email]
> Subject: Re: [DB2-L] Is there anything Written on the use and/or misuse of Collectin-ids ?
>
> Robert, thanks for responding ! We seem to have few plans with lots of
> wildcarding in the pklist which I am sure causes some overhead as well. Am I
> correct in thinking that DB2 then has to keep a copy of each package in the
> EDMPOOL and search looking for the one with the correct CONTOKEN to
> execute since there appears to be load libraries aligned with each of the
> different collection ids. It's an assumption on my part but I would guess each
> load library has a different version of each corresponding program which
> would mean each load had a different CONTOKEN that would align to one
> and only one package. In other words, even if a program is exactly the same
> but has been precompled and compiled into 30 different load libraries that
> would represent 30 different contokens for a program that was exactly the
> same but each one would have to have it's own corresponding package. I
> think they do this by the use of different libraries and collection ids.
>
> Then, all the developers have to do is issue the set current packageset sql
> command, change the JCL for the load libraries they want to execute from
> and they can bounce from silo to silo (their terminology). Or you can have parallel development cycles running simultaneously.
>
> Maintaining and cleaning up the environment before our SPT01 runs out of
> space is a real challenge.
>
> Any comments
>
> _____________________________________________________________________
>
> * IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
> _____________________________________________________________________
>
> http://www.idug.org/solutions-journal.html - home of the IDUG Solutions Journal
> Technical atricles from world famous authors in DB2's most prestigious, peer reviewed
> magazine now on-line!
> _____________________________________________________________________
>
> If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

_________________________________________________________________
Hotmail: Trusted email with Microsoft’s powerful SPAM protection.
http://clk.atdmt.com/GBL/go/177141664/direct/01/
_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/solutions-journal.html - home of the IDUG Solutions Journal
Technical atricles from world famous authors in DB2's most prestigious, peer reviewed
magazine now on-line!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Daniel Lapp

Re: Is there anything Written on the use and/or misuse of Collectin-ids ?
(in response to Suresh Sane)
Suresh, how can I reach you offline ? My email is [login to unmask email]

My concerns are definetly the size of SPT01 but also maintenance/cleanup is
a nightmare and can't be safely done until after a release cycle is
complete. The compression PTF is fine but does not really address the
issues I have with how the whole environment is managed and the amount of
redundancy it has created. Along with the redundancy is a waste of a lot of
memory and cpu. So, compressing only allows people to continue with bad
habits which waste resources.

I'd love to talk offline.

On Tue, Dec 22, 2009 at 10:33 AM, Suresh Sane <[login to unmask email]> wrote:

> Daniel,
>
> The scenario you describe is correct if you precompile/compile/link/bind 30
> different times.
>
> A few things to add:
>
> 1. In the Redbook, see chap 3, especially diagrams 3-6 and 3-7, which are
> an old-style flowchart of how the package is searched and loaded.
>
> 2. If the program issues a SET CURRENT PACKAGESET, only that collection is
> searched (29 others are irrelevant).
>
> 3. If you are concerned about the size of SPT01, a new PTF to compress it
> is now available. IBM reports savings of about 60% (I have not tried it).
>
> I am one of the co-authors of the Redbook Robert pointed you to (SG24-7688)
> and will be happy respond offline with any follow up questions.
>
> Thx
> Suresh
>
>
> > Date: Tue, 22 Dec 2009 13:00:33 +0000
> > From: [login to unmask email]
> > To: [login to unmask email]
> > Subject: Re: [DB2-L] Is there anything Written on the use and/or misuse
> of Collectin-ids ?
>
> >
> > Robert, thanks for responding ! We seem to have few plans with lots of
> > wildcarding in the pklist which I am sure causes some overhead as well.
> Am I
> > correct in thinking that DB2 then has to keep a copy of each package in
> the
> > EDMPOOL and search looking for the one with the correct CONTOKEN to
> > execute since there appears to be load libraries aligned with each of the
>
> > different collection ids. It's an assumption on my part but I would guess
> each
> > load library has a different version of each corresponding program which
> > would mean each load had a different CONTOKEN that would align to one
> > and only one package. In other words, even if a program is exactly the
> same
> > but has been precompled and compiled into 30 different load libraries
> that
> > would represent 30 different contokens for a program that was exactly the
>
> > same but each one would have to have it's own corresponding package. I
> > think they do this by the use of different libraries and collection ids.
> >
> > Then, all the developers have to do is issue the set current packageset
> sql
> > command, change the JCL for the load libraries they want to execute from
> > and they can bounce from silo to silo (their terminology). Or you can
> have parallel development cycles running simultaneously.
> >
> > Maintaining and cleaning up the environment before our SPT01 runs out of
> > space is a real challenge.
> >
> > Any comments
> >
> > _____________________________________________________________________
> >
> > * IDUG North America * Tampa, Florida, * May 10-14 2010 *
> http://IDUG.ORG/NA *
> > _____________________________________________________________________
> >
> > http://www.idug.org/solutions-journal.html - home of the IDUG Solutions
> Journal
> > Technical atricles from world famous authors in DB2's most prestigious,
> peer reviewed
> > magazine now on-line!
> > _____________________________________________________________________
> >
> > If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-Lis the home of IDUG's DB2-L
>
> ------------------------------
> Hotmail: Trusted email with Microsoft’s powerful SPAM protection. Sign up
> now. < http://clk.atdmt.com/GBL/go/177141664/direct/01/ >
>
> ------------------------------
>
> [image: IDUG - The Worldwide DB2 User Community! ] < http://www.idug.org/db2-north-america-conference/index.html >
>
> The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
> not already an IDUG member, please register here. < http://www.idug.org/register >
>



--
Dan Lapp
"The lapper"

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/solutions-journal.html - home of the IDUG Solutions Journal
Technical atricles from world famous authors in DB2's most prestigious, peer reviewed
magazine now on-line!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Robert Catterall

Re: Is there anything Written on the use and/or misuse of Collectin-ids ?
(in response to Daniel Lapp)
Regarding the EDM pool: when a program containing embedded SQL issues a call
to DB2 (the precompile process comments out SQL statements and replaces them
with calls to DB2), DB2 will look in the SKPT (skeleton package table)
portion of the EDM pool to see if the package section needed to execute the
SQL statement corresponding to the call is there (and yes, the call contains
the package's consistency token, as well as the package name and a SQL
statement number). If it isn't, DB2 will locate the package in the directory
and load it into the SKPT. It will also put a copy of the package section or
sections in the PT (package table) part of the EDM pool (the PT holds copies
of package sections that are being executed through various threads). The
set-up you've described would probably mean higher SKPT space utilization,
because of all the packages that differ only in the value of the consistency
token -- if all 30 editions of one package (same source precompiled and
bound 30 times into 30 different collections) were to be executed in a given
period of time, you'd have copies of all 30 in the SKPT in the EDM pool,
versus a copy of just the one package if the program were precompiled and
bound only once into one collection. I wouldn't expect an impact on the PT
part of the EDM pool, as you'll have a copy of the package for each thread
associated with program execution anyway (i.e., if each of 30 threads is
executing one of the 30 packages that are identical save for the consistency
token value, you'll have 30 copies of the package in the PT part of the EDM
pool, and so it will be if 30 threads execute the same package with the same
consistency token). I went into a lot more detail on EDM pool usage for
package sections in a blog entry I posted earlier this year. You can check
it out at
http://www.catterallconsulting.com/2009/02/db2-for-zos-static-sql-and-edm-pool.html
.

As Suresh pointed out, if you use SET CURRENT PACKAGESET, you're not
penalized, CPU-wise, for having a lot of collections in a plans PKLIST,
because that SQL statement will direct DB2 to the right collection (versus
DB2 having to search through as many as 30 collections if all of those
collections are in the plan's PKLIST).

I have a hard time understanding why things are set up as they are at your
site. Why precompile the same program 30 times if you use SET CURRENT
PACKAGESET to get to the desired collection? Are all the different
consistency tokens needed to ensure that a given load module (in a given
load library) will only work with the package in the collection associated
with the load library (just in case SET CURRENT PACKAGESET is issued with an
incorrect collection name)? If so, it would seem that the developers feel
that it's important to have different collections to go with all the
different load libraries. If the developers can make a good case for 1)
linking the same program into so many different load libraries (why is that
done?) and 2) having a different DB2 package collection for each load
library, you may just have to keep dealing with the situation. You've
mentioned something about "silos" and parallel development cycles, but it's
not clear to me that the many different load libraries (containing the same
program) and the corresponding package collections are needed. What would
"break" if there were very few load libraries and corresponding collections,
versus many?

Robert

On Tue, Dec 22, 2009 at 11:26 AM, Daniel Lapp <[login to unmask email]> wrote:

> Suresh, how can I reach you offline ? My email is [login to unmask email]
>
>
> My concerns are definetly the size of SPT01 but also maintenance/cleanup is
> a nightmare and can't be safely done until after a release cycle is
> complete. The compression PTF is fine but does not really address the
> issues I have with how the whole environment is managed and the amount of
> redundancy it has created. Along with the redundancy is a waste of a lot of
> memory and cpu. So, compressing only allows people to continue with bad
> habits which waste resources.
>
> I'd love to talk offline.
>
>
> On Tue, Dec 22, 2009 at 10:33 AM, Suresh Sane <[login to unmask email]>wrote:
>
>> Daniel,
>>
>> The scenario you describe is correct if you precompile/compile/link/bind
>> 30 different times.
>>
>> A few things to add:
>>
>> 1. In the Redbook, see chap 3, especially diagrams 3-6 and 3-7, which are
>> an old-style flowchart of how the package is searched and loaded.
>>
>> 2. If the program issues a SET CURRENT PACKAGESET, only that
>> collection is searched (29 others are irrelevant).
>>
>> 3. If you are concerned about the size of SPT01, a new PTF to compress it
>> is now available. IBM reports savings of about 60% (I have not tried it).
>>
>> I am one of the co-authors of the Redbook Robert pointed you to
>> (SG24-7688) and will be happy respond offline with any follow up questions.
>>
>> Thx
>> Suresh
>>
>>
>> > Date: Tue, 22 Dec 2009 13:00:33 +0000
>> > From: [login to unmask email]
>> > To: [login to unmask email]
>> > Subject: Re: [DB2-L] Is there anything Written on the use and/or misuse
>> of Collectin-ids ?
>>
>> >
>> > Robert, thanks for responding ! We seem to have few plans with lots of
>> > wildcarding in the pklist which I am sure causes some overhead as well.
>> Am I
>> > correct in thinking that DB2 then has to keep a copy of each package in
>> the
>> > EDMPOOL and search looking for the one with the correct CONTOKEN to
>> > execute since there appears to be load libraries aligned with each of
>> the
>> > different collection ids. It's an assumption on my part but I would
>> guess each
>> > load library has a different version of each corresponding program which
>>
>> > would mean each load had a different CONTOKEN that would align to one
>> > and only one package. In other words, even if a program is exactly the
>> same
>> > but has been precompled and compiled into 30 different load libraries
>> that
>> > would represent 30 different contokens for a program that was exactly
>> the
>> > same but each one would have to have it's own corresponding package. I
>> > think they do this by the use of different libraries and collection ids.
>> >
>> > Then, all the developers have to do is issue the set current packageset
>> sql
>> > command, change the JCL for the load libraries they want to execute from
>>
>> > and they can bounce from silo to silo (their terminology). Or you can
>> have parallel development cycles running simultaneously.
>> >
>> > Maintaining and cleaning up the environment before our SPT01 runs out of
>>
>> > space is a real challenge.
>> >
>> > Any comments
>> >
>> > _____________________________________________________________________
>> >
>> > * IDUG North America * Tampa, Florida, * May 10-14 2010 *
>> http://IDUG.ORG/NA *
>> > _____________________________________________________________________
>> >
>> > http://www.idug.org/solutions-journal.html - home of the IDUG Solutions
>> Journal
>> > Technical atricles from world famous authors in DB2's most prestigious,
>> peer reviewed
>> > magazine now on-line!
>> > _____________________________________________________________________
>> >
>> > If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-Lis the home of IDUG's DB2-L
>>
>> ------------------------------
>> Hotmail: Trusted email with Microsoft’s powerful SPAM protection. Sign up
>> now. < http://clk.atdmt.com/GBL/go/177141664/direct/01/ >
>>
>> ------------------------------
>>
>> [image: IDUG - The Worldwide DB2 User Community! ] < http://www.idug.org/db2-north-america-conference/index.html >
>>
>> The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
>> are not already an IDUG member, please register here. < http://www.idug.org/register >
>>
>
>
>
> --
> Dan Lapp
> "The lapper"
>
>
> ------------------------------
>
> [image: IDUG - The Worldwide DB2 User Community! ] < http://www.idug.org/db2-north-america-conference/index.html >
>
> The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
> not already an IDUG member, please register here. < http://www.idug.org/register >
>



--
Robert Catterall
Catterall Consulting
www.catterallconsulting.com

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/rug/index.html - with almost 150 IDUG Regional User Groups,
there is probably one near you!
Regional User Groups are your local connection to the Worldwide DB2 User Community
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L