What Is a Package?

A package is a single, bound DBRM with optimized access paths. By using packages, the table access logic is "packaged" at a lower level of granularity, at the package (or program) level.

To execute a package, you first must include it in the package list of a plan. Packages are not directly executed ”they are only indirectly executed when the plan in which they are contained executes (as discussed previously, UDFs and triggers are exceptions to this rule). A plan can consist of one or more DBRMs, one or more packages, or a combination of packages and DBRMs.

To help differentiate between plans and packages, consider a grocery store analogy. Before going to the grocery store, you should prepare a shopping list. As you go through the aisles , when you find an item on your list, you place the item in your shopping cart. After your paying for the items at the check-out register, the clerk places your grocery items in a bag. You can think of the purchased items as DBRMs. The bag is the plan. You have multiple DBRMs (grocery items) in your plan (shopping bag).

In a package environment, rather than actually removing the items from the shelf, you would mark on your shopping list the location of each item in the store. Upon checking out, you would give the list to the clerk at the counter. The clerk then would place the list in the bag ”not the actual items. The plan (bag) contains a list pointing to the physical location of the packages (grocery items) that are still on the shelf. This approach is a good way to compare and contrast the two different environments.

Package information is stored in its own DB2 Catalog tables. When a package is bound, DB2 reads the following DB2 Catalog tables:

SYSIBM.SYSCOLDIST	SYSIBM.SYSCOLDISTSTATS
SYSIBM.SYSCOLSTATS	SYSIBM.SYSCOLUMNS
SYSIBM.SYSINDEXES	SYSIBM.SYSINDEXSTATS
SYSIBM.SYSPACKAGE	SYSIBM.SYSPACKAUTH
SYSIBM.SYSTABLES	SYSIBM.SYSTABLESPACE
SYSIBM.SYSTABSTATS	SYSIBM.SYSUSERAUTH

NOTE

The SYSIBM.SYSUSERAUTH table (the last one in the list) is read only for BIND ADD .

Information about the package then is stored in the following DB2 Catalog tables:

SYSIBM.SYSPACKAGE	SYSIBM.SYSPACKAUTH
SYSIBM.SYSPACKDEP	SYSIBM.SYSPACKSTMT
SYSIBM.SYSPKSYSTEM	SYSIBM.SYSTABAUTH

The DB2 Catalog stores only information about the packages. The executable form of the package is stored as a skeleton package table in the DB2 Directory in the SYSIBM.SPT01 table.

A package also contains a location identifier, a collection identifier, and a package identifier. The location identifier specifies the site at which the package was bound. If your processing is local, you can forgo the specification of the location ID for packages.

The collection identifier represents a logical grouping of packages, and is covered in more detail in the next section of this chapter. The package identifier is the DBRM name bound into the package. This ties the package to the program to which it applies. A package is uniquely identified as follows when used in a statement to bind packages into a plan:

 LOCATION.COLLECTION.PACKAGE 

One final consideration when using packages is versioning. A package can have multiple versions, each with its own version identifier. The version identifier is carried as text in the DBRM, and is covered in more depth in the "Package Version Maintenance" section.

Package Benefits

Reduced bind time is the package benefit most often cited. When you are utilizing packages and the SQL within a program changes, only the package for that particular program needs to be rebound. If packages are not used when multiple DBRMs are bound into a plan and the SQL within one of those programs changes, the entire plan must be rebound. This wastes time because you must still rebind all the other DBRMs in that plan that did not change.

Another benefit of packages involves the granularity of bind parameters. With packages, you can specify your bind options at the program level because many of the bind parameters are now available to the BIND PACKAGE command, such as the ISOLATION level and RELEASE parameters. By specifying different parameters for specific packages and including these packages in a plan, many combinations of isolation level and release are possible. You can, for example, create a single plan that provides an isolation level of cursor stability ( CS ) for one of its packages and an isolation level of repeatable read ( RR ) for another package. This combination of strategies is not possible in a plan-only environment.

The third benefit, versioning, probably is the biggest benefit of all. Packages can be versioned, thus enabling you to have multiple versions of the same package existing at the same time in the DB2 Catalog. Simply by running the appropriate load module, DB2 chooses the correct package to execute. DB2 uses a package selection algorithm to execute the correct access path .

Packages also provide improved support for mirror tables. Because a package has a high level qualifier of collection, you can specify a collection for each of your mirror table environments. Suppose that you have an environment in which you have current and history data in separate tables. Using only plans, the following two options were available:

• You could write a program that specifically selected the appropriate high-level qualifier for each appropriate table, such as CURRENT or HISTORY , and hard-code that qualifier into your program.

• You could BIND the program's DBRM into different plans, specifying a different owner for each.

In a package environment, you can use separate collections for each of these environments. This technique is discussed in detail in the "What Is a Collection?" and "Bind Guidelines" sections later in this chapter.

