ALLBASE/SQL Reference Manual (36216-90216)

Chapter 12 527
SQL Statements S - Z
SET DML ATOMICITY
—AtCOMMIT 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)