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 statement is run that changes a special register setting. Changes of commonly used special register settings that might affect the behavior of an SQL statement are recorded in the pureQueryXML file during SQL capture.

For DB2® databases, the change of special registers values can affect the way the application behaves. For example, the special register CURRENT DECFLOAT ROUNDING MODE might affect the data returned by an SQL statement. Assume that the value of the special register is set to ROUND_CEILING when an SQL statement is captured. Then you bind the SQL statements in the pureQueryXML file with the bind option that sets the equivalent to the value ROUND_FLOOR. When the application runs the SQL statements in static mode, 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.

Special register values tracked and recorded by pureQueryXML client optimization

When pureQuery client optimization captures an SQL statement while an application is running, it 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 that have their values 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 information about the Configure utility and the information visible when editing a pureQueryXML, file see the related links. For the DB2 special register values that are recorded with the SQL statement in the pureQueryXML file, see Table 1.
Not safe and not recorded
Special register values that are not recorded, however changes to their values might affect the behavior of SQL statements. If the value of a special register that is not safe changes, pureQuery Runtime records that a change has occurred in the pureQueryXML file.
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 behavior of 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 recorded special register information is stored in the pureQueryXML file as a special register values set. The information can be used to create statement sets when configuring the pureQueryXML files with the Configure utility. The recorded special register information is visible when viewing the pureQueryXML file in the pureQueryXML editor in the workbench.

You can use the pureQuery Configure utility options -groupSQLBySpecialRegisters and -optionsFileForBind to help you run SQL statements statically. You can use the options separately or together.

The option -groupSQLBySpecialRegisters groups SQL statements into statement sets based on the special register information that were recorded when the statements were captured.

The -optionsFileForBind option creates a StaticBinder options file. The StaticBinder options file contains bind options for the statement sets that are in the pureQueryXML file. The bind options are based on the special register information that is recorded with the SQL statements. The file also contains comments with information and warnings about the statement sets, SQL statements, and special register information of the pureQueryXML file.

By using the Configure utility options, you can group the SQL statements according to special register information and generate a StaticBinder options file that contains the bind options. The bind options and additional information in the file help you specify a set of bind options so that when an SQL statement runs statically, the behavior is similar to when it is issued from the application and run dynamically. When you have a set of bind options, use the StaticBinder utility, the options, and the pureQueryXML file to bind packages to a database.

Note: pureQuery Runtime Version 2.2.0.3 and later versions record special register information in a pureQueryXML file. If your pureQueryXML file was created with a version of pureQuery Runtime that is earlier than Version 2.2.0.3, you might need to capture some SQL statements again to record the special register information.

The following table lists the special registers recorded by pureQuery Runtime and the bind options associated with the special registers.

Table 1. DB2 special registers and the associated DB2 bind options
Special register Bind option Notes
CURRENT DECFLOAT ROUNDING MODE1 ROUNDING for DB2 for z/OS® and DB2 for System i®.

No bind option for DB2 for Linux, UNIX, and Windows

The ROUNDING bind option is not available for all DB2 databases. Databases that don't support this bind option report a warning when it is used.
CURRENT PATH FUNCPATH for DB2 for Linux, UNIX, and Windows

PATH or FUNCPATH for DB2 for z/OS and DB2 for System i

When generating a StaticBinder options file, the Configure utility specifies the FUNCPATH bind option.
CURRENT PRECISION2 DEC The CURRENT PRECISION special register value can include the values of the precision and of the minimum divide scale for division operations. The bind option value cannot include the value for the minimum divide scale.

When generating a StaticBinder options file, the Configure utility generates a warning comment if it finds a minimum divide scale value in the CURRENT PRECISION value.

CURRENT SCHEMA3 QUALIFIER  
CURRENT SQLID3 QUALIFIER for DB2 for Linux, UNIX and DB2 for System i

No bind option for DB2 for z/OS

When generating a StaticBinder options file, the Configure utility generates a warning comment when the values of the CURRENT SCHEMA and CURRENT SQLID special registers are not identical.

For DB2 for z/OS, the OWNER bind option can be used in some situations.

Note:
  1. 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.
  2. The special register specifies the DECIMAL precision. The special register is supported only on DB2 for z/OS. The special register is not supported on DB2 for Linux,UNIX, and Windows and DB2 for System i.
  3. On DB2 for Linux, UNIX, and Windows and DB2 for System i, CURRENT SQLID and CURRENT SCHEMA are used as synonyms.

Feedback