captureMode property

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_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
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

Feedback