DB2 special registers overview

What special registers are

IBM’s SQL Reference defines a special register as a storage area defined for an application process by Db2 and used to store information that can be referenced in SQL statements. A reference to a special register is a reference to a value provided by the current server. If the value is a string, its CCSID is a default CCSID of that server.

Think of registers as named sticky notes taped to your thread, not as subsystem-wide switches. Two CICS transactions, two TSO users, or two DDF connections each have their own CURRENT SQLID, CURRENT SCHEMA, and CURRENT PATH. Changing yours does not change anyone else’s.

Registers are not host variables. You do not declare them in COBOL. You write the register name in SQL the same way you write a column name or a literal. Db2 substitutes the current value when the statement runs at the server (with a few local exceptions listed later).

Families you will meet first

Later pages in this section walk through client information, schema and authorization, datetime and temporal, and accelerator / materialized-query-table registers. A companion page covers application compatibility, encoding scheme, degree, explain mode, and other “behaviour” registers.

How to reference them

Put the register name anywhere a compatible expression is allowed: SELECT list, WHERE, VALUES, INSERT, default expressions (where the product allows it), and SET assignments into host variables.

1
2
3
4
5
6
7
SELECT CURRENT SQLID, CURRENT SCHEMA, CURRENT DATE,
       USER, CURRENT SERVER
  FROM SYSIBM.SYSDUMMY1;
 
VALUES CURRENT TIMESTAMP;
 
SET :HV-SQLID = CURRENT SQLID;

Underscore spellings match the SQL standard for several registers. These pairs are equivalent on Db2 for z/OS:

• CURRENT DATE and CURRENT_DATE
• CURRENT TIME and CURRENT_TIME
• CURRENT TIMESTAMP and CURRENT_TIMESTAMP
• CURRENT SCHEMA and CURRENT_SCHEMA
• CURRENT PATH and CURRENT_PATH
• CURRENT SERVER and CURRENT_SERVER
• CURRENT TIME ZONE, CURRENT TIMEZONE, and CURRENT_TIMEZONE

USER is a synonym for SESSION_USER. Prefer SESSION_USER in new SQL because that is the preferred spelling in the SQL Reference.

Reading versus setting

Reading is always the register name in an expression. Changing a register, when it is allowed, uses a dedicated SET statement — not an assignment like SET CURRENT DATE = …, which is invalid.

1
2
3
4
SET CURRENT SQLID = SESSION_USER;
SET SCHEMA = 'HR';
SET PATH = HR, SYSTEM PATH;
SET CURRENT QUERY ACCELERATION NONE;

Registers you cannot SET with SQL include CURRENT DATE, CURRENT TIME, CURRENT TIMESTAMP, CURRENT TIME ZONE, CURRENT MEMBER, USER / SESSION_USER, and the client-info family (those last ones are filled by attachment APIs, JDBC setClientInfo, RRSAF SIGNON, or WLM_SET_CLIENT_INFO). CURRENT SERVER changes when a CONNECT statement succeeds, not via SET CURRENT SERVER.

Where the value is evaluated

In distributed applications, CURRENT APPLICATION ENCODING SCHEME, CURRENT SERVER, and CURRENT PACKAGESET are processed locally. All other special registers are processed at the server. That matters when you CONNECT to a remote location: CURRENT DATE is the remote server’s clock, not the requester’s TSO session clock.

Session versus application scope

“Session” here means the application process — the Db2 thread for your TSO session, CICS transaction, IMS region, RRSAF connection, or DDF connection. Register values last for that process until you SET them, CONNECT (for CURRENT SERVER), the connection ends, or (for some registers that were never explicitly set) a commit re-initializes them.

They are not a single global value for the Db2 subsystem, and they are not automatically shared across different connections from the same human. A COBOL batch job and a SPUFI session for the same TSO ID are different processes.

Commit and rollback

• ROLLBACK has no effect on special registers. If you SET CURRENT SCHEMA and then roll back an INSERT, the schema register stays at the SET value.
• COMMIT might re-initialize a register that was never explicitly SET. PATH is the textbook case: if you never issued SET PATH, commit re-initializes PATH and can take the current CURRENT SQLID into account. After you issue SET PATH, that explicit path survives commit.

Plan for this in long-running programs that mix SET CURRENT SQLID with dynamic SQL and commit often: either SET PATH after changing SQLID, or SET PATH once at startup so commit does not surprise you.

Nested routines: inherit versus default

You can use all special registers inside a stored procedure or user-defined function. You can SET only the ones that have SET statements. After the routine returns, Db2 restores every special register to the values from before the CALL. The caller never keeps a SET that happened inside the routine.

CREATE PROCEDURE / CREATE FUNCTION options control the starting values inside the routine:

