IDMS: DB002025 on a CREATE TABLE or VIEW

Products

• IDMS
• IDMS - Database

Issue/Introduction

An attempt to create a relational entity (e.g. a TABLE or a VIEW) results in a DB002025 error:

CREATE VIEW EMPNET.EMP_ID_VIEW AS
SELECT EMP_ID_0415 FROM EMPNET.EMPLOYEE;
*+ Status = -4       SQLSTATE = 42603        Messages follow:
*+ DB002025 T62 C-4M6003: Tables not allowed, Schema:EMPNET

Environment

Release: All supported releases.
Component: CA IDMS/SQL Option.

Cause

In this example, EMPNET is an SQL schema defined for a non-SQL defined schema.

CREATE SCHEMA EMPNET
    FOR NONSQL SCHEMA APPLDICT.EMPSCHM VERSION 100
        DBNAME EMPDEMO
    ;

Such schemas, defined with the FOR NONSQL clause, exist only to expose the records in the associated network schema (EMPSCHM in this example) to the relational environment as tables.

They cannot be used to contain any other relational entity such as TABLEs, VIEWs, TABLE PROCEDUREs etc.

This is the meaning of the DB002025 message.

Resolution

The relational entity in question must be defined in a normal SQL schema - one that contains the FOR SQL clause.

In the case of a VIEW, this is true even if the view references tables which really represent network-defined records.

For example:

CREATE SCHEMA EMPSQL
    ;

CREATE VIEW EMPSQL.DEPT_ID_VIEW AS
SELECT DEPT_ID_0410 FROM EMPNET.DEPARTMENT;

SELECT * FROM EMPSQL.DEPT_ID_VIEW;
*+
*+ DEPT_ID_0410
*+ ------------
*+         5300
*+         5100
*+         2000
*+         1000
*+         3100
*+         3200
*+         4000
*+          100
*+         5200
*+
*+ 11 rows processed