When you configure a call level interface (CLI) application
to use pureQuery client optimization, the CLI client driver can enable
pureQuery capabilities, such as enabling existing DB2® CLI applications to execute SQL statements
statically. Executing SQL statements statically avoids preparing SQL
statements at run time and can improve application security and performance.
About this task
To configure a CLI application with pureQuery client optimization,
you first configure your CLI application to capture SQL statements
issued by your application. Then you configure the application to
control the execution of SQL statements that are issued by the application.
- If the application connects to a DB2 database,
you can configure the application to execute SQL statements statically.
The configuration process includes the additional steps of configuring
the pureQueryXML file containing the captured SQL statements and binding
the captured SQL statements.
- An Informix® database
does not support running SQL statements statically. If your application
connects to an Informix database,
you can use other pureQuery capabilities to control the SQL statements
that the application runs against the database. These capabilities
are available to CLI applications that are enabled with pureQuery
client optimization and that connect to either Informix or DB2 databases.
This task describes how to configure your CLI application
with pureQuery client optimization.
Procedure
To configure a CLI application with pureQuery client
optimization:
- Set the pureQuery keywords in your CLI application configuration
file to capture SQL statements issued by your application.
You
set the keywords in either the db2cli.ini file or the db2dsdriver.cfg
file. The settings in the db2cli.ini file take precedence over the
settings in the db2dsdriver.cfg file
- Set the value of the captureMode keyword
to ON.
- Set the value of the executionMode keyword
to DYNAMIC.
The default value for executionMode keyword
is DYNAMIC. Having the keyword in the file is reminder
of the default value.
- Set the value of the pureQueryXML keyword
to the path and file name of the pureQueryXML file that stores the
captured SQL data.
The pureQueryXML file must have the
extension .xml or .pdqxml.
- Run your application to capture SQL statements in the pureQueryXML
file.
If you move the pureQueryXML file for processing,
ensure that the pureQueryXML file is not being written to when you
retrieve the file. If you retrieve the file while it is being written
to, the contents of the file might not be valid or the application
might not be able to update the file, which causes an I/O error.
- Required to execute the SQL statements statically: Configure
the pureQueryXML file, and then use the SQL statements in the file
to create packages on the application database and bind the packages
to the database.
You process the pureQueryXML file
on a computer that has the pureQuery Runtime installed and is configured
to run the Java-based pureQuery utilities Configure and StaticBinder.
- If necessary, copy the pureQueryXML file to the computer
that is configured to run the pureQuery utilities.
- 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.
- 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 to bind your file with
the database. You run the StaticBinder utility with options that specify
the database and the package information.
- If necessary, replace the pureQueryXML file on the computer
where the CLI application runs with the processed file.
- Update the configuration of your CLI application to control
the execution of the SQL statements.
Locate the db2cli.ini
configuration file or the db2dsdriver.cfg configuration file and update
the keywords and values. The following list describes example configurations:
- To configure your application to run SQL statements statically,
update the following keywords and values:
- Set the captureMode keyword value to OFF.
- Set the executionMode keyword value to STATIC.
- Set the value of the pureQueryXML keyword to
the path and file name of the pureQueryXML that contains the captured
SQL statements.
With the keyword settings, your CLI application runs SQL statements
statically.
- To configure your application to run the application SQL statements
dynamically and restrict the application to run only statements in
the pureQueryXML file, update the following keywords and values:
- Set the value of the captureMode keyword to OFF.
- Set the value of the capturedOnly keyword to TRUE.
- Set the value of the executionMode keyword to DYNAMIC.
- Set the value of the pureQueryXML keyword to
the path and file name of the pureQueryXML that contains the captured
SQL statements.
With the keyword settings, your CLI application runs SQL statements
dynamically. Only the SQL statements in the pureQueryXML file are
run. If your application issues an SQL statement that is not in the
pureQueryXML file, an error is returned.
- Run or restart the application to use the updated information
in the configuration file and pureQueryXML file.
Results
The CLI application that is enabled with pureQuery client
optimization runs SQL statements based on the specified configuration.