When you configure a call level interface (CLI) or .NET
application to use pureQuery client optimization, the client driver
can enable pureQuery capabilities, such as enabling existing DB2® CLI or .NET 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 an application with pureQuery client optimization,
you first configure your 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 or .NET 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 or .NET
application with pureQuery client optimization.
Procedure
To configure a CLI or .NET application with pureQuery
client optimization:
- Set the pureQuery keywords in your application configuration
file to capture SQL statements issued by your application.
- For CLI applications, 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.
- For .NET applications, you set the keywords in the db2dsdriver.cfg.
- 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. The following example
db2dsdriver.cfg configuration
file specifies the pureQuery Runtime keywords for a CLI application:
<configuration>
<dsncollection>
<dsn alias="alias1" name="name1" host="server1.net1.com" port="50001">
<parameter name="captureMode" value="on"/>
<parameter name="executionMode" value="dynamic"/>
<parameter name="pureQueryXml" value="testclico.pdqxml"/>
</dsn>
</dsncollection>
<databases>
<database name="name1" host="server1.net1.com" port="50001">
<parameter name="CommProtocol" value="TCPIP"/>
</database>
</databases>
</configuration>
- 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 application runs with the processed file.
- Update the configuration of your 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 application runs SQL statements
statically. The following example db2dsdriver.cfg configuration
file specifies the pureQuery Runtime keywords for a CLI application:
<configuration>
<dsncollection>
<dsn alias="alias1" name="name1" host="server1.net1.com" port="50001">
<parameter name="captureMode" value="off"/>
<parameter name="executionMode" value="static"/>
<parameter name="pureQueryXml" value="testclico.pdqxml"/>
</dsn>
</dsncollection>
<databases>
<database name="name1" host="server1.net1.com" port="50001">
<parameter name="CommProtocol" value="TCPIP"/>
</database>
</databases>
</configuration>
- 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 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. The following example
db2dsdriver.cfg configuration
file specifies the pureQuery Runtime keywords for a CLI application:
<configuration>
<dsncollection>
<dsn alias="alias1" name="name1" host="server1.net1.com" port="50001">
<parameter name="captureMode" value="off"/>
<parameter name="capturedOnly" value="TRUE"/>
<parameter name="executionMode" value="dynamic"/>
<parameter name="pureQueryXml" value="testclico.pdqxml"/>
</dsn>
</dsncollection>
<databases>
<database name="name1" host="server1.net1.com" port="50001">
<parameter name="CommProtocol" value="TCPIP"/>
</database>
</databases>
</configuration>
- 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.
pureQuery
Runtime checks for a valid pureQuery license if any pureQuery Runtime
keywords are specified in a configuration file. If a valid pureQuery
license is not found, pureQuery Runtime ends and returns the error
code SQL8029N.
What to do next
You can store the pureQueryXML file data in a repository
created in a database. To have the application retrieve and use the
pureQueryXML file data, you can specify the pureQuery keywords pureQueryXmlRepository and propertiesGroupId to
point to the repository and active runtime group that contains data.
For information about repositories, see Accessing and storing pureQuery data from file systems and repositories.