Scenario: Configuring the pureQueryXML file and binding the SQL statements in the file

The pureQueryXML file that you created with the pureQuery Merge utility contains SQL statements that are captured from the WebSphere® application. You use the pureQuery Configure utility to specify characteristics of the DB2® packages in the pureQueryXML file. You use the StaticBinder utility to create DB2 packages from the SQL statements and other information in the pureQueryXML file, and bind the packages to the transaction database.

About this task

One important function of the Configure utility is to group the SQL statements in the pureQueryXML file into named statement sets. The name of the statement sets is based on the value of the -rootPkgName option, and the groupings are based on the Configure options you specify.

With the pureQuery StaticBinder utility, you create packages that contain the SQL statements, and bind those packages to the database. In the scenario environment, you create packages on the test database that use the qualifier TEST. The StaticBinder utility creates the packages from the statement sets that are defined in the pureQueryXML file.

Procedure

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

  1. Configure the pureQueryXML file that contains the captured SQL data.
    To use the Configure utility on the pureQueryXML file testApp.pdqxml, run the following command:
    java com.ibm.pdq.tools.Configure 
      -rootPkgName testApp 
      -cleanConfigure TRUE
      -pureQueryXml testApp.pdqxml 
      –validateXml TRUE 

    The -cleanConfigure TRUE option specifies that the Configure utility is to configure the entire file. If you are configuring a pureQueryXML file that was previously configured, you can use the -cleanConfigure FALSE option. The Configure utility attempts to preserve the contents of existing, previously configured, and bound statement sets in the pureQueryXML file.

    The -validateXml option specifies that the Configure utility performs XML validation of the pureQueryXML file against the pureQueryXML schema. The Configure utility displays any XML syntax errors that are in the pureQueryXML file.

    Tip: By default, the Configure utility uses the collection NULLID. If you want it to use a different collection, you can specify the collection with the -collection option.
  2. Bind the SQL statements in the pureQueryXML file.
    The application is running in the test environment for this scenario, and the SQL statements were captured in the test environment. To bind the captured SQL statements in the test environment, run the pureQuery StaticBinder utility with the -bindOptions "QUALIFIER TEST" option to bind the SQL statements in the test database.
    java com.ibm.pdq.tools.StaticBinder –url "jdbc:db2://testserver.test.com:32706/sample retrieveMessagesFromServerOnGetMessage=1;" 
      -username "myuser" -password "mypwd"
      -isolationLevel CS 
      -bindOptions "QUALIFIER TEST"  
      -pureQueryXml testApp.pdqxml -showDetails TRUE

    The StaticBinder utility binds packages into a connection indicated in the pureQueryXml file.

    The StaticBinder option -isolationLevel CS specifies that the StaticBinder create a single package with isolation level cursor stability (CS). The CS isolation level is the default level for DB2 for Linux, UNIX, and Windows databases.

    Tip: When you migrate an application from a test environment to a production environment, the production environment might use different schemas than the test environment. If the schemas are different, ensure that you specify the correct QUALIFIER bind option. In this scenario, the test environment schema is TEST and production environment schema is PROD. For the production environment, you specify the StaticBinder utility bind option -bindOptions "QUALIFIER PROD" to create packages in the production database.

    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.
    
    ================================================================================
    
    The StaticBinder utility is beginning the bind operation for the 
    pureQueryXml file 'C:\test\pureQuery-test\zz-merged.pdqxml'.
    
    Starting to process options:  -url "jdbc:db2://testserver.test.com:32706/sample
    " -username "*****" -password "*****" 
    -pureQueryXml "testApp.pdqxml"
    -isolationLevel "CS" 
    -bindOptions "QUALIFIER TEST" -showDetails "true"
    
    The StaticBinder utility successfully bound the package 'TESTAPP' 
    for the isolation level 'CS'.
    
    Number of statements input: '8', number of statements bound : '8', 
    bind options: 'ISOLATION CS QUALIFIER TEST'
    
    The bind operation completed successfully for 'testApp.pdqxml'.
        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' : [TESTAPP]
    
    ================================================================================
    
    Results of the StaticBinder utility's activity:
    
    Number of items for which the bind operation SUCCEEDED: 1

What to do next

In the next task, you update the repository with the pureQueryXML file and the pureQuery Runtime properties that enable the application to execute SQL statements statically.


Feedback