Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 3 down vote accepted

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
share|improve this answer
    
If I may so add to my question and thus your answer...what is the difference between .lst and .bnd files for binding? I've noticed we bind in some .lst files (usually DB2 BIND @myFile.lst ....) and .bnd files (same but without the @ sign). Could you add those to your answer too? –  Chris Aldrich Apr 17 '12 at 12:48
1  
@ChrisAldrich: the answer has been updated. Basically, the .bnds are the bind files, and the .lsts are lists of bind files. –  bhamby Apr 17 '12 at 13:17
    
another question. I guess it puzzles me still.... The .bnd and .lst files we are binding to are files that IBM has supplied with DB2 and not anything custom we have written to. So...I guess I'm still fuzzy on what exactly is binding to what. How does IBM know what we have? or vice versa? What does running binds with these files do exactly? Hope this doesn't frustrate. Like I said really looking for a "for dummies" style answer cause it is still fuzzy to me. –  Chris Aldrich Apr 17 '12 at 20:15
    
Accepting your answer, but still desiring clarification. –  Chris Aldrich Apr 18 '12 at 12:51
1  
Sorry about that, you caught me right before leaving on a vacation! Anyway, to answer your question, what I'm guessing that you're binding is db2ubind.lst and/or db2cli.lst. These files are automatically created when a new database is created on the server, and these files allow various remote client utilities to work (CLI/ODBC support; DB2 CLP; import/export utilities). Check out this link –  bhamby Apr 23 '12 at 2:48

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.