Recommendations for configuring and binding pureQueryXML files

Use the following recommended options with the pureQuery Configure utility and StaticBinder utility when you prepare SQL statements to be run statically with pureQuery client optimization.

Options for the Configure utility

The following list describes recommendations for processing pureQueryXML files with the Configure utility:
  • Use the pureQuery Configure utility with the -cleanConfigure option when a complete capture is performed.
  • Use the -collection option to set the collection during a configure operation if you know the collection ID that will be used during the bind operation.
  • Use the -groupSQLBySpecialRegisters option to group SQL statements based on special register information recorded with the captured SQL statements. Use the -optionsFileForBind option to generate a StaticBinder options file that contains bind options based on special register settings.
  • If you see a warning about recorded SQL SET statements or methods, the application might not run as expected. The SET statements or methods might affect the behavior of some SQL statements when the application runs the statements dynamically. The SET statements or methods might not affect the behavior of the same SQL statements when they are run statically on a DB2® database. For example, the following warning might be generated by the Configure utility:
    Warning: The pureQueryXML file contains n special register SET statements or 
    methods that were recorded after other SQL statements were issued using the associated 
    connection.  This might indicate that the file contains SQL statements that were 
    originally prepared using multiple, different special register settings.

    For example, you can use the SQL SET statement to change the value of the special register CURRENT SCHEMA. An application can use the special register CURRENT SCHEMA to control the database schema when running SQL statements dynamically. The application runs SQL statements that contain unqualified table names. The application runs the SET method to change the value of special register CURRENT SCHEMA to WEEK1_SALES before running some SQL statements. For other statements, the application sets the value to WEEK5_SALES before running the statements.

    When you run the SQL statements statically, you control the database schema by specifying bind option QUALIFER when you bind packages to the database. The value of the special register CURRENT SCHEMA does not affect SQL statements run statically.

    When you configure a pureQueryXML file, you can specify Configure utility options to help you create statement sets and bind options so that the SQL statements run correctly when the statements are run statically:
    • Specify the option -groupSQLBySpecialRegisters to group the SQL statements based on special register information recorded when the SQL statements were captured. When you configure the pureQueryXML file, the SQL statements that were run with the value of the special register CURRENT_SCHEMA set to WEEK1_SALES are in a different statement set than the SQL statements that were run with the value set to WEEK5_SALES.
    • Specify the option -optionsFileForBind to generate a StaticBinder options file that contains bind options for statement sets based on the recorded special register settings. When you configure the pureQueryXML file, the utility generates the bind option "QUALIFIER(WEEK1_SCHEMA)" for the statement sets that contain the SQL statements that were run with the value of the with the CURRENT_SCHEMA set to WEEK1_SCHEMA. The utility generates the bind option "QUALIFIER(WEEK5_SCHEMA)" for the statement sets that contain the SQL statements that were run with the value set to WEEK5_SCHEMA.

    The Configure utility groups any SQL statements that are run with both special register values in a separate statement set. You can specify that the statement set containing the statements not be run statically by setting the isBindable attribute to false.

Binding packages from pureQueryXML files

If the packages need to be bound only at specific isolation levels, use the StaticBinder -isolationLevel option to specify the isolation level.

As the default, the StaticBinder utility creates packages for the four isolation levels, UR, CS, RS, and RR. If your application uses more than one level, use the -isolationLevel option, and specify only the required isolation levels. If your application uses only one isolation level, you can specify the Configure utility option -forceSingleBindIsolation option during configuration. Then you specify the appropriate isolation level when running the StaticBinder utility. If you specify the -forceSingleBindIsolation option, the number for the isolation level is not appended to the name of the created package.

Validating pureQueryXML files

If a problem occurs with the pureQueryXML file, you can validate pureQueryXml files by specifying the -validateXml TRUE option with the pureQuery utilities Merge, Configure, and StaticBinder. Any XML validation errors are displayed when the utility is run. The utility validates the input XML documents with the pureQueryXML schema.

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 manually edit a pureQueryXML file, be sure that you create a backup copy of the file first. Use the -validateXml option with the pureQuery Merge, Configure, and StaticBinder utilities to perform XML schema validation on input pureQueryXML files.


Feedback