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.