DB001031 with DB error 1492 deleting "orphaned" SQL definitions

Products

• IDMS

Issue/Introduction

If a physical database file is deleted but the catalog still contains definitions of SQL segments, schemas and tables that reference this database area, then these definitions become “orphaned”. A subsequent DROP TABLE results in an SQLSTATE = DB001031 DB error 1492.

Environment

Release: All supported releases.
Component: SQL Option.

Cause

This occurs when a database area is physically deleted before any definitions that reference this area are dropped from the catalog.

Resolution

There are two possible solutions to this dilemma.

The first involves a number of steps:

1. First, re-create the physical files that were dropped, and name them the same database names as the files that were deleted. Do this by creating entirely new datasets, or by restoring the pre-existing ones from a backup. This will give the catalog definitions something to point to, so the definitions will appear to be accurate even though the table data is gone.
2. After the areas are re-created, there will be a timestamp error when any reference is made to the tables, because the new database areas won’t have a valid timestamp that matches the catalog. Remedy that by using the SYNCHRONIZE STAMPS utility with the UPDATE DATABASE option.
3. Once the files are back in place and the stamps are synced, drop all the definitions from the catalog by starting with the tables, then proceeding to the schemas, areas, files and segments.
4. Only after all these definitions have been removed from the catalog should the areas be physically deleted.

The second option only applies if the orphaned SQL definitions are the only things in the catalog. In that situation, simply FORMAT the catalog segments, and then delete them from the DMCL. If starting with this approach, back up the areas first, so that they can be restored in the event that the first approach must be used instead.