• INHERIT SPECIAL REGISTERS — many registers start with the invoker’s values (CURRENT SCHEMA, CURRENT PATH, CURRENT SQLID, acceleration registers, and others, per the SQL Reference table).
• DEFAULT SPECIAL REGISTERS — many registers start from bind options or subsystem defaults instead of the invoker. Datetime registers still get a new clock reading per statement in the routine. Temporal registers such as CURRENT TEMPORAL SYSTEM_TIME start as null under DEFAULT.

Client-info registers are inherited from the invoking application and are not SET with SQL inside the routine. CURRENT DATE / TIME / TIMESTAMP are not inherited as a frozen snapshot for ordinary routines: each SQL statement in the routine takes a new clock reading — unless the routine runs under a trigger, in which case datetime registers can follow the triggering statement’s timestamp.

Triggers

If a datetime special register is used in a function or procedure that sits in the scope of a trigger, Db2 uses the timestamp of the triggering SQL statement. That keeps BEFORE and AFTER trigger actions on the same logical clock as the INSERT or UPDATE that fired them.

Static SQL versus dynamic SQL

Several registers exist mainly to steer dynamic SQL: CURRENT SCHEMA, CURRENT SQLID (for many privilege and ownership checks), CURRENT PATH, CURRENT QUERY ACCELERATION, CURRENT REFRESH AGE, CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION. Static SQL uses bind options instead (QUALIFIER, PATH, QUERYACCELERATION, DYNAMICRULES, and so on). CURRENT PACKAGE PATH and CURRENT PACKAGESET are notable exceptions: they affect package resolution for both static and dynamic statements.

DYNAMICRULES on a package can also override how SET CURRENT SQLID behaves inside a stored procedure: if the package is not run-time behaviour, SET CURRENT SQLID may not change the authorization ID used for dynamic SQL in that package.

Profiles, drivers, and bind options

Besides SET in your program, shops often set registers from:

• DSN_PROFILE_ATTRIBUTES with keyword SPECIAL_REGISTER (especially for DDF). You list a SET statement as the attribute. Client interfaces treat assigned values as literal strings — for CURRENT REFRESH AGE use ANY, not the 14-digit duration.
• JDBC / CLI — DB2DataSource.setSpecialRegisters, URL specialRegisters= properties, and matching ODBC attributes.
• Bind options that seed the initial value when the package runs (APPLCOMPAT, QUERYACCELERATION, GETACCELARCHIVE, OPTHINT, PATH, QUALIFIER).
• Subsystem parameters (ZPARMs) that supply the default when nothing else has been set (QUERY_ACCELERATION, GET_ACCEL_ARCHIVE, MAINTYPE, and others).

Precedence is usually ZPARM (lowest), then bind option if specified, then an explicit SET in the application (highest). Always check the register’s own page in the SQL Reference when you need the exact stack.

Practical habits

• SELECT the registers at the start of a debugging session so you know SQLID, SCHEMA, PATH, and APPLCOMPAT before you chase −204 or −440.
• Do not use CURRENT TIMESTAMP as a unique key for every row of one INSERT. One statement shares one clock reading. Use a sequence, identity column, or GENERATE_UNIQUE when uniqueness matters.
• Qualify names when you can. Registers are a defaulting mechanism; explicit schema names survive SET SCHEMA surprises.
• Match SET to the environment. A SET CURRENT SQLID that works in SPUFI may be ignored under DYNAMICRULES(BIND) in a stored-procedure package.

Explain It Like I'm Five

Imagine each program that talks to Db2 wears a backpack. Inside the backpack are labelled cards: “today’s date”, “my name”, “which toy box to look in first” (schema), and “which extra helpers to search” (path). When SQL says CURRENT DATE, Db2 peeks at the date card in that backpack — not a giant sign on the machine room wall that everyone shares. You can rewrite some cards with SET. You cannot rewrite the date card; the clock writes that one. If you hand the backpack to a helper procedure, the helper might copy your cards or start with a fresh set, but when the helper is done, your backpack looks exactly like it did before you called them.

Exercises

1. In SPUFI or your shop’s SQL tool, select CURRENT SQLID, CURRENT SCHEMA, CURRENT PATH, CURRENT DATE, and USER from SYSIBM.SYSDUMMY1. Note which values match your TSO ID.
2. Issue SET SCHEMA to a schema you are allowed to use, SELECT CURRENT SCHEMA and CURRENT SQLID, then ROLLBACK and select them again. Did SCHEMA revert?
3. Write one SELECT that lists CURRENT DATE, CURRENT TIME, and CURRENT TIMESTAMP. Explain why the date and the date portion of the timestamp should agree.
4. List three special registers that SQL can SET and three that it cannot. Check your list against the SET statement chapter of the SQL Reference.
5. Ask whether your shop uses profile tables or JDBC specialRegisters= to set CURRENT QUERY ACCELERATION without changing application source.