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.
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 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 records SQL runtime information if the special register settings
captured with the SQL statement are different from the settings in
the pureQueryXML file. For example, if an SQL statement has been captured
and the same statement is captured again with a different a SCHEMA
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 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_STMTSpureQuery 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 LITEpureQuery 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
- N/A
Usage notes
CLI 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 has deployed the application and the pureQueryXML file prod-v1.pdqxml
in a production environment. They have 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
have been captured. To capture only SQL statements that have not been
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