By default, autocommit is ON in DB2. That means each SQL statement we execute is a unit of work or a transaction. There are many ways to execute the unit of work; the simplest one is to turn off autocommit, or use db2 + c at the beginning, or use atomic procedures.

The different options available for the DB2 command can be listed using the following command:

C:\>db2 "? options"
db2 [option ...] [db2-command | sql-statement |
            [? [phrase | message | sqlstate | class-code]]]
option: -a, -c, -d, -e{c|s}, -finfile, -i, -lhistfile, -m, -n, -o,
           -p, -q, -rreport, -s, -t, -td;, -v, -w, -x, -zoutputfile.
Option 		Description 				Default Setting
------ 		---------------------------------------- 		---------------
-a 		Display SQLCA 				OFF
-c 		Auto-commit 				ON

In this section, we'll see how multiple ALTER TABLE operations are allowed in three different units of work. This can be implemented in any language, but the user must have privileges to alter a table.

For demonstration purposes, we have created the EMPLOYEEE table, which is a replica of EMPLOYEE of the sample database without the dependent objects. If there are dependent objects, we may have to drop them and perform the ALTER TABLE statement.

REORG TABLE EMPLOYEEE

The following screenshot shows the sample output for the preceding statements:

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.