Specifies whether to capture information
about SQL statements and the type of information to capture.
This information is written to the file that is specified
by the pureQueryXml property or the location specified
by the outputPureQueryXml property. If the location
is a repository that was created in a database, the information is
written to the repository runtime group specified by the propertiesGroupId property.
When you run your application with captureMode set
to ON, you must run successfully all logic paths that contain the
SQL statements that you want to capture. pureQuery captures SQL statements
only when they are run successfully.
When
capturing SQL statements from an application that accesses a DB2® for z/OS® database, pureQuery Runtime tracks SQL
statements capable of Multiple Row Inserts (MRI). The pureQuery utilities
Configure and StaticBinder use the information when processing the
statements.
For Java applications,
the JDBC method setMaxRows controls the maximum
number of rows returned by an SQL statement. pureQuery Runtime captures
the value specified by the JDBC method setMaxRows when
capturing SQL information. The pureQuery StaticBinder utility uses
the setMaxRows value when creating packages that
contain the SQL statement and binding the packages. pureQuery Runtime
uses the setMaxRows value to limit the number of
rows returned when running SQL statements statically.
SQL statements
might be captured multiple times with different values specified by
the setMaxRows method. pureQuery Runtime uses the
largest value specified by the setMaxRows method.
If the statement also contains the FETCH FIRST clause, pureQuery Runtime
also uses the value specified by the clause to determine the maximum
number of rows returned.
Note: If the setMaxRows method
specifies the value 0, all rows are returned.
If an SQL statement
is run with optimistic locking, the statement cannot be run statically.
Consequently, pureQuery Runtime does not capture statements that are
run with optimistic locking. The statements are not affected by the
pureQuery Runtime options
allowDynamicSQL or
capturedOnly.
Optimistic locking is enabled by the following methods:
DB2Connection.prepareDB2OptimisticLockingQuery
DB2Statement.executeDB2OptimisticLockingQuery
This
property can take these values:
- ON
- Specifies to capture SQL statements. pureQuery Runtime records
SQL runtime information when the application runs.
- OFF
- The default value. Specifies not to capture SQL statements when
the application runs.
- NEW_STMTS
- Specifies that only new SQL statements are captured. pureQuery
Runtime also updates SQL runtime information for statements in the
pureQueryXML file. The captured information is recorded if it is different
from the information in the pureQueryXML file and the information
might affect future static execution of previously captured statements.
For example, if an SQL statement is captured and the same statement
is captured again with a different a SCHEMA setting or with a different
special register setting, pureQuery Runtime updates the information
associated with the SQL statement in pureQueryXML file with the new
SCHEMA information.
SQL information such as execution time and execution
counts are not captured.
This value is supported only for Java™ applications.
If you
specify different input and output pureQueryXML files, only new statements
and information that might affect future static execution of previously
captured statements are written to the output file.
If you set
the value of the captureMode property to
NEW_STMTS,
the default values for the pureQuery Runtime properties are changed.
The following table lists the changed default values:
Table 1. Default
values for pureQuery Runtime properties when the captureMode property
is set to NEW_STMTS| pureQuery Runtime property |
Default value |
|---|
| maxNonParmSQL |
100 |
| maxStackTracesCaptured |
1 |
| stackTraceDepth |
5 |
| sqlLiteralSubstitution |
NOT_SET |
You can specify values for pureQuery Runtime properties
to override the defaults.
- LITE
- Specifies that a reduced amount of stack trace information is
captured with SQL statements. Reducing the amount of data captured
makes capture process less resource intensive. pureQuery Runtime updates
the pureQueryXML file for both new and previously captured SQL statements.
SQL
information such as execution time and execution counts are captured.
This
value is supported only for Java applications.
If
you set the value of the captureMode property to
LITE,
the default values for the pureQuery Runtime properties are changed.
The following table lists the changed default values:
Table 2. Default
values for pureQuery Runtime properties when the captureMode property
is set to LITE| pureQuery Runtime property |
Default value |
|---|
| maxStackTracesCaptured |
1 |
| stackTraceDepth |
5 |
| sqlLiteralSubstitution |
NOT_SET |
You can specify values for pureQuery Runtime properties
to override the defaults.
DB2 CLI and IBM Data Server Driver usage notes
When using DB2 Call Level Interface (CLI) or
the IBM® Data Server Driver with
pureQuery Runtime, you can use the pureQuery Runtime property as a
configuration keyword.
- IBM CLI keyword syntax
- captureMode = ON | OFF
- IBM Data Server Driver configuration
syntax
- <parameter name="captureMode" value="ON | OFF" />
- Equivalent IBM Data Server
Provider for .NET connection string keyword
- captureMode=ON | OFF
Usage notes
The following
information applies when capturing SQL information:
- CLI and .NET applications that are enabled with pureQuery client
optimization support only the pureQueryXml keyword
to capture data in a file.
- To ensure that Java applications
with named parameter markers work correctly, regardless of the data
server type and version, before you use named parameter markers in
your applications, set the property Connection or DataSource enableNamedParameterMarkers to DB2BaseDataSource.YES.
Examples
In this example, a company previously
captured SQL statements from an application enabled with pureQuery
client optimization. The statements are in a pureQueryXML file. The
company deployed the application and the pureQueryXML file prod-v1.pdqxml
in a production environment. They performed the bind process so that
the SQL statements run statically on the production database. However,
they are not sure if all the SQL statements run by the application
were captured. To capture only SQL statements that were not previously
captured, the company can set the value of the
captureMode property
to
NEW_STMTS. The following property settings configure
pureQuery Runtime to capture SQL statements in the pureQueryXML file
newStmt.pdqxml if the statement is not the pureQueryXML file prod-v1.pdqxml.
executionMode=STATIC
captureMode=NEW_STMTS
pureQueryXml=prod-v1.pdqxml
outputPureQueryXml=newStmt.pdqxml
Only new statements are captured. Stack traces
and statistics are not captured for the new statements. The capture
process is a less resource-intensive process and might be acceptable
in a production environment.
In this example, a company wants
to capture SQL statements without capturing extensive stack trace
information from an application enabled with pureQuery client optimization.
With the following pureQuery Runtime properties, SQL statements run
by the application are captured in the pureQueryXML file capture.pdqxml.
The statements are captured with additional runtime information such
as statement execution time that can be used for tuning the application.
executionMode=DYNAMIC
captureMode=LITE
pureQueryXml=capture.pdqxml