DB2 - ALTER Statement




DB2 ALTER Statement

ALTER can be used to add/delete/modify the DB2 Objects which already defined during the CREATE. Primary and foreign key also can be defined by using ALTER once the table got created. For performing ALTER, user required SYSADM or SYSCTRL authority and DBADM authority on DATABASE.

Syntax

ALTER TABLE table-name
ADD column-name declaration;

Similarly the ALTER can be performed on DB2 objects like VIEWS, INDEXes, TABLE SPACES, DATABASES etc,

Syntax

ALTER DB2-Object-name
[Parameters newly added/modified]

What is DB2 ALTER Statement ?

The ALTER TABLE statement alters the definition of a table. This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is in effect for the package.

The privileges that are held by the authorization ID of the statement must include at least one of the following authorities, ALTER privilege on the table to be altered, CONTROL privilege on the table to be altered, ALTERIN privilege on the schema of the table, DBADM authority

To create or drop a foreign key, the privileges that are held by the authorization ID of the statement must include one of the following authorities on the parent table, REFERENCES privilege on the table, REFERENCES privilege on each column of the specified parent key, CONTROL privilege on the table, DBADM authority.

To drop the primary key or a unique constraint on table T, the privileges of the authorization ID of the statement must include at least one of the following authorities on every table that is a dependent of T's parent key, ALTER privilege on the table, CONTROL privilege on the table, ALTERIN privilege on the schema of the table, DBADM authority.

To alter a table to become a materialized query table (by using a fullselect), the privileges that are held by the authorization ID of the statement must include at least one of the following authorities, CONTROL privilege on the table, DBADM authority.

and at least one of the following authorities on each table or view that is identified in the fullselect, SELECT privilege and ALTER privilege (including group privileges) on the table or view CONTROL privilege on the table or view, SELECT privilege on the table or view, and ALTERIN privilege (including group privileges) on the schema of the table or view, DATAACCESS authority.

To alter a table so that it is no longer a materialized query table, the privileges of the authorization ID of the statement must include at least one of the following authorities on each table or view that is identified in the fullselect that is used to define the materialized query table, ALTER privilege on the table or view, CONTROL privilege on the table or view, ALTERIN privilege on the schema of the table or view, DBADM authority.

How it works…

When a table is altered with operations, such as dropping a column, altering a column data type, or altering the nullability feature of a column, the table may be placed in a REORG PENDING state. While the table is in the REORG state, no queries can be run until the table is brought online from the REORG PENDING state by executing the REORG command. Starting with DB2 9.7, one can perform an unlimited number of ALTER TABLE statements in a single transaction with a maximum of three transactions in a row before the need for table reorganization. This reduces the maintenance window requirement, in the case of a huge data warehouse environment.