< Previous | Next >

Lesson 3: Configuring pureQuery Runtime to run SQL statements statically

After you capture SQL statements from an application that is enabled with pureQuery client optimization, you configure pureQuery Runtime to run the SQL statements statically on a DB2® database.

Before you begin

You must have an active connection to the SAMPLE database. If you do not have an active connection, the workbench prompts for a connection when you perform the bind operation.

The pureQueryXML file that you created in the previous lesson contains a single statement set. If you perform the bind process with the file, you create a single set of packages on the target database. In this lesson, you update statement set name in the pureQueryXML file. Then you create a single set of packages from the pureQueryXML file and bind those packages.

After performing the binding process on the target database, you set pureQuery Runtime properties to specify that pureQuery runs the statements issued by the application statically.

When you update the pureQueryXML file, the workbench configures the pureQueryXML file as needed. The configuration process manages the SQL statements in the pureQueryXML file and adds information needed for the binding process. The workbench uses the options defined in the Default.genProps file to configure the capture.pdqxml pureQueryXML file.

Updating the pureQueryXML file and perform the binding process with the file

Use the workbench to update the pureQueryXML file that contains the captured SQL statements. Then bind SQL statements in the file by running the StaticBinder utility.

To update the pureQueryXML file and bind the SQL statements in the file:

  1. In the Package Explorer view, double-click the pureQueryFolder\Default.genProps file in the pq-tutorial Java project.
  2. Change the value of the -rootPkgName option to aatest
    C:\test\pq-tutorial\pureQueryFolder\capture.pdqxml= -rootPkgName aatest

    The -rootPkgName option specifies the base string that is used to create named statement sets in the pureQueryXML file. The statement set names in the pureQueryXML file are the base strings for the names of packages that are created on the target database.

  3. Save the file. Click Yes for the message that appears to confirm rebuilding the project.
  4. Configure the pureQueryXML file with a new statement set name.
    1. Open the file capture.pdqxml in the pureQueryXML editor.

      In the Package Explorer, double-click the file pureQueryFolder\capture.pqdxml to open it in the pureQueryXML editor.

    2. Delete the statement set name in the file.

      In the pureQueryXML editor, highlight the statement name pq-tutA and delete the text.

    3. Close and save the file.
    4. Open the file again.

    When you saved the file, the workbench configured the pureQueryXML file. The configuration process created a single statement set named aatestA containing the SQL statements. The statement set name is based on aatest, the value of the -rootPkgName option specified in the Default.genProps file.

    Figure 1. Configured pureQueryXML file with statement set name aatestA
    configured capture.pdqxml file in the pureQueryXML editor.
  5. Bind the SQL statements in the pureQueryXML file to the SAMPLE database.
    1. In the Package Explorer view, right-click the capture.pdqxml file and click pureQuery > Bind. The Select Connection dialog box opens.
    2. Select the SAMPLE connection and click Finish.
    The workbench runs the StaticBinder utility and sends the output from that utility in the Console view. The output is similar to the following example:
    ================================================================================
    
    The StaticBinder utility is beginning the bind operation for the pureQueryXml 
       file 'C:\test\ODS-tutorial\pq-tutorial\pureQueryFolder\capture.pdqxml'.
    
    Starting to process options:  
       -url "jdbc:db2://localhost:50000/SAMPLE:retrieveMessagesFromServerOnGetMessage=true;" 
       -username "*****" -password "*****" 
       -pureQueryXml "C:\test\ODS-tutorial\pq-tutorial\pureQueryFolder\capture.pdqxml"
    
    The StaticBinder utility successfully bound the package 'aatestA1' for the isolation level 'UR'.
    The StaticBinder utility successfully bound the package 'aatestA2' for the isolation level 'CS'.
    The StaticBinder utility successfully bound the package 'aatestA3' for the isolation level 'RS'.
    The StaticBinder utility successfully bound the package 'aatestA4' for the isolation level 'RR'.
    
    The bind operation completed successfully for
      'C:\test\ODS-tutorial\pq-tutorial\pureQueryFolder\capture.pdqxml'.
    
    ================================================================================
    
    Results of the StaticBinder utility's activity:
    
        Number of items for which the bind operation SUCCEEDED: 1
    
    
    Bind for XML file capture.pdqxml using connection SAMPLE in project pq-tutorial succeeded.

    The workbench ran the pureQuery StaticBinder utility to create a single set of packages and bound the packages on the database. The pureQuery StaticBinder utility created the packages based on information in the configured capture.pdqxml file and the default StaticBinder bind options.

    The set of packages are named based on the statement set name in the pureQueryXML file. The StaticBinder created four packages. As the default, the StaticBinder creates a package for each of the four DB2 isolation levels UR, CS, RS, and RR.

  6. Locate the packages in the Data Source Explorer from the SQL Outline view.
    1. In the SQL Outline view, open the Database Packages window by clicking the Database Packages tab at the bottom of the view.
    2. Right-click the package name and select Find in Data Source Explorer.

      The Data Source Explorer opens and expands the folders of the SAMPLE connection and highlights the packages.

      The package names are based on aatest, which is the name of the statement set in the pureQueryXML file.

