Dynamic SQL Guidelines

The following tips, tricks, and guidelines should be followed to ensure that dynamic SQL is used in an optimal manner in your shop.

Favor Static SQL

Static SQL might be more efficient than dynamic SQL because dynamic SQL requires the execution of the PREPARE statement during program execution. Static SQL is prepared (bound) before execution.

Static SQL should be sufficient for the programming needs of as much as 90% of the applications you develop. If static SQL does not provide enough flexibility for the design of changeable SQL statements, consider using dynamic SQL. But keep in mind that in many cases the perceived need for dynamic SQL is merely the need for a series of static SQL statements in disguise.

NOTE

Many newer applications use dynamic SQL. ODBC and JDBC require dynamic SQL. Most third-party applications, such as Peoplesoft and SAP R/3, use dynamic SQL. Usage of dynamic SQL is increasing and the days of being able to ignore dynamic SQL are long gone.

Use the Appropriate Class of Dynamic SQL

After you decide to use dynamic SQL rather than static SQL, be sure to code the correct class of dynamic SQL. Do not favor one class of dynamic SQL over another based solely on the difficulty of coding. Consider both the efficiency of the program and the difficulty of maintenance, as well as the difficulty of coding a dynamic SQL program. Performance is often the most important criterion. If a dynamic SQL program does not perform adequately, you should convert it to either static SQL or another class of dynamic SQL.

Favor non-select dynamic SQL over EXECUTE IMMEDIATE because the former gives the programmer additional flexibility in preparing SQL statements, which usually results in a more efficient program. Also, favor varying-list dynamic SQL over fixed-list dynamic SQL because the first gives the programmer greater control over which columns are accessed. Additionally, varying-list dynamic SQL gives the DB2 optimizer the greatest amount of freedom in selecting an efficient access path (for example, a greater opportunity for index-only access).

When you use varying-list dynamic SQL, overhead is incurred as the program determines the type of SQL statement and uses the SQLDA to identify the columns and their data types. Weigh the cost of this overhead against the opportunities for a better access path when you decide between fixed-list and varying-list dynamic SQL.

Do Not Fear Dynamic SQL

Dynamic SQL provides the DB2 programmer with a rich and useful set of features. The belief that dynamic SQL always should be avoided in favor of static SQL is slowly but surely evaporating. Dynamic SQL becomes more efficient with each successive release of DB2, thereby enticing users who have been frustrated in their attempts to mold dynamic SQL into the sometimes-rigid confines of static SQL.

If you design dynamic SQL programs with care and do not abuse SQL's inherent functionality, you can achieve great results. Follow all the guidelines in this chapter closely. See Part V, "DB2 Performance Tuning," for a discussion of tuning and resource governing for dynamic SQL applications.

By this guideline, I do not mean to imply that you should use dynamic SQL where it is not merited. Simply apply common sense when deciding between static and dynamic SQL for your DB2 applications. Remember, any rule with a "never" in it (such as " never use dynamic SQL") is usually unwise!

Avoid Dynamic SQL for Specific Statements

Not every SQL statement can be executed as dynamic SQL. Most of these types of SQL statements provide for the execution of dynamic SQL or row-at-a-time processing. The following SQL statements cannot be executed dynamically:

 BEGIN DECLARE SECTION CLOSE CONNECT DECLARE DESCRIBE END DECLARE SECTION EXECUTE EXECUTE IMMEDIATE FETCH INCLUDE OPEN PREPARE RELEASE  connection  SIGNAL SQLSTATE VALUES VALUES INTO WHENEVER 

CAUTION

Be aware of the following additional restrictions on dynamic SQL:

• Although the CALL statement can be dynamically prepared, it cannot be issued dynamically.

• You can DECLARE a global temporary table using dynamic SQL, but you cannot DECLARE a cursor, a table, or a statement.

• Usage of the SET statement is limited. You can use SET to set CURRENT DEGREE , CURRENT LC_TYPE , CURRENT OPTIMIZATION HINT , CURRENT PRECISION , CURRENT RULES , CURRENT SQLID , PATH , or a host variable to CURRENT APPLICATION ENCODING SCHEME . You cannot use SET to set CURRENT APPLICATION ENCODING SCHEME , CONNECTION , CURRENT PACKAGE PATH , CURRENT PACKAGESET , or a host variable to anything other than CURRENT APPLICATION ENCODING SCHEME .

Use Parameter Markers Instead of Host Variables

Dynamic SQL statements cannot contain host variables. They must use instead a device called a parameter marker . A parameter marker can be thought of as a dynamic host variable.

