IBM Support

DB2ADVIS returning error -220

Troubleshooting


Problem

db2advis tool can return error -220 even though the explain and advise tables do exist

Cause

This is caused by columns not having the proper definition or columns missing.

Diagnosing The Problem

When running db2advis tool, DB2 can return the following error:


    The db2advis tool needs the explain tables and the advise tables to exist. Please create them
    using the EXPLAIN.DDL script in the misc subdirectory of the sqllib directory.

    0 solutions were evaluated by the advisor

    exiting with error code [-220]

Resolving The Problem

1) Drop the explain and advise tables:

      db2 connect to <dbname>;

      db2 drop table EXPLAIN_INSTANCE;
      db2 drop table EXPLAIN_STATEMENT;
      db2 drop table EXPLAIN_ARGUMENT;
      db2 drop table EXPLAIN_OBJECT;
      db2 drop table EXPLAIN_OPERATOR;
      db2 drop table EXPLAIN_PREDICATE;
      db2 drop table EXPLAIN_STREAM;
      db2 drop table EXPLAIN_DIAGNOSTIC;
      db2 drop table EXPLAIN_DIAGNOSTIC_DATA;
      db2 drop table ADVISE_INSTANCE;
      db2 drop table ADVISE_INDEX;
      db2 drop table ADVISE_WORKLOAD;
      db2 drop table ADVISE_MQT;
      db2 drop table ADVISE_PARTITION;
      db2 drop table ADVISE_TABLE;
      db2 drop table EXPLAIN_ACTUALS;

Note: On DB2 v10.1 the table OBJECT_METRICS was also included as part of the set of tables created by the EXPLAIN.DDL script and should be dropped as below.

      db2 drop table OBJECT_METRICS;

2) Recreate them using the EXPLAIN.DDL script located in the directory $HOME/sqllib/misc:
      db2 -tvf EXPLAIN.DDL

3) Bind the db2advis packages below:
      db2advcatalog.bnd
      db2advindex.bnd
      db2advio.bnd
      db2advis.bnd
      db2advmdc.bnd
      db2advmisc.bnd
      db2advmqt.bnd
      db2advsimulation.bnd
      db2advworkload.bnd

These bind files are located under the Instance owner's home directory $HOME/sqllib/bnd.
To bind the files you can run the following command:
      db2 bind <filename.bnd>
       
4) Rerun the db2advis

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

Document Information

Modified date:
16 June 2018

UID

swg21607022

Page Feedback