DB002025 on EXPLAIN

Products

• IDMS
• IDMS - Database

Issue/Introduction

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

Environment

Release: All supported releases.

Cause

There are three types of SQL schema in 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;