Example: Enabling pureQuery client optimization for a simple Java application

In a simple example, you use the four steps, (capture, configure, bind, and execute) to enable a Java™ application with pureQuery client optimization and configure the application run SQL statements statically on a DB2® database.
  1. Capture SQL data.
  2. Configure the captured SQL with characteristics of the DB2 packages that will be created for the SQL statements.
  3. Create packages from the SQL statements and bind the packages to a DB2 database.
  4. Execute the SQL statements statically.

Before you begin

The pureQuery Runtime JAR files, pdq.jar and pdqmgmt.jar, must be listed in the application CLASSPATH.

About this task

The example assumes that the application is a stand-alone JDBC-based application that uses a DB2 database as the data source, and that you are performing the tasks in the directory that contains the application. The SQL data that is captured by pureQuery Runtime is stored on the local file system in a pureQueryXML file. The following illustration shows the configuration.
Figure 1. pureQuery client optimization configuration
Simple pureQuery client optimization configuration
In the illustration, the pureQuery Configure utility converts the pureQueryXML file that contains the captured SQL data file into a configured pureQueryXML file.
When you enable pureQuery client optimization and execute SQL statements statically, you use the following pureQuery capabilities:
  • You set pureQuery Runtime properties to first capture SQL statements and then to execute SQL statements statically.
  • You use the pureQuery utilities Configure and StaticBinder with the SQL statements that you capture from the application.

Procedure

To enable pureQuery client optimization for a simple Java application:

  1. Capture SQL data from an application.
    1. Create a file pdq.properties in your current working directory with the following pureQuery Runtime properties:
      pdq.captureMode=ON
      pdq.pureQueryXml=capture.xml
      pdq.executionMode=DYNAMIC

      The captureMode property specifies that SQL statements and other information that is associated with the execution of the SQL statement are captured in a pureQueryXML file. The pureQueryXml property specifies the name of the file capture.xml. The executionMode property specifies whether to run statically the SQL statements that are in the pureQueryXML file that is specified by the pureQueryXml option.

    1. Start and run the Java application to execute SQL statements.
    2. Stop the application.
    When you run the application, pureQuery Runtime creates the capture.xml file and stores the captured SQL data in the file.
  2. Configure the captured SQL data.
    Run the Configure utility with the following command:
    java com.ibm.pdq.tools.Configure 
      -rootpkgname TESTPKG 
      -cleanConfigure true 
      -pureQueryXml capture.xml

    In the pureQueryXML file capture.xml, the Configure utility sets characteristics of the DB2 packages that you will create from the SQL statements in the file. The Configure utility groups the SQL statements in the pureQueryXML file into statement sets. The statement sets correspond to packages in the database. The utility uses the value of the -rootPkgName option to generate the names that will be used for the packages in the database. For information about package naming, see DB2 package identification information.

    The -cleanConfigure true option, specifies that the previous configuration information in the pureQueryXML file is not saved.

    If the -cleanConfigure option is not specified, the Configure utility attempts to save some configuration information.

    When the pureQueryXML file is configured, it contains the necessary package and section information and can be used by the pureQuery StaticBinder utility to create packages and bind the packages to the DB2 database.

  3. Bind the captured SQL statements with the pureQuery StaticBinder utility.
    Run the StaticBinder utility with the following command. Specify the target URL, user ID, password, pureQueryXML file, and the -isolationLevel option:
    java com.ibm.pdq.tools.StaticBinder 
      –url jdbc:db2://testserver.test.com:446/STLEC1” 
      -username "testuser" -password "testpwd"
      -isolationLevel CS
      -pureQueryXml capture.xml
      -showDetails true

    The option -showDetails true displays the details of the bind process.

    Output from the command is similar to the following example:
    The StaticBinder utility is beginning to bind the pureQueryXml file capture.xml.
    
    Starting to process options : -username "*****" -password "*****" 
    -url "jdbc:db2://testserver.test.com:446/STLEC1"
    -pureQueryXml  capture.xml 
    -isolationLevel "CS"
    -showDetails "true"
    
    The StaticBinder utility successfully bound the package 'TESTPKG2' for the isolation level 'CS'.
       Number of statements input: '5', number of statements bound: '5', bind options : 'ISOLATION CS'
    
    The StaticBinder utility successfully bound 'capture.xml'.
        Number of packages input :'1'
        Number of input packages for which isBindable is false : '0'
        Number of input packages with zero bindable statements : '0'
        Number of bindable input packages processed by the Static Binder utility : '1' : [TESTPKG]
    
    =======================================================================
    
    Results of the StaticBinder utility's activity:
    
        Number of items for which the bind operation SUCCEEDED: 1
    If the bind is successful, the package is created with the name TESTPKG2. The command creates only one package because you specified -isolationLevel CS. By default, the StaicBinder utility creates four packages for the four isolation levels.
  4. Execute the SQL statements statically with pureQuery Runtime.
    1. Edit the file pdq.properties file that you created earlier.
      Set executionMode to STATIC and set captureMode to OFF and save the file. The file contains the following lines:
      pdq.captureMode=OFF
      pdq.pureQueryXml=capture.xml
      pdq.allowDynamicSQL=TRUE
      pdq.executionMode=STATIC

      The default value for pureQuery Runtime property allowDynamicSQL is TRUE and is not required in the properties file. Having the property in the file is reminder of the default value. By default, pureQuery Runtime allows SQL statements that are issued by the application to be run dynamically even if the SQL statement was not been previously captured.

    2. Run the application.

      The application executes the SQL statements that are listed in the pureQueryXML file statically.

    Note: One method of determining if the SQL statements are being executed statically is to drop the package. If you drop the package TESTPKG from the database, the application will not be able to run the SQL statements that are listed in the pureQueryXML file.

    If you run the StaticBinder utility with the pureQueryXML file to re-create the package, the application will run the SQL statements statically.

    You can also use a database monitoring tool such as Tivoli® OMEGAMON® XE for DB2 on z/OS® to verify that SQL is being executed statically.

What to do next

In a more complex application scenario, you can capture SQL statements from the application in multiple iterations. When you capture SQL data in multiple iterations, you perform the additional step of merging the captured SQL data with the configured pureQueryXML file prior to configuring and binding the SQL statements.

The Configure and StaticBinder utilities support an options file where all the deployment information can be listed with various options that applicable for the packages. After you create an options file, you can invoke the utility with the option -optionsFile to specify the file.

You can also create a shell script that contains utility commands to repeat and automate the four-step process. If the pureQuery JAR files are in the CLASSPATH, all of the utilities are available as Java programs.

In Optim™ Development Studio, you can enable a Java project with pureQuery support and develop Java applications that are enabled with pureQuery client optimization in the Eclipse IDE.


Feedback