Example: Running SQL statements statically from a CLI application

A call level interface (CLI) application that is enabled with pureQuery client optimization can be configured to run SQL statements statically on a DB2® database. Executing SQL statements statically avoids preparing certain SQL statements at run time and can improve application security and performance.

Before you begin

Enable your CLI application with pureQuery client optimization.

On the computer where you run the Java-based pureQuery Configure utility and StaticBinder utility, you must install and enable pureQuery Runtime. You must have the following products installed on the computer:
  • Java Runtime Environment (JRE) Version 1.5 or later
  • IBM® Data Server Driver for JDBC and SQLJ Version 3.57.xx
The pureQuery Runtime JAR files, pdq.jar and pdqmgmt.jar, and the Data Server Driver JAR files must be listed in the CLASSPATH on the computer where you run the utilities.

About this task

The task assumes your CLI application connects to a DB2 database and that you can connect to the database with the following connection URL:

jdbc:db2://svl01:500/DB2M -user user01 -password myPass

You set keywords in the db2cli.ini configuration file to configure the application. You configure your CLI application to capture SQL statements that are issued by the application, and then configure the application to execute the statements statically.

Procedure

To run SQL statements statically from a CLI application:

  1. Set the pureQuery keywords in your CLI application configuration file to capture SQL statements that are issued by your application.

    In the db2cli.ini configuration file that is used by the application to capture the SQL statements that are issued by the application, update the following keywords:

    captureMode=ON
    executionMode=DYNAMIC
    pureQueryXml=C:\testapp\Sample1Cptr.pdqxml
    Alternatively, if you use a db2dsdriver.cfg file, you set the keywords as attributes in parameter elements:
    <parameter name="captureMode" value="ON"/>
    <parameter name="executionMode" value="DYNAMIC"/>
    <parameter name="pureQueryXml" value="C:\testapp\Sample1Cptr.pdqxml"/>

    The keyword settings configure your CLI application to run SQL statements dynamically and capture successfully run statement in the file C:\testapp\Sample1Cptr.pdqxml.

    The value DYNAMIC for the executionMode keyword is the default, and is not required in the properties file. Including the property in the file is a reminder of the default value. The value will be changed to execute SQL statements statically.

    The value for the pureQueryXml keyword specifies the location and name of the pureQueryXML file where pureQuery client optimization stores the SQL data that are captured from the application. The value is also the location and name of the pureQueryXML file that pureQuery client optimization uses to control the execution of SQL statements. When you configure pureQuery client optimization to run the SQL statements statically on a DB2 database, pureQuery Runtime uses the information in the file to determine the SQL statements to run.

  2. Run your application to capture SQL statements in the pureQueryXML file.

    The SQL statements are captured in the pureQueryXML file.

  3. Configure the pureQueryXML file that contains the captured SQL statements and bind the captured SQL statements.
    1. If necessary copy the pureQueryXML file to the computer where the pureQuery Runtime files installed.
    2. Run the pureQuery Configure utility to configure the pureQueryXML file with package name and collection ID.

      At a command prompt, run the Configure utility to add information to the pureQueryXML file, such as the package name and collection ID, that is used by the pureQuery StaticBinder utility. You can also specify other options to manage the SQL statements in the file.

      The following example command runs the Configure utility and specifies the base package name SMPL1 and a collection named COLL01. Enter the command on a single line.

      java com.ibm.pdq.tools.Configure -pureQueryXml Sample1Cptr.pdqxml 
         -rootPkgName SMPL1 -collection COLL01
    3. Run the pureQuery StaticBinder utility with the configured pureQueryXML file to create packages on the target DB2 server and bind the packages.
      At a command prompt, run the StaticBinder utility with options specifying the database and the package information. The following example StaticBinder command creates packages on the target DB2 server and binds the packages. Enter the command on a single line.
      java com.ibm.pdq.tools.StaticBinder
        -url jdbc:db2://svl01:500/DB2M -user user01 -password myPass
         -pureQueryXml Sample1Cptr.pdqxml
    4. If necessary, replace the pureQuerXML file on the computer where your application runs with the configured pureQueryXML file.
  4. Update your CLI application configuration file to run your captured statements statically.

    Update the pureQuery executionMode keyword in the db2cli.ini configuration to enable static execution of the SQL statements that are issued by the application. The keyword value is changed from DYNAMIC to STATIC:

    captureMode=ON
    executionMode=STATIC
    pureQueryXml=C:\testapp\Sample1Cptr.pdqxml
    For in a db2dsdriver.cfg file, you set the keywords as attributes in parameter elements:
    <parameter name="captureMode" value="ON" />
    <parameter name="executionMode" value="STATIC" />
    <parameter name="pureQueryXml" value="C:\testapp\Sample1Cptr.pdqxml" />
  5. Run or restart the application to use the updated information in the configuration file and pureQueryXML file.

Results

When you run the application, the two SQL statements that are issued by the application are run statically. If you update the application and change or add SQL statements, the updated or new SQL statements are executed dynamically and are captured in the pureQueryXML file.

Tip: You can restrict the application to run only the SQL statements captured in the pureQueryXML file. One configuration that restricts the execution of SQL statements is to set the value of the captureMode keyword to OFF and add the keyword and value capturedOnly=TRUE:
captureMode=OFF
capturedOnly=TRUE
executionMode=STATIC
pureQueryXml=C:\testapp\Sample1Cptr.pdqxml
For in a db2dsdriver.cfg file, you set following parameter elements:
<parameter name="captureMode" value="OFF" />
<parameter name="capturedOnly" value="TRUE" />
<parameter name="executionMode" value="STATIC" />
<parameter name="pureQueryXml" value="C:\testapp\Sample1Cptr.pdqxml" />

With this configuration, only the SQL statements in the pureQueryXML file are run and the SQL statements run statically. Updated or new SQL statements are not captured in the pureQueryXML file.


Feedback