Delete of "orphaned" SQL definitions yields error DB001031 T88 C-4M351: DB error 1492; Tbl: , Area: ErrSet: , ErrTbl: , ErrArea:
Article ID: 33667
Updated On:
Products
Issue/Introduction
Symptoms:
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:
This can occur in any operating system whenever CA IDMS SQL databases are created.
Cause:
This occurs when a database area is physically deleted before any definitions that reference this area are dropped from the catalog.
Resolution/Workaround:
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. You can do this be 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, you will get 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. You can remedy that by using the SYNCHRONIZE STAMPS utility with the UPDATE DATABASE option.
3- Once the files are back in place & the stamps are synced you should be able to 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 you physically delete the areas.
The second option only applies if the orphaned SQL definitions are the only things in the catalog. In that situation, you should be able to simply FORMAT the catalog segments, and then delete them from the DMCL. If you start with this approach you should probably back up the areas first, so that you can restore them and take the other approach (outlined above) if necessary.
Environment
Component:
Feedback
