Preparing the pureQueryXML file to run SQL statements statically on DB2 databases

For a CLI application that is enabled with pureQuery client optimization and that connects to a DB2® database, you can configure the application to run SQL statements statically. As part of the configuration process, you run the Java-based pureQuery utilities Configure and StaticBinder to create packages on the database and bind the packages.

Before you begin

On the computer where you run the Configure utility and StaticBinder utility, you must install and enable pureQuery Runtime. The pureQuery Runtime JAR files, pdq.jar and pdqmgmt.jar, must be listed in the CLASSPATH. You must also have the following products installed on the computer:
  • Java Runtime Environment (JRE) Version 1.5 or later
  • IBM® Data Server Driver for JDBC and SQLJ Version 3.57.xx or 4.7.xx

About this task

After you capture the SQL data in a pureQueryXML file, you copy the file to a computer where pureQuery Runtime is installed. You run the Configure and StaticBinder utilities on that computer, and then copy the updated pureQueryXML file back to the computer where the CLI application runs.

The Configure utility updates the pureQueryXML file for use by the StaticBinder utility. The CLI application that was used to capture the SQL data also uses the pureQueryXML file to determine which SQL statements to run statically.

Procedure

To configure CLI applications to run SQL statements statically on DB2 databases:

  1. Copy the pureQueryXML file to the computer where pureQuery Runtime is installed.

    Ensure that the pureQueryXML file is not being written to when you retrieve the file. If you retrieve the file while it is being written to, the contents of the file might not be valid or the application might not be able to update the file, which causes an I/O error.

    You can use the -validateXml option with the Configure utility to ensure the validity of the file. The -validateXml option is supported with pureQuery Runtime version 2.2.0.3 and later.

  2. Configure the pureQueryXML file with the pureQuery Configure utility.
    The following example command configures the pureQueryXML file capture.pdqxml. The -rootpkgname option specifies the base string that the StaticBinder utility uses to create package names from the pureQueryXML SQL information. The -validateXml option specifies that the Configure utility performs XML schema validation to ensure that the pureQueryXML file conforms to the pureQuery XML schema.
    java com.ibm.pdq.tools.Configure -rootpkgname TESTPKG 
    		-validateXml TRUE
       -pureQueryXml capture.pdqxml
  3. Run the pureQuery StaticBinder utility to create a package on the target database and bind the packages on the database.
    The following example command creates packages and binds the packages on the database that is specified by the -url option. The packages contain the SQL statements from the pureQueryXML file capture.pdqxml. The -isolationLevel option specifies that the StaticBinder utility creates packages only for the CS isolation level.
    java com.ibm.pdq.tools.StaticBinder 
      –url "jdbc:db2://testserver.test.com:446/STLEC1" 
      -username "testuser" -password "testpwd"
      -isolationLevel "CS"
      -pureQueryXml "capture.pdqxml"

    By default, the StaticBinder utility creates four packages for a given pureQueryXML file; one for each type of isolation level. This example specifies the isolation level CS. The StaticBinder creates only one package for that isolation level.

  4. Copy the configured pureQueryXML file back to the computer that is running the CLI application where the pureQueryXML file was created.

    pureQuery Runtime uses the information from the configured pureQueryXML file to determine the SQL statements to execute statically.

  5. In the CLI configuration file for the application, set the value of the pureQuery keyword executionMode to STATIC.
    For example, in the db2cli.ini, set the following pureQuery keywords:
    executionMode=STATIC
    allowDynamicSQL=TRUE
    pureQueryXml=capture.pdqxml
    Alternatively, if you use a db2dsdriver.cfg file, you set the keywords as attributes in parameter elements:
    <parameter name="executionMode" value="STATIC" />
    <parameter name="allowDynamicSQL" value="TRUE" />
    <parameter name="pureQueryXml" value="capture.pdqxml" />

    With the keywords values allowDynamicSQL=TRUE, pureQuery Runtime runs SQL statements dynamically if the SQL statement is not in the pureQueryXML file capture.pdqxml.

    The default value for pureQuery keyword allowDynamicSQL is TRUE and is not required in the properties file. Including the property in the file is reminder of the default value.

  6. Run or restart the application to use the updated information in the configuration file and pureQueryXML file.

    The CLI application reads the updated pureQuery keywords and pureQueryXML information and runs the SQL statements statically.

What to do next

If you capture new SQL statements in the pureQueryXML file, you can use the Configure and StaticBinder utilities to update or create new packages and bind the packages on the database.


Feedback