Db2 – How to resolve this data source connection issue with Cognos

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

If you built your database at C:\Daten\db\db2\IBM\DB2, then that should be your path for your database directory. (ie, it should be one higher than where your database resides).

Also, just wondering what command you used when you built your database? Where did you specify that it lives?