Db2 – What exactly is binding in DB2

db2

I recently crossed over from being a Java developer to an actual DBA in our company. I am learning the ropes, so to speak, about being a DBA (which is actually somewhat of a new position for our company).

I have seen several scripts where we run the command DB2 BIND bind_file other_parameters.

I am puzzled by what these do. I have asked our other DBAs, but they were not able to explain it to me in a way that made sense. I have looked at IBM's Information Center for the BIND command, but it wasn't clear to me either.

I know binding is somehow important, because we are supposed to run REORGS, run STATS, and re BIND on our databases regularly to assist with performance.

Since I am still a n00b DBA, I was wondering if anyone can provide a "What is BINDing for Dummies?" explanation?

EDIT: In edition to the answer below, I recently came across the following developerworks article: "DB2 packages: Concepts, examples,
and common problems: Understanding DB2 system and user application
packages". Very helpful. Especially for the system packages, which
is what we were mostly running into.

20130905 EDIT: This blog entry by DB2 DBA Ember Crooks is stellar with regards to binding and what it means. She also wrote a
previous entry on packages not being found and when to up the
CLIPKG number for the binds and what that means. These articles are
very well explained. Basically like reading "DB2 Binding and Packages
for Dummies" if such a thing existed.

Best Answer

I see your Info Center link goes to LUW 9.7, and you mention that you've programmed in Java, but most of the experience I have with binding is with DB2 on the Mainframe with COBOL. So, you may need to adapt the explanation a bit (but generally, the concepts should be the same).

I believe that binding is only relevant when you are compiling programs that include embedded SQL which is precompiled (statically bound SQL). If, for example, you're using JDBC, you aren't required to run a BIND. The JDBC driver will PREPARE the statement dynamically.

When you run a program through a DB2 pre-compiler, PRECOMPILE runs through your program, and if it finds any embedded SQL (in COBOL, these are statement blocks that go from EXEC SQL to END-EXEC.), it carefully rips the SQL out, and replaces it with a call to the COBOL-DB2 interface. After this, there are two outputs of the PRECOMPILE, the COBOL source that has had all the embedded SQL removed (A from now on), and a DBRM that contains all the SQL that was removed (B).

Precompile does do some basic syntax checking, but be aware that the checks are only based on your table declarations within the program. It doesn't attach to DB2 to verify these!

These two files are completely separate, and when you run the COBOL program, it has to find an A and a B that were generated at the same time.

At this point, A is compiled and linked with the standard COBOL compiler into a load module and placed in a load library to be used later.

However, there is still a lot of work to be done with B, the DBRM. This is where BIND comes in. BIND is sort of like a compiler for the embedded SQL code, and the output of the "compile" is a package.

In order to BIND the SQL into an executable "package", the BIND process attaches to DB2 and does a few things:

  • Verifies that the current AuthID is authorized to perform a bind.
  • Checks the syntax of your SQL, with help from the data in the DB2 catalog.
  • Finally, and most importantly, the bind will optimize your SQL

During the last step, all of your SQL is run through the Optimizer, which takes into account all of the statistics and various paths that the DB2 engine could take to fetch your data. It then chooses the path it came up with that has the lowest cost associated (with newer versions of DB2 [DB2 10 for z/OS], it may decide to take a "higher cost", but "lower risk" path). Once the path is selected, it is compiled and becomes a package, which is stored in the catalog (you can see all of your current packages with SELECT * FROM SYSIBM.SYSPACKAGE (z/OS)).

Finally, there is a last piece that allows our programs to reunite with their packages, the PLAN. You create a plan by doing another BIND (BIND PLAN). A plan is a collection of packages that the program is allowed to look through to find the package that shares the same name. With COBOL, you specify which plan the program should search in in your JCL.

In short, compiled code goes through these steps to generate a usable BIND PLAN:

Precompile -> Creates a DBRM (with C[++], the precompiler outputs the precompiled SQL to an HFS file, which can be sent through the command-line bind program) -> the DBRM is optimized and a set of access paths (a package) is created -> The package is added to a BIND PLAN, which is a group of packages that allow you to create a "search path" for your programs to look through.

Since these programs are statically bound, if your table statistics change drastically, then the access path the optimizer chose at bind-time might not be the best path anymore, and re-binding will allow it to re-evaluate the SQL and perhaps choose a better path.

Edit (update for comment): If you are using the command line processor, you can pass in either a single bind package (.bnd), or a list of bind filenames (.lst). If you pass in a list, the filename has to be prepended with a @ (e.g. /path/to/@packages.lst). Inside the .lst file, you can either put each package on an individual line, or you can separate them with +:

package1.bnd
package2.bnd
package3.bnd+package4.bnd+package5.bnd
Related Question