Configuring pureQuery Runtime to run SQL statements statically

Update the pureQuery Runtime properties for your application to run the SQL statements statically.

To configure pureQuery Runtime to run SQL statements statically:

  1. In the Package Explorer view, double-click the pdq.properties file.

    The file opens in the PDQ Properties editor.

  2. Update the pureQuery Runtime properties file.
    • Change the value of the captureMode property to OFF
    • Add the capturedOnly property with the value TRUE
    • Change the value of the executionMode property to STATIC
    The updated file contains the following properties:
    pdq.captureMode=OFF
    pdq.capturedOnly=TRUE
    pdq.executionMode=STATIC
    pdq.pureQueryXml=pureQueryFolder/capture.pdqxml
  3. Save and close the file.
  4. Run the application.

    Right-click the MySample.java file and click Run As > Java application.

    The application runs and returns results in the Console window.

Testing the mapping between the pureQueryXML file and the packages on the database

When you change information in the pureQueryXML file, the mapping between the SQL statements in the file and the packages on the database that run SQL statements statically also changes. If the mapping changes, you must create new packages on the database and bind the packages.

To test the mapping between the pureQueryXML file and the packages on the database:

  1. In the pureQueryXML file, change the statement set name from aatest to aatut
    1. In the Package Explorer, double-click the capture.pdqxml.

      The file opens in the pureQueryXML editor.

    2. In the pureQueryXML editor, right-click the statement set name and change the name to aatut.
    3. Save and close the file.

      Click OK to confirm saving the changes.

  2. Run the application.

    In the Console window, the application returns a package not found error (SQLCODE=-805).

    The packages are still on the database, but pureQuery Runtime cannot find them because the statement set name in the pureQueryXML file changed. pureQuery Runtime uses the statement set name to determine package name.

  3. Bind the SQL statements in the updated pureQueryXML file.
    1. Right-click the capture.pdqxml file and click pureQuery > Bind.
    2. In the Select Connection dialog box, select the SAMPLE database and click Finish.

    The bind information appears in the Console window.

    The StaticBinder utility uses the updated statement set name in the pureQueryXML file to create new packages on the database. The packages you created previously based on the statement name aatestA are not dropped.

  4. Run the application.

    In the Package Explorer, right-click the MySample.java file and click Run As > Java application.

    The application runs and returns results in the Console window.

Lesson checkpoint

From the workbench, you configured an application that is enabled with pureQuery client optimization to run SQL statements statically.
From the workbench, you performed the following tasks:
  • Updated a pureQueryXML file and confirmed that the workbench configured the file.
  • Ran the StaticBinder from the workbench to create packages and bind the packages to a database.
  • Viewed the pureQueryXML file in the pureQueryXML editor.
  • Set pureQuery Runtime properties to run SQL statements issued by the application statically.
  • Found packages on a DB2 database.
  • Updated the pureQueryXML file and bound the SQL statements on the database again.
< Previous | Next >

Feedback