DB2intermediate
Plans, Packages, and BIND
Understand how embedded SQL becomes an executable access path through the precompile-BIND process, and what plans and packages actually are.
Embedded SQL does not run as written. It goes through a precompile and bind pipeline that produces the access path DB2 will actually use.
Precompile-and-BIND is like a restaurant pre-planning the exact prep steps for a dish based on today's specific inventory — the recipe (SQL) doesn't change, but the kitchen's step-by-step execution plan (access path) gets refreshed whenever the pantry's stock (statistics) meaningfully changes, which is why a periodic REBIND after restocking (RUNSTATS) can produce a noticeably faster dish.
Key Concepts
1
The pipeline.
source with EXEC SQL
| DB2 precompiler
+--> DBRM the extracted SQL
+--> modified source, SQL replaced by CALLs
| COBOL compiler and binder
+--> load module2
DBRM --BIND--> package the executable access path3
The load module and the package are separate artefacts, produced from the same source, and both must be
present and in step at runtime.
4
BIND is where the optimiser decides. It reads the SQL, looks at the catalog statistics, and chooses:
which index to use, the join order, the join method, whether a sort is needed.
5
That decision is made once, at bind time, and frozen into the package. It is not re-evaluated when the
program runs.
6
Package and plan.
package one DBRM's bound SQL
plan what the program executes under; in modern use, a list
of packages7
The indirection matters operationally. With SQL bound directly into a plan, changing one program means
rebinding the whole plan and every program in it. With packages, you rebind the one package that changed.
8
Which explains the question interviewers ask: why REBIND without a code change?Because the access path was chosen from the statistics as they were at bind time. The table then grew from
ten thousand rows to ten million.
9
at BIND: table had 10,000 rows -> nested loop is cheapest
now: table has 10,000,000 -> nested loop is a disaster10
The SQL is unchanged and the plan is unchanged, because nothing re-evaluates it. RUNSTATS refreshes the
statistics, and REBIND lets the optimiser choose again with the new numbers.
RUNSTATSREBIND
11
That is why the standard maintenance sequence is REORG, then RUNSTATS, then REBIND — and why skipping the
rebind wastes the other two.
12
What can go wrong. A rebind can also produce a worse path, so production rebinds are done with
EXPLAIN beforehand and plan management enabled, which keeps the previous access path so it can be
switched back.
EXPLAIN
13
The mismatch to recognise. -805 at runtime means the package the program is looking for is not in the
collection it is bound to — usually a load module deployed without its package, or bound into the wrong
collection. The program is fine; the pair is not.
-805
14
What the interviewer is probing.1. "What is the difference between a plan and a package?" Probing: the fundamentals. Stalls:
"Nothing." Moves up: a package holds the access paths for one program's SQL; a plan binds a
collection of packages for execution.
15
2. "Why did packages replace binding everything into a plan?" Probing: the reason. Stalls:
"They are newer." Moves up: changing one program then requires rebinding only its package, not the
whole plan and every program in it.
16
3. "When must you rebind?" Probing: the operational trigger. Stalls: "Never." Moves up:
after a recompile, after RUNSTATS changes statistics materially, and after index or schema changes —
otherwise old access paths persist.
17
4. "What is the risk of a rebind?" Probing: honesty. Stalls: "None." Moves up: the
optimiser may pick a worse access path, which is why plan management lets you keep and switch back
to the previous one.