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:
- Enable pureQuery client optimization for the project.
- Capture SQL data from the simple application.
To capture SQL statements by using pureQuery client optimization:
- 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.
- Enable pureQuery client optimization for the project.
- Right-click the project and select Properties.
- In the Properties window, open
the pureQuery page by clicking the pureQuery node.
- 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.
- Run the application and capture SQL data.
- In the Package Explorer, right-click
the MySample.java file click .
- 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.
- Capture another SQL statement from the application.
- 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 ;
- Save the file.
- Run the application to capture the SQL statement.
In the Package Explorer, right-click
the MySample.java file, and select .
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
- View the pureQueryXML file that contains the two SQL statements
that you captured.
- In the Package Explorer, go to
the pureQueryFolder folder.
- Refresh pureQueryFolder folder
to display the capture.pqdxml file.
- Double-click the capture.pqdxml file
to open it in the pureQueryXML editor.
The pureQueryXML editor displays the pureQueryXML file.
- 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
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.
- 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.
- Close the pureQueryXML editor.
Click the X on
the capture.pdqxml tab.