EXPLAIN PLAN returns SQL0220N with wrong schema on EXPLAIN_INSTANCE
Priyanka Joshi 270003HTNX Visits (737)
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 SYSP
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 ”SYS
The following call :
..creates the explain tables under the SYSTOOLS schema by default.
To create explain tables under a different schema (like B in this case), specify a schema name as the last parameter in the call.
More explanation can be found in below technote :