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 ON (See note 2)

• LABEL ON (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 constraints need to be added or removed, as well as column definitions changed, the operations are processed one at a time, so the entire SQL statement is not atomic. The order of operation is:

   • Remove constraints

   • Drop columns for which the RESTRICT option was specified

   • All other column definition changes (DROP COLUMN CASCADE, ALTER COLUMN, ADD COLUMN)

   • Add constraints

2. If multiple columns are specified for a COMMENT ON or LABEL ON statement, the columns are processed one at a time, so the entire SQL statement is not atomic, but the COMMENT ON or LABEL ON 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 PROCEDURE

• ALTER SEQUENCE

• CREATE ALIAS

• CREATE DISTINCT TYPE

• CREATE FUNCTION

• CREATE INDEX

• CREATE PROCEDURE

• CREATE SCHEMA

• CREATE SEQUENCE

• CREATE TABLE

• CREATE TRIGGER

• CREATE VIEW

• DROP ALIAS

• DROP DISTINCT TYPE

• DROP FUNCTION

• DROP PROCEDURE

• DROP SCHEMA

• DROP SEQUENCE

• DROP TRIGGER

• RENAME (See note 1)

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 can be interrupted after the DB2^® UDB for iSeries™ 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 may need to drop the object and then create it again. In the case of a DROP SCHEMA statement, you may need to drop the schema again or use the CL command Delete Library (DLTLIB) to remove the remaining parts of the schema.

 

Parent topic:

Data integrity