< Previous | Next >

Lesson 2: Configuring your pureQuery environment outside the workbench environment

Configure the environment on your computer to run your Java application with pureQuery client optimization enabled.
Before you begin
Ensure that you have the following software installed on your system:
  • Java developer kit version 1.5 or later
  • IBM® Data Server Driver for JDBC and SQLJ Version 3.57
  • pureQuery Runtime version 2.2.1

In the previous lesson, you exported your Java application to the C:\pureQuery-test directory on your computer and ran the application against the SAMPLE DB2® database.

In this lesson, you configure your Java environment and enable your application with pureQuery client optimization. You capture SQL statements from your application and configure pureQuery Runtime and the target database to run the SQL statements issued by your application statically.

  1. If you closed the command window from the previous lesson, open a new command window and set the CLASSPATH environment variable for the window.
    1. Open a command prompt window and set the directory to c:\pureQuery-test.

      In the window, enter the command cd c:\pureQuery-test.

    2. Add the files to the CLASSPATH by using the SET command.
      For example, if the DB2 database driver JAR files are installed in the C:\DB2\drivers directory, add the files to the CLASSPATH by using the following command in command prompt window. Enter the following SET command on a single line.
      set CLASSPATH=C:\DB2\drivers\db2jcc_license_cu.jar;C:\DB2\drivers\db2jcc.jar;
          C:\pureQuery-test\MyTestApp.jar;%CLASSPATH%;
  2. Add the location of the pureQuery Runtime JAR files to the CLASSPATH by using the SET command.
    For example, if the pureQuery JAR files are installed in the C:\pureQuery directory, add the files to the CLASSPATH by issuing the following command:
    set CLASSPATH=C:\pureQuery\pdq.jar;C:\pureQuery\pdqmgmt.jar;.;%CLASSPATH%;
    With the pureQuery Runtime JAR files in the application CLASSPATH, your application can use pureQuery Runtime capabilities including pureQuery client optimization.
  3. Create a pureQuery Runtime properties file that is used when running an application that is enabled with pureQuery client optimization.
    1. Create the mytest.properties properties file in your test directory.
    2. Add pureQuery properties to the file.

      With a text editor, add the following pureQuery Runtime properties in the mytest.properties file:

      pdq.captureMode=ON
      pdq.executionMode=DYNAMIC
      pdq.pureQueryXml=./mytestsql.pdqxml
    3. Save the file.

    The pureQuery Runtime properties specify that pureQuery Runtime run SQL statements dynamically and capture SQL statements in the mytestsql.pdqxml pureQueryXML file.

  4. Run the application twice with pureQuery client optimization enabled to capture SQL statements.

    You can specify pureQuery Runtime properties several ways. Each of the following tasks runs the application with the properties set to capture SQL statements in the mytest.pdqxml pureQueryXML file.

    Do one of the following tasks:

    • Run the application and specify the finalRepositoryProperties pureQuery Runtime property with the pdqProperties option. Enter each of the following two Java commands on a separate single line in the command prompt window:
      java -Ddb2.jcc.pdqProperties=finalRepositoryProperties(file://C:/pureQuery-test/mytest.properties)
         myTestApp.MySample
      
      java -Ddb2.jcc.pdqProperties=finalRepositoryProperties(file://C:/pureQuery-test/mytest.properties)
         myTestApp.MySample 1

      The pureQuery Runtime property finalRepositoryProperties specifies that pureQuery Runtime retrieves the pureQuery properties from the mytest.properties file.

    • Run the application and specify three pureQuery Runtime properties with the pdqProperties option. In the command prompt window, enter each of the following two Java commands on a separate single line. Do not enter spaces between the pureQuery Runtime properties.
      java -Ddb2.jcc.pdqProperties=captureMode(ON),pureQueryXml(./mytestsql.pdqxml),
         capturedOnly(FALSE),executionMode(DYNAMIC) myTestApp.MySample
      
      java -Ddb2.jcc.pdqProperties=captureMode(ON),pureQueryXml(./mytestsql.pdqxml),
         capturedOnly(FALSE),executionMode(DYNAMIC) myTestApp.MySample 1
    • Run the application with the configuration from the pdq.properties default properties file.
      Make a copy of the mytest.properties pureQuery properties file named C:\pureQuery-test\pdq.properties. Then enter the following two Java commands in the command prompt window:
      java myTestApp.MySample
      
      java myTestApp.MySample 1

      pureQuery Runtime searches for property values in a set of specific locations in a particular order. The search locations include the application CLASSPATH directories for the pdq.properties file.

      If pureQuery Runtime does not find a value for a property, it uses the default value for that property. The default for the executionMode and captureMode properties is to run statements dynamically and to not capture SQL statements.

    The application runs and pureQuery Runtime captures the SQL statements in the mytestsql.pdqxml pureQueryXML file.

  5. Run the pureQuery Configure utility to configure the pureQueryXML file.
    Enter the following command on a single line in the command prompt window:
    java com.ibm.pdq.tools.Configure -pureQueryXml mytestsql.pdqxml
       -rootPkgName MYTEST -validateXml TRUE
    The Configure utility validates and configures the pureQueryXML file. The value of -rootPkgName option is MYTEST, the packages created from this pureQueryXML file will have different names than the packages you created from the workbench.
  6. Run the pureQuery StaticBinder utility to perform the bind operation on the database.
    Enter the command on a single line in the command prompt window. Replace the values for options -url, -user, and -password with the values to connect to your SAMPLE database.
    java com.ibm.pdq.tools.StaticBinder
      -url jdbc:db2://localhost:50000/SAMPLE -user user01 -password myPass
       -pureQueryXml mytestsql.pdqxml
    Note: To display more detailed information about the bind process you can add the -showDetails true option to the command.
  7. Update the mytest.properties pureQuery Runtime properties file.
    • Change the value of the captureMode property to OFF
    • Change the value of the executionMode property to STATIC
    • Add the allowDynamicSQL property with the value FALSE
    The updated file contains the following properties:
    pdq.captureMode=OFF
    pdq.executionMode=STATIC
    pdq.allowDynamicSQL=FALSE
    pdq.pureQueryXml=./mytestsql.pdqxml

    The changes configure pureQuery Runtime to run SQL statements from your application statically and to run SQL statements that are in the mytestsql.pdqxml pureQueryXML file.

  8. Run the application with pureQuery client optimization enabled and the configuration from the mytest.properties file.
    Enter the following command on a single line in the command prompt window:
    java -Ddb2.jcc.pdqProperties=finalRepositoryProperties(file://C:/pureQuery-test/mytest.properties)
       myTestApp.MySample 2

    When you use the value 2, the application issues an SQL statement that is not in the pureQueryXML file. The application runs and you get an error that says that the SQL statement was not found in the pureQueryXML file.

  9. Run the application with the same pureQuery Runtime configuration and issue a statement that is in pureQueryXML file.
    Enter the following command on a single line in the command prompt window:
    java -Ddb2.jcc.pdqProperties=finalRepositoryProperties(file://C:/pureQuery-test/mytest.properties)
       myTestApp.MySample
    The application runs and runs the SQL statement statically.

Lesson checkpoint

In this lesson, you enabled your Java application with pureQuery client optimization, and you configured your application and the SAMPLE database to run SQL statements statically.
You completed the following tasks:
  • Updated your CLASSPATH with the pureQuery Runtime JAR files.
  • Created a pureQuery Runtime properties file and set pureQuery properties.
  • Ran the Java application and captured SQL statements that the application issued.
  • Ran the pureQuery Configure and StaticBinder utilities to process the pureQueryXML file and perform a bind operation with the pureQueryXML file.
  • Ran the Java application and ran an SQL statement statically.

In this lesson, you configured a pureQueryXML file that contained SQL queries. When you configure a pureQueryXML file that contains DDL statements, the DDL statements, with exception of DECLARE GLOBAL TEMPORARY TABLE, are moved to a separate file. For example, you captured an SQL statement that created a table in your pureQueryXML file. When you configure the file the statement creating a table is moved to a separate file. For information about how the pureQuery Configure utility handles DDL statements, see Configure utility.

In a workbench Java project enabled with pureQuery support, the pureQueryXML file configuration process handles DDL statements in the same manner as the Configure utility.

For information about setting pureQuery Runtime properties, see Order of precedence for the locations in which you can set properties for client optimization.

For information about the pureQuery Runtime property finalRepositoryProperties, see finalRepositoryProperties property.

< Previous | Next >

Feedback