June 15, 2009

Reducing the Impact of Long Package Lists

In the early days of DB2, you had to bind all DBRMs of any program you wanted to call to the plan being executed. The problem with this is if you changed one program, that DBRM and all other DBRMs had to be bound again. When packages were introduced, the bind was moved from the plan to a collection. Any program being executed has to be available to the plan through either a DBRM or a package list. The
package list is defined using the PKLIST parameter of the BIND PLAN statement. The format of
this statement is:

           BIND PLAN plan-name PKLIST(collection1.*, collection2.*, ….,collectionxx.*)

The asterisk (*) is a wild card that gives the PLAN access to all packages in the collection. You could restrict this to a specific package, but this could increase maintenance. Each time you change a program and add a subroutine call, you'd have to update the package list and rebind the PLAN. To avoid this you'd normally use the "*" to make all programs available, even packages that will be created in the future.

I usually put all packages for a given business area into its own collection. For example, Payroll programs would be in collection PR01_C and Human Resources would be in HR01_C.  I'd put any common programs used between business areas into a common collection like CM01_C. This example shows how to create a PLAN that included both the Payroll collection and Common program collection:

                 BIND PLAN prplan PKLIST(PR01_C.*, CM01_C.*)

This works great and has no performance implication--that is, unless you start increasing the number of collections in the package list and use BIND parameter RELEASE(COMMIT). (Indeed, that's what most people use.)

When both special registers (CURRENT PACKAGE PATH and CURRENT PACKAGESET) are blank, DB2 follows this search order (documented in the "DB2 for z/OS Application Programming and SQL

  • At the local location (if CURRENT SERVER is blank or names the server explicitly), DB2 searches:

        o    All DBRMs that are bound directly to the plan.
        o    All packages that are already allocated to the plan while the plan is running.
        o    All packages in the order they're listed in the package list.

  • At a remote location, DB2 searches:

        o    All packages that are already allocated to the plan while the plan is running.
        o    All packages in the order they're listed in the package list.

For best performance, make sure your program is defined early in the package list. Having the program listed at the end of the search isn't usually a problem for long-running programs--unless your long-running program issues commits. On each commit the structures built by DB2 to support execution are deleted, and then rebuilt with the very next execution of an SQL statement. To avoid this you could use BIND option RELEASE(DEALLOCATE). However, this poses some other implications with concurrency and memory usage.

One way you can continue to use RELEASE(COMMIT) and retain the package structures is to define a cursor using the WITH HOLD clause. If your program already uses WITH HOLD, then you're set. Otherwise, you may want to define a dummy cursor that uses SYSIBM.SYSDUMMY1 and WITH HOLD. Open this cursor at the beginning of your program execution and leave it open.

Whether or not you use this technique, you should tune your commit points for batch processing to occur every five-15 minutes. If you must perform the commit every few seconds to support running with online transactions, then you'll want to use the WITH HOLD technique. You may also want to use this on a high-volume CICS transaction.

When you're using RELEASE(COMMIT) and have a long list of collections--and the program you're executing is usually in the collection at the end of the package list--consider the dummy cursor WITH HOLD.