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. The matched statements run on the database.
If your application runs SQL statements dynamically, you can benefit from the security of running only the captured statements with the literals replaced by parameter markers. You set the value of the pureQuery Runtime property capturedOnly to TRUE.
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 the capture_file.pdqxml file, and then run the StaticBinder utility to bind the statements into DB2 packages.
When you run the application with value of the executionMode property 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 the capture_file.pdqxml file. 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 when capturing SQL statements. 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 and log level is FINE or FINER, it logs a warning message. For information about logged warning messages, see SQL literal replacement |
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. 2 |
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 when possible. If the value was DISABLE or NOT_SET, 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 or NOT_SET, pureQuery does not try to match SQL statements that the application attempts to run to parameterized SQL statements in a pureQueryXML file. |
If you enable SQL statement literal substitution without specifying a pureQueryXML file, pureQuery Runtime replaces literal values in SQL statement with parameter markers before running the statement. You do not have capture SQL statement and create a pureQueryXML file. The statements are run dynamically, they do not need to be run statically. However, you cannot enable other pureQuery Runtime options that require a pureQueryXML file such as capturedOnly and executionMode.
Beginning with version 2.2.0.1, pureQuery Runtime supports two types of casting when replacing literal values with parameter markers.
SQL literal substitution for any of the parameters in CAST functions is not supported in a CLI application environment.
For example, if a statement contains the clause WHERE mySMALLINTcolumn=CAST(99 as INTEGER), the value 99, the value 99 becomes a parameter marker when the statement is captured.
To take another example, if you use the CAST function CAST(6 as DECIMAL(7,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.
INSERT INTO DEPARTMENT VALUES(1,5,56,'000010','A00','aa','2008-09-09')
pureQuery Runtime does not perform any other types of implicit casting.
If the prepare and the checks on the values succeed, then pureQuery Runtime uses the parametrized SQL statement. If the tests do not succeed, pureQuery uses to the original version of the SQL and logs a warning to alert the user about why literal substitution was not performed for the statement. The first such failure for a connection is logged at the level FINE. Subsequent failures are logged at FINER.
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.
You can enable SQL literal substitution without specifying a pureQueryXML file. You do not have capture SQL statement and create a pureQueryXML file. The statements are run dynamically, they do not need to be run statically.
<configuration> <dsncollection> <dsn alias="sample",name="sample",host="server1.test.com", port="50001"> <parameter name="sqlLiteralSubstitution", value="ENABLE"/> </dsn> </dsncollection> <databases> <database name="sample", host=" server1.test.com", port="50001"> </database> </databases> </configuration>For CLI applications enabled with pureQuery client optimization, pureQuery Runtime replaces literal values in SQL statement with parameter marker before attempting to run the statement dynamically.