Choosing Between Dynamic Compound Statements and SQL Procedures

After knowing the differences between inline SQL PL and SQL PL, you are probably wondering when to choose dynamic compound statements as opposed to SQL procedures and vice versa.

First of all, both are compound statements if the SQL procedure contains more than one statement in the procedure body. Using compound statements usually improves performance because statements are grouped in one execution block. This minimizes network flow by sending only one request to DB2 for a set of statements versus one request for each statement.

At SQL procedure creation time, the procedure is compiled and a package is created. The package contains the execution path of how data will be accessed (also known as the data access path). In other words, the optimizer evaluates the best data access path when the procedure is being created. Depending on the query optimization class, the compile time or procedure creation time varies. On the other hand, dynamic compound statements are dynamic in nature, and data access plans are generated at execution time.

Other than understanding when dynamic compound statements and SQL procedures are being compiled, you should also take notice of the complexity of the logic you are implementing. If the logic is complex, we recommend you use SQL procedures because they support the comprehensive SQL PL with which you can easily implement efficient logic.

On other hand, if the logic is simple and the number of statements is relatively small, consider using inline SQL PL instead. With the power of SQL PL, SQL procedures can definitely handle simple logic but use of stored procedures incurs some overhead. In cases when only one or two SQL statements are wrapped inside a stored procedure, there might not be any performance gain. Sometimes it might actually negatively impact performance by using such a stored procedure. In such a case, you can probably obtain better performance results by using inline SQLs.

When inline SQLs are being executed, they are "inlined" into the calling SQL statements. These statements will be expanded to also contain the logic of the compound statements. Therefore, it is always a good practice to keep the inline SQL simple and short so that statement compilation and optimization can be performed efficiently.

Atomicity is also another area to think about. As highlighted in the previous section, commit and rollback are not allowed in dynamic compound statements because they must be atomic. If you need to implement transaction controls inside the compound statements, SQL procedures will be your only choice.