Limitations when using pureQuery client optimization with CLI applications

There are limitations when a CLI application is enabled with pureQuery client optimization.

The following are limitations when using pureQuery client optimization with a CLI application.

Support for dynamic VALUES statements and static VALUES statements
Some VALUES statements that are supported during dynamic SQL execution cannot be run with static SQL execution. To correct this issue, a VALUES statement such as VALUES ( CURRENT TIME, CURRENT_TEMPERATURE()... ) can be rewritten as SELECT CURRENT TIME, CURRENT_TEMPERATURE()... FROM SYSIBM.SYSDUMMY1.
Running dynamic XQuery statements with static execution must use the XMLQUERY function
Dynamic XQuery statements must be wrapped inside the DB2® SQL scalar function XMLQUERY to be run with static execution. For more information about using XQuery statements with XMLQUERY, see the DB2 XMLQUERY function.

If you do not use the XMLQUERY function to run XQuery statements statically, you must set the value of the allowDynamicSQL keyword in the db2cli.ini or db2dsdriver.cfg to TRUE, or remove the keyword to use the default value of TRUE.

pureQuery Runtime properties cannot be changed during an active database connection
After a database connection is established, pureQuery Runtime properties specified in a properties file cannot be changed. To change pureQuery properties, you must disconnect from the database, update the properties file or connection string, and then reconnect.
CLI application support of pureQueryXML files
CLI applications that are enabled with pureQuery client optimization support version 3 pureQueryXML files. Some pureQuery capabilities generate or use version 4 pureQueryXML files are not compatible with version 3 pureQueryXML files. The following pureQuery capabilities generate or use version 4 pureQueryXML files:
  • The pureQuery utility GeneratePureQueryXml generates version 4 pureQueryXML files.
  • For the pureQuery Configure utility, the options -groupSQLBySpecialRegisters and -removeSQLInactiveForDays requires information that is only in pureQueryXML version 4 files.
Modifying a pureQueryXML file might result in unexpected behavior
Editing the file by hand might cause unexpected behavior or might create an invalid pureQueryXML file. The only modifications recommended are for selectively tuning queries using the tuned query replacement capability of pureQuery Runtime.
Named parameter markers are replaced with "?" parameter markers
Named parameter markers in SQL statements were introduced in DB2 Version 9.7. pureQuery Runtime does not support named parameter markers. Named parameter markers are replaced with "?" parameter markers.
Changes to the pureQueryXML file that are made while an application is running are not recognized by pureQuery Runtime
When an application that is enabled with pureQuery client optimization starts, the information in the pureQueryXML file used by the application is cached. To reload the pureQueryXML file, the application must be restarted.
When CLI applications that are enabled with pureQuery client optimization are concurrently capturing SQL data, each application must specify a different pureQueryXML file
When running CLI applications concurrently and the applications are capturing SQL data (the value of the pureQuery keyword captureMode is set to ON), each application that is capturing SQL data must specify a different pureQueryXML. The file specified by the value of the keyword pureQueryXml must be different. If you want to use same pureQueryXML file, the application cannot be run concurrently. Concurrent applications using the same pureQueryXML capture file can result in a corrupted pureQueryXML file.
pureQuery client optimization is not supported on 32–bit UNIX and LINUX platforms
CLI applications that are enabled with pureQuery client optimization are not supported on 32–bit UNIX and Linux platforms.
When capturing SQL data or when executing SQL statements statically, CLI deferred prepare is disabled.
If either the value of the pureQuery keyword setting captureMode=ON, or the setting executionMode=STATIC, CLI deferred prepare is disabled.
Running SQL statements statically and capturing SQL statements simultaneously is not supported.
Setting both captureMode=ON and executionMode=STATIC at the same time on the same connection is not supported.
pureQuery Runtime does not capture positioned UPDATE or DELETE statements
For an application that is enabled with pureQuery client optimization, pureQuery Runtime does not capture positioned UPDATE or DELETE statements. For example, an UPDATE statement using WHERE CURRENT OF clause. These SQL statements are run dynamically with the keyword setting executionMode=STATIC when the keyword allowDynamicSQL=TRUE is set.
pureQuery Runtime restricts statementType SET_METHOD when the keyword capturedOnly=TRUE is set
When the keyword capturedOnly=TRUE is set, statements with the statementType SET_METHOD are restricted by pureQuery Runtime if the statement is not in the pureQueryXML.
Changes to DB2 special register settings when executing SQL statements might cause different application behavior when compared to the same SQL statements that are executed statically
For DB2 databases, the change of special registers values can change the way the application behaves. The following example uses the special register CURRENT DECFLOAT ROUNDING MODE and assumes that the value is set to ROUND_CEILING when a SQL statement is captured. If you bind the SQL statements in the pureQueryXML file and run the application so that SQL statements are executed statically (the keyword executionMode=STATIC is set) with the special register value set to ROUND_FLOOR, the returned data might be different if the data are dependent on the value of the special register. The results might be different even though there was no change in the application code.

Feedback