Eddy_G

CA Tech Tip: CA IDMS - SQL EXPLAIN statement fails with error message: DB001901 Tnnnn C-4M6021: Invalid access plan table

Discussion created by Eddy_G Employee on Nov 3, 2015

CA IDMS Tech Tip by Edward Gorga, Senior Principal Support Engineer for November 3, 2015

 

When doing an EXPLAIN of an SQL statement or access module into a existing ACCESS_PLAN result table, the table's columns are first validated to ensure they are defined correctly. The error indicates the ACCESS_PLAN table is defined incorrectly. The ACCESS_PLAN result table doesn't need to exist. When it doesn't exit, the system will create it.

 

To resolve the problem either DROP the existing ACCESS_PLAN table before the EXPLAIN:

 

DROP TABLE  ACCESS_PLAN;

EXPLAIN STATEMENT 'SELECT * FROM SQLDEMO.EMPLOYEE';

 

 

or do the EXPLAIN into a result table that doesn't exist:

 

EXPLAIN STATEMENT 'SELECT * FROM SQLDEMO.EMPLOYEE' INTO TABLE SQLDEMO.EMPLOYEE_PLAN;

 

Another reason for this error is specifying the IN clause with an invalid area name, for example:

 

EXPLAIN STATEMENT 'SELECT * FROM SQLDEMO.EMPLOYEE' INTO TABLE SQLDEMO.EMPLOYEE_PLAN IN SQLDEMO.BADAREA;

Outcomes