SQL Statements › ALTER TABLE

ALTER TABLE

Note: YES indicates a valid execution method for this statement. To learn about using SQL keywords in CA Dataquery, see the CA Dataquery User Guide. For information about the access rights required to execute this statement, see the CA Datacom/DB Database and System Administration Guide.

Note: If you are using CA Datacom/DB as part of the CA Datacom/AD environment, you cannot use the ALTER TABLE statement. With regard to table partitioning, ALTER statements may not be issued against a table which is partitioned nor against a partition. An SQL integrity constraint cannot reference a partitioned table, nor a partition of a partitioned table. That is to say, constraints and partitioned tables are mutually exclusive. For more information about table partitioning, see the CA Datacom/DB Database and System Administration Guide.

The ALTER TABLE statement is a CA Datacom/DB extension. ALTER TABLE allows you to alter a table definition without having to reload manually. You may change the table's column definition and/or the table's constraints. You may make only one change to a column in an ALTER TABLE statement.

You can query the SYSCONSTRSRC and SYSCONSTRDEP tables (see the CA Datacom/DB SQL User Guide) to view information about constraints. You can then use ALTER TABLE to drop the constraint or modify the constraint's selection criteria.

When a table is altered, the prepared statements in plans that reference the table are marked non-executable until rebound. An attempt to execute these statements invokes automatic rebind. Depending on the change, the rebind may be successful and require no manual intervention. But if the plans containing referencing statements are currently executing or binding, the ALTER request is aborted. This is always the case when the plan containing the ALTER statement references the table, because this is an attempt to invalidate its own plan and is not allowed.

A Datadictionary Relationship Report can tell you what plans are dependent on a table. See the CA Datacom Datadictionary Batch Reference Guide for information on Relationship Reports.

See the CA Datacom/DB SQL User Guide for information about the results in Datadictionary of using the ALTER TABLE statement.

The ALTER TABLE statement causes the CONSTRAINT attribute-value to be marked A (for ALTERED) for all TEST and HISTory versions of the Datadictionary definition. The next time you try to copy that definition from TEST to PRODuction status, Datadictionary issues an ALT DSF return code that indicates it cannot be copied because the PROD version has been altered.

Note: For details, see the appendix on constrained tables in the CA Datacom Datadictionary Online Reference Guide or CA Datacom Datadictionary Batch Reference Guide.

The table must be at least half empty before alteration, if using the ALTER TABLE statement causes a table's rows to be reformatted (adding, dropping, or modifying columns). But the table must be more than half empty before alteration (that is, less than half full) if the adding, dropping, or modifying of columns causes the size of the table's rows to grow.

Issuing an ALTER TABLE statement involves a process that:

1. Copies the contents of the table to the Temporary Table Manager (TTM),
2. Changes the definition of the table, and then
3. Copies the reformatted rows back to the table.

However, a check is made before this operation begins to see if enough space is available in the TTM, and if the answer is no, SQL code -561 (SQLSTATE 57S05) is generated: TTM TOO SMALL - SEE ERROR ACTION FOR SUGGESTION. In response to this message you could make the TTM larger, but it can take a long time to perform that operation if there are many rows in the table. Therefore, instead of making the TTM larger consider doing the following:

1. Copy the rows to an external file using the EXTRACT function of DBUTLTY.
2. Convert the rows yourself.
3. For single table areas, do a null LOAD. For multi-table areas, do a REPLACE on the specific table.
4. Perform the ALTER of the empty table.
5. For single table areas, LOAD the converted table. For multi-table areas, do a REPLACE using the converted data.

Note: Be aware that because the TTM space check is made at the beginning of the process, there is the possibility that other tasks could use some space and the TTM still become full even if the -561 SQL code was not initially received.

When the NOMAINT option of the CA Datacom/DB Utility (DBUTLTY) ACCESS function is in force, an ALTER TABLE statement receives a CA Datacom/DB return code 94(87), where 87 is a decimal internal return code (hex 57) that tells you no maintenance statements are allowed while NOMAINT is in force.

Following is the syntax diagram for the ALTER TABLE statement:

►►─ ALTER TABLEtable-name ────────────────────────────────────────────────────►

   ┌────────────────────────────────────────────────────────────┐
 ►─▼─┬─ ADD ─┬─ column definition ───────────┬────────────────┬─┴─────────────►◄
     │       └─ table constraint definition ─┘                │
     ├─ DROP ─┬─ CONSTRAINT ─ constraint-name ──┬─────────────┤
     │        ├─ column-name ───────────────────┤             │
     │        ├─ PRIMARY KEY ───────────────────┤             │
     │        └─ FOREIGN KEY ─ constraint-name ─┘             │
     ├─ MODIFY ─ column-name ─┬─ DEFAULT ─┬─ literal ─────┬─┬─┤
     │                        │           ├─ NULL ────────┤ │ │
     │                        │           ├─ USER ────────┤ │ │
     │                        │           └─ SYSTEM USER ─┘ │ │
     │                        └─ datatype ──────────────────┘ │
     └─ RENAME ─ column-name ─ TO ─ column-name ──────────────┘