SET DML ATOMICITY

The SET DML ATOMICITY statement sets the general error checking level in data manipulation statements.

Scope

ISQL or Application Programs

SQL Syntax

  SET DML ATOMICITY AT {ROW 
                        STATEMENT} LEVEL

Parameters

ROW: specifies that general error checking occurs at the row level. The term general error checking refers to any errors, for example, arithmetic overflows or constraint violation errors.
STATEMENT: specifies that general error checking occurs at the statement level. This is the default general error checking level.

Description

Constraint errors (UNIQUE, REFERENTIAL, or CHECK constraint violations) are handled just like any other general error when constraint checking is in IMMEDIATE mode. In this case, error handling follows the behavior outlined below. However, when you SET CONSTRAINTS DEFERRED, constraint error checking behaves differently as described in the SET CONSTRAINTS statement in this chapter. The following discussion assumes that constraint checking is in IMMEDIATE mode.
Setting DML ATOMICITY affects the BULK INSERT, DELETE, UPDATE, UPDATE WHERE CURRENT, DELETE WHERE CURRENT statements, and the ISQL LOAD command when they operate on a set of rows.
When you use SET DML ATOMICITY AT STATEMENT LEVEL (the default), and if an error occurs:
- Work done by the statement before an error occurs is undone, and the statement is no longer in effect.
- At COMMIT WORK, work done by statements within the transaction that executed without error will be written to the DBEnvironment, while statements with errors will have no effect.
When you use SET DML ATOMICITY AT ROW LEVEL (not the default), and if an error occurs:
- Work done by a statement before an error occurs is not undone, but no further action is taken by the statement.
- At COMMIT WORK, work done by statements within the transaction that executed without error will be written to the DBEnvironment. Within statements which generated errors at a specific row, work done on rows prior to the row generating the error will be written to the DBEnvironment; no work will be done from the erroneous row, forward.
Unless you have a severe error (4008, 4009, or -14024 or greater), the transaction is not rolled back, and previous statements within the transaction are still in effect.
When a transaction ends, DML ATOMICITY remains at or is returned to STATEMENT level.
The SET DML ATOMICITY statement is sensitive to savepoints. If you establish a save point, then change the atomicity level, and then roll back to the savepoint, the atomicity level set after the savepoint will be undone.
If DML ATOMICITY is set at ROW and you set it to ROW again, a warning message is issued. If DML ATOMICITY is set at STATEMENT and you set it to STATEMENT again, a warning message is issued.
DML ATOMICITY does not apply to DDL statements. DDL statements are always checked at statement level.
DML ATOMICITY does not apply to statements that may fire rules. Such statements are always checked at statement level.
When the SET CONSTRAINTS statement sets constraint error checking to IMMEDIATE, constraint error checking will be performed at the level set by the most recent SET DML ATOMICITY statement. Refer to the SET CONSTRAINTS statement for more information.

Authorization

Anyone can use the SET DML ATOMICITY statement.

Example

The user wants to load supposedly error-free data into PurchDB.Parts.

BEGIN WORK

Immediately after DBEnvironment creation, when initially loading the tables while non-archive mode logging is in effect, performance can be improved if you SET DML ATOMICITY to ROW LEVEL. However, if an error is encountered, the insertion of rows prior to the erroneous row will not be rolled back.

Error checking is set at row level.

SET DML ATOMICITY AT ROW LEVEL

The rows to be inserted are in the array called PartsArray.

   BULK INSERT INTO PurchDB.Parts
             VALUES (:PartsArray, :StartIndex, :NRows)

You can set the level back to statement level before the transaction ends.

   .
   .
   .
   Other statements are listed here.
   .
   .
   .
   COMMIT WORK

If you have not already set error checking back to statement level, it is automatically set back to statement level when the transaction ends.