sqlLiteralSubstitution property

Specifies whether to perform literal substitution. When this option is enabled, pureQuery Runtime attempts to replace literal values in SQL statements with parameter markers and capture the parameterized versions of the statements. Your application can then run those SQL statements statically and benefit from the security and speed of static SQL. If your application runs SQL statements that share the same syntax and differ only in the literal values that they contain, you can capture and consolidate those statements.

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.

Topic sections

Example
Setting the values of the sqlLiteralSubstitution property
Casting
SQL literal replacement
DB2 CLI and IBM Data Server Driver usage notes

Example

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(?,?,?,?,?)

Note: In the CLI application environment, the NULL is not replaced with parameter marker, and the comments within the SQL statement are not removed.

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.

If the value of capturedOnly is TRUE, pureQuery throws an SQLException and does not run the statement because the statement is not in the pureQueryXML file.
If the value of capturedOnly is FALSE and the value of allowDynamicSQL is TRUE, pureQuery tries to run the statement dynamically.
If the value of capturedOnly is FALSE and the value of allowDynamicSQL is FALSE, pureQuery throws an SQLException and does not run the statement.

Setting the values of the sqlLiteralSubstitution property

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.

Table 1. The effects of setting or not setting a value for the sqlLiteralSubstitution 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.¹ 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. ²
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.

Note:

The property maxStackTracesCaptured and stack traces are not supported in a CLI application environment.
If pureQuery Runtime cannot match a parametrized SQL statement the pureQueryXML file, pureQuery Runtime behavior depends on the value of captureMode property:
- If the value of captureMode is ON and pureQuery Runtime determines that literal substitution is supported for the SQL statement, pureQuery executes and captures the parameterized SQL statement.
- If the value of captureMode is OFF, pureQuery Runtime then looks for the original (non-parametrized) SQL statement in the pureQueryXML file. pureQuery Runtime either executes the statement or throws an exception. The pureQuery action depends on whether it finds the statement in the file and on the values of the pureQuery Runtime properties allowDynamicSQL and capturedOnly.
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.
For DB2 for z/OS V10 and above: If your application uses SET SESSION TIME ZONE and your environment has the sqlLiteralSubstitution property set to ENABLE, make sure that the setting for IMPLICIT_TIMEZONE in your DB2 for z/OS DSNHDECP structure is SESSION. Any other setting might cause incorrect values to be used as the TIME ZONE portion of a TIMESTAMP WITH TIME ZONE column or parameter.

Casting

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.

CAST function

When an SQL statement uses the CAST function to cast a literal value to a data type, pureQuery replaces that value with a parameter marker.

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.

Implicit casting

pureQuery Runtime performs implicit casting between string and datetime constants.

Converting strings to datetime constants

INSERT INTO DEPARTMENT VALUES(1,5,56,'000010','A00','aa','2008-09-09')

pureQuery Runtime does not perform any other types of implicit casting.

SQL literal replacement

pureQuery Runtime does not attempt SQL literal replacement on following types of SQL statements:
- DDL statements
- CALL statements
- XQUERY expressions
- COMPOUND SQL statements
- The SQL statements that contain a ? parameter marker, a :name parameter marker, or the XMLQUERY function.
- SQL statements that contain one ore more literals where pureQuery Runtime does not replace the literal with a parameter marker. See the following item.
In an SQL statement, pureQuery replaces a literal value that is a number, a quoted string, a date or time constant, or NULL only. For numbers, the plus or minus is generally considered part of the numeric value. pureQuery replaces a quoted string when that string is a stand-alone string. If the quoted string is part of a DATE, TIME, TIMESTAMP (for example DATE '2014-06-30'), the entire datetime literal is replaced. pureQuery Runtime does not replace literals in some cases including the following situations:
- When value NULL is in IS NULL or IS NOT NULL
- When the value n is in the OPTIMIZE FOR n ROWS clause
- When the value n is in the FETCH FIRST n ROWS ONLY clause
Note: There are special rules for which literals are replaced in CAST functions. See Casting.
In a Java™ application environment, pureQuery Runtime attempts to replace the literal with either an untyped parameter marker or, when needed, a typed parameter marker. For a typed parameter marker, the data type specified in the CAST specification is determined by the literal that is replaced. For example, a literal specifying a time is replaced with a typed parameter marker CAST(? AS TIME).
- DATE, TIME, and TIMESTAMP literals in the JDBC escape syntax. JDBC encloses date and time literals in braces. For example, a date of 2014-06-30 is {d '2014-06-30'}. For example, this SQL statement uses a DATE with the JDBC escape syntax.
```
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 = ?
```
- Literal values in a SELECT expression list that specifies the columns of the final result table. For example, this SELECT expression list contains 1.
```
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)) )
```
- Literal values in labeled durations. A labeled duration is a number followed by one of the duration keywords YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, or MICROSECOND (optionally plural). For example, this SQL statement returns information regarding events which took place in the last two weeks:
```
	SELECT EVENT_DATE, EVENT_SUBJECT FROM TABLEQ
		WHERE EVENT_DATE >= CURRENT_DATE - 14 DAYS
```
  When performing literal substitution, pureQuery Runtime generally replaces the number 14 in the labeled duration 14 DAYS with a typed parameter marker.
