DB2 PLANMGMT, APREUSE, and access path BIND options

ACTION

ACTION says whether the bind replaces an existing object or only adds a new one.

• ACTION(REPLACE) — default for BIND PACKAGE and BIND PLAN. Replaces the catalog entry with the same identifier. If nothing exists, Db2 creates it. If the bind fails, the old object remains. On REPLACE, the OWNER of the new object becomes owner; prior grants are rewritten to name the new owner as grantor.
• REPLVER(version-id) — BIND PACKAGE only. Replace a specific version. If that version is not in the catalog, the bind fails. version-id is not folded to uppercase. Default version-id comes from the DBRM (MEMBER) or COPYVER (COPY).
• RETAIN — BIND PLAN only. Keeps EXECUTE privileges when replacing the plan. Without RETAIN, everyone except the new owner loses EXECUTE (BIND remains). If ownership changes, the new owner grants BIND to the previous owner; with RETAIN they also get EXECUTE.
• ACTION(ADD) — default for BIND SERVICE. Fails if the name already exists. A failed ADD leaves no catalog row.

You cannot REPLACE a remote package that was bound ENABLE or DISABLE. REPLVER fails if the incoming version already exists, or if the version you named to replace does not exist.

PLANMGMT

PLANMGMT applies to REBIND PACKAGE and REBIND TRIGGER PACKAGE (not BIND PACKAGE, and not REBIND PLAN). It keeps extra copies of the package in the catalog and directory so you can SWITCH back after a bad rebind. Only one copy is current; the others are inactive.

PLANMGMT(EXTENDED)

Discard the previous copy. The current copy becomes previous. If no original exists, Db2 clones current as original; if an original exists, it stays. The incoming rebind result becomes current. EXTENDED is the usual PLANMGMT subsystem-parameter default and is required for rebind phase-in (new copy takes effect as threads commit).

PLANMGMT(BASIC)

Discard previous. Current becomes previous. Incoming becomes current. An existing original remains, but BASIC does not create one.

PLANMGMT(OFF)

Replace the current copy. Previous and original are left alone—unless the same command also changes OWNER, QUALIFIER, ENABLE, DISABLE, PATH, PATHDEFAULT, IMMEDWRITE, BUSTIMESENSITIVE, SYSTIMESENSITIVE, or ARCHIVESENSITIVE. Then Db2 purges all saved copies. Specifying EXTENDED or BASIC together with a change to those options is an error. If PLANMGMT is only inherited from the ZPARM and you change those options, Db2 internally disables PLANMGMT and purges copies. Repeating the same OWNER/QUALIFIER values without changing them is ignored, so PLANMGMT still applies.

Pattern characters (*) on REBIND still honor PLANMGMT per package. SYSPACKAGE.PLANMGMT is blank for a brand-new BIND PACKAGE. Native SQL routines created with CREATE inherit the PLANMGMT ZPARM in SYSPACKAGE. Copies appear in SYSIBM.SYSPACKCOPY. APRETAINDUP(YES) (REBIND default) keeps an old copy even when access paths did not change; NO drops that duplicate when PLANMGMT BASIC or EXTENDED is in effect.

APREUSE

APREUSE tries to reuse previous access paths from the directory as hints. Reuse is not guaranteed: missing indexes, version incompatibilities, or incomplete hints (a merge join hint may lack matching column names) can fail.

• APREUSE(NONE) or NO — do not reuse. Default.
• APREUSE(ERROR) — reuse; if a statement that had a previous path cannot reuse it, that package’s bind ends and Db2 continues with the next package. Brand-new or changed statements never fail the operation just because no old path exists.
• APREUSE(WARN) — reuse where possible; generate a new path where reuse fails; the package still completes.

On BIND PACKAGE, Db2 looks for a matching location, collection, name, and version. If that version is missing, it tries the most recently created matching package and issues DSNT294I. DSNT292I means no previous package was found and APREUSE is ignored. DSNT286I reports how many statements reused versus how many could not. APREUSESOURCE picks which PLANMGMT copy to reuse from. Successful reuse sets PLAN_TABLE.HINT_USED to APREUSE; failures put detail in REMARKS. APREUSE is not valid for REBIND of advanced triggers.

