SQL Statements › ALTER TABLE › Processing

Processing

In one ALTER TABLE statement, all ADD column-name, DROP column-name, and MODIFY column-name with data type functions are done first and as one group. Processing this group is done in the following sequence:

1. Lock the table.
2. Read the first/next row in the table.
3. Add the row to the SQL Temporary Table (TTM).
4. Delete the row.
5. Loop back through all the rows.
6. Update the definitions in the CA Datacom Datadictionary and Directory (CXX).
7. Read the records from the SQL Temporary Table (TTM).
8. Reformat the row by adding new columns to the end, deleting any dropped columns, and changing the format and/or length of any modified column.
9. Add the row to the original table.
10. Loop back to process all rows.

Primary and unique constraints are validated as rows are added back in step 9. If a duplicate is found, the entire ALTER statement is backed out.

Foreign keys and domain constraints are added after all rows have been added back. After adding the constraint definition(s), the rows of the table are read in Native Key sequence to validate the constraints. If a row violates one or more domain constraints, the table is placed in check pending status and an SQL return code 170 is returned. You must use the CONFIRM function of DBUTLTY to remove the check pending status before the table can be used. If a row violates foreign keys, the ALTER TABLE statement fails with an SQL return code -176.

If the ALTER TABLE statement cannot complete, it is rolled back to its beginning. If the ALTER TABLE process is force checkpointed (because of the size of the Log Area (LXX) and the concurrent activity), the rollback is incomplete. Therefore, before altering large tables, ensure that the Log Area (LXX) is large enough and take a backup of the data area.