HP 3000 Manuals HP 3000 Manuals
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 } LEVEL
{STATEMENT}
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.