DB002025 on EXPLAIN

Document ID : KB000004796
Last Modified Date : 14/02/2018
Show Technical Document Details
Issue:

Trying to run an EXPLAIN gives this error message:

EXPLAIN STATEMENT '
SELECT * FROM EMPNET.EMPLOYEE
' INTO TABLE EMPNET.AP;
*+ Status = -4       SQLSTATE = 42603        Messages follow:
*+ DB002025 T50 C-4M6003: Tables not allowed, Schema:EMPNET
*+ DB001903 T50 C-4M6021: ACCESS_PLAN table creation error

 

What does this mean?

Environment:
CA-IDMS any supported release.
Cause:

There are three types of SQL schema in CA-IDMS.

Type R: This is a native SQL schema which is used for collecting native tables, views, functions and any of the procedure types under one logical identifier.

Type A: This is a cloned SQL schema defined to reference a native (type R) SQL schema.

Type N: This is an SQL schema which points to a non-SQL schema and database name combination, which provides for SQL access to that non-SQL defined database.

SQL defined entities can only be placed in a type R schema.

The output of an EXPLAIN statement is a native SQL table and as such it must be directed to a type R schema.

 

The DB002025 message means you are trying to direct the output of an EXPLAIN statement to a schema of one of the other types, most likely N.

Resolution:

Change the INTO TABLE clause of the EXPLAIN statement to reference a native SQL schema (type R).

For example:

EXPLAIN STATEMENT '
SELECT * FROM EMPNET.EMPLOYEE
' INTO TABLE SQLSCHM.AP;    
      

where SQLSCHM is defined thus:

CREATE SCHEMA SQLSCHM
     DEFAULT AREA SQLWORK.SQL-AREA-1
     ;
    

Additional Information:

The following SQL statement can be used to list all of your schemas and their types:
SELECT NAME, TYPE FROM SYSTEM.SCHEMA;

 

For more information, see the following CA IDMS DocOps pages:

EXPLAIN

DB002025

CREATE SCHEMA