When pureQuery client optimization captures SQL statement
from a running application, warning messages are sent to the log file
when a SET SPECIAL REGISTER statement is executed. Changes of commonly
used special register settings that might affect data returned by
an SQL statement are recorded in the pureQueryXML file during SQL
capture.
For DB2® databases, the change
of special registers values can impact the way the application behaves.
The following example uses the special register CURRENT DECFLOAT
ROUNDING MODE and assumes the value is set to ROUND_CEILING when
a SQL statement is captured. If you bind the SQL statements in the
pureQueryXML and execute the application in static mode with the value
set to ROUND_FLOOR, the returned data might be different
if the data are dependant on the value of the special register. The
results might be different even though there was no change in the
application code.
Special register values tracked and recorded by pureQueryXML
client optimization
When pureQuery client optimization captures
an SQL statement while a application is running, tracks and records
some special register values. When merging and configuring the captured
SQL, the special register information recorded with the SQL statement
is kept with the statement.
The following are the pureQuery
categories of special registers:
- Tracked and recorded
- Special registers that are tracked and changes recorded as pureQueryXML
data. The special register information stored in the pureQueryXML
file can be used when configuring pureQueryXML files using the Configure
utility, and is visible when editing a pureQueryXML file in the workbench.
For more information on the Configure utility and the information
visible when editing a pureQueryXML file see the related links.
- Not safe and not recorded
- These special registers that are not recorded, however changes
to their values might affect the data returned from SQL statements.
If a change to a special register that is not safe and not recorded
occurs, the value of the unTrackedRegisters attribute is set to true.
- Safe and not recorded
- Special registers that are safe and that are not recorded in the
pureQueryXML file. In general, changes to these special registers
do not affect the data returned from SQL statements. For example,
the special register CURRENT DEGREE specifies the degree of parallelism
for the execution of queries that are dynamically prepared by the
application process. Other special registers that belong to this category
are: CURRENT PACKAGE PATH, CURRENT PACKAGESET, CURRENT CLIENT_ACCTNG,
CURRENT CLIENT_APPLNAME, CURRENT CLIENT_USERID, CURRENT CLIENT_WRKSTNNAME,
and CURRENT DEBUG MODE.
Note: The special registers PACKAGE PATH and
PACKAGE SET are not recorded, however they affect static execution.
If PACKAGE SET is used, the static packages must be bound in the same
COLLECTION, otherwise an -805 error is issued during static execution.
The values of the following DB2 special registers are also captured and stored
with the SQL statement in the file:
- CURRENT SCHEMA
- CURRENT PATH
- CURRENT PRECISION1
- CURRRENT DECFLOAT ROUNDING MODE2
- CURRENT SQLID3
Note: - The special register is supported only on DB2 for z/OS®.
The special register not supported on DB2 for Linux®,UNIX® and Windows® and DB2 for System i®.
- The special register is supported on DB2 for z/OS Version 9.1 and later and
on DB2 for Linux Windows and UNIX Version 9.5 and later.
- On DB2 for Linux,UNIX and Windows and DB2 for System
i, CURRENT SQLID and CURRENT SCHEMA are
used as synonyms and can be used with the same bind options QUALIFIER.
The captured special register information stored
in the pureQueryXML file can be used to create statement sets when
configuring the pureQueryXML files with the Configure utility. The
captured special register information is visible when viewing the
pureQueryXML file in the pureQueryXML editor in the workbench.