Tracking DB2 special register changes with pureQuery client optimization

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:
  1. 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®.
  2. 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.
  3. 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.


Feedback