Differences between static and dynamic SQL

Static and dynamic SQL are each appropriate for different circumstances. You should consider the differences between the two when determining whether static SQL or dynamic SQL is best for your application.

Flexibility of static SQL with host variables

Introductory concepts

When you use static SQL, you cannot change the form of SQL statements unless you make changes to the program. However, you can increase the flexibility of static statements by using host variables.

Example: In the following example, the UPDATE statement can update the salary of any employee. At bind time, you know that salaries must be updated, but you do not know until run time whose salaries should be updated, and by how much. 
01  IOAREA.
    02  EMPID              PIC X(06).
    02  NEW-SALARY         PIC S9(7)V9(2) COMP-3.
⋮ (Other declarations)
READ CARDIN RECORD INTO IOAREA
  AT END MOVE 'N' TO INPUT-SWITCH.
⋮ (Other COBOL statements)
EXEC SQL
  UPDATE DSN8A10.EMP
    SET SALARY = :NEW-SALARY
    WHERE EMPNO = :EMPID
END-EXEC.
The statement (UPDATE) does not change, nor does its basic structure, but the input can change the results of the UPDATE statement.

Flexibility of dynamic SQL

What if a program must use different types and structures of SQL statements? If there are so many types and structures that it cannot contain a model of each one, your program might need dynamic SQL.

You can use one of the following programs to execute dynamic SQL:
DB2 Query Management Facility™ (QMF™)
Provides an alternative interface to DB2 that accepts almost any SQL statement
SPUFI
Accepts SQL statements from an input data set, and then processes and executes them dynamically
command line processor
Accepts SQL statements from a UNIX System Services environment.

Limitations of dynamic SQL

You cannot use some of the SQL statements dynamically.

Start of changeFor reactive governing cases, the ASUTIME limit specified for the top-level calling package is applied for the entire thread, regardless of any value specified for the routines that are called.End of change

Dynamic SQL processing

A program that provides for dynamic SQL accepts as input, or generates, an SQL statement in the form of a character string. You can simplify the programming if you can plan the program not to use SELECT statements, or to use only those that return a known number of values of known types. In the most general case, in which you do not know in advance about the SQL statements that will execute, the program typically takes these steps:

  1. Translates the input data, including any parameter markers, into an SQL statement
  2. Prepares the SQL statement to execute and acquires a description of the result table
  3. Obtains, for SELECT statements, enough main storage to contain retrieved data
  4. Executes the statement or fetches the rows of data
  5. Processes the information returned
  6. Handles SQL return codes.

Performance of static and dynamic SQL

To access DB2 data, an SQL statement requires an access path. Two big factors in the performance of an SQL statement are the amount of time that DB2 uses to determine the access path at run time and whether the access path is efficient. DB2 determines the access path for a statement at either of these times:
  • When you bind the plan or package that contains the SQL statement
  • When the SQL statement executes
The time at which DB2 determines the access path depends on these factors:
  • Whether the statement is executed statically or dynamically
  • Whether the statement contains input host variables
  • Start of changeWhether the statement contains a declared global temporary table.End of change

Static SQL statements with no input host variables

For static SQL statements that do not contain input host variables, DB2 determines the access path when you bind the plan or package. This combination yields the best performance because the access path is already determined when the program executes.

Static SQL statements with input host variables

For static SQL statements that have input host variables, the time at which DB2 determines the access path depends on the REOPT bind option that you specify: REOPT(NONE) or REOPT(ALWAYS). REOPT(NONE) is the default. Do not specify REOPT(AUTO) or REOPT(ONCE); these options are applicable only to dynamic statements. DB2 ignores REOPT(ONCE) and REOPT(AUTO) for static SQL statements, because DB2 caches only dynamic SQL statements.

If you specify REOPT(NONE), DB2 determines the access path at bind time, just as it does when there are no input variables.

If you specify REOPT(ALWAYS), DB2 determines the access path at bind time and again at run time, using the values of the following types of input variables:

  • Host variables
  • Parameter markers
  • Special registers

DB2 must spend extra time determining the access path for statements at run time. However if DB2 determines a significantly better access path using the variable values, you might see an overall performance improvement. With REOPT(ALWAYS), DB2 optimizes statements using known literal values. Knowing the literal values can help DB2 to choose a more efficient access path when the columns contain skewed data. DB2 can also recognize which partitions qualify if there are search conditions with host variables on the limit keys of partitioned table spaces.

