My previous article on packages (z/Journal, February/ March 2006) provided what one might consider an introduction or refresher. However, how to create packages and how some aspects of a package work are just a small part of the picture. A package is the DB2 process that lets you get to your data. Understanding how your choice of parameters can impact a package’s behavior is important.
This article takes an in-depth look at packages and discusses how some of the different bind keywords affect a package’s behavior, the use of packages by triggers, CURRENT PACKAGESET and CURRENT PACKAGE PATH special registers, and versioning. Topics discussed are in no particular order, and no importance is implied by ordering.
You can specify many bind parameters in DB2. Some are for plans, packages, or for both; some are for packages and triggers. Which object type you’re attempting to bind controls which parameters you can work with. This article focuses on packages, so there will be little discussion about parameters that are strictly for plans—though we’ll spend some time covering plans because of their direct affect on a package’s behavior. To me, only a few parameters are interesting enough to write about, but Figure 1 lists all the parameters the bind package command uses.
ACQUIRE and RELEASE control the duration of a table, partition, or table space lock held by, in most cases, static SQL statements in a plan or package. These two keywords have no effect on page or row locks. Page/row locks are always removed at commit. Most of the attention around this keyword couple focuses on the RELEASE half. Starting with the easier of the two, ACQUIRE, all packages use ACQUIRE(USE); you can’t override this.
Back in the days when we were still binding DBRMs directly into plans, you had to think about ACQUIRE. ACQUIRE(USE) tells DB2 to acquire the necessary resources to run an SQL statement when that statement is executed. This is the only behavior of a package. If you were dealing with a plan that had DBRMs bound into it directly, you could choose ACQUIRE(ALLOCATE), which tells DB2 to acquire all the resources for all the SQL statements in all the DBRMs bound into that plan when the plan’s thread is allocated. This would give a plan predictable performance because everything was allocated the same way all the time. However, it also meant you could be allocating resources for SQL statements that may never be executed in a particular plan execution. The result would be unnecessary overhead and resource allocation. When binding a plan, ACQUIRE(ALLOCATE) must always be paired with RELEASE(DEALLOCATE).
Let’s now consider the RELEASE keyword, which can impact:
- When table space locks are released
- Reuse of EDM pool pages occupied by Cursor Tables (CT) and Package Tables (PT)
- Release of thread storage
- Sequential detection resetting of and index look-aside information
- Destruction of procedures.
This keyword has two values: DEALLOCATE and COMMIT. RELEASE(COMMIT) releases the above resources at commit time while RELEASE(DEALLOCATE) releases resources at thread deallocation. Usually, RELEASE(COMMIT) is the right choice. In a high-performance online transaction, there’s probably only a single piece of work being executed anyway, which makes RELEASE(COMMIT) an obvious choice. In other environments, however, such as batch and distributed transactions, you need to consider the pros and cons of your choice of keyword values.