Adding a column to a table

When you use the ALTER TABLE statement to add a column to a table, the table space is placed in an advisory REORG-pending (AREO*) state.

About this task

Also, the new column might become the rightmost column of the table, depending on whether you use basic row format or reordered row format.

The physical records are not actually changed until values are inserted in the new column. Start of changeWhen you use the ALTER TABLE ADD COLUMN statement, packages are not invalidated, unless the following criteria are true:End of change

  • The data type of the new column is DATE, TIME, or TIMESTAMP.
  • You specify the DEFAULT keyword.
  • You do not specify a constant (that is, you use the system default value).

However, to use the new column in a program, you need to modify and recompile the program and bind the plan or package again. You also might need to modify any program that contains a static SQL statement SELECT *, which returns the new column after the plan or package is rebound. You also must modify any INSERT statement that does not contain a column list.

Access time to the table is not affected immediately, unless the record was previously fixed length. If the record was fixed length, the addition of a new column causes DB2® to treat the record as variable length, and access time is affected immediately.

Procedure

To change the records to fixed length:

  1. Run the REORG utility with the COPY option on the table space, using the inline copy.
  2. Run the MODIFY utility with the DELETE option to delete records of all image copies that were made before the REORG that you ran in step 1.
  3. Create a unique index if you add a column that specifies PRIMARY KEY.

Results

Tip: Inserting values in the new column might degrade performance by forcing rows onto another physical page. You can avoid this situation by creating the table space with enough free space to accommodate normal expansion. If you already have this problem, run REORG on the table space to fix it.

You can define the new column as NOT NULL by using the DEFAULT clause unless the column has a ROWID data type or is an identity column. If the column has a ROWID data type or is an identity column, you must specify NOT NULL without the DEFAULT clause. You can let DB2 choose the default value, or you can specify a constant or the value of the CURRENT SQLID or USER special register as the value to be used as the default. When you retrieve an existing row from the table, a default value is provided for the new column. Except in the following cases, the value for retrieval is the same as the value for insert:

  • For columns of data type DATE, TIME, and TIMESTAMP, the retrieval defaults are:
    Data type
    Default for retrieval
    DATE
    0001-01-01
    TIME
    00.00.00
    TIMESTAMP
    0001-01-01-00.00.00.000000
  • For DEFAULT USER and DEFAULT CURRENT SQLID, the retrieved value for rows that existed before the column was added is the value of the special register when the column was added.

If the new column is a ROWID column, DB2 returns the same, unique row ID value for a row each time you access that row. Reorganizing a table space does not affect the values on a ROWID column. You cannot use the DEFAULT clause for ROWID columns.

If the new column is an identity column (a column that is defined with the AS IDENTITY clause), DB2 places the table space in REORG-pending (REORP) status, and access to the table space is restricted until the table space is reorganized. When the REORG utility is run, DB2

  • Generates a unique value for the identity column of each existing row
  • Physically stores these values in the database
  • Removes the REORP status

You cannot use the DEFAULT clause for identity columns.

If the new column is a short string column, you can specify a field procedure for it. If you do specify a field procedure, you cannot also specify NOT NULL.

Example

Begin general-use programming interface information.
The following example adds a column to the table DSN8910.DEPT, which contains a location code for the department. The column name is LOCATION_CODE, and its data type is CHAR (4). 

ALTER TABLE DSN8910.DEPT
  ADD LOCATION_CODE CHAR (4);

End general-use programming interface information.