Compatibility and other special registers in DB2

How these registers fit together

Think of a Db2 session as a workbench. Bind options nail down static SQL at BIND time. Special registers are the knobs you can still turn for dynamic SQL after the package is allocated. The initial value of most registers comes from a bind option, a CREATE/ALTER routine option, or a subsystem parameter (ZPARM). SET statements then change the value for later statements in that application process. Inside a stored procedure or user-defined function, inheritance follows the INHERIT SPECIAL REGISTERS versus DEFAULT SPECIAL REGISTERS rules.

You can display a register with VALUES, SELECT from SYSIBM.SYSDUMMY1, or SET into a host variable. Changing a register is not under transaction control: ROLLBACK does not undo a SET CURRENT DEGREE.

1
2
3
4
5
6
SELECT CURRENT APPLICATION COMPATIBILITY,
       CURRENT DEGREE,
       CURRENT RULES,
       CURRENT PRECISION,
       CURRENT EXPLAIN MODE
  FROM SYSIBM.SYSDUMMY1;

CURRENT APPLICATION COMPATIBILITY

CURRENT APPLICATION COMPATIBILITY (data type VARCHAR(10)) specifies the application compatibility level for dynamic SQL in packages. You can write APPLCOMPAT as an abbreviation when setting or referencing the register. The initial value for a package is the APPLCOMPAT bind option. For a user-defined function or stored procedure it is inherited. For advanced triggers the initial value is V12R1.

Application compatibility is how Db2 continuous delivery lets the subsystem move forward while each application still sees a chosen SQL language level. New SQL syntax and some incompatible behaviors are gated by this level. The subsystem function level can be higher than the application; the application does not get the new SQL until APPLCOMPAT (static) or this register (dynamic) says so.

You cannot SET the register higher than the APPLCOMPAT of the allocated package. If the package is bound APPLCOMPAT(V12R1M503), SET CURRENT APPLICATION COMPATIBILITY = 'V12R1M505' fails even if the group is already at function level 505. You can SET a lower level, which is useful when a higher APPLCOMPAT package must still run an older DDL behavior (for example creating a traditional segmented table space after that syntax was restricted).

If a workstation tool returns SQLCODE -4743, check SYSPACKAGE.APPLCOMPAT for the IBM Data Server Driver packages it is using (often collection NULLID). Rebind those packages to the level you need; you cannot SET the register above the package.

1
2
SET CURRENT APPLICATION COMPATIBILITY = 'V12R1M500';
SET :CS = CURRENT APPLICATION COMPATIBILITY;

CURRENT APPLICATION ENCODING SCHEME

CURRENT APPLICATION ENCODING SCHEME tells Db2 which encoding scheme to use when processing data for dynamic statements. The data type is CHAR(8). You may SET the register to ASCII, EBCDIC, or UNICODE, but what is stored is the character form of the corresponding numeric CCSID, padded on the right with blanks to 8 bytes. Use the CCSID_ENCODING scalar function if you need the name ASCII, EBCDIC, or UNICODE back from a CCSID.

This register is not supported in REXX applications or REXX stored procedures. For stored procedures and UDFs, the initial value comes from the ENCODING bind option of the associated package, or from the APPLICATION ENCODING SCHEME clause on CREATE/ALTER FUNCTION or PROCEDURE for compiled SQL functions and native SQL procedures. If that option was omitted, the DEFAULT APPLICATION ENCODING SCHEME field on installation panel DSNTIPF is used. Basic triggers start at Unicode; advanced triggers start at the DSNTIPF default.

Mismatched encoding is a common reason a dynamic INSERT “looks almost right” in QMF or a JDBC client. Set the register to match the CCSID of the host variables or the application encoding you actually send.

1
2
VALUES (CURRENT APPLICATION ENCODING SCHEME) INTO :HV1;
SET CURRENT APPLICATION ENCODING SCHEME = 'EBCDIC';

CURRENT DEBUG MODE

CURRENT DEBUG MODE (VARCHAR(8)) supplies the default DEBUG MODE option when you create or alter certain SQL objects: a new version of an SQL scalar function, a new version of a native SQL procedure, a new version of an advanced trigger, CREATE FUNCTION for an SQL scalar function, CREATE PROCEDURE for a Java procedure, CREATE PROCEDURE for a native SQL procedure, and CREATE TRIGGER (advanced).

DISALLOW is the initial value outside UDFs, procedures, and triggers. DISABLE is the strong choice for production objects you never want in the Unified Debugger. ALLOW is for development. Changing the register does not alter existing objects; it only defaults the next CREATE or ALTER that omits an explicit DEBUG MODE clause.

1
2
SET CURRENT DEBUG MODE = 'DISALLOW';
VALUES CURRENT DEBUG MODE INTO :DEBUG_MODE_OPT;

CURRENT DECFLOAT ROUNDING MODE

CURRENT DECFLOAT ROUNDING MODE (VARCHAR(128)) is the default rounding mode for DECFLOAT values. The initial value is the ROUNDING bind option or native SQL procedure option; if that is omitted, DEF DECFLOAT ROUND MODE on DSNTIPF is used.

