ALLBASE/SQL Reference Manual (36216-90216)

56 Chapter1
Introduction
Error Conditions in ALLBASE/SQL
Error Conditions in ALLBASE/SQL
When you issue an SQL statement, error messages are returned if the statement cannot be
carried out as intended. In an interactive session with ISQL, the messages are displayed
on your terminal. In application programs, you access the message buffer directly by using
the SQLEXPLAIN statement. The effect of an error on your session depends on three factors:
• Severity of the error
• Atomicity level set within the transaction
• Constraint checking mode set within the transaction
Severity of Errors
In general, errors result in partially or completely undoing the effects of an SQL
statement. If the error is very severe, the transaction is rolled back. When a transaction is
rolled back, ALLBASE/SQL displays a message like the following along with other
messages:
Your current transaction was rolled back by DBCore. (DBERR 14029)
If an error is less severe, the statement is undone, but the transaction is allowed to
continue.
Atomicity of Error Checking
By default, error checking is done at the statement level. In other words, the entire
statement either succeeds or fails. This means that for set operations, the statement
succeeds for all members of the set or fails for all members of the set. For example, if there
is an error on the fifteenth row of a twenty-row BULK INSERT statement, the entire
statement has no effect, and no rows are inserted. Or if an UPDATE statement that affects
twenty rows creates a uniqueness violation for one row, the statement will fail for all rows.
This approach guarantees data integrity for the entire statement. Under special
circumstances, you can choose a different atomicity level for error checking:
• Row level
• Beyond the statement level
Setting the Atomicity to the Row Level
Sometimes statement level atomicity has drawbacks which you can correct. For example,
data manipulation statements involving large amounts of data require considerable
overhead for logging when issued at statement level, and this can impair performance. For
better performance, you can set atomicity to row level. With row level atomicity, if an error
occurs on one row, earlier rows are not undone. For example, for an error on the fifteenth
row of a twenty-row BULK INSERT, statement execution stops at the fifteenth row, but the
first fourteen rows will be processed unless you use the ROLLBACK WORK statement. To use
row level error checking, issue the following statement:
SET DML ATOMICITY AT ROW LEVEL