Modifying Schema, View, Table, and Routine Definitions › Dropping and Recreating a Table › Method 2—Using DDL and Utility Statements

Method 2—Using DDL and Utility Statements

Steps

To use a combination of DDL and utility statements to drop and recreate a table, take the following steps:

1. Identify all tables related through a linked constraint to the target table (the table whose definition is to be changed). Either the related tables must be unloaded and reloaded together with the target table or the constraints will become unlinked when they are redefined.
2. For each view in which the target table is referenced (or views of those views), display the definition syntax by selecting from SYSCA.SYNTAX. Save the resulting output so the views can be recreated later.
3. For each table to be unloaded, extract the data to a sequential file using either:
   • A user-written program
   • A CA Culprit report

   Use separate extract files for each table or place an indicator in each output record to identify the table from which the data was extracted. Be sure the data was extracted successfully before proceeding to the next step.

4. Drop the target table (specifying the CASCADE option) and delete the rows from the related tables that were unloaded by using a DELETE statement. If no other tables or indexes exist within the affected areas and all relationships are within those areas (and were unloaded), format the area before issuing the DROP and DELETE statements. Be sure to vary the areas offline to the DC/UCF system before formatting them.
5. Redefine the table making any necessary changes.
6. Redefine the indexes and CALC key on the target table.
7. Redefine the referential constraints in which the target table participates. If any of the constraints involve non-empty tables, those constraints must be defined as unlinked.
8. Reload the tables using the LOAD utility statement and the sequential file as input.

   Note: For more information about how to perform the load operation, see Chapter 23, “Loading an SQL-Defined Database".

9. Complete the process as follows:
   • Recreate the views whose syntax was previously saved; examine those view definitions to see if changes are required
   • Respecify privilege definitions on the target table and its referencing views if access is controlled through CA IDMS internal security

Guaranteeing the Integrity of the Data

You must ensure that no updates are made to any of the unloaded tables once their data has been extracted. To ensure that no changes are made between the time the data is extracted and the time the tables have been reloaded:

• Prohibit access to the tables by explicitly dropping all views that reference it. This is effective only if all update access to the table is done through a view.
• Revoke all INSERT, UPDATE, and DELETE privileges from the tables (and any matching wildcarded table names) if access is controlled though CA IDMS internal security.