Compound SQL (embedded) statement

Combines one or more other SQL statements (sub-statements) into an executable block.

Invocation

This statement can only be embedded in an application program. The entire compound SQL (embedded) statement construct is an executable statement that cannot be dynamically prepared. The statement is not supported in REXX.

Authorization

No privileges are required to invoke an compound SQL (embedded). However, the privileges held by the authorization ID of the statement must include all necessary privileges to invoke the SQL statements that are embedded in the compound statement.

Syntax

Read syntax diagramSkip visual syntax diagramBEGIN COMPOUNDATOMICNOT ATOMICSTATICSTOP AFTER FIRSThost-variableSTATEMENTSsql-statement;END COMPOUND

Description

ATOMIC
Specifies that, if any of the sub-statements within the compound SQL (embedded) statement fail, then all changes made to the database by any of the sub-statements, including changes made by successful sub-statements, are undone.
NOT ATOMIC
Specifies that, regardless of the failure of any sub-statements, the compound SQL (embedded) 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. For example, if 
  SELECT ... INTO :abc ...
is followed by: 
  UPDATE T1 SET C1 = 5 WHERE C2 = :abc
the UPDATE statement will use the value that :abc had at the start of the execution of the compound SQL (embedded) statement, not the value that follows the SELECT INTO.

If the same variable is set by more than one sub-statement, the value of that variable following the compound SQL (embedded) statement is the value set by the last sub-statement.

Note: Non-static behavior is not supported. This means that the sub-statements should be viewed as executing non-sequentially and sub-statements should not have interdependencies.
STOP AFTER FIRST
Specifies that only a certain number of sub-statements will be executed.
host-variable
A small integer that specifies the number of sub-statements to be executed.
STATEMENTS
Completes the STOP AFTER FIRST host-variable clause.
sql-statement
All executable statements except the following can be contained within an embedded static compound SQL (embedded) statement: 
  CALL                FETCH
  CLOSE               OPEN
  CONNECT             PREPARE
  Compound SQL        RELEASE (Connection)
  DESCRIBE            ROLLBACK
  DISCONNECT          SET CONNECTION
  EXECUTE IMMEDIATE   SET variable
Note: INSERT, UPDATE, and DELETE are not supported in compound SQL for use with nicknames.

If a COMMIT statement is included, it must be the last sub-statement. If COMMIT is in this position, it will be issued even if the STOP AFTER FIRST host-variable STATEMENTS clause indicates that not all of the sub-statements are to executed. For example, suppose COMMIT is the last sub-statement in a compound SQL block of 100 sub-statements. If the STOP AFTER FIRST STATEMENTS clause indicates that only 50 sub-statements are to be executed, then COMMIT will be the 51st sub-statement.

An error will be returned if COMMIT is included when using CONNECT TYPE 2 or running in an XA distributed transaction processing environment (SQLSTATE 25000).

Rules

  • Db2® Connect does not support SELECT statements selecting LOB columns in a compound SQL block.
  • No host language code is allowed within a compound SQL (embedded) statement; that is, no host language code is allowed between the sub-statements that make up the compound SQL (embedded) statement.
  • Only NOT ATOMIC compound SQL (embedded) statements will be accepted by Db2 Connect.
  • Compound SQL (embedded) statements cannot be nested.
  • A prepared COMMIT statement is not allowed in an ATOMIC compound SQL (embedded) statement

Notes

  • One SQLCA is returned for the entire compound SQL (embedded) statement. Most of the information in that SQLCA reflects the values set by the application server when it processed the last sub-statement. For instance:
    • The SQLCODE and SQLSTATE are normally those for the last sub-statement (the exception is described in the next point).
    • If a no data found warning (SQLSTATE 02000) is returned, that warning is given precedence over any other warning so that a WHENEVER NOT FOUND exception can be acted upon. (This means that the SQLCODE, SQLERRML, SQLERRMC, and SQLERRP fields in the SQLCA that is eventually returned to the application are those from the sub-statement that triggered the no data found warning. If there is more than one no data found warning within the compound SQL (embedded) statement, the fields for the last sub-statement will be the fields that are returned.)
    • The SQLWARN indicators are an accumulation of the indicators set for all sub-statements.
  • If one or more errors occurred during NOT ATOMIC compound SQL execution and none of these are of a serious nature, the SQLERRMC will contain information about these errors, up to a maximum of seven errors. The first token of the SQLERRMC will indicate the total number of errors that occurred. The remaining tokens will each contain the ordinal position and the SQLSTATE of the failing sub-statement within the compound SQL (embedded) statement. The format is a character string of the form: 
       nnnXsssccccc
    with the substring starting with X repeating up to six more times and the string elements defined as follows.
    nnn
    The total number of statements that produced errors. (If the number would exceed 999, counting restarts at zero.) This field is left-aligned and padded with blanks.
    X
    The token separator X'FF'.
    sss
    The ordinal position of the statement that caused the error. (If the number would exceed 999, counting restarts at zero.) For example, if the first statement failed, this field would contain the number one left-aligned (1 ).
    ccccc
    The SQLSTATE of the error.
  • The second SQLERRD field contains the number of statements that failed (returned negative SQLCODEs).
  • The third SQLERRD field in the SQLCA is an accumulation of the number of rows affected by all sub-statements.
  • The fourth SQLERRD field in the SQLCA is a count of the number of successful sub-statements. If, for example, the third sub-statement in a compound SQL (embedded) statement failed, the fourth SQLERRD field would be set to 2, indicating that 2 sub-statements were successfully processed before the error was encountered.
  • The fifth SQLERRD field in the SQLCA is an accumulation of the number of rows updated or deleted due to the enforcement of referential integrity constraints for all sub-statements that triggered such constraint activity.

Examples

  • Example 1:  In a C program, issue a compound SQL (embedded) statement that updates both the ACCOUNTS and TELLERS tables. If there is an error in any of the statements, undo the effect of all statements (ATOMIC). If there are no errors, commit the current unit of work. 
       EXEC SQL BEGIN COMPOUND ATOMIC STATIC
     UPDATE ACCOUNTS SET ABALANCE = ABALANCE + :delta
       WHERE AID = :aid;
     UPDATE TELLERS SET TBALANCE = TBALANCE + :delta
       WHERE TID = :tid;
     INSERT INTO TELLERS (TID, BID, TBALANCE) VALUES (:i, :branch_id, 0);
     COMMIT;
   END COMPOUND;
  • Example 2:  In a C program, insert 10 rows of data into the database. Assume the host variable :nbr contains the value 10 and S1 is a prepared INSERT statement. Further, assume that all the inserts should be attempted regardless of errors (NOT ATOMIC). 
       EXEC SQL BEGIN COMPOUND NOT ATOMIC STATIC STOP AFTER FIRST :nbr STATEMENTS
     EXECUTE S1 USING DESCRIPTOR :*sqlda0;
     EXECUTE S1 USING DESCRIPTOR :*sqlda1;
     EXECUTE S1 USING DESCRIPTOR :*sqlda2;
     EXECUTE S1 USING DESCRIPTOR :*sqlda3;
     EXECUTE S1 USING DESCRIPTOR :*sqlda4;
     EXECUTE S1 USING DESCRIPTOR :*sqlda5;
     EXECUTE S1 USING DESCRIPTOR :*sqlda6;
     EXECUTE S1 USING DESCRIPTOR :*sqlda7;
     EXECUTE S1 USING DESCRIPTOR :*sqlda8;
     EXECUTE S1 USING DESCRIPTOR :*sqlda9;
   END COMPOUND;