Altering Db2 tables

When you alter a table, you do not change the data in the table. You merely change the specifications that you used in creating the table.

Procedure

Begin general-use programming interface information.To alter a table:

Issue the ALTER TABLE statement.
With the ALTER TABLE statement, you can:
  • Add a new column
  • Rename a column
  • Drop a column
  • Change the data type of a column, with certain restrictions
  • Add or drop a parent key or a foreign key
  • Add or drop a table check constraint
  • Add a new partition to a table space, including adding a new partition to a partition-by-growth table space, by using the ADD PARTITION clause
  • Change the boundary between partitions, extend the boundary of the last partition, rotate partitions, or instruct Db2 to insert rows at the end of a table or appropriate partition
  • Register an existing table as a materialized query table, change the attributes of a materialized query table, or change a materialized query table to a base table
  • Change the VALIDPROC clause
  • Change the DATA CAPTURE clause
  • Change the AUDIT clause by using the options ALL, CHANGES, or NONE
  • Add or drop the restriction on dropping the table and the database and table space that contain the table
  • Alter the length of a VARCHAR column using the SET DATA TYPE VARCHAR clause
  • Add or drop a clone table
  • Alter APPEND attributes
  • Drop the default value for a column
  • Activate or deactivate row-level or column-level access control for the table
    Tip: When designing row-level or column-level access control for a table, first create the row permissions or column masks to avoid multiple invalidations to packages and dynamically cached statements. After you create row permissions or column masks, use the ALTER TABLE statement to activate row-level or column-level access control for the table. If you must drop or alter a column mask, first activate row-level access control to prevent access to the table, and then drop or alter the column mask. Otherwise, the rows are accessible, but the column values inside the rows are not protected.

    If a security administrator with SECADM authority activates row-level access control before the explicit creation of the row permission database object, a default row permission is created. This default row permission blocks all access to the table, including access by the owner.

End general-use programming interface information.

What to do next

Start of changeYou might need to rebind packages that depend on the altered table, and possibly other related objects through cascading effects. For more information, see Changes that invalidate packages and Changes that might require package rebinds.End of change