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

Products

• Datacom
• DATACOM - AD
• CIS
• COMMON SERVICES FOR Z/OS
• 90S SERVICES
• DATABASE MANAGEMENT SOLUTIONS FOR DB2 FOR Z/OS
• COMMON PRODUCT SERVICES COMPONENT
• Common Services
• CA ECOMETER SERVER COMPONENT FOC
• Easytrieve Report Generator for Common Services
• INFOCAI MAINTENANCE
• IPC
• UNICENTER JCLCHECK COMMON COMPONENT
• Mainframe VM Product Manager
• CHORUS SOFTWARE MANAGER
• CA ON DEMAND PORTAL
• CA Service Desk Manager - Unified Self Service
• PAM CLIENT FOR LINUX ON MAINFRAME
• MAINFRAME CONNECTOR FOR LINUX ON MAINFRAME
• GRAPHICAL MANAGEMENT INTERFACE
• WEB ADMINISTRATOR FOR TOP SECRET
• Xpertware

Issue/Introduction

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

Environment

z/OSz/VSE

Resolution

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

Impact

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.

https://docops.ca.com/ca-datacom/15-1/en/using/using-sql/deleting-sql-objects/dropping-a-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.

https://docops.ca.com/ca-datacom/15-1/en/administrating/ca-datacom-db-database-and-system-administration/creating-and-maintaining-the-directory-cxx/deleting-databases#DeletingDatabases-DeleteaTable