Binding Packages

I approach binding from a physical/system DBA point of view. I’m not an application developer, and don’t understand all the possible intricacies of binding. But there is some information and tips I can share on binding packages in DB2. There are definitely some application developer topics included here.

Static vs. Dynamic SQL

This is a basic division in how SQL is handled. I’m not sure that other RDBMSes handle it in quite the same way.

Static SQL is SQL where the structure of the SQL does not change based on any factors in the execution, so Application Developers may choose to have DB2 generate access plans ahead of time and store those access plans in the database as packages. The advantage is speed at execution time – since DB2 does not have to find the access plan at run time, the SQL execution is faster. The disadvantage is that the access plan may not change based on varying runstats information (depending on the REOPT option), and that the execution plans may not be able to take advantage of distribution statistics depending on how host variables are used within the SQL.

Dynamic SQL is intended for SQL where the structure or syntax of the SQL may change depending on variables at execution time. Theoretically, anything executed in a static way can also be executed in a dynamic way, but all dynamic statements could not be executed in the static way. The advantage of dynamic SQL is the flexibility. Using parameter markers with dynamic SQL, you can reduce the time to calculate execution plans for frequently executed SQL, however, the use of parameter markers also makes it so that your statements cannot make use of distribution statistics – which can be a negative performance impact, especially for unevenly distributed data.

What is Bind?

The process of preparing program files containing static SQL looks like this:

1. Precompile program file – this separates out the code from the SQL, and puts the SQL in a separate bind file.
2. Compile the code file using the high-level language compiler (C compiler, etc)
3. Link object modules with the high-level language libraries and DB2 libraries
4. Bind the SQL files created to a specific database using the BIND command

Binding System Packages

So why would a physical DBA ever need to mess with all this?

The fact is that there are some critical system packages for which the bind files are already provided by IBM that must be bound to the database at various times. Minimally after fixpacks, binding them is required and at some other times, binding them is also required. Generally, an administrative user will run the bind commands for these and grant permissions on the packages to every user who accesses the database. The three commands you’ll most often use for this are:

db2 bind @db2ubind.lst blocking all grant public
db2 bind @db2cli.lst blocking all grant public
db2 bind db2schema.bnd blocking all grant public sqlerror continue

cd to $INSTHOME/sqllib/bnd and connect to the database before issuing these commands.

Specifying a file with a ‘@’ tells DB2 that the files is a list of bind files and not a file itself. The files in the first two commands above are a list of bind files in that same directory. The ‘grant public’ tells DB2 to granting permissions on the resulting packages to the DB2 special group PUBLIC, which means essentially “anyone who can connect to the database”. Side note on security – whenever I create a database, I immediately revoke connect from PUBLIC, because if you don’t, it means that anyone who can log on to the server can connect to your database, which is not a good idea from a security standpoint.

Bind files are version and sometimes fixpack specific. Thus upgrading a database requires doing the binds. Binds may also be needed for each new version of client that you have connecting into a database. Usually this is as easy as going to the client and issuing the commands as a privileged user. Specific bind files, are available for download them here: http://www-01.ibm.com/support/docview.wss?uid=swg21501302

The system binds like this are required after every fixpack or upgrade. But there may be other situations that cue a bind. The main one that comes to mind is:

SQL0805N  Package "" was not found.

Whenever I encounter that error, my first response is to do the three binds above at the server. The second response if that doesn’t work is to do the three binds above from a client that matches the version and fixpack of the client receiving the error.

I’d love to hear from readers of any other error messages that trigger them to bind system packages.

Implicit Binds

If there is a particular user or group who is likely to be connecting in from various versions or clients, it may be worthwhile to grant them BINDADD database authority. In some scenarios, some client applications can implicitly bind packages from a new version or fixpack if the user connecting has this permission. The user will not even know the binds are taking place, though that particular connection or statement may appear to run slower while appropriate binds are done

CLIPKG issues

There is a known issue when statement handles are not being properly closed by an application and a clause may need to be added to the cli bind like this:

db2 bind @db2cli.lst blocking all grant public clipkg 30

See my blog entry on this issue for more information and a link to a IBM tech note on the issue.

RBIND

After runstats and reorgs you should always do an rbind. I prefer it with the ‘all’ flag. An rbind goes out and rebinds already bound packages. Without the ‘all’ flag, it will do invalidated packages only. With the ‘all’ flag, it will do all packages. The reason it is important to do this after running runstats is that the rbind causes access plans to be recalculated based on the newly collected runstats. Without this rbind, static SQL in your database will not see the performance benefits of runstats.