IDMS SQL EXPLAIN DB002007 Undefined Area creating ACCESS_PLAN table
Article ID: 197114
Updated On: 05-17-2023
Products
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 : 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
Feedback
