DB002028 when running TABLEDDL

Products

• IDMS

Issue/Introduction

An attempt to run TABLEDDL against an SQL catalog returns the following error:

ALTER SCHEMA SYSTEM
       DEFAULT AREA SYSCAT.DDLCAT;
Status = -4       SQLSTATE = 42601        Messages follow:
DB002028 T46 C-4M6001: DDL not allowed on catalog definition
DB003044 T46 C-4M6012: Access has been denied

                                                   

Environment

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

Cause

The most likely cause of this error is that someone, possibly years previously, attempted to run TABLEDDL against the same catalog, was not successful, and just left the catalog as it was without fixing it. This is feasible in particular if it's the SYSTEM catalog (segment CATSYS) because for the most part, there will be no user-defined tables in SYSTEM and the SYSTEM SCHEMA which TABLEDDL adds will not be required, or the fact that it is incomplete will not be noticed as a problem by the user.

Resolution

The only way to resolve this problem is to FORMAT the catalog and re-run TABLEDDL.

However that will remove any definitions currently in the catalog, and therefore they must be saved and re-added afterwards.

If there are any user-defined tables, views or any other database objects in the catalog, the syntax of those objects must be saved to a pds member with the PUNCH verb in an IDMSBCF batch job first.

The most likely scenario is that this is happening on segment CATSYS in dbname SYSTEM, because if it were a user catalog, it would not have been acceptable to have left the catalog in this state for such a long time. So it is likely that there will not be any user-defined database objects.

But if it is SYSTEM, then the source of any SEGMENTS, DMCLs and DBTABLEs in the catalog should be saved with PUNCH SEGMENT xxx AS SYN;, PUNCH DMCL xxx AS SYN; and PUNCH DBTABLE xxx  AS SYN;. Use DIS ALL SEGMENT;, DIS ALL DMCL; and DIS ALL DBTABLE; to identify the names of the SEGMENTs, DMCLs and DBTABLES.

Once the source of the necessary objects has been saved, take a backup of the catalog and any relevant user-defined database areas, FORMAT the catalog (by AREA), run TABLEDDL, and then add back the saved entities.

If there were any user-defined tables saved, it will also be necessary to run SYNCHRONIZE STAMPS xxx UPDATE DATABASE; for all of the involved user database areas.