Improving dynamic SQL performance

You can use several techniques to improve performance for dynamic SQL applications.

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 dynamic 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 Enabling the dynamic statement cache to improve dynamic SQL performance.

  • At the package level, use the KEEPDYNAMIC(YES) bind option to enable dynamic SQL statements to be kept after the point of commit or rollback. 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 the point of commit or rollback. 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 the point of commit or rollback .

• Stabilize dynamic SQL statements, especially short running statements that run many times per day.
  For more information, see Stabilizing access paths for dynamic SQL statements.
  
• 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:

  • Disable cursor hold behavior for more efficient resource use
  • Held and non-held cursors
  • DECLARE CURSOR statement