Use parameter markers instead of simply changing the dynamic SQL to code a new, specific literal in the predicate each time. Doing so allows DB2 to make use of the dynamic statement cache and avoid rebinding the mini-plan for the statement. To take advantage of the dynamic SQL cache the dynamic SQL statement must be exactly the same for each execution, and changing a literal changes the statement invalidating the mini-plan for use. For example, favor this dynamic statement:

 SELECT LASTNAME FROM   DSN8810.EMP WHERE  EMPNO = ?; 

over this one:

 SELECT LASTNAME FROM   DSN8810.EMP WHERE  EMPNO = '000010'; 

In the first case you can supply multiple different values for the parameter marker. In the second case, each time the EMPNO value changes you will have to change the SQL statement (and thereby forgo use of the dynamic statement cache).

Consider Dynamic SQL When Accessing Non-Uniform Data

If you're accessing a table in which the data is not evenly distributed, dynamic SQL may perform better than static SQL. Distribution statistics are stored in the DB2 Catalog in two tables: SYSIBM.SYSCOLDISTSTAT and SYSIBM.SYSCOLDIST .

By default, RUNSTATS stores the 10 values that appear most frequently in the first column of an index along with the percentage that each value occurs in the column. As of DB2 V5, the RUNSTATS utility provides options for which distribution statistics can be collected for any number of values (and for any number of columns).

In some cases, the optimizer uses this information only for dynamic SQL. Static SQL still assumes even distribution unless the pertinent predicates use hard-coded values instead of host variables or dynamic reoptimization was specified at bind time using the REOPT(VARS) parameter.

Use Bind-Time Authorization Checking

Prior to DB2 V4, users of dynamic SQL programs required explicit authorization to the underlying tables accessed by the program being executed. For complex programs, the task of granting authority multiple types ( INSERT , UPDATE , DELETE , INSERT ) of security for multiple tables to multiple users is time consuming, error prone, and difficult to administer.

The DYNAMICRULES parameter of the BIND command provides flexibility of authorization checking for dynamic SQL programs. Specifying DYNAMICRULES(BIND) causes DB2 to check for authorization at BIND time using the authority of the binding agent. Just like static SQL programs, no additional runtime authorization checking is required.

Specifying DYNAMICRULES(RUN) causes dynamic SQL programs to check for authorization at runtime (just like pre-V4 dynamic programs).

Consider Caching Prepared Statements

Prepared dynamic SQL statements can be cached in memory so that they can be reused. This feature enables programs to avoid redundant optimization and its associated overhead. Dynamic SQL caching must be enabled by the system administrator, and is either on or off at the DB2 subsystem level.

When dynamic SQL caching is enabled, dynamic SELECT , INSERT , UPDATE , and DELETE statements are eligible to be cached. The first PREPARE statement creates the dynamic plan and stores it in the EDM pool. If a PREPARE is requested for the same SQL statement, DB2 can reuse the cached statement. DB2 performs a character-by-character comparison of the SQL statement, rejecting reuse if any differences are found between what is cached and what is being requested for execution. A good rule of thumb is to assume that the second execution of a dynamic SQL statement costs approximately .01 more than the same static SQL statement. For example, if the static SQL executes in 1 second, the second execution of an equivalent, already optimized dynamic SQL statement should take about 1.01 seconds.

To ensure that dynamic statements are cached, the following two conditions must be met:

• Dynamic SQL cache is turned on by the system administrator. Dynamic SQL caching is not the default; it must be explicitly specified to be turned on.

• Do not use the NOREOPT(VARS) BIND option for the plan or package. The purpose of caching is to avoid having to reoptimize, so NOREOPT(VARS) is the compatible option for dynamic SQL caching.

Cached statements can be shared among threads, plans, and packages. However, cached statements cannot be shared across data sharing groups because each member has its own EDM pool.

NOTE

To share a cached dynamic SQL statement, the following must be the same for both executions of the statement:

• BIND authority

• DYNAMICRULES value

• CURRENTDATA value

• ISOLATION level

• SQLRULES value

• QUALIFIER value

• CURRENT DEGREE special register

• CURRENT RULES special register

In general, for systems with heavy dynamic SQL use, especially where dynamic SQL programs issue the same statement multiple times, dynamic SQL caching can improve performance by reducing the overhead of multiple PREPARE s. However, dynamic SQL caching requires additional memory to increase the size of the EDM pool and can cause performance degradation for dynamic SQL that does not meet the preceding requirements because of the following:

• A cost is associated with caching an SQL statement. (DB2 must spend time moving the dynamic plan to the EDM pool.)

