REBIND commands in DB2 for z/OS

REBIND versus BIND

Automatic rebind (subsystem parameter) can kick in when an invalid package runs. Many shops set that off so access paths do not change in the middle of the night without a change record. Prefer an explicit REBIND window with EXPLAIN(YES) or EXPLAIN(ONLY).

REBIND PACKAGE

REBIND PACKAGE rebinds application packages other than basic trigger packages. You can change any bind option that BIND PACKAGE accepts. Identify the package as location.collection.package.(version). Omit location for the local server.

1
2
3
4
5
6
7
8
9
10
DSN SYSTEM(DB2A)
REBIND PACKAGE(PAYROLL.PAYCALC.(V1)) -
  EXPLAIN(YES) ISOLATION(CS) -
  PLANMGMT(EXTENDED) APREUSE(WARN)
END
 
REBIND PACKAGE(PAYROLL.*)
REBIND PACKAGE(*.*)
REBIND PACKAGE(*.DEBIT)
REBIND PACKAGE(SNTERSA.GROUP1.PROGA.(V1)) ENABLE(BATCH)

Wildcards: *.* is all versions of all local packages you are allowed to rebind. LEDGER.* is everything in that collection. *.DEBIT is the empty-string version of DEBIT in every collection. Asterisks are not valid for remote packages—spell the location, collection, package, and version.

Authorization is the BIND privilege on the package, ownership, BINDAGENT from the owner, PACKADM, or SYSADM/SYSCTRL/System DBADM. You only rebind packages you are authorized for when you use *.

PLANMGMT and SWITCH

• PLANMGMT(OFF) — no extra copies
• PLANMGMT(BASIC) — keep a previous copy
• PLANMGMT(EXTENDED) — previous and original copies (the usual production setting with APREUSE)
• SWITCH(PREVIOUS | ORIGINAL) — make that copy current without a brand-new optimization from scratch
• APRETAINDUP — whether to keep a new copy if it duplicates an old access path

After a weekend REBIND that regresses a statement, SWITCH(PREVIOUS) is faster and safer than guessing bind options. FREE PACKAGE PLANMGMTSCOPE(INACTIVE) later reclaims the unused copies.

APREUSE and APCOMPARE

Db2 ignores APREUSE for a package last bound before DB2 9; RELBOUND in SYSPACKAGE / SYSPACKCOPY tells you the release. Rebind once accepting path changes, then you can reuse. EXPLAIN(YES) or EXPLAIN(ONLY) with APREUSE(ERROR) and APCOMPARE(WARN) is a common “show me what would change” pattern. APREUSE(WARN) plus APCOMPARE(WARN) can succeed even when reuse fails if Db2 happens to pick the same path again.

REBIND PLAN

REBIND PLAN updates plan-level options. The usual reason is the package list.

• Specify PKLIST — replace the old list
• Omit PKLIST — keep the previous list
• NOPKLIST — delete the entire package list

1
2
REBIND PLAN(PLANA) PKLIST(GROUP1.*) MEMBER(ABC)
REBIND PLAN(PLANA) NOPKLIST

MEMBER on REBIND PLAN is the same deprecated compatibility path as BIND PLAN: avoid it for new work. You can still change ISOLATION, CACHESIZE, CURRENTSERVER, and other plan options. Authorization is ownership, BIND on the plan, BINDAGENT, or a system authority.

REBIND TRIGGER PACKAGE

REBIND TRIGGER PACKAGE rebinds a package for a basic trigger. Identify basic triggers by a blank SQLPL column in SYSIBM.SYSTRIGGERS. Advanced triggers (SQL PL) use REBIND PACKAGE, not this command.

Reasons to rebind a trigger package: a new index, RUNSTATS, a dropped dependent object that marked the package invalid, or a small set of options (CURRENTDATA, DESCSTAT, EXPLAIN, ISOLATION, RELEASE, APPLCOMPAT, time-sensitive flags, CONCURRENTACCESSRESOLUTION, PLANMGMT, APREUSE, APCOMPARE, SWITCH). REBIND TRIGGER PACKAGE(*) affects only basic trigger packages you are authorized to rebind. A successful rebind marks the package valid.

1
2
3
REBIND TRIGGER PACKAGE(HR.EMP_SAL_TRIG)
  EXPLAIN(YES) ISOLATION(CS)
  PLANMGMT(EXTENDED) APREUSE(WARN)

Operational habits

• Take EXPLAIN output (or access-path comparison reports) before mass REBIND PACKAGE (*.*)
• Use PLANMGMT(EXTENDED) so SWITCH is available
• Invalid packages: query SYSPACKAGE VALID and SYSPACKDEP after DROP INDEX
• High-performance DBATs and RELEASE(DEALLOCATE) can block the package lock—MODIFY DDF PKGREL(COMMIT) or recycle idle threads before a rebind window
• REBIND is group scope; you do not rebind once per data sharing member

Explain It Like I'm Five

BIND is cooking a new meal from a new recipe card. REBIND is tasting last week’s frozen meal again with a better oven (new statistics) or a different timer (isolation). APREUSE is saying “use the same oven rack as last time.” SWITCH is pulling the backup meal from the freezer labeled PREVIOUS when the new baking tastes wrong. Trigger packages are meals the database cooks by itself when someone touches a table; basic ones have a smaller kitchen and their own REBIND command.

Exercises

1. After CREATE INDEX on HR.EMPLOYEE, write REBIND PACKAGE for collection HRCOLL with EXPLAIN(YES) and PLANMGMT(EXTENDED).
2. A production package got slower after rebind. Write the SWITCH command to go back, assuming EXTENDED copies exist.
3. Differentiate APREUSE(ERROR) from APCOMPARE(ERROR) in one sentence each.
4. Query SYSTRIGGERS.SQLPL. Which rebind command do you use when SQLPL is blank versus not blank?
5. REBIND PLAN(PAYPLAN) without PKLIST after someone added packages to collection NEWCOL. Does the plan see NEWCOL.*? What option would make it see them?