Advanced Database Feature Guide


Using Compound SQL

Compound SQL allows multiple statements to be grouped into a single executable block. This block of statements, together with any input parameter values can then be executed in a single contiguous stream, reducing the execution time and network traffic. Compound SQL is most often used to efficiently execute a series of INSERT, UPDATE and DELETE statements.

Any SQL statement that can be prepared dynamically, other than a query, can be executed as a statement inside a compound statement. Statements within a compound SQL statement are referred to as sub-statements. Compound SQL does not guarantee the order in which the sub-statements are executed, therefore there must be no dependency between the statements.

Compound SQL statements cannot be nested. The authorization ID of the Compound SQL statement must be the appropriate authorization for all the individual sub-statements contained within the compound SQL statement.

When executed, a compound SQL statement will return an instance of the UtyDB2Result class from which the number of affected rows can be determined.

Attention: Compound SQL is supported when connected to DB2 Universal Database, or DRDA environments with DB2 Connection V 2.3 or higher.

The following table describes additional parameters that may be used to control how the sub-statements within a compound statement are executed. The first column shows the message that can be sent to an instance of a compound SQL descriptor class while the second show the equivalent SQL syntax.

Message SQL Syntax Descriptions
useAtomicMode ATOMIC Specifies that, if any of the sub-statements within the compound SQL statement failed, then all changes made to the database by any of the sub-statements are undone. ATOMIC is not supported by DRDA environments.
useNotAtomicMode 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
executionCount:? STOP AFTER FIRST ? STATEMENTS Specifies that only a certain number of sub-statements will be executed. A value of nil indicates that all of the statements will be executed.
autoCommit: true COMMIT Indicates that the compound SQL statement should be committed upon successful execution.

Setting the compound statement’s auto commit option to true will commit all the sub-statements if they executed successfully. This option also applies to the current transaction, included statements that precede the compound statement.

If compound statement’s auto commit is not set to true, the sub-statements will not be committed unless the application is operating under auto-commit mode, in which case the commit will be issued after the last sub-statement has been executed.

Compound SQL Example

The following is a simple example that does not involve the use of parameters, although both parameter markers and named parameters are allowed.

result := 
connection newCompoundSqlDescriptor halt 
addSql: 'INSERT INTO TEST VALUES ( 10, ''ten'' )'; 
addSql: 'INSERT INTO TEST VALUES ( 11, ''eleven'' )'; 
useAtomicMode; 
executeOn: connection. 
result numberOfAffectedRows. 
2 
 


[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]