DB2 schema, path, and authorization registers

Map of the registers

These registers affect dynamic SQL for object names and most authorization defaults. Static SQL uses bind options (QUALIFIER, PATH, PKLIST) and package ownership. CURRENT PACKAGE PATH and CURRENT PACKAGESET are the important exception: they apply to package lookup for both static and dynamic statements.

CURRENT SQLID

CURRENT SQLID specifies the SQL authorization ID of the process. Data type VARCHAR(128).

IBM lists these uses:

• Authorization checking on dynamically prepared CREATE, GRANT, and REVOKE
• Owner of a table space, database, storage group, or synonym created by a dynamically issued CREATE

The value changes when you execute SET CURRENT SQLID, or when a SIGNON or re-SIGNON arrives from a CICS transaction subtask or an IMS independent region.

Initial value can come from the connection or sign-on exit. If the exit does not supply one, the initial value is the primary authorization ID. It lasts until the Db2 connection ends or, in a trusted connection, the user is switched.

You may SET CURRENT SQLID to the primary ID or to a secondary authorization ID of the process (often a RACF group). CURRENT SQLID cannot be a role.

1
2
3
SET CURRENT SQLID = SESSION_USER;
SET CURRENT SQLID = 'GROUP34';
SELECT CURRENT SQLID FROM SYSIBM.SYSDUMMY1;

If SET CURRENT SQLID runs in a stored-procedure or UDF package whose DYNAMICRULES behaviour is not run behaviour, the statement does not change the authorization ID used for dynamic SQL in that package. DYNAMICRULES wins.

CURRENT SCHEMA

CURRENT SCHEMA (also CURRENT_SCHEMA) is the schema name used to qualify unqualified database object references in dynamically prepared SQL. Data type VARCHAR(128). The stored value is a single identifier without delimiters.

Initial value is CURRENT SQLID at connect time. If the connection is a trusted connection with a role as the object owner and qualifier, the initial schema is that role name.

CURRENT SCHEMA stays equal to CURRENT SQLID until you issue SET SCHEMA. After that, the two registers are independent. Changing SQLID later does not drag SCHEMA along; you must SET SCHEMA again if you want them aligned.

1
2
3
SET SCHEMA = 'D123';
-- Unqualified EMP now means D123.EMP in dynamic SQL
SELECT COUNT(*) FROM EMP;

When CURRENT SCHEMA and CURRENT SQLID differ, qualifier versus owner on CREATE depends on the object type. For tables, views, indexes, aliases, and several other objects, an unqualified CREATE uses SCHEMA as qualifier and SQLID as owner. For triggers, sequences, procedures, user-defined functions, and user-defined types, an unqualified name still takes SCHEMA as qualifier and SQLID as owner, but a qualified name keeps SQLID as owner while the explicit qualifier names the schema. Read IBM’s tables before you split the two registers in production DDL.

Static CREATE is not driven by these registers. Bind QUALIFIER and the package owner apply instead.

CURRENT PATH

CURRENT PATH (also CURRENT_PATH, and SET with SET PATH) is the SQL path for resolving unqualified distinct type names, function names, and procedure names specified as host variables on CALL. Data type VARCHAR(2048). The value is a list of schema names, each delimited, separated by commas. Delimiters and commas count toward the 2048 limit.

Typical initial value:

1
"SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","current-sqlid"

In a trusted connection with role as owner and qualifier, the last name is the role name instead of CURRENT SQLID. SQL routines and triggers can inherit a SET PATH from the invoker, or use the SQL PATH routine/trigger option.

If CURRENT SQLID changes after PATH was initialized, PATH does not automatically follow. After a later COMMIT, if you never issued SET PATH, PATH is re-initialized and can pick up the new SQLID. Issue SET PATH at the start of an application if you need portable, stable resolution.

1
2
SET PATH = SMITH, SYSTEM PATH;
SELECT CURRENT PATH FROM SYSIBM.SYSDUMMY1;

CURRENT PACKAGE PATH

CURRENT PACKAGE PATH identifies the path used to resolve packages for SQL statements. It applies to both static and dynamic statements. Data type VARCHAR(4096).

The value is an empty or blank string, or a list of collection IDs in double quotes separated by commas. The initial value is empty until the process issues SET CURRENT PACKAGE PATH.

1
2
3
SET CURRENT PACKAGE PATH = SQLJ1, SQLJ2, DB2JAVA;
SET :HVPKLIST = CURRENT PACKAGE PATH;
-- :HVPKLIST becomes "SQLJ1","SQLJ2","DB2JAVA"

When both PACKAGE PATH and PACKAGESET are set, PACKAGE PATH takes priority. In a distributed environment, the PACKAGE PATH at the remote server takes precedence over the requester’s PACKAGE PATH.