Rounding matters when a DECFLOAT result must be shortened to fit a target precision. ROUND_HALF_EVEN is the usual IEEE-style choice. ROUND_HALF_DOWN and ROUND_UP are documented as not recommended for portable applications because they are not in the IEEE floating-point standard. SET the register before dynamic statements that mix DECFLOAT with other numeric types if you need a specific policy.

1
SET CURRENT DECFLOAT ROUNDING MODE = 'ROUND_CEILING';

CURRENT DEGREE

CURRENT DEGREE specifies the degree of parallelism for queries that are dynamically prepared by the application process. The data type is CHAR(3). The only valid values are 1 (padded on the right with two blanks) and ANY.

• 1 — when the query is dynamically prepared, execution will not use most forms of parallelism. Setting 1 does not disable DPSI parallelism; use the PARAMDEG_DPSI subsystem parameter for that.
• ANY — the query may use parallelism. Db2 still decides how many parallel tasks based on cost, buffer pools, and other limits.

The initial value comes from the CDSSRDEF subsystem parameter (CURRENT DEGREE field on DSNTIP8). The IBM default is 1 unless your shop changed it. CURRENT DEGREE is a register at the database server; it applies to queries dynamically prepared there and to queries dynamically prepared on another Db2 through a private connection from that server.

Use 1 when you need a predictable, single-task plan (online transactions, tight CPU caps). Use ANY for large dynamic reporting when Sysplex query parallelism or CPU parallelism can help. Static SQL uses the DEGREE bind option instead of this register.

1
2
SET CURRENT DEGREE = '1';
SET CURRENT DEGREE = 'ANY';

CURRENT EXPLAIN MODE

CURRENT EXPLAIN MODE (VARCHAR(128)) controls whether eligible dynamic SQL statements write EXPLAIN information into the EXPLAIN tables. The initial value is NO. Prepared statements are not saved in the dynamic statement cache when the value is YES or EXPLAIN.

Prerequisites: PLAN_TABLE and DSN_STATEMENT_CACHE_TABLE must exist, qualified with the current SQLID used when the application runs, the dynamic statement cache must be enabled, and the application must contain explainable statements. The privilege set for the underlying statement must be allowed to use EXPLAIN. When the EXPLAIN privilege is in effect and the register is EXPLAIN, an SQLCODE from that privilege overrides an SQLCODE that would otherwise come from the register itself.

YES is the “run it and also capture the path” setting. EXPLAIN is the “show me the path but do not change data” setting, which is safer in production-like copies. Static SQL still uses the EXPLAIN bind option; if that option is ALL and this register is NO, dynamic SQL can still be explained at run time from the bind option. If this register is not NO, the EXPLAIN bind option is ignored for that interaction.

1
2
3
4
SET CURRENT EXPLAIN MODE = EXPLAIN;
-- Prepare and "run" a SELECT; the SELECT itself is not executed
SELECT EMPNO, LASTNAME FROM HR.EMPLOYEE WHERE WORKDEPT = 'A00';
SET CURRENT EXPLAIN MODE = NO;

CURRENT LOCALE LC_CTYPE

CURRENT LOCALE LC_CTYPE (CHAR(50), blank-padded) is the LC_CTYPE locale used when SQL runs a built-in function that consults a locale. LCASE, UCASE, and TRANSLATE with a single argument use this register.

• blank (IBM default) — simple A–Z / a–z folding. Diacritical marks are not converted. Mixed or DBCS data folds full-width Latin A–Z the same way. Fastest; use this unless you need locale rules.
• UNI — Unicode Services NORMAL and SPECIAL casing. Not for EBCDIC data.
• UNI_60 — Unicode Standard 6.0.0 with NORMAL casing. Not for EBCDIC.
• UNI_90 — Unicode Standard 9.0.0 with NORMAL casing. Not for EBCDIC.
• UNI_SIMPLE — Unicode Services NORMAL casing only. Not for EBCDIC.
• locale name — a named locale such as En_US, Fr_FR@EURO, Fr_BE, or Ja_JP. EBCDIC locale names follow XL C/C++ conventions; Unicode and ASCII locales follow z/OS Unicode Services.

The initial value is the LOCALE LC_CTYPE field on DSNTIPF (blank unless your shop changed it). If UCASE of a name with accents looks “wrong,” the register is probably still blank. Do not SET UNI_* when the expression is EBCDIC.

1
2
SET CURRENT LOCALE LC_CTYPE = 'En_US';
SELECT UCASE(LASTNAME) FROM HR.EMPLOYEE;

CURRENT OPTIMIZATION HINT

CURRENT OPTIMIZATION HINT (VARCHAR(128)) names the user-defined optimization hint Db2 should use when it builds the access path for dynamic statements. The value matches rows in owner.PLAN_TABLE whose OPTHINT column equals the register. An empty string or all blanks means normal optimization; hints are ignored. If the register contains non-blank characters and the subsystem was installed with optimization hints disabled (OPTIMIZATION HINTS on DSNTIP8), you get a warning.

