IBM Support

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:
| | | | | db2advis_setup entry
| | | | | | parser entry
(snip)
| | | | | | | | sqljrDrdaArExecute exit [rc = 0x8037006D = -2143879059 = SQLJR_CABLT]
| | | | | | | sqlacall error [probe 3317]
| | | | | | | | sqlofica entry
| | | | | | | | | pdLog entry
(snip)
| | | | | | | | | sqlnxDbgPrint::printf entry
| | | | | | | | | sqlnxDbgPrint::printf exit [rc = 0x000001B7 = 439]
| | | | | | | | sqldesOutputMsg exit
| | | | | | | usage exit
| | | | | | parser exit [rc = 0xFFFFFC7B = -901]
| | | | | | report_error_with_loc entry
| | | | | | | report_diag_error entry
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:
  1. 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.
  2. See technote DB2ADVIS returning error -220
     

Related Information

db2advis

BIND Command

[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Component":"Programming Interface - CLI","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"9.7;9.5;9.1;10.1;10.5;11.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
17 April 2019

UID

swg21514525

Page Feedback