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.
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:
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.
The following table lists the special registers recorded by pureQuery Runtime and the bind options associated with the special registers.
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. |