The initial value is the OPTHINT bind option or native SQL procedure option. Hints are a last resort after statistics, indexes, and REOPT. A stale hint can freeze a bad plan after a Db2 upgrade. Prefer updating RUNSTATS and rewriting SQL before pinning a path.

1
2
3
SET CURRENT OPTIMIZATION HINT = :NOHYB;
-- empty string returns to normal optimization
SET CURRENT OPTIMIZATION HINT = '';

CURRENT PRECISION

CURRENT PRECISION (CHAR(5)) chooses the decimal arithmetic rules used when both operands in a decimal operation have precision 15 or less. It affects dynamic SQL only. If either operand already has precision greater than 15, DEC31 rules are always used.

• DEC15 — rules that do not allow a precision greater than 15 digits. Often the installation default (DECIMAL ARITHMETIC on DSNTIP4).
• DEC31 — rules that allow precision up to 31 digits. More likely to raise errors on division than DEC15.
• Dpp.s — pp is 15 or 31 (which rule set), s is a minimum divide scale from 1 to 9. The separator may be a period or a comma regardless of the default decimal point. Example: D31.3 means DEC31 rules with minimum divide scale 3, which can make some division errors less likely when pp is 31.

1
2
SET CURRENT PRECISION = 'DEC31';
SET CURRENT PRECISION = 'D31.3';

CURRENT ROUTINE VERSION

CURRENT ROUTINE VERSION (VARCHAR(64)) is the version identifier used when you CALL a native SQL procedure and the procedure name is supplied by a host variable. An empty string (the initial value outside UDFs and procedures) means no override: Db2 uses the currently active version from the catalog. If you SET a version that exists for that procedure, that version runs; if the procedure has no matching version, the active catalog version still runs.

This is how shops test a new native SQL procedure version in one session without activating it for everyone. CALL statements that use a literal procedure name do not use this register the same way; the host-variable form is the documented case.

1
2
3
SET CURRENT ROUTINE VERSION = 'V2';
CALL :PROC-NAME;
VALUES CURRENT ROUTINE VERSION INTO :ROUTINE_VER;

CURRENT RULES

CURRENT RULES (CHAR(3)) chooses whether SQL statements follow Db2 rules or the SQL standard. Valid values are DB2 and STD. At a remote server that is not the local Db2, the initial value is DB2. Locally, the initial value is the SQLRULES bind option.

• All statements / missing names — DB2 issues an existence error such as SQLCODE -204 for an object that does not exist. STD issues an authorization error such as SQLCODE -551 so that “does not exist” and “no privilege” look alike.
• ALTER TABLE — STD enforces a new check constraint immediately; if a row violates it, the constraint is not added. DB2 adds the constraint and may place the table in check-pending if existing rows might violate it. Also affects the default delete rule for referential constraints, whether Db2 implicitly creates LOB table spaces, auxiliary tables, and auxiliary indexes for added LOB columns, and whether Db2 creates an index for an added ROWID GENERATED BY DEFAULT column.
• CREATE TABLE — same default delete-rule, implicit LOB objects, and ROWID index behaviors when the table is explicitly created.
• GRANT / REVOKE — whether you can grant privileges to yourself and how privileges are revoked from authorization IDs.

1
2
3
SET CURRENT RULES = 'STD';
-- later ALTER TABLE check constraints follow SQL standard enforcement
SET CURRENT RULES = 'DB2';

Practical habits

SET these registers in a controlled place: a front-end stored procedure, a JDBC specialRegisters connection property, or a well-known setup module. Do not scatter SET CURRENT DEGREE = 'ANY' through random dynamic SQL. Document which packages rely on APPLCOMPAT versus the special register. After a function-level activate, raise package APPLCOMPAT in a planned wave; the register cannot leapfrog the bind option.

Explain It Like I'm Five

These registers are stickers on your homework folder. One sticker says “use the old spelling rules” (application compatibility). Another says “write in block letters or cursive” (encoding). Another says “you may use ten helpers or only yourself” (degree). Another says “show your work but do not actually hand in the paper” (EXPLAIN mode). The teacher (Db2) looks at the stickers before grading each new sentence you write (dynamic SQL). Printed worksheets from last year (static SQL) already have their stickers from when they were copied (BIND).

Exercises

1. Query CURRENT APPLICATION COMPATIBILITY and SYSPACKAGE.APPLCOMPAT for the package you are using. Explain why SET to a higher V12R1M5nn value would fail.
2. Set CURRENT DEGREE to 1, run a large dynamic SELECT, then set ANY and compare parallelism in EXPLAIN or accounting. Note that DPSI parallelism is a separate knob.
3. Use CURRENT EXPLAIN MODE = EXPLAIN on a dynamic UPDATE in a test system. Confirm the UPDATE did not change rows, then inspect PLAN_TABLE.
4. Demonstrate CURRENT RULES DB2 versus STD by referencing a table name that does not exist, and record the SQLCODE difference.
5. Set CURRENT PRECISION to DEC15 and DEC31 and compare a decimal division that is close to overflow. Then try D31.3 and explain the minimum divide scale.