Adding alternative statements to files in the pureQueryXML editor

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:

Procedure

To add alternative statements or to edit alternative statements:

  1. Right-click the statement that the alternative statement will replace at run time and select Edit Statement.
  2. In the Default schema field that appears at the top of the editor, select or type the name of the schema to use for unqualified database object references in SQL statements that you are adding or editing. If you want SQL statements to refer to database objects that are in other schemas, you must qualify the names of the objects with the names of their corresponding schemas.
    Attention: The Default schema field applies to all of the SQL statements in the current statement set.
  3. If a DB2® data source is associated with your Java project, in the Default path field that appears at the top of the editor, type the SQL path to use when resolving function references and data type references in SQL statements that you are adding or editing. This value is also used to resolve stored procedure references in CALL statements.

    Specify the default path as a list of one or more comma-separated schema names. Optionally, to preserve case, enclose a schema name in double quotation marks; for example, FERMAT, "McDrw #8", SYSIBM.

    Attention: The Default path field applies to all of the SQL statements in the current statement set.
  4. Paste in or type the alternative statement.

    Tip: You can compare the alternative statement with the original statement by using the Compare editor. Select both statements, right-click them, and select Compare.

    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.
  5. After you add the alternative statement or edit it, press Enter. The editor checks the new statement for compatibility with the previous one.

    If the alternative statement is compatible, you can decide whether to run it statically or dynamically.

    • If you want to run the statement statically when you run your application with executionMode set to STATIC, select the Bind check box next to it. The statement is included in the DB2 package that you create from the statement set when you bind the pureQueryXML file.
    • If you want to run the statement dynamically even if you run your application with executionMode set to STATIC, do not select the Bind check box next to it. Before you run your application, remember to set the client optimization property enableDynamicSQLReplacement to TRUE.

Feedback