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/AD, 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 Schema Information Tables (SIT)) 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 nonexecutable 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 Results of Using ALTER TABLE for information about the results in CA Datacom Datadictionary of using the ALTER TABLE statement. See Preliminary Information—Lock Levels for information about lock levels with regard to the ALTER TABLE statement.

The ALTER TABLE statement causes the CONSTRAINT attribute-value to be marked A (for ALTERED) for all TEST and HIST versions of the CA Datacom Datadictionary definition. The next time you try to copy that definition from TEST to PROD status, CA Datacom Datadictionary issues an ALT DSF return code that indicates it cannot be copied because the PROD version has been altered. 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 is generated (for details, see the CA Datacom/DB Message Reference Guide), telling you the TTM is too small.

In response to SQL code -561, you could make the TTM larger by using dynamic extensions of the TTM size.

Dynamic extensions of the TTM can be done with no loss of access to any other task areas or TTM usage. You need not close the TTM. Support is provided when the TTM is defined as either DASD or VIRTUAL. When the TTM is DASD, define the dynamic extend or on-demand dynamic extend for the TTM as for any other DASD data set. When the TTM is VIRTUAL, use the dynamic extend or on-demand dynamic extend as for any other VIRTUAL database area.

To specify dynamic extend at MUF startup, use MUF startup options as described in the CA Datacom/DB Database and System Administration Guide.

To change dynamic extend settings for VIRTUAL TTMs after the MUF has started, use the VIRTUAL_DYNAMIC_EXTEND console-like command as described in the CA Datacom/DB Database and System Administration Guide.

To use on-demand dynamic extend, use the DYNAMIC_EXTEND console-like command described in the CA Datacom/DB Database and System Administration Guide.

A new column, TTM_BLKS_MAX_USE, that lists the highest number of 4K-blocks used, is found in the SQL_STATUS (SQS) table. For details, see the CA Datacom/DB System Tables Reference Guide.

Instead of making the TTM larger using dynamic extend, in response to SQL code -561 you could alternately do 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 ──────────────┘