After a version migration, APREUSE(WARN) is a common fleet-wide REBIND: you pick up runtime improvements in the package format while keeping old access paths where they still work, and you get messages for the rest.

APCOMPARE

APCOMPARE compares old and new access paths for matching statements.

• NO or NONE — no compare (default).
• WARN — if paths are structurally different, issue DSNT285I and continue.
• ERROR — same message, but stop that package.

APCOMPARE is ignored for packages bound before DB2 9 (check RELBOUND on SYSPACKAGE or SYSPACKCOPY). Statements that reference temporal or archive tables may be bound twice (implicit query transformation); Db2 then matches on statement text plus the same implicit predicates (PLAN_TABLE.EXPANSION_REASON). Combine APCOMPARE with APREUSE and EXPLAIN(ONLY) to see reuse versus genuine change before you commit a production rebind.

EXPLAIN bind option

The bind option EXPLAIN is not the SQL EXPLAIN statement, but it fills the same tables.

• EXPLAIN(NO) — default for BIND; no EXPLAIN rows.
• EXPLAIN(YES) — insert rows for every explainable statement in the plan or package (not statements inside packages listed on a plan, not EXPLAIN statements themselves, and not SQL against declared temporary tables, which are incrementally bound at run time). owner.PLAN_TABLE must exist (base table or alias). If it is missing, VALIDATE decides whether the bind fails.
• EXPLAIN(ONLY) — populate EXPLAIN tables without inserting or updating SYSPACKAGE or keeping a new directory package. ACTION(REPLACE) will not drop an existing version. The binder does not need EXECUTE on the statements. BIND_EXPLAIN_ONLY = 'Y' marks those PLAN_TABLE rows. If APREUSE or APCOMPARE fails under ONLY, rows are still written so you can read HINT_USED and REMARKS. ONLY is invalid for advanced trigger REBIND and for BIND SERVICE / REST service REBIND.

QUERYNO in the inserted rows is the precompiler statement number from the DBRM. Remote objects are not explained. Automatic rebind uses EXPLAIN(YES) only if the package was bound that way and installation EXPLAIN PROCESSING is YES. A REBIND that fails only because PLAN_TABLE is wrong rolls back changes but does not invalidate the package; other REBIND failures with RC greater than 4 do invalidate.

OPTHINT

OPTHINT('hint-id') tells static SQL to use PLAN_TABLE rows whose OPTHINT column matches that id (up to 128 characters, single quotes only). Blank means normal optimization (the default). The same value seeds CURRENT OPTIMIZATION HINT for dynamic SQL. The package does not inherit OPTHINT from the plan. The OPTHINTS subsystem parameter must be YES or hints are ignored.

DYNAMICRULES

DYNAMICRULES controls authorization, default schema, which precompiler-like options parse dynamic SQL, and whether GRANT/REVOKE/DDL can run dynamically. Behaviour also depends on whether the package runs stand-alone or as (or under) a stored procedure or user-defined function.

RUN is the usual default for stand-alone programs: CURRENT SQLID and CURRENT SCHEMA, and dynamic DDL is allowed. BIND is how shops make dynamic SQL inside a COBOL package use the same owner and qualifier as static SQL (CURRENT SCHEMA is ignored). DEFINE* uses the routine owner when the package runs in a routine; INVOKE* uses the invoker. BIND, define, and invoke behaviours share extra limits (dynamic GRANT/DDL typically not allowed). DYNAMICRULES on BIND PLAN only affects packages that inherit a missing value or packages generated from MEMBER. It is not valid for REBIND of native SQL procedures, REST services, or advanced triggers.

VALIDATE and SQLERROR

