To reduce database manager overhead, you can group several SQL statements into a single executable block. Because the SQL statements in the block are substatements that could be executed individually, this kind of code is called compound SQL. In addition to reducing database manager overhead, compound SQL reduces the number of requests that have to be transmitted across the network for remote clients.
There are two types of compound SQL:
The application receives a response from the database manager when all substatements have completed successfully or when one substatement ends in an error. If one substatement ends in an error, the entire block is considered to have ended in an error. Any changes made to the database within the block are rolled back.
Atomic compound SQL is not supported with DB2 Connect.
Example: 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;
The application receives a response from the database manager when all substatements have completed. All substatements within a block are executed regardless of whether or not the preceding substatement completed successfully. The group of statements can only be rolled back if the unit of work containing the NOT ATOMIC compound SQL is rolled back.
Example: 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;
Compound SQL is supported in stored procedures, which are also known as DARI routines, and in the following application development processes:
Embedded static SQL
DB2 Call Level Interface
Dynamic Compound SQL Statements
Dynamic compound statements are compiled by DB2(R) as a single statement. This statement can be used effectively for short scripts that require little control flow logic but significant data flow. For larger constructs with nested complex control flow, consider using SQL procedures.
In a dynamic compound statement you can use the following elements in declarations:
SQL variables in variable declarations of substatements
Conditions in the substatements based on the SQLSTATE values of the condition declaration
One or more SQL procedural statements
Dynamic compound statements can also use several flow logic statements, such as the FOR statement, the IF statement, the ITERATE statement, and the WHILE statement.
If an error occurs in a dynamic compound statement, all prior SQL statements are rolled back and the remaining SQL statements in the dynamic compound statement are not processed.
A dynamic compound statement can be embedded in a trigger, SQL function, or SQL method, or issued through dynamic SQL statements. This executable statement can be dynamically prepared. No privileges are required to invoke the statement but the authorization ID associated with the statement must have the necessary privileges to invoke the SQL statements in the compound statement.