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 to 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 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.
SELECT ORDERID FROM ORDERS WHERE DATE = {d '2014-06-30'}
The literal including the braces is replaced with a parameter marker ?.
SELECT ORDERID FROM ORDERS WHERE DATE = ?
SELECT 1 FROM TABLEA
When performing literal substitution, pureQuery Runtime replaces the 1 with the typed parameter marker CAST(? AS INT).
SELECT CAST(? AS INT) FROM TABLEA
In this example, the literal is a date in the JDBC escape syntax.
SELECT {d '2014-06-30'} FROM TABLEA
The literal is replaced with the typed parameter marker CAST(? AS DATE).
SELECT CAST(? AS DATE) FROM TABLEA
For a DB2 for Linux, UNIX, Windows database, a VALUES clause can contain literals. For example, the following VALUES clause contains an integer and a decimal literal.
VALUES ( 300, 3215.67 )
When performing literal substitution, pureQuery Runtime replaces the literals with typed parameter markers.
VALUES ( CAST(? AS INT), CAST(? AS DECIMAL(10,5)) )
SELECT EVENT_DATE, EVENT_SUBJECT FROM TABLEQ
WHERE EVENT_DATE >= CURRENT_DATE - 14 DAYS
SELECT EVENT_DATE, EVENT_SUBJECT FROM TABLEQ
WHERE EVENT_DATE >= CURRENT_DATE - CAST(? AS INT) DAYS
LOCATE ('MILLER', LASTNAME)
LOCATE (CAST(? AS VARCHAR(10)), LASTNAME)
For example, a database does not support the BIGINT data type. An integer literal in a captured SQL statement might be replaced by CAST(? AS DECIMAL (19,0)). Then the target database is changed to a one that supports BIGINT. At run time, pureQuery Runtime attempts to match the statement with the integer literal that is replaced by CAST (? AS BIGINT) and does not match the captured SQL statement. If the value of the pureQuery Runtime property capturedOnly is TRUE, pureQuery Runtime might start restricting the execution of SQL statements that were not restricted on the original database.
The ability to bind SQL statements might be affected. For example, an SQL statement is captured that contains CAST( ? as TIMESTAMP WITH TIME ZONE ). On a DB2 for z/OS® Version 10 database, the StaticBinder utility can perform a bind operation on the pureQueryXML file that contains the statement. On a DB2 for Linux, UNIX, and Windows database, you must use the StaticBinder to remove or mark the statement invalid.
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 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 to 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 or .NET applications enabled
with pureQuery client optimization, pureQuery Runtime replaces
literal values in SQL statement with parameter marker before attempting
to run the statement dynamically.