Application plans in DB2 for z/OS

What an application plan is

The DSN subcommand BIND PLAN builds an application plan. IBM’s command reference wording is the definition to memorize: all Db2 programs require an application plan to allocate Db2 resources and support SQL requests made at run time. The catalog row is SYSIBM.SYSPLAN. The directory holds control information used when the plan is allocated into the EDM pool.

In the 1980s a plan often contained every DBRM in the application. Today a healthy plan is thin: a name, some plan-level options (isolation default, release, cache size, enable/disable), and a package list. You rebind the plan rarely — when the list of collections changes — and you rebind packages often when statistics or indexes change.

Plan packages

Plan packages are simply the packages the plan is allowed to run. You designate them with PKLIST. A plan can specify individual packages, entire collections, or a mix. At run time Db2 searches that list for a package that matches the consistency token coming from the load module.

1
2
3
4
5
6
7
8
DSN SYSTEM(DB2A)
BIND PLAN(PAYPLAN) -
  PKLIST(PAYROLL.*, COMMON.*, HR.EMPINQ) -
  ACTION(REPLACE) RETAIN -
  ISOLATION(CS) RELEASE(COMMIT) -
  CACHESIZE(1024) -
  OWNER(PAYBIND) QUALIFIER(HR)
END

PAYROLL.* is the pattern you want for an application collection: add a new program, BIND PACKAGE into PAYROLL, ship the load module, and the existing plan finds it without a plan rebind. Naming a single package (HR.EMPINQ) is stricter — useful for a one-program plan or for locking down a sensitive package.

The binder needs EXECUTE on each package (or PACKADM / SYSADM / DATAACCESS) to put it on the PKLIST. End users need EXECUTE on the plan (typical for CICS/IMS transactions) or on the package, depending on how the shop grants access.

PLAN as a runtime name

Operations cares about the plan name more than about collections. CICS DB2CONN/DB2ENTRY (or RCT in older shops), IMS SSM, TSO RUN, and CAF/RRSAF OPEN all name a PLAN. If allocation fails, the transaction never reaches the first SQL. Typical failures: plan does not exist, not authorized, ENABLE list excludes CICS, or the plan is inoperative after a failed automatic rebind.

• SPUFI and many DSNTIAUL/DSNTEP2 jobs use a plan created at install (often DSNESPCS / DSNTEP2)
• Batch COBOL names the plan on the RUN subcommand or in the attachment call
• CICS associates transactions with a plan (or with a pool plan plus package lists)

You bind plans locally even when they reference packages that run at a remote location. Bind those remote packages at the remote server. Location-qualified PKLIST entries and CURRENTSERVER are the distributed pieces.

BIND PLAN options you will actually set

MEMBER is deprecated

BIND PLAN still accepts MEMBER(dbrm, …). IBM states that the option is deprecated. When you specify MEMBER, Db2 binds those DBRMs into packages and includes the packages in a package list for the plan. Use that path only when you cannot run BIND PACKAGE. New automation should be BIND PACKAGE then BIND/REBIND PLAN PKLIST.

ENABLE and CACHESIZE

IBM’s sample BIND PLAN(IMSONLY) uses ENABLE(IMS) so TSO batch cannot allocate the plan. A CICS-only plan can ENABLE(CICS) CICS(applid). CACHESIZE(0) is appropriate when many different users run the plan once; a larger cache helps when the same authorization ID runs the plan repeatedly while it stays in the EDM pool.

1
2
3
4
5
6
7
8
BIND PLAN(IMSONLY) -
  PKLIST(DSN8IC12.*) -
  ACTION(ADD) -
  ISOLATION(CS) -
  OWNER(DEPTM92) -
  QUALIFIER(PRODUCTN) -
  CACHESIZE(0) -
  ENABLE(IMS)

REBIND PLAN and the package list

Rebind a plan when you change attributes such as the package list, not when you change COBOL SQL (that is BIND PACKAGE from a new DBRM). Rules:

• Omit PKLIST — keep the previous package list
• Specify PKLIST — replace the list
• NOPKLIST — delete the entire package list (the plan then has no packages to search)

1
2
REBIND PLAN(PAYPLAN) PKLIST(PAYROLL.*, COMMON.*)
REBIND PLAN(PAYPLAN) NOPKLIST

You can change any bind options on the plan when you rebind it. Isolation and release on the plan interact with package-level options: packages can inherit or override depending on the option (for example RELEASE(INHERITFROMPLAN) on a package).

PLANMGMT and the plan

PLANMGMT is easy to misread. It is not “management of the PLAN object” in the SYSPLAN sense. It is plan management policy for packages: keep extra bound copies so access paths can be switched. You specify PLANMGMT(OFF | BASIC | EXTENDED) on BIND/REBIND PACKAGE. The default policy is typically EXTENDED (up to three copies: current, previous, original).

From the plan’s point of view, the package name on the PKLIST does not change when you SWITCH copies. Threads keep allocating PAYPLAN and finding PAYROLL.PAYCALC. What changes is which compiled runtime structure is current. Automatic rebind of an invalid package replaces only the current copy; previous and original are untouched — which is why a SWITCH after a bad autobind/rebind is a standard recovery move.

FREE PACKAGE and BIND PACKAGE delete phased-out copies as part of that same machinery. PLANMGMTSCOPE on FREE lets you target inactive copies without dropping the active package the plan needs.

Plans versus packages in daily work

• New program — precompile/coprocessor, BIND PACKAGE into the application collection, no plan change if PKLIST already has collection.*
• SQL change — new DBRM, BIND PACKAGE ACTION(REPLACE) (or ADD a new VERSION)
• New index / RUNSTATS — REBIND PACKAGE (often with APREUSE and PLANMGMT), not REBIND PLAN
• New collection on an application — REBIND PLAN PKLIST(…)
• Invalid package — autobind or explicit REBIND PACKAGE; the plan stays

Query SYSPLAN for the plan, SYSPACKLIST for the package list, and SYSPACKAGE for each package. If a CICS transaction fails with a package-not-found condition, check PKLIST and CURRENT PACKAGESET before you assume the BIND never ran.

Explain It Like I'm Five

The plan is the lunch ticket you hand the cafeteria. It does not contain the food. It lists which shelves (collections) the cook may take meals (packages) from. You reprint the ticket only when the list of shelves changes. You recook a meal when the recipe (SQL) or the oven settings (statistics) change. PLANMGMT is keeping yesterday’s tray in the fridge so if today’s tray is burnt you can switch trays without printing a new ticket.

Exercises

1. Rewrite BIND PLAN MEMBER(A,B,C) as BIND PACKAGE commands plus BIND PLAN PKLIST.
2. Explain the difference between omitting PKLIST, specifying PKLIST, and NOPKLIST on REBIND PLAN.
3. When would ENABLE(CICS) on a plan be better than relying only on RACF/CICS security?
4. Why does adding a program to a collection.* plan not require REBIND PLAN?
5. Look up SYSPLAN and SYSPACKLIST for a plan at your site and name every collection on its PKLIST.