With REOPT(ALWAYS) DB2 does not start the optimization over from the beginning. For example DB2 does not perform query transformations based on the literal values. Consequently, static SQL statements that use host variables optimized with REOPT(ALWAYS) and similar SQL statements that use explicit literal values might result in different access paths.

Dynamic SQL statements

For dynamic SQL statements, DB2 determines the access path at run time, when the statement is prepared. The repeating cost of preparing a dynamic statement can make the performance worse than that of static SQL statements. However, if you execute the same SQL statement often, you can use the dynamic statement cache to decrease the number of times that those dynamic statements must be prepared.

Dynamic SQL statements with input host variables

When you bind applications that contain dynamic SQL statements with input host variables, consider using the REOPT(ALWAYS), REOPT(ONCE), or REOPT(AUTO) bind options, instead of the REOPT(NONE) option.

Use REOPT(ALWAYS) when you are not using the dynamic statement cache. DB2 determines the access path for statements at each EXECUTE or OPEN of the statement. This option ensures the best access path for a statement, but using REOPT(ALWAYS) can increase the cost of frequently used dynamic SQL statements.

Consequently, the REOPT(ALWAYS) option is not a good choice for high-volume sub-second queries. For high-volume fast running queries, the repeating cost of prepare can exceed the execution cost of the statement. Statements that are processed under the REOPT(ALWAYS) option are excluded from the dynamic statement cache even if dynamic statement caching is enabled because DB2 cannot reuse access paths when REOPT(ALWAYS) is specified.

Use REOPT(ONCE) or REOPT(AUTO) when you are using the dynamic statements cache:
  • If you specify REOPT(ONCE), DB2 determines and the access path for statements only at the first EXECUTE or OPEN of the statement. It saves that access path in the dynamic statement cache and uses it until the statement is invalidated or removed from the cache. This reuse of the access path reduces the prepare cost of frequently used dynamic SQL statements that contain input host variables; however, it does not account for changes to parameter marker values for dynamic statements.

    The REOPT(ONCE) option is ideal for ad-hoc query applications such as SPUFI, DSNTEP2, DSNTEP4, DSNTIAUL, and QMF DB2 can better optimize statements knowing the literal values for special registers such as CURRENT DATE and CURRENT TIMESTAMP, rather than using default filter factor estimates.

  • If you specify REOPT(AUTO), DB2 determines the access path at run time. For each execution of a statement with parameter markers, DB2 generates a new access path if it determines that a new access path is likely to improve performance.

Coding PREPARE statements for efficient optimization

You should code your PREPARE statements to minimize overhead. With REOPT(AUTO), REOPT(ALWAYS), and REOPT(ONCE), DB2 prepares an SQL statement at the same time as it processes OPEN or EXECUTE for the statement. That is, DB2 processes the statement as if you specify DEFER(PREPARE). However, DB2 prepares the statement twice in the following situations:
  • Start of changeYour program issues the DESCRIBE statement before the OPEN statementEnd of change
  • You issue the PREPARE statement with the INTO parameter
For the first prepare, DB2 determines the access path without using input variable values. For the second prepare, DB2 uses the input variable values to determine the access path. This extra prepare can decrease performance.

If you specify REOPT(ALWAYS), DB2 prepares the statement twice each time it is run.

If you specify REOPT(ONCE), DB2 prepares the statement twice only when the statement has never been saved in the cache. If the statement has been prepared and saved in the cache, DB2 will use the saved version of the statement to complete the DESCRIBE statement.

If you specify REOPT(AUTO), DB2 initially prepares the statement without using input variable values. If the statement has been saved in the cache, for the subsequent OPEN or EXECUTE, DB2 determines if a new access path is needed according to the input variable values.

For a statement that uses a cursor, you can avoid the double prepare by placing the DESCRIBE statement after the OPEN statement in your program.

If you use predictive governing, and a dynamic SQL statement that is bound with either REOPT(ALWAYS) or REOPT(ONCE) exceeds a predictive governing warning threshold, your application does not receive a warning SQLCODE. However, it will receive an error SQLCODE from the OPEN or EXECUTE statement.