DB2 package versions and status

Versions and catalog status

Version comes from the precompiler/coprocessor VERSION option (or the empty-string default). SYSPACKAGE shows NAME, COLLID, VERSION, CONTOKEN, VALID, OPERATIVE, and the bind options last used. Typical VALID values:

• Y — valid; Db2 can execute the current copy
• N — invalid; next use normally triggers automatic rebind
• S — statement-level invalidation (DEPLEVEL(STATEMENT)); some statements incremental-bind

OPERATIVE tells you whether the last autobind succeeded. Inoperative packages need an explicit REBIND. LASTUSED helps you find packages nobody runs before you FREE them.

PKLIST

The plan’s package list is how a version gets executed at all. Wildcards on REBIND PACKAGE (collection.*, *.*, and so on) are a different wildcard language from PKLIST, but the idea is the same: name a set of packages. PKLIST entries can be:

• collection.package-id — one package (any matching version/token)
• collection.* — every package in the collection
• location.collection.package-id — remote package

Db2 does not pick “the latest VERSION string” by sorting names. It picks the package whose consistency token matches the program. Keep old versions bound until every load module that still uses them is gone, then FREE the obsolete version.

Bind, rebind, and free

Bind

BIND PACKAGE reads a DBRM (MEMBER) or copies an existing package (COPY). Use it when SQL changed or when you add a collection/version. ACTION(ADD) versus ACTION(REPLACE) decides whether you create or overlay. BIND also deletes phased-out PLANMGMT copies.

Rebind

REBIND PACKAGE does not read a DBRM. Use it when something that affects access paths or bind options changed: new index, RUNSTATS, isolation, APPLCOMPAT, invalid package. You may change any ordinary package bind option on REBIND. Trigger packages are special: basic triggers use REBIND TRIGGER PACKAGE with a limited option set; advanced SQL PL triggers use REBIND PACKAGE.

Free

FREE PACKAGE removes the package from the catalog and directory. FREE PLAN removes the plan. FREE SERVICE drops an active REST service package. PLANMGMTSCOPE(INACTIVE) can free leftover copies without dropping the current package.

1
2
3
BIND PACKAGE(APP) MEMBER(ORDS01) ACTION(REPLACE) VERSION(V3)
REBIND PACKAGE(APP.ORDS01.(V3)) APREUSE(WARN) PLANMGMT(EXTENDED)
FREE  PACKAGE(APP.ORDS01.(V2))

Access path retention

Production rebinds are scary because the optimizer might pick a worse path. PLANMGMT(EXTENDED) keeps original and previous copies so you can SWITCH. APREUSE asks Db2 to keep the old path:

• NONE (also documented as NO in some summaries) — do not reuse
• WARN — reuse when possible; if not, take a new path and warn
• ERROR — reuse must succeed or the statement/package rebind fails

APCOMPARE(NONE | WARN | ERROR) compares the newly chosen path with the saved one. Packages bound before DB2 9 cannot reuse until they have been rebound once on a current release. APREUSESOURCE can name CURRENT, PREVIOUS, or ORIGINAL as the reuse source.

Bind options (and rebind options)

REBIND accepts the same option set as BIND PACKAGE for ordinary packages. Trigger rebinds allow only a documented subset. Know every value below.

ISOLATION

CURRENTDATA

With isolation CS, CURRENTDATA(NO) is the common setting: Db2 can avoid locking the current row in some read cases and use block fetching more aggressively. CURRENTDATA(YES) keeps the data the cursor just fetched current — more locking, sometimes required for correctness with positioned updates. Know your shop default before you copy JCL from another system.

RELEASE

• COMMIT — release locks and other resources at COMMIT (typical)
• DEALLOCATE — hold until the thread deallocates the plan/package (can reduce lock/prepare cost for high-volume reused threads; can hurt concurrency)
• INHERITFROMPLAN — package uses the plan’s RELEASE

VALIDATE

