Improving dynamic SQL performance
You can use several techniques to improve performance for dynamic SQL applications.
About this task
Introductory concepts
Procedure
To improve the performance of dynamic SQL statement, use any of the following methods:
- Use pureQuery® to execute SQL. With pureQuery you can redirect dynamic queries to become static. You can also use pureQuery to lock in access plans, and choose an execution mode of either static or dynamic.
For more information about pureQuery, see:Submitting SQL statements to DB2
- Enable the caching of dynamic SQL statements. You can use dynamic statement caching to give more static functionality to dynamic SQL
statements. Dynamic statement caching saves statements that are already prepared and reuses them
when identical statements are called. Dynamic statements can be cached when they have passed the
authorization checks if the dynamic statement caching is enabled on your system. You can take any or
both of following actions to enable caching for dynamic SQL statements:
- At the
subsystem level, use the CACHEDYN=YES subsystem parameter value to enable the dynamic statement
cache. When
CACHEDYN=YES is set, applications that issue PREPARE or EXECUTE IMMDEDIATE statements can benefit if
the skeleton copy of the statement is found in the global statement cache. If the appropriate
conditions are met, the skeleton copy can be copied into the storage for the thread in a process
called a short prepare. That is, two programs can share the same prepared statement.
The application has extra PREPARE operations, but the cost of a full prepare is saved.
For more information about this approach, see Improving dynamic SQL performance by enabling the dynamic statement cache.
-
At the package level, use the KEEPDYNAMIC(YES) bind option to enable
dynamic SQL statements to be kept after commit points. Any single SQL statement that is bound with
the KEEPDYNAMIC(YES) bind option can issue a single PREPARE statement for an SQL statement and omit
subsequent prepare operations, even after commit points. To achieve the cost savings of this
approach, you must omit the unneeded PREPARE statements from the application program.
For more information about this approach, see Methods for keeping prepared statements after commit points.
- At the
subsystem level, use the CACHEDYN=YES subsystem parameter value to enable the dynamic statement
cache. When
CACHEDYN=YES is set, applications that issue PREPARE or EXECUTE IMMDEDIATE statements can benefit if
the skeleton copy of the statement is found in the global statement cache. If the appropriate
conditions are met, the skeleton copy can be copied into the storage for the thread in a process
called a short prepare. That is, two programs can share the same prepared statement.
The application has extra PREPARE operations, but the cost of a full prepare is saved.
- Specify appropriate REOPT bind options. You can also use the REOPT bind option to control when DB2 re-optimizes the access path for an SQL statement. These options can make the SQL statements behave more statically or dynamically. You can use them to customize when and how to optimize your SQL statements.
For more information about REOPT bind options, see Reoptimizing SQL statements at run time and REOPT bind option .
- Specify the DEFER(PREPARE) bind option. DB2 does not prepare a dynamic SQL
statement until the statement runs. For dynamic SQL that is used in DRDA access, consider specifying the DEFER(PREPARE) option when you bind or
rebind your plans or packages. When a dynamic SQL statement accesses remote data, the PREPARE and
EXECUTE statements can be transmitted together over the network together and processed at the remote
server. The remote server can then send responses to both statements to the local subsystem
together, thereby reducing network traffic.
For more information about the DEFER(PREPARE) bind option, see REOPT bind option and BIND options for distributed applications
- Eliminate use of the WITH HOLD option for cursors. Defining a cursor WITH HOLD requires sending an extra network message to close the cursor.
You can improve performance by eliminating the WITH HOLD option when your application doesn't need
to hold cursors open across a commit. This recommendation is particularly true for dynamic SQL
applications. For more information about the WITH HOLD option for cursors, see: