What Is a Collection?

A collection is a user -defined name from 1 to 128 characters that the programmer must specify for every package. A collection is not an actual, physical database object.

NOTE

Prior to DB2 V8, the maximum length of a collection name was 18 characters; this was increased to a maximum of 128 characters as of DB2 V8.

You can compare collections to databases. A DB2 database is not actually a physical object (ignoring, for the moment, the DBD). In much the same way that a database is a grouping of DB2 objects, a collection is a grouping of DB2 packages.

By specifying a different collection identifier for a package, the same DBRM can be bound into different packages. This capability permits program developers to use the same program DBRM for different packages, enabling easy access to tables that have the same structure (DDL) but different owners .

Assume, for example, that you have created copies of the DB2 sample tables and given them an authid of DSNCLONE . You now have a DSN8810.DEPT table and a DSNCLONE.DEPT table with the same physical construction (such as the same columns and keys). Likewise, assume that you have duplicated all the other sample tables. You then could write a single program, using unqualified embedded SQL, to access either the original or the cloned tables.

The trick is to use unqualified SQL. You could simply bind a program into one package with a collection identifier of ORIG and into another package with a collection identifier of CLONE . The bind for the package with the ORIG collection identifier specifies the DSN8810 qualifier, and the bind for the CLONE collection package specifies the DSNCLONE qualifier. You would store both of these in the DB2 Catalog.

But how do you access these packages? Assume that both packages were generated from a DBRM named SAMPPROG . This would give you packages named ORIG.SAMPPROG and CLONE.SAMPPROG . You can bind both these packages into a plan called SAMPPLAN , for example, as in the following:

 BIND  PLAN (SAMPPLAN)       PKLIST(ORIG.SAMPPROG, CLONE.SAMPPROG) 

The program then specifies which collection to use with the SET CURRENT PACKAGESET statement. By issuing the following statement, the plan is instructed to use the package identified by the value of the host variable (in this example, either ORIG or CLONE ).

 EXEC SQL     SET CURRENT PACKAGESET = :HOST-VAR END-EXEC. 

Another use of packages is to identify and relate a series of programs to a given plan. You can bind a plan and specify a wildcard for the package identifier. This effectively ties to the plan all valid packages for the specified collection. Consider the following BIND statement, for example:

 BIND  PLAN(SAMPLE)    PKLIST(ORIG.*) 

All valid packages in the ORIG collection are bound to the SAMPLE plan. If new packages are bound specifying the ORIG collection identifier, they are included automatically in the SAMPLE plan; no bind or rebind is necessary.

CURRENT PACKAGE PATH Special Register

DB2 V8 adds the CURRENT PACKAGE PATH special register to ease distributed development, because not every environment supports package list specification using PKLIST . You can use CURRENT PACKAGE PATH to specify the collections to use for package resolution. CURRENT PACKAGE PATH differs from CURRENT PACKAGESET , because the package path contains a list of collection names , whereas the PACKAGESET is a single collection. Whichever was set last is used to determine the package that is to be invoked.

Consider setting CURRENT PACKAGE PATH for DB2 for z/OS Application Requestors connected via DRDA and requesting the execution of a package on a remote DB2 for z/OS. Doing so can reduce network traffic and improve performance.

Another useful purpose for CURRENT PACKAGE PATH is to set the list of collections in programs that run from a workstation to access DB2 for z/OS. Such programs do not have a plan, and therefore have no PKLIST . Such programs had to issue a new SET CURRENT PACKAGESET statement each time the program needed to switch to a different collection. With CURRENT PACKAGE PATH multiple collections may be specified once.

Finally, stored procedure and user-defined functions may benefit from using CURRENT PACKAGE PATH to specify multiple collections.

Collection Size

Do not concern yourself with collection size. Bind as many packages into a single collection as you want. Remember, a collection is not a physical entity. It is merely a method of referencing packages.

Quite often people confuse collections with package lists. The size of a collection is irrelevant. The size of a package list can be relevant ”the smaller the better.

Package List Size

You do not need to go to extraordinary means to limit the size of the package list as the performance gain realized due to smaller package lists usually is not significant. A recently conducted test shows that the difference between accessing the first entry in a package list is only milliseconds faster than accessing the one hundredth entry in the package list. Of course, milliseconds can sometimes make a difference.

The length of the PKLIST starts to become a performance issue only when the list contains thousands of packages. A better reason to limit the size of the package list is to enhance maintainability. The fewer entries in the package list, the easier maintenance will be.

Consider using wildcarding to limit the size of the package list, thereby simplifying maintenance.