|
|
The SET DML ATOMICITY statement sets the general error checking level in
data manipulation statements.
ISQL or Application Programs
SET DML ATOMICITY AT {ROW
STATEMENT} LEVEL
- 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.
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.
Anyone can use the SET DML ATOMICITY statement.
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.
|