BEGIN WORK statement

Use the BEGIN WORK statement to start a transaction, which is a series of database operations that the COMMIT WORK or ROLLBACK WORK statement terminates, and that the database server treats as a single unit of work. This statement is an extension to the ANSI/ISO standard for SQL.

Syntax

(1)
Notes:
  • 1 ESQL/C only

Usage

The BEGIN WORK statement is valid only in a database that supports transaction logging. This statement is not valid in an ANSI-compliant database.

Each row that an UPDATE, DELETE, INSERT, or MERGE statement affects during a transaction is locked and remains locked throughout the transaction. A transaction that contains many such statements or that contains statements that affect many rows can exceed the limits that your operating system or the database server configuration imposes on the number of simultaneous locks.

If no other user is accessing the table, you can avoid locking limits and reduce locking overhead by locking the table with the LOCK TABLE statement after you begin the transaction. Like other locks, this table lock is released when the transaction terminates. The example of a transaction on Example of BEGIN WORK includes a LOCK TABLE statement.
Important: Issue the BEGIN WORK statement only if a transaction is not in progress. If you issue a BEGIN WORK statement while you are in a transaction, the database server returns an error.
The WORK keyword is optional. The following two statements are equivalent: 
BEGIN;
BEGIN WORK;

In reading SQL source code that omits the WORK keyword, do not confuse the BEGIN statement of SQL with the SPL keyword BEGIN, which, together with the END keyword, can be used as a delimiter to define a statement block within an SPL routine

In , if you use the BEGIN WORK statement within a UDR called by a WHENEVER statement, specify WHENEVER SQLERROR CONTINUE and WHENEVER SQLWARNING CONTINUE before the ROLLBACK WORK statement. These statements prevent the program from looping endlessly if the ROLLBACK WORK statement encounters an error or a warning.