Posted by When connecting between different versions of DB2, you generally need to bind packages from the different versions against the DB2 database. Just because you’ve bound db2ubind and db2cli on the server does not mean that all possible clients connecting in are covered. Sometimes binds happen implicitly, but sometimes they don’t. And you don’t necessarily want to grant a user rights to bind packages just because they’re going to need to do it once every 6 months when you apply a Fixpack or upgrade. It used to be that we didn’t have much choice in the matter – we had to get a client of the right version to bind from that client. But, luckily, in recent years, IBM has provided us with the files we need to perform binds for any version and Fixpack we may need.
Scenario
After a client’s recent upgrade (from 9.5 to 10.1), they reported getting this error when running SQL from the Control Center:
SQL0572N Package "NULLID.SQLC2H20" is inoperative
I had, of course, run binds for db2ubind, db2cli, db2schema, and an overall db2rbind just after the upgrade. But this error clearly tells me that there’s something not bound – likely db2ubind.
I proceeded to ask the client their version, and they sent me this screen shot:
That translates to 9.7 Fixpack 0. I suggested the client be upgraded (preferably to 10.1, which is the server version), but also proceeded to get the bind files and bind them.
Resolution
First I had to get the bind files. I had no desire to install 9.7 Fixpack 0 on any of my test systems, so I got the files from the IBM site: http://www-01.ibm.com/support/docview.wss?uid=swg21501302
Each of the files there contains directories for each fixpack.
After uploading the file to my system and untarring/uncompressing it, I have these directories:
fixpak00a_s090610 fixpak02_s100514 fixpak04_s110330 fixpak07_s121002
fixpak00_s090521 fixpak03a_s101006 fixpak05_s111017 fixpak08_s130316
fixpak01_s091114 fixpak03_s100901 fixpak06_s120629 fixpak09_s131204
Note that in each directory name is the Build level that matches up to the value in the screen shot above – I can use that to understand which directory I need in this case. I think it is possible that if you have a special build, these numbers might not match up.
Changing directories into fixpak00_s090521, I see the normal list of bind files like I would in $HOME/sqllib/bnd. It is then an easy matter to connect to the database and issue the bind command. I do get some fairly normal warnings.
$ db2 connect to redacted
Database Connection Information
Database server = DB2/LINUXX8664 10.1.4
SQL authorization ID = DB2INST1
Local database alias = REDACTED
$ db2 "bind @db2ubind.lst blocking all grant public"
LINE MESSAGES FOR db2ubind.lst
------ --------------------------------------------------------------------
SQL0061W The binder is in progress.
LINE MESSAGES FOR db2ueiwi.bnd
------ --------------------------------------------------------------------
2239 SQL0204N "SYSTEM.SYSUSERAUTH" is an undefined name.
SQLSTATE=42704
2243 SQL0204N "SYSTEM.SYSUSERAUTH" is an undefined name.
SQLSTATE=42704
LINE MESSAGES FOR db2clpnc.bnd
------ --------------------------------------------------------------------
SQL0595W Isolation level "NC" has been escalated to "UR".
SQLSTATE=01526
LINE MESSAGES FOR db2arxnc.bnd
------ --------------------------------------------------------------------
SQL0595W Isolation level "NC" has been escalated to "UR".
SQLSTATE=01526
LINE MESSAGES FOR db2ats_sps.bnd
------ --------------------------------------------------------------------
1173 SQL0204N "SYSTOOLS.ADMINTASKS" is an undefined name.
SQLSTATE=01532
1203 SQL0204N "SYSTOOLS.ADMINTASKS" is an undefined name.
SQLSTATE=01532
1234 SQL0204N "SYSTOOLS.ADMINTASKS" is an undefined name.
SQLSTATE=01532
1482 SQL0204N "SYSTOOLS.ADMINTASKS" is an undefined name.
SQLSTATE=01532
1499 SQL0204N "SYSTOOLS.ADMINTASKS" is an undefined name.
SQLSTATE=01532
1517 SQL0204N "SYSTOOLS.ADMINTASKS" is an undefined name.
SQLSTATE=01532
1555 SQL0204N "SYSTOOLS.ADMINTASKS" is an undefined name.
SQLSTATE=01532
1679 SQL0204N "SYSTOOLS.ADMINTASKS" is an undefined name.
SQLSTATE=01532
1696 SQL0204N "SYSTOOLS.ADMINTASKS" is an undefined name.
SQLSTATE=01532
1715 SQL0204N "SYSTOOLS.ADMINTASKS" is an undefined name.
SQLSTATE=01532
1732 SQL0204N "SYSTOOLS.ADMINTASKS" is an undefined name.
SQLSTATE=01532
1895 SQL0204N "SYSTOOLS.ADMINTASKSTATUS" is an undefined name.
SQLSTATE=01532
1950 SQL0204N "SYSTOOLS.ADMINTASKSTATUS" is an undefined name.
SQLSTATE=01532
1962 SQL0204N "SYSTOOLS.ADMINTASKS" is an undefined name.
SQLSTATE=01532
1979 SQL0204N "SYSTOOLS.ADMINTASKSTATUS" is an undefined name.
SQLSTATE=01532
LINE MESSAGES FOR db2adminotm.bnd
------ --------------------------------------------------------------------
342 SQL0204N "SYSTOOLS.ADMIN_MOVE_TABLE" is an undefined name.
SQLSTATE=01532
371 SQL0204N "SYSTOOLS.ADMIN_MOVE_TABLE" is an undefined name.
SQLSTATE=01532
455 SQL0204N "SYSTOOLS.ADMIN_MOVE_TABLE" is an undefined name.
SQLSTATE=01532
536 SQL0204N "SYSTOOLS.ADMIN_MOVE_TABLE" is an undefined name.
SQLSTATE=01532
570 SQL0204N "SYSTOOLS.ADMIN_MOVE_TABLE" is an undefined name.
SQLSTATE=01532
590 SQL0204N "SYSTOOLS.ADMIN_MOVE_TABLE" is an undefined name.
SQLSTATE=01532
671 SQL0204N "SYSTOOLS.ADMIN_MOVE_TABLE" is an undefined name.
SQLSTATE=01532
767 SQL0204N "SYSTOOLS.ADMIN_MOVE_TABLE" is an undefined name.
SQLSTATE=01532
903 SQL0204N "SYSTOOLS.ADMIN_MOVE_TABLE" is an undefined name.
SQLSTATE=01532
LINE MESSAGES FOR db2ubind.lst
------ --------------------------------------------------------------------
SQL0091N Binding was ended with "0" errors and "28"
warnings.
Very fresh info: From windows client V10.5.5 to v9.7.10 linux database, regular bind won’t do the job . I had to add ” sqlerror continue” at the end of bind command.
I’m just wondering: What if we use these IBM files and bind all packages from all versions as an step after applying fixpacks and migrations?
This way it should proactively cover and avoid bind issue from any client version.
Can anyone think about any capacity or performance impact by binding all packages? From last time I count it was 75 versions/FP’s.
There is the overhead of performing the binds and the space that all of those packages take up. I have never heard of someone going that extreme with it. I don’t think it would have runtime consequences, but you’d want to test to be sure since it isn’t best practice or a normal practice.