< Previous | Next >

Lesson 2: Capturing SQL statements by using pureQuery client optimization

In this lesson, you configure your project for pureQuery client optimization. You run a Java™ application and capture SQL statements in a pureQueryXML file.

Before you begin

Ensure that your environment meets the following requirements:
  • Support for pureQuery must be enabled for your Java project.
  • You must have a DB2® database with the SAMPLE database installed.
  • The database connection properties for your project must specify a connection to the SAMPLE database, and the IBM Data Server Driver for JDBC and SQLJ must be on the project build path.
In this lesson, you perform the following tasks:

To capture SQL statements by using pureQuery client optimization:

  1. If necessary, activate the connection to the SAMPLE database.

    In the Data Source Explorer, select Database Connections\SAMPLE, right-click, and select Connect. If Properties appears, enter any required connection information and click OK.

  2. Enable pureQuery client optimization for the project.
    1. Right-click the project and select Properties.
    2. In the Properties window, open the pureQuery page by clicking the pureQuery node.
    3. Under Client Optimization, enable SQL capturing and binding for JDBC applications. Click OK.
    The pdq.properties pureQuery Runtime properties file is created in the src folder of the project with the pureQuery Runtime property settings:
    pdq.captureMode=ON
    pdq.executionMode=DYNAMIC
    pdq.pureQueryXml=pureQueryFolder/capture.pdqxml

    When these settings are enabled, pureQuery Runtime captures SQL statements issued by your application and stores the captured SQL data in the pureQueryFolder/capture.pdqxml pureQueryXML file.

    In the pureQueryFolder project folder, the Default.genProps pureQuery configuration properties file is updated with the following -rootPkgName option information:
    C:\test\pq-tutorial\pureQueryFolder\capture.pdqxml= -rootPkgName pq-tut
    The -rootPkgName option information is used by the workbench to configure the pureQueryXML file. Configuring the pureQueryXML file is one step in the process of setting up pureQuery Runtime to execute SQL statement statically on a DB2 database.
  3. Run the application and capture SQL data.
    1. In the Package Explorer, right-click the MySample.java file click Run As > Java application.
    2. In the Package Explorer, right-click the pq-tutorial\pureQueryFolder folder and select Refresh.

    The pureQueryXML file appears in the pureQueryFolder folder. The file contains SQL information captured by pureQuery Runtime.

  4. Capture another SQL statement from the application.
    1. In your source code, change the value that determines the default SQL statement to run.

      Change the value of the i variable from 0 to 1 in the following code:

         if ( args.length == 0) {
         // value for the default SQL statement to run
            i = 1 ;
    2. Save the file.
    3. Run the application to capture the SQL statement.

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

      The application runs and pureQuery Runtime captures SQL information from the application. Output appears in the Console window. The output is similar to the following example:
      =======
      IBM Optim pureQuery Runtime 2.2.1.0 build 2.25.63
       =======
      case 1
      case 1 - non-systems employees: 24
      Finished case 1
  5. View the pureQueryXML file that contains the two SQL statements that you captured.
    1. In the Package Explorer, go to the pureQueryFolder folder.
    2. Refresh pureQueryFolder folder to display the capture.pqdxml file.
    3. Double-click the capture.pqdxml file to open it in the pureQueryXML editor.

    The pureQueryXML editor displays the pureQueryXML file.

  6. Expand the statement set to view the two SQL statements captured by pureQuery Runtime.

    When you select the SQL statement, you can see view captured SQL data such as the schema name, current path, and DECFLOAT rounding mode.

    The following illustration shows a pureQueryXML file that contains a single statement set with two SQL statements.

    Figure 1. The capture.pdqxml pureQueryXML file in the pureQueryXML Editor
    capture.pdqxml file displayed in the pureQueryXML editor.

    The previous illustration shows a pureQueryXML file configured by the workbench. As part of the pureQueryXML configuration process, the statement set is named pq-tutA. The statement set names are based on the string pq-tut, the value of the -rootPkgName option in the Default.genProps file.

    You can manage the SQL statements in the pureQueryXML file with the pureQueryXML editor. You can perform actions such as rename a statement set, run an SQL statement, and add an alternate statement for a captured SQL statement.

  7. Run an SQL statement from the pureQueryXML editor.

    In the pureQueryXML editor, right-click the first SQL statement and select Run SQL.

    The workbench runs the SQL statement. The SQL Results window opens and displays the results.

    When running the SQL statement from the editor, the workbench uses the connection defined for the project. When you run the application, the application uses the connection defined in the application.

  8. Close the pureQueryXML editor.

    Click the X on the capture.pdqxml tab.

Lesson checkpoint

You configured the pureQuery support in your Java project to enable capturing and binding of SQL data from the application. You ran the application and captured SQL data from the application.
From the workbench, you performed the following tasks:
  • Configured pureQuery support with the project properties.
  • Ran a Java application from the workbench.
  • Viewed the SQL data that you captured in the pureQueryXML editor.
< Previous | Next >

Feedback