captureStatementBatchSQL property

Specifies whether to capture SQL DML statements that the application passes to a Statement.addBatch() method, up to the number of SQL statements (not including SELECT statements) that is specified by the maxNonParmSQL property.

The captureStatementBatchSQL property applies only when the value of captureMode is ON.

This property can take these values:

ON
pureQuery captures SQL DML statements that the application passes to a Statement.addBatch() method. This is the default value.
OFF
pureQuery does not capture SQL DML statements that the application passes to a Statement.addBatch() method.
Attention: SQL DDL statements that are passed to a Statement.addBatch() method are still captured.
If executionMode is STATIC and if allowDynamicSQL is FALSE, pureQuery tries to run statically all SQL statements that the application passes to a Statement.addBatch() method. If executionMode is STATIC and if allowDynamicSQL is TRUE, all SQL statements that the application passes to a Statement.addBatch() method are run dynamically.

Therefore, set captureStatementBatchSQL to OFF only if allowDynamicSQL will be TRUE when you run the application with executionMode equal to STATIC. In this situation, each Statement.addBatch() call will execute dynamically, with no check to see if the SQL statement is in the pureQueryXML file.

When to run batched SQL statements statically
An application with batched SQL statements that set up tables with the same data every time that the application runs can benefit from using static SQL for statements that are run by the Statement.addBatch() method. For example, the application might set up correspondences between postal codes and cities, or populate a table with the locations of stores in a retail chain.

When you capture statements, set captureStatementBatchSQL to ON. This setting causes pureQuery to save in a pureQueryXML file all statements that are processed by the Statement.addBatch() method, up to the limit set by maxNonParmSQL.

When you run your application after binding the SQL statements, set executionMode to STATIC, and allowDynamicSQL to FALSE. Whenever your application requests to run the batched SQL statements, pureQuery looks in the pureQueryXML file for them. If the statements exist in the pureQueryXML file and they are bound, pureQuery runs them. If they do not exist in the pureQueryXML file or are not bound, pureQuery throws an exception because allowDynamicSQL is FALSE.

When to run batched SQL statements dynamically
An application that uses the Statement.addBatch() method for heterogeneous SQL statement batching might run many hundreds of SQL statements that it does not run again. This type of activity might occur in applications that take user input and construct INSERT, UPDATE, or DELETE statements that use SQL literals for user-provided values.

Heterogeneous SQL statement batching is useful for this type of application, reducing network traffic, but not losing the necessary ordering of execution of the INSERT, UPDATE, or DELETE statements.

When you capture statements for the application, set captureStatementBatchSQL to OFF. This setting causes pureQuery not to capture any of the SQL statements that the Statement.addBatch() method processes. None of those statements count against the limit set by maxNonParmSQL.

When you run your application after binding the other SQL statements, set executionMode to STATIC and allowDynamicSQL to TRUE. When the application requests to run the batched SQL statements, pureQuery does not look for them in the pureQueryXML file and runs them dynamically.


Feedback