Why does CA Datacom SQL DROP TABLE appear to take a long time?

Document ID : KB000011963
Last Modified Date : 14/02/2018
Show Technical Document Details

Why does CA Datacom SQL DROP TABLE appear to take a long time?


Quite a bit of work may be needed when dropping an SQL Table. Here are excerpts from the CA Datacom® Core - 15.1 documentation.

From https://docops.ca.com/ca-datacom/15-1/en/reference/sql-reference/sql-statements/drop-statement :

DROP Statement

Dropping a Table


The results of processing the DROP TABLE have a far reaching impact.

•The table data is deleted and the space is reclaimed.

•All views and synonyms based on the table are dropped.

•All application plans and statements that reference the table are invalidated.

•All columns, keys, elements, and support data associated with the table are also obsoleted (columns appear as FIELD occurrences in CA Datacom® Datadictionary™ ). The support data includes aliases, descriptors, relationship definitions, and text (SQL comments and text added through CA Datacom® Datadictionary™ ).

•If the table definition also exists in TEST or HIST status in CA Datacom® Datadictionary™, those substructure status/versions, including their elements, keys and fields, are deleted at the same time as the PRODuction status.

From https://docops.ca.com/ca-datacom/15-1/en/using/using-sql/deleting-sql-objects/dropping-a-table :

The DROP TABLE statement removes the table from the schema, obsoletes the table from the CA Datacom® Datadictionary™, and removes the table from the CA Datacom®/DB Directory. You must be the creator of the table, or have the authority to drop the table.


SQL Procedure to Delete a Table

For SQL accessible tables, you can use an SQL DROP TABLE statement to delete a table. However, the DROP TABLE statement obsoletes the table, which removes all versions and statuses of the table from the CA Datacom® Datadictionary™ databases, deletes the Directory definition, and deletes the data.