A Couple of db2look Problems

I recently ran into a couple of db2look problems for different clients, and thought I’d share them in case someone else runs into them.

db2look is a tool used to mimic databases, objects, or statistics. If you are not familiar with it, check out my post on DB2 Basics: db2look.

db2look Problem with Packages Bound

This problem occurred when I was generating the syntax for a database to look at the syntax of a specific SQL stored procedure. I tend to generate full database syntax even when I’m looking only for a specific object because I kind of like having db2look files laying around – they tend to be handy. Also in some now-antiquated version, the options for pulling the syntax for only a single object were very unreliable, so I got into the habit of generating the file for the whole database.

Anyway, I went to run db2look and got this error (note, this was on a windows server – the same error could occur on other platforms):

PS D:\> db2look -d sample -e -a -x -o db2look.ddl
-- Generate statistics for all creators
-- Creating DDL for table(s)
-- Output is sent to file: db2look.ddl


SQL0805N  Package "NULLID.DB2L2K1M 0X4141414141644864" was not found.
SQLSTATE=51002

This error clearly tells me that there’s some issue with the binding of packages relating to db2look. This isn’t normal, as db2look will normally take care of the binds for me. To figure out which specific package I need to bind, I went to this useful DB2 reference page, and searched on the package name returned (DB2L2K1M). I find that the bind I need to perform is for the file db2lkfun.bnd:

Once I have that information, it’s a simple matter to bind the package. I happen to be using an ID that has dbadm. I connect to the database, and change directories to the bnd subdirectory of the SQLLIB directory (this is done from a powershell prompt – syntax for the cd may be different in a DB2 command window):

PS D:\> cd C:\"Program Files"\IBM\SQLLIB\bnd
PS C:\Program Files\IBM\SQLLIB\bnd> db2 bind db2lkfun.bnd blocking all grant public sqlerror continue

LINE    MESSAGES FOR db2lkfun.bnd
------  ----------------------------------------------         ----------------------
        SQL0061W  The binder is in progress.
        SQL0091N  Binding was ended with "0" errors and "0" warnings.

After this is complete, db2look ran as expected with no further problems.

db2look Problem when Run as Lower-Privilged ID

Now most of the time when I’m doing anything with a DB2 database, I have SYSADM and DBADM. So I don’t personally have many issues with permissions – but users come to me when they do. This issue was a user on a fairly newly-created database, so db2look wasn’t something they had run before as this user in this environment. They were using an application-specific ID to extract the syntax for a single table.

I was able to log in as the user in this case to exactly re-create the problem – which is nice when your security setup allows it.

The error they were getting was (this one is a Linux box):

app_user@server1:~$ db2look -d sample -e -z schema1 -t table1 -o table1.ddl
-- No userid was specified, db2look tries to use Environment variable USER
-- USER is: APP_USER
-- Specified SCHEMA is: SCHEMA1
-- The db2look utility will consider only the specified tables 
-- Creating DDL for table(s)

-- Schema name is ignored for the Federated Section
-- Output is sent to file: table1.ddl
--An error has occured during Binding

Error Message = 
SQL0551N  The statement failed because the authorization ID does not have 
the required authorization or privilege to perform the operation.  
Authorization ID: "APP_USER".  Operation: "EXECUTE". Object: 
"NULLID.DB2L2K1S".  SQLSTATE=42501


SQLCA   
Size    = 136
SQLCODE = -551
Tokens  = APP_USER � EXECUTE � NULLID.DB2L2K1S
RDS fn  = SQLRA13D
RC      = 0x801A006D = -2145779603
Reason  = 0x0000 = 0
Reason2 = 0x0000 = 0
Line #  = -20
Warning flags =         

In this case, DB2 is attempting to bind the db2look packages for us, but is unable to because this lower-privileged user does not have permissions needed to perform the binds or use the bound packages.

In this case, the easiest solution is to explicitly bind the packages, granting access to PUBLIC. To do that, you must know the names of the three packages related to db2look:

• db2look.bnd
• db2lkfun.bnd
• db2lksp.bnd

Once you know the package names, it is just a matter of changing directories to the bnd sub-directory of the SQLLIB directory and connecting to the database as a privileged user and performing the binds with the grant to PUBLIC. Note that you want to be careful of what permissions you give to PUBLIC – I tend to like to make sure PUBLIC does not have connect authority, which makes it a group for any permission you want to give to anyone who can connect to the database. Some restrict PUBLIC’s use even more than that, so depending on your security set up, you may be doing the grant differently.

db2inst1@server1:~/sqllib/bnd$ db2 connect to sample

   Database Connection Information

 Database server        = DB2/LINUXX8664 10.5.5
 SQL authorization ID   = DB2INST1
 Local database alias   = SAMPLE

db2inst1@server1:~/sqllib/bnd$ db2 "bind db2look.bnd blocking all grant public sqlerror continue"

LINE    MESSAGES FOR db2look.bnd
------  ----------------------------------------------         ----------------------
        SQL0061W  The binder is in progress.
        SQL0091N  Binding was ended with "0" errors and "0" warnings.
db2inst1@server1:~/sqllib/bnd$ db2 "bind db2lkfun.bnd blocking all grant public sqlerror continue"

LINE    MESSAGES FOR db2lkfun.bnd
------  ----------------------------------------------         ----------------------
        SQL0061W  The binder is in progress.
        SQL0091N  Binding was ended with "0" errors and "0" warnings.
db2inst1@server1:~/sqllib/bnd$ db2 "bind db2lksp.bnd  blocking all grant public sqlerror continue"

LINE    MESSAGES FOR db2lksp.bnd
------  ----------------------------------------------         ----------------------
        SQL0061W  The binder is in progress.
        SQL0091N  Binding was ended with "0" errors and "0" warnings.

Once these three binds were complete, I logged back in as the application-specific id and tried the db2look again:

app_id@server1:~$ db2look -d sample -e -z schema1 -t table1 -o table1.ddl
-- No userid was specified, db2look tries to use Environment variable USER
-- USER is: APP_ID
-- Specified SCHEMA is: SCHEMA1
-- The db2look utility will consider only the specified tables 
-- Creating DDL for table(s)

-- Schema name is ignored for the Federated Section
-- Output is sent to file: table1.ddl