Sometimes you want to make updates only with other updates (or deletions). For example, a new employee is hired and is assigned to a project. This involves updates to the EMPLOYEE and EMP_ACT tables. In addition, the employee is made responsible for the project, requiring an update of the PROJECT table (RESPEMP column). The three tables are updated simultaneously, because if only some of the updates are made, and an interval of time passes before the remaining updates are made, anyone accessing the tables during the interval for information about the new employee receives inaccurate information.
For simultaneous updating of tables, you can group SQL statements into a single unit. The statements are run as a group, and only upon your specification. You can also cancel the statements as a group. Such a grouping of one or more statements is called a logical unit of work (LUW). Two system settings are used in LUW processing: AUTOCOMMIT ON and AUTOCOMMIT OFF.
With the AUTOCOMMIT ON setting, each statement is treated as an LUW. The work performed by each statement is committed as part of statement processing, resulting in a permanent change. There are, however, a few exceptions. When an INSERT, UPDATE, or DELETE statement that affects more than one row is typed, the LUW is not completed until the next statement is typed. If the next statement is CANCEL or ROLLBACK, the work performed by the INSERT, UPDATE, or DELETE operation before the CANCEL or ROLLBACK is undone instead of being committed. If the next statement is not CANCEL or ROLLBACK, the work performed by the INSERT, UPDATE, or DELETE is committed automatically before the new statement is processed. The CANCEL command is described under Chapter 10, ISQL Commands.
The default setting in ISQL is AUTOCOMMIT ON.
Use the AUTOCOMMIT OFF setting to control the committing of information. After you type SET AUTOCOMMIT OFF, any subsequent statements you type are grouped into a single LUW. The statements are processed but not committed until you type the SQL statement COMMIT. As you type the statements, the table data shown on the display looks as if the changes are being committed: they are not. In addition, no other system user can view or modify the changes that you are making until you type the COMMIT statement. Typing COMMIT completes your LUW and commits all processing performed since the beginning of the LUW.
After you set AUTOCOMMIT OFF, and if for any reason, such as an update error, you decide not to commit the LUW processing, you can cancel all processing performed since the beginning of the LUW by typing ROLLBACK. After you set AUTOCOMMIT OFF, you must explicitly end the LUW by typing COMMIT or ROLLBACK.
Attention Use AUTOCOMMIT OFF cautiously because it prevents other users from accessing the rows of tables used in your processing. In particular, avoid using it for a query.
To return to the condition in which ISQL automatically commits your work, type SET AUTOCOMMIT ON. The system displays a message requesting you to commit or rollback any work done during the logical unit of work. You must respond to this message before any further statements can be typed.
Messages are displayed after data has been inserted, deleted, or updated to indicate the number of rows affected by each operation. These messages help you verify the changes.
A single statement can change many rows in a table. If a statement error occurs after only some rows have been changed, all changes are rolled back or withdrawn, and the entire operation fails. If the failed statement is part of an LUW, previously completed statements in the LUW are not affected. You still have the option of entering COMMIT or ROLLBACK for the other statements in the LUW.