• If the SQL statements do not match, a cost is associated with the comparison that DB2 performs.

• EDM pool contention can occur when caching is enabled for environments in which dynamic SQL is used heavily.

The bottom line is that each shop must determine whether dynamic SQL caching will be beneficial given its current and planned mix of static and dynamic SQL. At any rate, the DBA group must communicate whether dynamic SQL caching is enabled to assist application developers in their decisions to use dynamic or static SQL.

CAUTION

Caching dynamically prepared statements can have a dramatic impact on your EDM pool usage. Be sure to plan accordingly and ensure that you have sized your EDM pool appropriately to accommodate the additional usage for dynamic statement caching. Even better, set up your dynamic statement cache in a data space to avoid thrashing in the EDM pool.

Reduce Prepares with KEEPDYNAMIC(YES)

Use the KEEPDYANMIC(YES) BIND option to save dynamic plans across COMMIT points. With KEEPDYNAMIC(NO) , dynamic SQL statements must be re-prepared after a COMMIT is issued. By specifying KEEPDYNAMIC(YES) , dynamic SQL programming is easier and more resulting programs can be efficient because fewer PREPARE s are required to be issued.

Encourage Parallelism

Use the SET CURRENT DEGREE = "ANY" statement within dynamic SQL programs to encourage the use of query I/O, CPU, and Sysplex parallelism. When DB2 uses multiple, parallel engines to access data, the result can be enhanced performance.

Before you blindly place this statement in all dynamic SQL programs, however, be sure to analyze your environment to ensure that adequate resources are available to support parallelism. For example, ensure that adequate buffer space is available for multiple concurrent read engines.

Use Dynamic SQL to Access Dynamic Data

Dynamic SQL can prove beneficial for access to very active tables that fluctuate between many rows and few rows between plan rebinding. If you cannot increase the frequency of plan rebinding, you can use dynamic SQL to optimize queries based on current RUNSTATS .

Consider the QMFCI

QMF customers have another reason to use dynamic SQL ”to take advantage of the capabilities of the QMF Command Interface (QMFCI). Dynamic SQL is invoked when you use QMF to access DB2 data. The functionality provided by the QMFCI includes left and right scrolling and data formatting. The addition of these capabilities can offset any performance degradation that dynamic SQL might cause.

Be Wary of Poorly Designed Dynamic SQL

Online transaction-based systems require well-designed SQL to execute with subsecond response time. If you use dynamic SQL, the system is less likely to have well-designed SQL. If a program can change the SQL "on the fly," the control required for online systems is relinquished and performance can suffer.

Do Not Avoid Varying-List SELECT

Often, application developers do not take the time to design a dynamic SQL application properly if it requires variable SELECT s. Usually, a varying-list SELECT is needed for proper performance, but a fixed-list SELECT is used to avoid using the SQLDA and pointer variables. This use limits the access path possibilities available to the optimizer and can degrade performance.

Be Aware of Dynamic SQL Tuning Difficulties

Dynamic SQL is more difficult to tune because it changes with each program execution. Dynamic SQL cannot be traced using the DB2 Catalog tables ( SYSDBRM , SYSSTMT , SYSPLANREF , and SYSPLAN ) because the SQL statements are not hard-coded into the program and therefore are not in the application plan.

If your shop is using dynamic SQL, you should consider purchasing a SQL performance monitor that can track and analyze dynamic SQL ”for example, Apptune from BMC Software or Detector from Computer Associates.

Use DB2's Performance Governing Facilities

DB2 provides two types of resource governing: reactive and predictive. Both types of governing can be used to control the amount of resources consumed by dynamic SQL.

Proper administration of the Resource Limit Facility (RLF) is needed to control DB2 resources when dynamic SQL is executed. Thresholds for CPU use are coded in the RLF on an application-by-application basis.

When the RLF threshold is reached, the application program does not ABEND with reactive governing. An SQL error code is issued when any statement exceeds the predetermined CPU usage. This environment requires additional support from a DBA standpoint for RLF administration and maintenance, as well as additional work from an application development standpoint for enhancing error-handling procedures.

With predictive governing, you can code the RLF to stop a statement from even starting to execute. This is not possible with reactive governing where the statement must execute until the threshold is reached, at which point the RLF stops the query. By stopping a resource-hogging query before it begins to execute, you can avoid wasting precious resources on a statement that will never finish anyway.

For details on using the RLF to set up reactive and predictive governing, refer to Chapter 29, "DB2 Resource Governing."

Use Dynamic SQL for Tailoring Access

