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
>>-BEGIN COMPOUND--+-ATOMIC-----+--STATIC----------------------->
'-NOT ATOMIC-'
>--+---------------------------------------------+-------------->
'-STOP AFTER FIRST--host-variable--STATEMENTS-'
.----------------------.
V |
>----+------------------+-+--END COMPOUND----------------------><
'-sql-statement--;-'
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;
DB2 10.5 for Linux, UNIX, and Windows