Additionally, packages provide for remote data access. If you are using a DB2 remote unit of work, you can specify the location in which you want to bind the package. The DBRM will exist at the site from which you are issuing the BIND , but the package is created at the remote site indicated by the high-level qualifier of the package.

Package Administration Issues

Before deciding to implement packages, you will need to consider the potential administrative costs of packages. This section covers several areas of administrative concern surrounding package implementation.

Systematic Rebinding

A concern that might not be obvious immediately is the approach to systematic rebinding. At some shops , a production job is set up to rebind plans after executing a REORG and RUNSTATS . This setup ensures that access paths are optimal given the current state of the DB2 table spaces and indexes. In an environment in which plans consist of multiple DBRMs, you can rebind a plan in a single job step. However, after migrating to an environment in which multiple packages exist per plan (rather than multiple DBRMs) you need to rebind each package individually. Remember that access paths exist at the package level, not at the plan level, so packages must be rebound. This results in multiple job steps: one per package. The administration of this environment will be more difficult because you will need to create and maintain additional job steps.

Package Version Maintenance

Another potential administrative headache is package version maintenance . Every time a DBRM with a different version identifier is bound to a package, a new version is created. This can cause many unused package versions to be retained. Additionally, when packages are freed, you must specify the location, collection, package, and version of each package you want to free.

If your shop allows many versions of packages to be created, a method is required to remove versions from the DB2 Catalog when their corresponding load modules no longer exist. Your shop, for example, may institute a policy that specifies the 5 most recent package versions are maintained in a production environment. The number 5 is not important; your shop may support 2, 12, or whatever is deemed appropriate. What is important is the notion that the number of versions be limited. Failure to do so causes your DB2 environment to be inundated with a very large DB2 Catalog. To administer versions, consider using a third party tool to manage package versions as required.

Whenever the need arises to drop an old package from the system, you must know the version name associated with it. Consider the situation in which 100 versions exist and only 5 must be kept. To accomplish this, you must know the 95 version names you want to drop. If you created these versions using the VERSION(AUTO) option, you will need to remember versions named using a 26-byte timestamp. Without a tool to automate package maintenance, remembering automatic version names is a difficult task.

Consider using DB2 Catalog queries to generate statements you can use to remove package versions. By using the information in the DB2 Catalog and the power of SQL, you can eliminate many of the tedious tasks associated with freeing old package versions. The following SQL will generate the commands required to free all but the most recently created package version, as in the following:

 SELECT   'FREE PACKAGE('  COLLID  '.'           NAME  '.('  VERSION  '))' FROM     SYSIBM.SYSPACKAGE A WHERE    TIMESTAMP < (SELECT   MAX(TIMESTAMP)                       FROM     SYSIBM.SYSPACKAGE B                       WHERE    A.COLLID = B.COLLID                       AND      A.NAME = B.NAME) 

The result of this query is a series of FREE commands that can be submitted to DB2. Alternatively, you can modify the query to generate DROP statements that can be submitted to DB2 via SPUFI. You can add additional predicates to generate FREE commands for specific collections or packages.

Before executing the FREE commands, be sure that you really want to eliminate all package versions except for the most recent one. Additionally, inspect the generated FREE commands to ensure that they are syntactically correct. These statements may need to be modified prior to being executed. And, of course, after the package versions have been freed, you cannot use them again.

Production and Test in the Same Subsystem

There may be some easing of the overall administrative burden by moving to packages. Consider shops that support both test and production applications within the same DB2 subsystem. Although these types of shops are becoming increasingly rare, some still do exist and they may have valid reasons for the continuing coexistence of production and test with the same DB2 subsystem. In this case, converting to packages eases the administrative burden by enabling the application developer to specify production and test collections. An indicator, for example, can be embedded within the collection name specifying PROD or TEST . By binding packages into the appropriate collection, the production environment is effectively separated from the test environment.

Package Performance

Probably the biggest question that most shops have as they implement packages is "How will the packages perform in comparison to my current environment?" By following the advice in this section you will understand how to make packages perform every bit as well, if not better than, your current environment.

Usually, DB2 can retrieve the package quite easily because indexes exist on the DB2 Catalog tables that contain package information. Indexes on the LOCATION , COLLID (collection), NAME (package), and CONTOKEN columns make efficient package retrieval quite common.

Improper package list specification, however, can impact performance. Specifying the appropriate package list can shave critical sub-seconds from performance-critical applications. Follow these general rules of thumb when specifying your PKLIST :

• Keep the plan package list as short as possible. Do not go to excessive lengths, however, to make the list contain only one or two packages. Make the PKLIST as short as possible, given the considerations and needs of your application.

• Place the most frequently used packages first in the package list.

• Consider specifying collection.* to minimize plan binding. If you bind multiple packages into a collection, you can include all those packages in the plan simply by binding the plan with collection.* . Any package that is added to that collection at a future point in time automatically is available to the plan.

• Avoid *.* because of the runtime authorization checking associated with that.

NOTE

Specifying collection.* should perform best because only a single search is performed for everything in that collection.