Call Level Interface Guide and Reference

ATOMIC and NOT ATOMIC Compound SQL

A Compound SQL statement block is specified by surrounding the sub-statements by a BEGIN COMPOUND statement and an END COMPOUND statement. The BEGIN COMPOUND syntax is shown below:

>>-BEGIN COMPOUND--+-ATOMIC-----+-STATIC--+----------------------------------+->
                   +-NOT ATOMIC-+         +-STOP AFTER FIRST--?--STATEMENTS--+
 
>--------------------------------------------------------------><
 

ATOMIC

Specifies that, if any of the sub-statements within the Compound SQL statement fails, then all changes made to the database by any of the sub-statements are undone. ATOMIC is not supported in DRDA environments.

NOT ATOMIC

Specifies that, regardless of the failure of any sub-statements, the Compound SQL statement will not undo any changes made to the database by the other sub-statements.

STATIC

Specifies that input variables for all sub-statements retain their original value. If the same variable is set by more than one sub-statement, the value of that variable following the Compound SQL statement is the value set by the last sub-statement.

STOP AFTER FIRST ? STATEMENTS

Specifies that only a certain number of sub-statements are to be executed. If this clause is omitted, all the sub-statements are executed.

The END COMPOUND syntax is shown below:

>>-END COMPOUND--+--------+------------------------------------><
                 +-COMMIT-+
 

Specifying the COMMIT option will commit all the sub-statements if they executed successfully. The COMMIT applies to the current transaction, including statements that precede the compound statement. If COMMIT is specified, and the connection is a coordinated distributed connection (SQL_COORDINATED_TRANS), an error will be returned (SQLSTATE of 25000).

If the COMMIT option is not specified after END COMPOUND, the sub-statements will not be committed unless the application is operating under auto-commit mode, in which case the commit will be issued at the END COMPOUND. For information on the auto-commit mode, refer to "Commit or Rollback".

Figure 15 shows the general sequence of function calls required to execute a compound SQL statement. Note that:

• SQLPrepare() and SQLExecute() can be used in place of SQLExecDirect().

• The BEGIN COMPOUND and END COMPOUND statements are executed with the same statement handle.

• Each sub-statement must have its own statement handle.

• All statement handles must belong to the same connection, and have the same isolation level.

• The sub-statements should be, but do not need to be, prepared before the BEGIN COMPOUND statement, especially in DRDA environments where some optimization may be possible to reduce network flow.

• The statement handles must remain allocated until the END COMPOUND statement is executed.

• The only functions that may be called using the statement handles allocated for the compound sub-statements are:
  • SQLAllocHandle()
  • SQLBindParameter()
  • SQLBindFileToParam()
  • SQLParamData()
  • SQLPutData()
  • SQLExecDirect(), SQLPrepare(), SQLExecute()

• SQLTransact() cannot be called for the same connection, or any connect requests between BEGIN and END COMPOUND.

• The sub-statements may be executed in any order.

• SQLRowCount() (or SQLGetSQLCA()) can be called using the same statement handle as the BEGIN, END COMPOUND statement to get an aggregate count of the rows affected.

Figure 15. Compound SQL

[ DB2 List of Books | Search the DB2 Books ]