Knowledge to identify when to use Compound SQL


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:

Compound SQL is supported in stored procedures, which are also known as DARI routines, and in the following application development processes:

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:

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.

Professional hosting     Belorussian informational portal         Free SCWCD 1.4 Study Guide     Free SCDJWS 1.4 Study Guide     SCDJWS 1.4 Quiz     Free IBM Certified Associate Developer Study Guide     IBM Test 000-287. Enterprise Application Development with IBM WebSphere Studio, V5.0 Study Guide     SCDJWS 5.0 Quiz