CA IDMS SQL EXPLAIN DB002007 Undefined Area creating ACCESS_PLAN table

book

Article ID: 197114

calendar_today

Updated On:

Products

CA IDMS CA IDMS - Database CA IDMS - ADS

Issue/Introduction

Created a new CA IDMS SQL segment, area and schema.

The segment is named SQLMAINT and the area SQL-MAINT-AREA.
The SQL schema is SQLMAINT defined with default area SQLMAINT.SQL-MAINT-AREA.

An EXPLAIN statement fails trying to create the ACCESS_PLAN table in the new area:

SET SESSION CURRENT SCHEMA SQLMAINT ;                                       
Status = 0        SQLSTATE = 00000                                           
EXPLAIN STATEMENT 'SELECT ....... ';

Status = -4       SQLSTATE = 42505        Messages follow:                  
DB002007 T870 C-4M6003: Undefined Area:SQLMAINT.SQL-MAINT-AREA               
DB001903 T870 C-4M6021: ACCESS_PLAN table creation error                     

 

Environment

Release : 19.0

Component : CA IDMS SQL

Resolution

The SQL segment definition needs to be copied from the system dictionary into the application dictionary and everything must match, the area name, stamp level, page range and page size .

If anything is different Iike having a different page size then the DB002007 error will occur.

The documentation states:


Copy the segment definition from the system dictionary into the application dictionary in which you wish to define your tables.
The segment and area names you use in the logical definition must match those defined in the physical definition in the system dictionary. The stamp level, which tells CA IDMS/DB to check the date and time of definition at either the area level or table level, must also match in both definitions. It is recommended that the page range and page size of areas match in both definitions since this information is used for optimization and index sizing. It is not necessary to define the files in the application dictionary.

 

Additional Information

See documentation section Defining a Database Using SQL