Task-Level Commit vs. Record-Level Commit
For each import task or SQL task, if you do not set the Commit Type attribute to: None, you can choose between record-level commits and task-level commits, by setting the Commit Type attribute of a task to the appropriate value.
The commit type you choose has important consequences when an error occurs. Suppose, for example, that your job manipulates 1,000 records, and that record number 800 produces a violation, and is rejected.
In the case of task-level commits:
In the case of record-level commits:
However, there is an important difference in the way import tasks and SQL tasks are processed when you choose record-level commits, as follows.
A SQL task has one or more SQL task statements. Each statement manipulates the records addressed in its WHERE clause. Each manipulated record is committed separately. Then, the following statement is performed, and the addressed records are committed individually again (if they do not produce violations). This continues until all statements have been executed.
With a record-level commit, each SQL statement runs in a separate transaction. A violation causes a rollback of all manipulations caused by the SQL statement.
With a task-level commit, all SQL statements run in the same transaction. A violation causes a rollback for the whole transaction.
In the case of import tasks, ALL the import SQL statements are performed on a single record, which is then committed (assuming no violations occur).
With a record-level commit, all SQL statements are executed for each record in the driving set. For each record in the driving set, all manipulations run in the same transaction, and a violation causes a rollback for all manipulations on this record.
With a task-level commit, all SQL statements are executed for each record in the driving set. All manipulations for all driving set records run in the same transaction. A violation causes a rollback for all maniulations for all driving set records.