Adding a column to a table

You can add a new column to a table by specifying the ADD COLUMN clause in an ALTER TABLE statement.

Before you begin

Determine that attributes of the column that you want to define. For more information about attributes that you can specify, other rules, and restrictions for adding new columns, see "ADD COLUMN" in ALTER TABLE.

Be aware that adding a column might affect subsequent requests to recover to a point in time. For information about possible restrictions, effects on recovery status, and other considerations, see Point-in-time recovery.

As values are assigned to the new column, performance might degrade as rows are forced onto another page physical page. You can avoid this situation by ensuring that the table space with enough free space to accommodate normal expansion. If you already have this problem, you can run REORG on the table space to resolve it. For more information, see Reserving free space in table spaces.

About this task

When you add a new column, it becomes the rightmost column of the table. Db2 determines the value for existing rows based on the attributes of the column. If the default is a special register, the value for existing rows is the setting when the column is added. However, existing physical data records might remain unchanged until values are explicitly assigned in the new column, or the table space is reorganized.

If the table is a system-period temporal table or archive-enabled, the new column is also added in the associated history or archive table.

In most cases, the table space is placed in an advisory REORG-pending (AREO*) state. However, the table space is placed in a restrictive REORG-pending (REORP) status in certain situations, such as when the new column is one of the following types, which require generated values and cannot use the same default for every existing row:
  • ROWID column
  • Identity column
  • Row change timestamp column
  • Row-begin column
  • Row-end column
  • Transaction-start-ID column

Procedure

To add a new column to a table, complete the following steps:

  1. Issue an ALTER TABLE statement and specify the ADD COLUMN clause with the attributes for the new column.
  2. Consider running the REORG utility for the table space to materialize the values for the new column in the physical data records. If the table space is placed in restrictive REORG-pending (REORP) status, this step is required.
    The REORG utility generates any required values for the new column in each existing row, physically stores the generated values in the database, and removes the REORP status.
  3. Check your applications and update static SQL statements to accept the new column. Then recompile the programs and rebind the packages.

    For example, the following situations might require application updates:

    • Statements that use SELECT * start returning the value of the new column after the package is rebound.
    • INSERT statements with no list of column names imply that the statement specifies a list of every column (unless defined as implicitly hidden) in left-to-right order. Such statements can continue to run after the new column is added, but Db2 returns an error for this situation when the package is rebound. To avoid this problem, it is best to always list the column names in INSERT statements.
  4. If the new column is a DATE, TIME, or TIMESTAMP column with a default based on certain system defaults, rebind any invalidated dependent packages.
    For more information, see Changes that invalidate packages.

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.

What to do next

If the table previously contained fixed-length records, adding a new column causes Db2 to treat them as variable-length records, and access time can be affected immediately. To change the records back to fixed-length, complete the following steps:

  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 in the previous step.