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

Method 1—Using DDL and DML Statements

Steps

To use a combination of DDL and DML statements to recreate a table, follow these steps:

1. Define a new table that has the same definition as the original table except for the desired changes.
2. Define the same indexes and CALC keys for the new table as for the old (unless changes in these are desired).
3. For each referential constraint in which the original table is the referencing table, define a similar constraint on the new table. The new constraint must be defined with a different name and if the referenced table is not empty, it must be defined as unlinked. (The unlinked constraint may also require that an index be defined, including the foreign key of the new table).
4. For each referential constraint in which the original table is the referenced table, determine if the referencing table is empty. If it is, define a similar constraint with a different name in which the new table is the referenced table. If the referencing table is not empty, determine if additional indexes are needed, including the foreign key of the referencing table, to support a similar constraint defined as unlinked. If additional indexes are required, create them now.
5. For each view in which the original 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.
6. Copy the data from the original table to the new table using an INSERT statement with the SELECT option.
7. For each referential constraint in which the original table is the referenced table and the referencing table is not empty, define a constraint in which the new table is the referenced table. The new constraint must have a different name and be defined as unlinked.
8. Drop the original table using the CASCADE option of DROP table.
9. For each self-referencing constraint defined on the original table, define a similar constraint on the new table. (A self-referencing constraint is a referential constraint in which the referenced and referencing table are the same.)
10. Complete the transition to the new table as follows:
    • Define a view on the new table with the same name as the original table and including all of its columns.
    • Recreate the views whose syntax was previously saved; examine those view definitions to see if changes are required.
    • Re-specify privilege definitions on the individual table and views if access is controlled through CA IDMS internal security.

Guaranteeing Integrity of the Data

Steps 6 through 8 should be performed within a single transaction to minimize the potential of changes to the data in the original table and any of its related tables until the entire operation is completed. To ensure that no changes are made between the time the data is copied and the time the table is dropped, take one of the following actions just prior to issuing the SELECT statement:

• Prohibit access to the table 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 table (and any matching wildcarded table names) if access is controlled through CA IDMS internal security.
• Alter the original table and add a dummy column. This has the effect of prohibiting access to the table until the transaction has terminated.

Recreating Empty Tables

If the table to be recreated is empty, you need not define a new table. Instead, simply drop and redefine the table making the desired changes to its definition. However, be sure to take appropriate steps to preserve referential constraints, views derived from the table, and privilege definitions.