VALIDATE(BIND) — missing objects or privileges fail the bind, except LOCK TABLE and some CREATE/ALTER/DROP checks that wait until run time. VALIDATE(RUN) — warnings at bind, recheck at run time using the owner authorization ID. SQLERROR(CONTINUE) can still produce a package whose failing statements cannot execute.

OWNER and QUALIFIER

OWNER owns the package and must be able to execute the embedded static SQL (SYSADM and DATAACCESS include that). BINDAGENT and trusted-context roles change who you may name. QUALIFIER is prepended to unqualified tables, views, indexes, and aliases in static SQL. Changing QUALIFIER on REBIND can retarget a package from TEST to PROD schemas without changing source — if the SQL is fully unqualified.

DYNAMICRULES

DYNAMICRULES also affects how dynamic SQL gets a qualifier and SQL path. BIND behavior for dynamic SQL is a security feature: the runner cannot use the package as a general SQL engine with their own table privileges.

SQLERROR

• NOPACKAGE — SQL errors at bind prevent creating the package
• CONTINUE — package is created; statements in error are not executable
• CHECK — syntax/semantic check without producing a full executable package (binder needs BIND, BINDAGENT, or EXPLAIN)

EXPLAIN

NO — do not populate EXPLAIN tables. YES — write PLAN_TABLE (and related) rows for the statements. ONLY — explain without replacing the executable package (binder needs the EXPLAIN privilege). Use EXPLAIN(YES) on important production binds so you can compare paths after the next REBIND.

REOPT and DEGREE

DEGREE(1) disables query I/O parallelism for the package. DEGREE(ANY) allows Db2 to consider parallelism. Parallelism helps large queries; it is usually wrong for short OLTP packages.

QUERYACCELERATION

Controls whether static SQL is eligible for IBM Db2 Analytics Accelerator: NONE, ENABLE, ENABLE WITH FAILBACK, ELIGIBLE, ALL. Pair with GETACCELARCHIVE when archive accelerator data matters. This is a bind-time eligibility setting, not a substitute for accelerator configuration.

Putting options on a real job

1
2
3
4
5
6
7
8
BIND PACKAGE(APP) MEMBER(ORDS01) -
  ACTION(REPLACE) OWNER(APPBIND) QUALIFIER(ORD) -
  ISOLATION(CS) CURRENTDATA(NO) RELEASE(COMMIT) -
  VALIDATE(BIND) DYNAMICRULES(BIND) -
  SQLERROR(NOPACKAGE) EXPLAIN(YES) -
  DEGREE(1) REOPT(NONE) -
  PLANMGMT(EXTENDED) APREUSE(NONE) -
  QUERYACCELERATION(NONE)

That is a conservative OLTP package: CS, no parallelism, no accelerator, fail on bind errors, explain on, plan management on so later REBINDs can SWITCH. Your shop standard may add APPLCOMPAT, ENCODING, PATH, and CONCURRENTACCESSRESOLUTION.

Explain It Like I'm Five

A package version is a labeled lunchbox. The stamp inside must match the stamp on your schoolbag (consistency token) or the cafeteria will not give you food. VALID is a sticker that says “still edible.” If someone throws away the fridge the lunch depended on (DROP INDEX), the sticker becomes “not edible” until a cook remakes it (REBIND). BIND is cooking from a new recipe card. FREE is throwing the lunchbox away. The option list is the cook’s instructions: how carefully to lock the fridge door, whose name is on the permission slip, and whether to reuse last week’s oven settings.

Exercises

1. For each ISOLATION value, write one sentence on what a second SQL session can see while your cursor is open.
2. Choose VALIDATE, SQLERROR, and EXPLAIN for a production replace bind and defend the combination.
3. When is RELEASE(DEALLOCATE) a good idea, and when does it cause timeouts?
4. Write REBIND PACKAGE that reuses paths strictly (APREUSE ERROR) with PLANMGMT EXTENDED.
5. Query SYSPACKAGE for VALID <> 'Y' at your site and classify each row as “needs REBIND” versus “needs BIND from new source.”