```
 	SELECT EVENT_DATE, EVENT_SUBJECT FROM TABLEQ
		WHERE EVENT_DATE >= CURRENT_DATE - CAST(? AS INT) DAYS
```
- Literal values in function invocations. If an SQL statement contains a function invocation, the literals in the function invocation are generally replaced with typed parameter markers. For example, this function invocation contains a string literal.
```
 LOCATE ('MILLER', LASTNAME) 
```
  When performing literal substitution, pureQuery Runtime replaces the string literal in this function invocation with a typed parameter marker.
```
LOCATE (CAST(? AS VARCHAR(10)), LASTNAME) 
```
Note: Support of data types varies among DB2 database versions and platforms. The replacement of SQL literals with typed parameter markers might affect pureQuery Runtime functionally if you change the target database after capturing SQL statements.
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.
When performing SQL literal substitution, pureQuery Runtime attempts to prepare the statement after replacing the literals with parameters. If the database allows the SQL statement to be prepared, pureQuery checks the literal values:
- For an IBM® Data Server Driver for JDBC and SQLJ database driver, pureQuery Runtime analyzes each literal value. pureQuery Runtime checks to ensure that replacing the literal with a parameter does not result in a change such as a loss of precision or scale. If pureQuery Runtime does not identify any problems, it sets the parameter values in the Java PreparedStatement object to ensure that the database allows the literal values to be set.
- If the database driver is not an IBM Data Server Driver for JDBC and SQLJ database driver, pureQuery sets the parameter values in the PreparedStatement object to ensure that the database allows the literal values to be set. It is the developers responsibility to ensure that the literal substitution does not result in a change such as due to a loss of precision.
Note: There are cases where a database does not allow an SQL statement to be prepared because database does not allow a literal to be replaced with parameter. The literals that can be replaced with parameters varies between databases.

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.
The following notes apply when running a pureQuery enabled application with SQL literal substitution enabled:
- For a Java application, an SQLException object that pureQuery returns because of a problem with an SQL statement might differ from an SQLException object that JDBC would return for the same problem in the same statement. Therefore, applications cannot depend on SQLException objects being the same with client optimization as they would be without client optimization.
- Applications must accommodate changes in the data type of an SQL expression. However, numeric expressions remain Numeric objects (though the precision and scale might change), and string expressions remain String objects.

In a CLI application environment, the following limitations apply for the substitution of numeric literals and string literals in DML statements:
- Literals are not replaced when they are used in the following statements:
  - In a MERGE statement
  - In the projection list of a SELECT statement
- NULL values are not replaced with parameter markers.
- For built-in functions and user-defined functions (UDF), literal arguments are replaced only for the DATE() function. When a CLI application uses the DATE() function in a SQL query, the DATE() function is replaced with the following function:
```
DATE( CAST( ? AS VARCHAR(255) ) )
```
  When used in INSERT statements, the literal in the DATE() function is not replaced with a parameter marker.
  For the syntax DATE 'date-literal', the value date-literal is not replaced with a parameter marker. For example, the literal is not replaced in the following WHERE clause:
```
WHERE datecol = DATE '2001-01-01'
```
Literal substitution for an SQL statement might not occur based on reasons not previously described.

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: sqlLiteralSubstitution = ENABLE | DISABLE | NOT_SET
IBM Data Server Driver configuration syntax: <parameter name="sqlLiteralSubstitution" value="ENABLE | DISABLE | NOT_SET" />
Equivalent IBM Data Server Provider for .NET connection string keyword: sqlLiteralSubstitution=ENABLE | DISABLE | NOT_SET

Enabling SQL literal substitution without a pureQueryXML file

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.

The following sample db2dsdriver.cfg configuration file enables the sqlLiteralSubstitution keyword and does not contain the pureQueryXml keyword.

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

Feedback