If you need to tailor access to DB2 tables based on user input from a screen or pick list, using dynamic SQL is the most efficient way to build your system. If you use static SQL, all possible rows must be returned, and the program must skip those not requested. This method incurs additional I/O and usually is less efficient than the corresponding dynamic SQL programs.

Consider the following: What if, for a certain query, 20 predicates are possible. The user of the program is permitted to choose up to 6 of these predicates for any given request. How many different static SQL statements do you need to code to satisfy these specifications?

First, determine the number of different ways that you can choose 6 predicates out of 20. To do so, you need to use combinatorial coefficients. So, if n is the number of different ways, then

 n = (20 x 19 x 18 x 17 x 16 x 15) / (6 x 5 x 4 x 3 x 2 x 1) n = (27,907,200) / (720) n = 38,760 

You get 38,760 separate static SELECT s, which is quite a large number, but it is still not sufficient to satisfy the request! The total number of different ways to choose 6 predicates out of 20 is 38,760 if the ordering of the predicates does not matter (which, for all intents and purposes, it does not). However, because the specifications clearly state that the user can choose up to six, you have to modify the number. You therefore have to add in the following:

• The number of different ways of choosing 5 predicates out of 20

• The number of different ways of choosing 4 predicates out of 20

• The number of different ways of choosing 3 predicates out of 20

• The number of different ways of choosing 2 predicates out of 20

• The number of different ways of choosing 1 predicate out of 20

You can calculate this number as follows :

Ways to Choose 6 Predicates Out of 20

(20 x 19 x 18 x 17 x 16 x 15) / (6 x 5 x 4 x 3 x 2 x 1) = 38,760

Ways to Choose 5 Predicates Out of 20

(20 x 19 x 18 x 17 x 16) / (5 x 4 x 3 x 2 x 1) = 15,504

Ways to Choose 4 Predicates Out of 20

(20 x 19 x 18 x 17) / (4 x 3 x 2 x 1) = 4,845

Ways to Choose 3 Predicates Out of 20

(20 x 19 x 18) / (3 x 2 x 1) = 1,140

Ways to Choose 2 Predicates Out of 20

(20 x 19) / (2 x 1) = 190

Ways to Choose 1 Predicate Out of 20

20 / 1 = 20

Total Ways to Choose Up To 6 Predicates Out of 20

38,760 + 15,504 + 4,845 + 1,140 + 190 + 20 = 60,459

The grand total number of static SQL statements that must be coded actually comes to 60,459. In such a situation, in which over 60,000 SQL statements must be coded if static SQL must be used, you have one of two options:

• You can code for 40 days and 40 nights hoping to write 60,459 SQL statements successfully.

• You can compromise on the design and limit the users' flexibility.

Of course, the appropriate solution is to abandon static SQL and use dynamic SQL in this situation.

Use Dynamic SQL for Flexibility

Dynamic SQL programs sometimes respond more rapidly to business rules that change frequently. Because dynamic SQL is formulated as the program runs, the flexibility is greater than with static SQL programs. Users can react more quickly to changing business conditions by changing their selection criteria.

Using Dynamic SQL or Static SQL with Reoptimization

Both dynamic SQL and static SQL using the REOPT(VARS) BIND option can be used to reoptimize SQL when host variables or parameter marker values change. The ability to reoptimize enables DB2 to choose an appropriate access path for the SQL statement. When the values to be used in SQL statements vary considerably and impact the access path, be sure to enable one of the reoptimization strategies to optimize performance. But which is the better choice? It depends on the following factors:

• Dynamic SQL is more flexible but more complex.

• Dynamic SQL is implemented at the statement level. A program can contain both dynamic and static SQL statements.

• Static SQL with REOPT(VARS) is easy to specify because it is a simple BIND parameter. The program does not need to be changed.

• The REOPT(VARS) parameter is specified at the plan or package level. It cannot be specified at the statement level.

In general, favor dynamic SQL with the dynamic statement cache when the cost of the bind is high compared to the cost of running the SQL. Use static SQL with reoptimization when the cost of the bind is low compared to the cost of running the SQL.

Reexamine Database Design for Systems with High Dynamic SQL Usage

For systems with a lot of dynamic SQL, consider designing your database objects with maintenance in mind. For example, you might choose to create one table space per database (as well as one table per table space) to improve the availability window for making structural changes.

To understand why such an approach can be helpful, consider the following scenario. TABLE_A and TABLE_B are defined within the same database. Now then, suppose a column must be added to TABLE_A . You must perform such an operation when no concurrent dynamic SQL is accessing any table in the database ”that is, both TABLE_A and TABLE_B . However, if the tables are assigned to separate databases, the problem is limited to just the table being changed.