Combines one or more other SQL statements (sub-statements) into an executable block. Please see SQL Procedures for Compound SQL statements within SQL procedures.
Invocation
This statement can only be embedded in an application program. The entire Compound SQL statement construct is an executable statement that cannot be dynamically prepared. The statement is not supported in REXX.
Authorization
None for the Compound SQL statement itself. The authorization ID of the Compound SQL statement must have the appropriate authorization on all the individual statements that are contained within the Compound SQL statement.
Syntax
>>-BEGIN COMPOUND----+-ATOMIC-----+--STATIC--------------------->
'-NOT ATOMIC-'
>-----+----------------------------------------------+---------->
'-STOP AFTER FIRST--host-variable--STATEMENTS--'
.-------------------------.
V |
>--------+-------------------+--+--END COMPOUND----------------><
'-sql-statement--;--'
Description
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 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 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. |
|
| CALL | OPEN |
|
| CLOSE | PREPARE |
|
| CONNECT | RELEASE (Connection) |
|
| Compound SQL | RELEASE SAVEPOINT |
|
| DESCRIBE | ROLLBACK |
|
| DISCONNECT | SAVEPOINT |
|
| EXECUTE IMMEDIATE | SET CONNECTION |
|
| FETCH |
|
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
One SQLCA is returned for the entire Compound SQL 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:
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 on up to a maximum of seven of these 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 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.
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 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 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;