DB2 Plan

A DB2 plan is the executable form of a program's SQL. DB2 chooses the access strategy when the plan is bound, not when the program runs.

What a plan is

• A plan stores the access paths DB2 will use for a program's SQL statements: which indexes, which join order, which tablespaces.
• DB2 picks these paths at bind time using catalog statistics, so run time stays fast and predictable.
• Before a program's first SQL runs, DB2 must allocate the plan named for that program. No plan means no SQL.
• In CICS, the plan name usually comes from the DB2ENTRY definition for the transaction.

How a plan is built

• The DB2 precompile extracts the SQL from the source into a Database Request Module (DBRM).
• BIND PACKAGE binds one program's DBRM into a package, optionally tagged with a collection id.
• BIND PLAN binds the plan, listing the packages it contains in the PKLIST parameter.
• The plan and packages are stored in the DB2 catalog and directory, ready for execution.

Plan versus package

• A package is the bound form of one program's DBRM. A plan is a list of packages (or DBRMs) a transaction runs under.
• Binding packages separately means you can rebind one program without rebinding everything else.
• PKLIST on BIND PLAN names the packages, for example PKLIST(CUSTCOLL.*) for every package in a collection.
• Two classic bind-time errors: -805 (package not in the plan) and -818 (load module timestamp differs from the bound package).

Example

• A BIND PLAN job that binds a plan containing every package in the CUSTCOLL collection.
• //BINDPLAN EXEC PGM=IKJEFT01 //SYSTSIN DD * DSN SYSTEM(DB2P) BIND PLAN(CUSTPLAN) PKLIST(CUSTCOLL.*) ACTION(REPLACE) ISOLATION(CS) END /*
• ACTION(REPLACE) replaces the old plan. ISOLATION(CS) sets cursor stability locking for the plan.