Your application might, for example, generate INSERT statements that are identical syntactically, but that include literal values that are provided by users in the fields of a form.
Normally, you would not be able to run these statements statically because they are generated at run time. However, pureQuery client optimization can capture such statements by replacing the literal values with parameter markers and recognizing when the syntax of an SQL statement is identical to that of a statement that it already captured. You can therefore bind these SQL statements into DB2® packages.
When you run your application with the client optimization property executionMode set to STATIC, pureQuery can match SQL statements to the parameterized SQL statements that it captured. Matching statements run statically.
When you set the client optimization properties before capturing SQL statements, set the property sqlLiteralSubstitution to ENABLE, as in this example:
pdqProperties=captureMode (ON), executionMode (DYNAMIC), pureQueryXml (C:/workspace/capture_file.pdqxml), sqlLiteralSubstitution (ENABLE)
When your application runs an INSERT statement like the first statement in the next example, pureQuery captures it in the form of the second statement:
insert into EMPLOYEES/*inserting new row into EMPLOYEES table*/values('Dong','Margaret',NULL,60000,12) insert into EMPLOYEES values(?,?,?,?,?)
After you finish capturing statements, you can run the Configure utility on capture_file.pdqxml, and then run the StaticBinder utility to bind the statements into DB2 packages.
When you run the application with executionMode set to STATIC and the application issues the statement insert into EMPLOYEES values('Hinkis','Tali','R',68000,20), pureQuery matches the statement to the parameterized version in the pureQueryXML file and runs it statically.
However, you can tell pureQuery not to match SQL statements with parameterized statements in capture_file.pdqxml. Before you run the application, when you set executionMode to STATIC you can also set sqlLiteralSubstitution to DISABLE. When you run the application and it issues the INSERT statement in the previous paragraph, whether pureQuery runs the statement depends on the values of the client optimization properties capturedOnly and allowDynamicSQL.
The following table lists the three values of the sqlLiteralSubstitution property and their effects. The table also shows what happens when you do not set a value for this property.
Value | Effects when you capture statements for the first time | Effects when you incrementally capture | Effects when you run the application in either STATIC or DYNAMIC mode |
---|---|---|---|
ENABLE | pureQuery replaces literal values with parameter
markers. pureQuery also captures the stack trace for the original SQL statements. If you want pureQuery to capture stack traces for all of the original SQL statements, you might need to increase the value of the property maxStackTracesCaptured.1 pureQuery does not count the original SQL statements against the value of the property maxNonParmSQL. If pureQuery cannot parameterize an SQL statement, it logs a warning message. |
The effects are the same as when you capture SQL statements for the first time. | pureQuery tries to match SQL statements that the application attempts to run to parameterized SQL statements in a pureQueryXML file. |
DISABLE | pureQuery does not replace literal values with parameter markers. | pureQuery does not replace literal values with parameter markers. | pureQuery does not try to match SQL statements that the application attempts to run to parameterized SQL statements in a pureQueryXML file. |
NOT_SET (or not specified) | pureQuery does not replace literal values with parameter markers. | If the value was ENABLE when pureQuery last
captured statements in the specified pureQueryXML file, pureQuery
replaces literal values with parameter markers. If the value was DISABLE, pureQuery does not replace literal values with parameter markers. |
If the value was ENABLE when pureQuery last
captured statements in the specified pureQueryXML file, pureQuery
tries to match SQL statements that the application attempts to run
to parameterized SQL statements in a pureQueryXML file. If the value was DISABLE, pureQuery does not try to match SQL statements that the application attempts to run to parameterized SQL statements in a pureQueryXML file. |
Beginning with version 2.2.0.1, pureQuery Runtime supports two types of casting when replacing literal values with parameter markers.
The support of sqlLiteralSubstitution for the CAST() function is unavailable in a CLI application environment.
For example, if a statement contains the clause WHERE mySMALLINTcolumn=CAST(32767999 as INTEGER), the value 32767999 becomes a parameter marker when the statement is captured.
To take another example, if you use the CAST function CAST(6 as DECIMAL(3,3)), only the 6 becomes a parameter marker when the statement is captured.
User-defined external scalar functions that are named CAST are not supported.
Because the conversion takes place on database clients, you can run such SQL statements on database management systems that do not support implicit casting.
pureQuery supports the following types of implicit casting:
select DEPTNAME from DEPARTMENT WHERE DEPTNO = '1' select DEPTNAME from DEPARTMENT WHERE DEPTNO BETWEEN '20' AND '30'
select DEPTNO from DEPARTMENT WHERE DEPTNAME = 123456
INSERT INTO DEPARTMENT VALUES(1,5,56,'000010','A00','aa','2008-09-09')
insert into sales values (5, ?, 9);
DATE( CAST( ? AS VARCHAR(255) ) )
When used in INSERT statements, the literal in the DATE() function is not replaced with a parameter marker.
WHERE datecol = DATE '2001-01-01'
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.