Troubleshooting
Problem
Attempting to explain a query from Data Server Manager (DSM) fails with warning message SQL20459W, "The procedure "SYSPROC.DESIGN_ADVISOR" has encountered an internal processing error. The value for parameter "7" contains further information about the error.".
Symptom
The stored procedure for index recommendations did not run.
A SQL error occurred within DB2 with SQLCODE: -901. The error message is:
Warning information:
SQL20459W The procedure "SYSPROC.DESIGN_ADVISOR" has encountered an internal processing error. The value for parameter "7" contains further information about the error.
Exception Stack Trace:
com.ibm.datatools.dsoe.wda.luw.exception.DBAdvisorException
at com.ibm.datatools.dsoe.wda.luw.util.CallDB2Advis.runDB2Advis(Unknown Source)
at com.ibm.datatools.dsoe.wia.luw.impl.RecommendationManager.generateAdvisRcmd(Unknown Source)
at com.ibm.datatools.dsoe.wia.luw.impl.WIAInfoGenerator.generateRcmd(Unknown Source)
at com.ibm.datatools.dsoe.wia.luw.impl.WIAInfoGenerator.generate(Unknown Source)
at com.ibm.datatools.dsoe.wia.luw.IndexAdvisorForLUW$1.run(Unknown Source)
Cause
Missing or Invalid Design Advisor packages.
Resolving The Problem
Rebind the Design Advisor packages on the monitored database.
db2 connect to <DBNAME>
cd <bind directory> (e.g. sqllib/bnd)
// Then bind the DESIGN_ADVISOR files
db2 bind mdcadvise.bnd
db2 bind partadvise.bnd
db2 bind db2advcatalog.bnd
db2 bind db2advindex.bnd
db2 bind db2advio.bnd
db2 bind db2advis.bnd
db2 bind db2advmdc.bnd
db2 bind db2advmisc.bnd
db2 bind db2advmqt.bnd
db2 bind db2advsimulation.bnd
db2 bind db2advworkload.bnd
db2 bind db2advis.bnd
db2 bind db2advisSP.bnd
db2 connect reset
Restart DSM.
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg22009012