Scenario: Updating pureQuery Runtime properties to run SQL statically

After you create packages containing SQL statements on the transaction database and bind the packages, you update the pureQuery Runtime properties and pureQueryXML data in the repository to enable the application to run the SQL statements statically. When the application refreshes the pureQuery properties, the SQL statements are run statically.

About this task

You use the ManageRepository utility to update the data in the runtime group and create a version in the repository. You perform the following actions:
  • Upload the data in the pureQueryXML file.
  • Update the pureQuery Runtime properties.
  • Active the runtime group version in the repository.

Procedure

To update pureQuery Runtime properties to run SQL statically:

  1. Update the local version of the pqtest.properties file and set the value of the executionMode property to STATIC.
    The file contains the following text:
    pdq.captureMode=ON
    pdq.allowDynamicSQL=TRUE
    pdq.executionMode=STATIC 

    With the pureQuery Runtime settings, the application executes SQL statements statically if the SQL statement is in the pureQueryXML file. If the application executes an SQL statement that is not in the pureQueryXML file the statement are captured and executed dynamically.

  2. Create a version for the runtime group the repository.

    Create the version V2 for the runtime group testApp. You will upload the pureQuery Runtime properties and pureQueryXML data to the repository.

    The following ManageRepository command creates the runtime group version:
    java com.ibm.pdq.tools.ManageRepository -create runtimeGroup 
      -repositoryDriverClass  com.ibm.db2.jcc.DB2Driver 
      -repositoryURL  "jdbc:db2://testserver.test.com:32706/sample" 
      -repositoryUsername  "myuser" -repositoryPassword  "mypwd" 
      -runtimeGroupId testApp 
      -runtimeGroupVersion V2 
    The output from the command is similar to the following example:
    IBM Optim pureQuery runtime 2.2.0.3 build 2.18.118
    Licensed Materials - Property of IBM
    5724-U16
    (c) Copyright IBM Corp. 2006, 2010 All Rights Reserved.
    
    =========================================================
    
    Starting to process options:  -create "runtimeGroup" 
      -bind "packages" -runtimeGroupVersion "V2" 
      -repositoryURL "jdbc:db2://testserver.test.com:32706/sample" 
      -repositoryUsername "myuser" -repositoryPassword "******" 
      -repositoryDriverClass "com.ibm.db2.jcc.DB2Driver" 
      -runtimeGroupId "testApp"
    
    The ManageRepository utility successfully created the runtime group "testApp" version "V2".
  3. Update the runtime group version in the repository with the data in pqtest.properties file and pureQueryXML file.
    Run the command:
    java com.ibm.pdq.tools.ManageRepository 
    -update runtimeGroup 
    -repositoryDriverClass  com.ibm.db2.jcc.DB2Driver 
    -repositoryURL  "jdbc:db2://testserver.test.com:32706/sample" 
    -repositoryUsername  "myuser" -repositoryPassword  "mypwd" 
    -runtimeGroupId testApp 
    -pureQueryProperties ./pqtest.properties 
    -pureQueryXml ./testApp.pdqxml
    -runtimeGroupVersion V2
    The output from the command is similar to the following example:
    IBM Optim pureQuery Runtime 2.2.0.3 build 2.18.118
    Licensed Materials - Property of IBM
    5724-U16
    (c) Copyright IBM Corp. 2006, 2010 All Rights Reserved.
    
    ===================================================
    
    Starting to process options:  -update "runtimeGroup" -runtimeGroupVersion "V2" 
    -repositoryURL "jdbc:db2://testserver.test.com:32706/sample" 
    -repositoryUsername "*****" –repositoryPasword "*****"
    -pureQueryProperties ".\pqtest.properties" 
    -pureQueryXml "testApp.pdqxml" 
    -runtimeGroupId "testApp"
    
    The ManageRepository utility successfully updated the runtime group "testApp" version "V2".
  4. Activate the V2 version in the runtime group testApp.
    Run the command:
    java com.ibm.pdq.tools.ManageRepository -activate runtimeGroup 
    -repositoryDriverClass  com.ibm.db2.jcc.DB2Driver 
    -repositoryURL  "jdbc:db2://testserver.test.com:32706/sample" 
    -repositoryUsername  "myuser" 
    -repositoryPassword  "mypwd" 
    -runtimeGroupId testApp  
    -runtimeGroupVersion V2

    When you activate version V2, version V1 is automatically deactivated. pureQuery Runtime will retrieve the pureQuery information from the active version of the runtime group in the repository.

    The output from the command is similar to the following example:
    IBM Optim pureQuery Runtime 2.2.0.3 build 2.18.118
    Licensed Materials - Property of IBM
    5724-U16
    (c) Copyright IBM Corp. 2006, 2010 All Rights Reserved.
    
    ========================================
    
    Starting to process options:  -activate "runtimeGroup" -runtimeGroupVersion "V2" 
    -repositoryURL "jdbc:db2://testserver.test.com:32706/sample" 
    -repositoryUsername "*****" -repositoryPassword "*****"
    -runtimeGroupId "testApp" 
    
    The ManageRepository utility successfully activated the runtime group "testApp" version "V2".

Results

The pureQuery Runtime refresh interval property propertiesRefreshInterval was set to two minutes for this application. After two minutes, the updated pureQuery Runtime properties take effect. With the value of the executionMode property set to STATIC, the application runs the SQL statements statically.

In version V2 of the runtime group testApp, the pureQuery Runtime property captureMode is set to ON. pureQuery Runtime is still capturing SQL data from the application. If you did not capture all the SQL statements from the application, you can extract the captured SQL and the pureQueryXML data, and repeat the process of merging, configuring, and binding the SQL statements and then uploading the updated pureQueryXML file to the repository. If performance is a concern while capturing SQL data, you can set the value of the captureMode property to NEW_STMT or LITE to decrease the amount of data captured.

The pureQuery data In version V1 of the runtime group testApp is not changed. You can activate version V1 to return the previous pureQuery Runtime settings.

Note: One method of determining if SQL statements are being executed statically is to drop the package. If you drop the package TESTAPP from the database, the application will not be able to run SQL statements 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

This set of tasks showed how a WebSphere® application can be configured with pureQuery client optimization and showed how a repository in a database can be used to store pureQuery Runtime properties and pureQuery captured SQL data.


Feedback