Adding alternative SQL statements to pureQueryXML files

In a pureQueryXML file, you can add SQL statements for your application to run instead of statements that you captured.

About this task

After you capture SQL statements in a pureQueryXML file, you might discover that one or more statements do not perform as well as your application requires. However, because you discover this fact late in your development cycle, modifying the application might not be possible. You can edit the pureQueryXML file by adding an alternative SQL statement for each SQL statement that is underperforming. When you run your application, pureQuery uses the alternative SQL statements, instead of the SQL statements that you originally captured.

For example, an SQL statement might start to perform poorly after you collect statistics for a table. If this problem occurs on a production system, a database administrator might need to reverse the change to the table's statistics or request a change to the SQL in the application. With pureQuery, the database administrator can forego these options and instead add an alternative SQL statement to the pureQueryXML file in which the underperforming statement appears.

By adding alternative statements, you can do the following:
  • Influence an access path by adding OPTIMIZE FOR 1 ROW
  • Influence index usage by adding an ORDER BY clause
  • Influence fetch size for distributed queries by adding a FETCH FIRST n ROWS ONLY clause, an OPTIMIZE FOR n ROWS clause, or a FOR FETCH ONLY clause.
  • Change locking behavior with a WITH ISOLATION clause or a SKIP LOCKED DATA clause.
  • Manage EXPLAIN data with a QUERYNO clause.

You can choose whether to bind the alternative SQL statements after you add them. If you do not bind them but want to use them, you must set the enableDynamicSQLReplacement property to TRUE when you run your application.

You can add an alternative statement for any SQL statement except one of either of these two types:

Recommendation: IBM® strongly recommends that you edit pureQueryXML files by using the workbench only. If you edit a pureQueryXML file outside of the workbench, you risk making the file unusable.

If you must edit a pureQueryXML file outside of the workbench, in the <statementSet> tag of the statement set in which an edit occurs set the value of the configureStatus attribute to REQUIRED, like this: configureStatus="REQUIRED" Then, run the Configure utility on the file.

Procedure

To add alternative statements:

  1. Capture the SQL statements in your application. See Capturing SQL statements for client optimization.
  2. Edit the resulting pureQueryXML file to add alternative statements.
    Restrictions:
    • If an SQL statement contains named parameter markers, the alternative statement must have the same number of parameter markers and those markers must be in the same order.
    • The result set for an alternate SELECT statement must match the result set for the original SELECT statement.
  3. Follow either of these steps:
    • If you want to run the alternative statements statically, run the Configure utility and the StaticBinder utility on the pureQueryXML file, as usual.
    • If you want to run the alternative statements dynamically, set the enableDynamicSQLReplacement property to TRUE in the pdq.properties file.
  4. Run your application. See Running non-pureQuery API applications so that SQL statements run statically.

Feedback