Explain Plans, Packages, and BIND in IBM Mainframes

34 / 60

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 doesn't run directly the way interpreted SQL might — it goes through a distinct precompile-and-BIND process that produces the actual executable access path DB2 uses at runtime, and understanding this pipeline is essential to explaining how a COBOL-DB2 program actually gets from source code to production execution. This is one of the more commonly misunderstood DB2 topics, and interviewers use it to separate developers who've only written SQL from those who've actually built and deployed DB2 programs.

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

When a program is precompiled, the DB2 precompiler extracts all embedded SQL into a DBRM (Database Request Module) and leaves modified source (with the SQL replaced by CALL statements) for the COBOL compiler to process normally. The DBRM alone isn't executable against the database yet — it must be BOUND, a process where DB2's optimizer analyzes the SQL, chooses access paths (which indexes to use, join order, and so on), and produces either a package or, in older/simpler setups, ties directly into an application plan.

A package is the bound, executable form of a single DBRM's SQL; a plan is a higher-level construct that a program actually binds to at execution time, and in modern DB2 usage a plan typically just references a list of packages rather than embedding SQL access paths directly — this indirection (packages inside a plan) makes it much easier to update one program's SQL and rebind just its package, without needing to rebind every other program sharing that plan.

A frequent interview question asks you to explain why REBIND is sometimes necessary even without any code change — the answer is that after RUNSTATS refreshes DB2's statistics about table sizes and data distribution, a REBIND lets the optimizer re-evaluate access paths against fresh statistics, potentially choosing a better plan than the one chosen (and frozen) at the last BIND, which is exactly why REBIND is a standard step after any significant data volume change.