IBM Support

EXPLAIN PLAN returns SQL0220N with wrong schema on EXPLAIN_INSTANCE

Technical Blog Post


Abstract

EXPLAIN PLAN returns SQL0220N with wrong schema on EXPLAIN_INSTANCE

Body

 
We recently had a case where customer was attempting to get the EXPLAIN PLAN of a query and kept getting SQL0220N with wrong schema name.
 
Steps followed were : 
1> SET SCHEMA B

2> CALL SYSPROC.SYSINSTALLOBJECTS('EXPLAIN', 'C', CAST (NULL AS VARCHAR(128)), CAST (NULL AS VARCHAR(128)))

Then explain plan was run on desired SQL statement : 

 EXPLAIN PLAN FOR <SQL statement> 

This returned following error, but note that it was for a different SCHEMA SYSTOOLS and not for B :

DB21034E The command was processed as and SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0220N The Explain table ”SYSTOOLS.EXPLAIN_INSTANCE”, column “25” does not have the proper definition or is missing. SQLSTATE=55002

 

Cause : 

The following call :

CALL SYSPROC.SYSINSTALLOBJECTS('EXPLAIN', 'C', CAST (NULL AS VARCHAR(128)), CAST (NULL AS VARCHAR(128)))

..creates the explain tables under the SYSTOOLS schema by default.

 

Solution : 

To create explain tables under a different schema (like B in this case), specify a schema name as the last parameter in the call.

CALL SYSPROC.SYSINSTALLOBJECTS('EXPLAIN', 'C', CAST (NULL AS VARCHAR(128)), CAST (NULL AS VARCHAR(128)), ‘B')

 

More explanation can be found in below technote : 

/support/pages/node/284325

[{"Business Unit":{"code":"BU029","label":"Data and AI"}, "Product":{"code":"SSEPGG","label":"DB2 for Linux, UNIX and Windows"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":""}]

UID

ibm13285915