Atomic operations

When running under COMMIT(*CHG), COMMIT(*CS), or COMMIT(*ALL), all operations are guaranteed to be atomic.

That is, they will complete or they will appear not to have started. This is true regardless of when or how the function was ended or interrupted (such as power failure, abnormal job end, or job cancel).

If COMMIT (*NONE) is specified, however, some underlying database data definition functions are not atomic. The following SQL data definition statements are guaranteed to be atomic:

  • ALTER TABLE (See note 1)
  • COMMENT (See note 2)
  • LABEL (See note 2)
  • GRANT (See note 3)
  • REVOKE (See note 3)
  • DROP TABLE (See note 4)
  • DROP VIEW (See note 4)
  • DROP INDEX
  • DROP PACKAGE
  • REFRESH TABLE
Notes:
  1. If multiple alter table options are specified, the operations are processed one at a time so the entire SQL statement is not atomic. The order of operations is:
    • Remove constraints
    • Remove materialized query table
    • Remove partitions
    • Remove partitioning
    • Drop columns for which the RESTRICT option was specified
    • All other column definition changes (DROP COLUMN CASCADE, ALTER COLUMN, ADD COLUMN)
    • Alter partition
    • Add or alter materialized query table
    • Add partition or partitioning
    • Add constraints
  2. If multiple columns are specified for a COMMENT or LABEL statement, the columns are processed one at a time, so the entire SQL statement is not atomic, but the COMMENT or LABEL to each individual column or object will be atomic.
  3. If multiple tables, SQL packages, or users are specified for a GRANT or REVOKE statement, the tables are processed one at a time, so the entire SQL statement is not atomic, but the GRANT or REVOKE to each individual table will be atomic.
  4. If dependent views need to be dropped during DROP TABLE or DROP VIEW, each dependent view is processed one at a time, so the entire SQL statement is not atomic.

The following data definition statements are not atomic because they involve more than one database operation:

  • ALTER FUNCTION
  • ALTER MASK
  • ALTER PERMISSION
  • ALTER PROCEDURE
  • ALTER SEQUENCE
  • ALTER TRIGGER
  • CREATE ALIAS
  • CREATE TYPE
  • CREATE FUNCTION
  • CREATE INDEX
  • CREATE MASK
  • CREATE PERMISSION
  • CREATE PROCEDURE
  • CREATE SCHEMA
  • CREATE SEQUENCE
  • CREATE TABLE
  • CREATE TRIGGER
  • CREATE VARIABLE
  • CREATE VIEW
  • DECLARE GLOBAL TEMPORARY TABLE
  • DROP ALIAS
  • DROP FUNCTION
  • DROP MASK
  • DROP PERMISSION
  • DROP PROCEDURE
  • DROP SCHEMA
  • DROP SEQUENCE
  • DROP TRIGGER
  • DROP TYPE
  • DROP VARIABLE
  • RENAME (See note 1)
Note: RENAME is atomic only if the name or the system name is changed. When both are changed, the RENAME is not atomic.

For example, a CREATE TABLE statement can be interrupted after the Db2 for i physical file has been created, but before the member has been added. Therefore, in the case of create statements, if an operation ends abnormally, you might need to drop the object and then create it again. In the case of a DROP SCHEMA statement, you might need to drop the schema again or use the CL command Delete Library (DLTLIB) to remove the remaining parts of the schema.