CNDBA社区
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
版权声明：本文为博主原创文章，未经博主允许不得转载。
DB2
