You can choose either statement level or row level error checking. The default is statement level; either the entire statement succeeds, or none of it succeeds. 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. When you choose row level error checking, an error on the fifteenth row results in fourteen rows being inserted into the database. An error message is returned in both cases.
Row level checking enhances performance but generally requires additional coding on your part. Statement level checking requires less coding possibly at the expense of additional overhead during statement execution. Partial changes by a statement are tracked by ALLBASE/SQL if statement level error checking is in effect. This allows the statement to be undone should errors occur. Statement level checking is the ISO/ANSI SQL standard.
The SET DML ATOMICITY statement can be used to set error checking to row or statement level at any point in an application. The statement sets the error checking level for all ALLBASE/SQL statements, including those involving constraints. However, it can be temporarily overridden for constraint errors only, as described in a later section.
Note that when a transaction terminates (with either a COMMIT WORK statement or by being rolled back), the error checking level always returns to the default of statement level.
Refer to the "SQL Commands" chapter of the ALLBASE/SQL Reference Manual for the full syntax of the
SET DML ATOMICITY statement.