CURRENT PACKAGESET

CURRENT PACKAGESET is an empty string, blanks, or a single collection ID for the package that will execute SQL. Data type VARCHAR(128). Initial value is empty until SET CURRENT PACKAGESET.

1
EXEC SQL SET CURRENT PACKAGESET = 'ALPHA';

Classic pattern: before transferring control to another program bound in a different collection, SET CURRENT PACKAGESET so Db2 finds that program’s DBRM package. If you already maintain a list in CURRENT PACKAGE PATH, that list wins and PACKAGESET is not consulted.

CURRENT PACKAGESET is processed locally in distributed applications (along with CURRENT SERVER and CURRENT APPLICATION ENCODING SCHEME). You cannot change it through the generic JDBC setSpecialRegisters interface; use SQL SET CURRENT PACKAGESET.

CURRENT SERVER

CURRENT SERVER (also CURRENT_SERVER) is the location name of the current server. Data type CHAR(16), padded with blanks.

Initial value comes from the CURRENTSERVER bind option if specified; otherwise the local Db2 location name. A successful CONNECT changes it. The value is blanks when the process is unconnected or the local subsystem has no location name. There is no SET CURRENT SERVER.

1
EXEC SQL SET :CS = CURRENT SERVER;

SESSION_USER and USER

SESSION_USER specifies the primary authorization ID of the process. Data type VARCHAR(128). USER is a synonym; SESSION_USER is the preferred spelling.

If the statement runs at a remote Db2 and the primary ID was translated inbound, SESSION_USER / USER show the translated ID.

1
2
3
4
SELECT * FROM SYSIBM.SYSTABLES
 WHERE CREATOR = SESSION_USER;
 
SET CURRENT SQLID = SESSION_USER;

You cannot SET USER or SESSION_USER. They are not CURRENT SQLID. A developer can SET CURRENT SQLID to a group while SESSION_USER stays the logged-on person. Audit columns that should record “who was signed on” belong on SESSION_USER, not SQLID.

SYSTEM_USER on z/OS versus other platforms

The SQL standard and Db2 LUW expose SYSTEM_USER as the authorization ID that established the session (the connecting user), while SESSION_USER can be changed with SET SESSION AUTHORIZATION on those platforms.

Db2 for z/OS does not list SYSTEM_USER among special registers in the SQL Reference. z/OS uses SESSION_USER / USER for the primary authorization ID and SET CURRENT SQLID (plus trusted contexts and roles) instead of SET SESSION AUTHORIZATION. If you copy LUW SQL that references SYSTEM_USER onto z/OS, expect a name-resolution error unless you rewrite it to SESSION_USER or USER.

Putting it together

• SESSION_USER — who the process is (primary ID)
• CURRENT SQLID — which ID dynamic GRANT/CREATE is using
• CURRENT SCHEMA — which schema prefixes unqualified tables in dynamic SQL
• CURRENT PATH — which schemas are searched for unqualified functions
• CURRENT PACKAGE PATH / PACKAGESET — which collections are searched for packages
• CURRENT SERVER — which location you are connected to

When debugging −204 (object not found) or −440 (function not found), SELECT these registers before you rewrite the statement. Many “missing table” bugs are a SCHEMA or PATH that still points at yesterday’s SQLID.

Explain It Like I'm Five

SESSION_USER is the name on your library card. CURRENT SQLID is the stamp you are using today — maybe your card, maybe the “science club” stamp you are allowed to borrow. CURRENT SCHEMA is the default shelf label so “EMP” means “HR.EMP” without writing HR. CURRENT PATH is the list of rooms you search when someone shouts a function name without saying which room. PACKAGE PATH is the list of boxes of punch-cards (packages) to try in order. CURRENT SERVER is the building you are standing in after CONNECT. SYSTEM_USER is a name other Db2 products use for “the person who walked in the door”; on z/OS that idea is SESSION_USER.

Exercises

1. Select SESSION_USER, CURRENT SQLID, CURRENT SCHEMA, CURRENT PATH, and CURRENT SERVER. Record which pairs are equal.
2. SET SCHEMA to a schema you may use, then SET CURRENT SQLID to a secondary ID if your shop allows it. Select both registers. Create (or explain) an unqualified table name and predict qualifier versus owner.
3. SET PATH so your personal schema is searched before SYSTEM PATH. Call or SELECT an unqualified function and explain which schema Db2 picked.
4. Write SET CURRENT PACKAGE PATH for two collections you use in SQLJ/JDBC and SELECT the register to see the quoted list form.
5. Search your shop’s code for SYSTEM_USER. If you find it in LUW samples, rewrite the z/OS version with SESSION_USER.