SQL0818N/SQL0901N error when running db2advis

Troubleshooting

Problem

Running the db2advis command returns error message SQL0818N or SQL0901N.

Symptom

Related error information in the db2diag.log:

Error at line 11257, in file partadvise.SQC
DATA #2 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
sqlcaid : SQLCA sqlcabc: 136 sqlcode: -818 sqlerrml: 0
sqlerrmc:
sqlerrp : SQLRA145
sqlerrd : (1) 0x801A006D (2) 0x00000000 (3) 0x00000000
(4) 0x00000000 (5) 0xFFFFFD76 (6) 0x00000000
sqlwarn : (1) (2) (3) (4) (5) (6)
(7) (8) (9) (10) (11)
sqlstate: 51003

In some cases, SQL0901N instead of SQL0818N is returned and there is no corresponding message in db2diag.log. db2trc will show:

SQLCARD from DRDA flow will show SQLSTATE 51003 "Consistency tokens do not match" which corresponds to SQL0818N.

Cause

Packages required to run db2advis command were not bound

Resolving The Problem

To resolve the issue you will need bind the packages for the db2advis command to work.

1) Create a list file called db2advis.lst1 in the $HOME/sqllib/bnd (Unix/Windows) or C:\Program Files\IBM\SQLLIB\BND (Windows) directory with the following content

db2advis.lst

db2advcatalog.bnd+
db2advindex.bnd+
db2advio.bnd+
db2advis.bnd+
db2advmdc.bnd+
db2advmisc.bnd+
db2advmqt.bnd+
db2advsimulation.bnd+
db2advworkload.bnd+
gseClAdv.bnd+
mdcadvise.bnd+
partadvise.bnd

2) Connect to the database at the command line

For Example to connect to the SAMPLE database

db2 connect to SAMPLE

db2 "drop package nullid.db2advio"

3) Bind the list file using the following command

cd sqllib/bnd db2 "bind @db2advis.lst action replace blocking all grant public sqlerror continue messages bindadvs.out"

4) Re-run db2advis. If SQL0220N is returned see Note2

Notes:

Root required to create files in ~/sqllib/bnd (Unix/Linux). Alternative is to copy the contents of ~/sqllib/bnd to another directory then create db2advis.lst in new directory. Ensure there are no spaces at end of each file in db2advis.lst.
See technote DB2ADVIS returning error -220