VALIDATE(RUN) (default) lets the bind succeed with warnings if objects or privileges are missing; Db2 checks again at run time with the owner’s ID. VALIDATE(BIND) fails the bind if they are missing, except BIND PACKAGE with SQLERROR(CONTINUE), and except some CREATE/ALTER/DROP names and LOCK TABLE, which are still deferred.

• SQLERROR(NOPACKAGE) — default for BIND PACKAGE and BIND SERVICE; no package if an SQL error occurs.
• SQLERROR(CONTINUE) — still create the package; statements in error cannot run.
• SQLERROR(CHECK) — syntax and semantic check without creating a package, even if the binder cannot execute the statements. An existing version is not replaced.

SQLERROR is not a REBIND PACKAGE option; the previous setting remains. Statements that were in error under CONTINUE are not rebound.

FLAG, ROUNDING, QUERYACCELERATION

FLAG controls bind messages: I (all, default), W (warning and worse), E (error and completion), C (completion only). Use FLAG(E) on REBIND * of many packages so message storage does not fill.

ROUNDING is the DECFLOAT rounding mode at bind time: HALFUP (default), CEILING, DOWN (truncate toward 0), FLOOR, HALFDOWN, HALFEVEN, UP. It matters when the package does decimal-floating-point arithmetic.

QUERYACCELERATION binds static queries for IBM Db2 Analytics Accelerator and seeds CURRENT QUERY ACCELERATION for dynamic SQL. There is no bind default; if you omit it, the QUERY_ACCELERATION ZPARM initializes the register. Values:

• NONE — do not accelerate static queries in this package.
• ENABLE — accelerate if cost and heuristic checks pass; failures at run time return a negative SQLCODE.
• ENABLEWITHFAILBACK — like ENABLE, but the first OPEN can fall back to Db2 if the accelerator is unavailable; after a successful accelerator OPEN, failback is not possible.
• ELIGIBLE — ignore cost/heuristics; still fail the statement on accelerator errors.
• ALL — every static query must be accelerable or the BIND fails (except DGTT/CGTT references).

Acceleration applies to cursor queries, INSERT FROM SELECT, and local static SELECT INTO. A remote SELECT INTO bound for acceleration fails at run time. Accelerated statements show SYSPACKSTMT.STATUS = 'O'.

A careful production rebind

1
2
3
4
REBIND PACKAGE(PAYROLL.PAYUPD.(*)) -
  PLANMGMT(EXTENDED) -
  APREUSE(WARN) APCOMPARE(WARN) -
  EXPLAIN(ONLY) FLAG(E)

Read PLAN_TABLE for HINT_USED and REMARKS. When you are satisfied, drop ONLY and rebind for real with EXPLAIN(YES) so the directory copy is replaced and you still have previous and original copies to SWITCH to.

Explain It Like I'm Five

PLANMGMT is keeping yesterday’s Lego model on the shelf when you rebuild today’s. EXTENDED keeps the first masterpiece, yesterday’s model, and today’s. APREUSE means “try to build it the old way.” WARN lets you change a piece if the old way will not fit; ERROR stops the rebuild if a piece will not fit. APCOMPARE is a friend who looks at both models and shouts if they differ. EXPLAIN is taking a photograph of the instructions. EXPLAIN(ONLY) takes the photo without replacing the model on the table. VALIDATE is whether missing bricks fail the build now or when you actually play. DYNAMICRULES is whose permission slip you show when you make up a new instruction at play time instead of following the printed ones.

Exercises

1. For one production package, query SYSPACKAGE and SYSPACKCOPY. How many copies exist, and what is PLANMGMT?
2. Write a REBIND that uses PLANMGMT(EXTENDED) APREUSE(ERROR) EXPLAIN(ONLY). What column tells you the rows did not come from a real replace?
3. Explain why changing QUALIFIER on the same REBIND as PLANMGMT(EXTENDED) fails.
4. A package uses DYNAMICRULES(RUN) and QUALIFIER(PRODHR). A PREPARE of SELECT * FROM EMP uses which schema if CURRENT SCHEMA is TESTHR?
5. Choose FLAG for a REBIND of COLL.* with thousands of packages, and say why.