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
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.
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 DSN8D10.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.
- 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
- Db2 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.
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:
- Translates the input data, including any parameter markers, into an SQL statement
- Prepares the SQL statement to execute and acquires a description of the result table
- Obtains, for SELECT statements, enough main storage to contain retrieved data
- Executes the statement or fetches the rows of data
- Processes the information returned
- Handles SQL return codes.
Performance of static and dynamic SQL
- When you bind the plan or package that contains the SQL statement
- When the SQL statement executes
- Whether the statement is executed statically or dynamically
- Whether the statement contains input host variables
- Whether the statement contains a declared global temporary table.
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.
- 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
- Your program issues the DESCRIBE statement before the OPEN statement
- You issue the PREPARE statement with the INTO parameter
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.