DB2 client special registers

Why client information exists

Authorization still uses RACF, primary and secondary IDs, and CURRENT SQLID. Client registers do not replace that. They add descriptive strings that drivers, middleware, and attachment facilities can set so that:

• SMF accounting and performance traces can group work by application or workstation
• Resource Limit Facility (RLF) and profile tables can match a client user or application name
• Your own SQL can filter with WHERE APPL_NAME = CURRENT CLIENT_APPLNAME
• Operations can find one bad connection among thousands of DDF threads

On Db2 for z/OS you typically read these registers in SQL and write them through APIs. They are not on the list of special registers changed by ordinary SQL SET statements (SET CURRENT SQLID, SET SCHEMA, and friends).

The application compatibility (APPLCOMPAT) of the package can change the length and blank padding of ACCTNG, APPLNAME, USERID, and WRKSTNNAME when they are returned. After V11R1, values are not padded to the old short maximums and trailing blanks are removed.

How values get into the registers

IBM documents these application programming interfaces for changing client information:

• Set Client Information (sqleseti) — CLI / embedded with SQLE_CLIENT_INFO_* fields (APPLNAME, USERID, WRKSTNNAME, accounting, program ID)
• SQLSetConnectAttr (ODBC)
• java.sql.Connection.setClientInfo (JDBC), plus IBM Data Server Driver helpers such as setDB2ClientAccountingInformation
• RRSAF DSNRLI SIGNON, AUTH SIGNON, CONTEXT SIGNON, or SET_CLIENT_ID
• WLM_SET_CLIENT_INFO stored procedure — sets client information for the current connection at the Db2 for z/OS server

If an API is never used, several registers return an empty string when selected. Others have attachment-specific defaults (especially APPLNAME and WRKSTNNAME). Explicit API values overwrite those defaults.

1
2
3
4
5
6
SET :ACCT_STRING = CURRENT CLIENT_ACCTNG;
SET :WS_NAME = CURRENT CLIENT_WRKSTNNAME;
 
SELECT DEPT
  FROM DEPT_APPL_MAP
 WHERE APPL_NAME = CURRENT CLIENT_APPLNAME;

CURRENT CLIENT_ACCTNG

CURRENT CLIENT_ACCTNG (synonym CLIENT ACCTNG) holds the accounting string from client information for the connection. Data type VARCHAR(255).

The accounting string is obtained first from the string set by SET_CLIENT_ID, AUTH SIGNON, or sqleseti. If that string was never set, Db2 can fall back to the RRSAF accounting token.

CICS: the CICS attachment facility normally does not pass an accounting string unless the origin data for the CICS task contains adapter data. When adapter data is present, CICS passes it to Db2 preceded by an eye-catcher such as CICS_ORIGIN_DATA:ADAPTER_DATA_1_2_3: followed by the adapter data.

Empty string is returned if no API (and no CICS origin path) supplied accounting information. Do not assume ACCTNG equals the RACF user ID.

CURRENT CLIENT_APPLNAME

CURRENT CLIENT_APPLNAME (synonym CLIENT APPLNAME) is the application name. Data type VARCHAR(255).

Defaults depend on how you connected:

• Remote application driver — name the driver supplies (JDBC DatabaseMetaData.getClientInfoProperties documents IBM driver defaults)
• TSO foreground (SPUFI-style) — TSO logon user ID
• TSO background (DSNTEP2-style) — job name
• RRSAF — correlation ID provided on the DSNRLI SIGNON call
• Call attachment facility — job name
• CICS — if origin adapter data exists, an eye-catcher plus adapter ID (examples: IBM_zOS_Connect_CICS_SP, IBM WebSphere MQ for z/OS). If not, Db2 uses the first 8 bytes of the 12-byte CICS correlation ID
• IMS — 8-byte PSB name or program name

When the client application name is set explicitly, it overwrites that default.

You can start a statistics class 10 trace (IFCID 411) to monitor remote DRDA applications by CURRENT CLIENT_APPLNAME. Monitoring is limited to 6000 unique application name values. If that limit is exceeded, Db2 issues DSNL030I with reason code 00D3105D and stops collecting statistics for new names until DDF is restarted. IBM recommends CLIENT_CORR_TOKEN to identify individual connections instead of stuffing uniqueness into APPLNAME.

CURRENT CLIENT_CORR_TOKEN

CURRENT CLIENT_CORR_TOKEN holds the client correlation token. Data type VARCHAR(255).

Set it with SQLE_CLIENT_INFO_PROGRAMID (sqleseti), JDBC setClientInfo, or RRSAF SIGNON / AUTH SIGNON / CONTEXT SIGNON / SET_CLIENT_ID. If nothing sets it, the value defaults to a correlation identifier from the client driver (for example an application identifier). If the client system provides no correlation identifier, Db2 generates an LUWID (Logical Unit of Work ID) and that becomes the token.

1
2
3
SELECT DEPT
  FROM DEPT_CORR_TOKEN_MAP
 WHERE CORR_TOKEN_NAME = CURRENT CLIENT_CORR_TOKEN;

Use this register when you need a per-connection handle in your own tables, in traces, and in operator displays. Keep APPLNAME as a stable product or service name (PAYROLL-BATCH, WEB-API) and put the unique request id in CORR_TOKEN.

CURRENT CLIENT_USERID

CURRENT CLIENT_USERID (synonym CLIENT USERID) is the client user ID string. Data type VARCHAR(255), but if the API sets more than 128 bytes, Db2 truncates to 128.

The default is the primary authorization ID used to establish the connection. An explicit client user ID overwrites that default. Middleware often sets this to an end-user id that is not the same as the technical ID that passed RACF.

That overwrite is still only a label. Privileges continue to follow the Db2 authorization IDs and CURRENT SQLID. A forged client user ID does not make you SYSADM.

Statistics class 11 (IFCID 412) can monitor remote DRDA users by this register, also capped at 6000 unique values (DSNL030I reason 00D3105E). Again, prefer CORR_TOKEN for one-row-per-connection identity.

1
2
3
SELECT DEPT
  FROM DEPT_USERID_MAP
 WHERE USER_ID = CURRENT CLIENT_USERID;

CURRENT CLIENT_WRKSTNNAME

CURRENT CLIENT_WRKSTNNAME (synonym CLIENT WRKSTNNAME) is the workstation name. Data type VARCHAR(255).

Defaults:

• Db2 for z/OS requester — client hostname
• Remote application driver — hostname where the request is submitted
• TSO foreground — the string TSO
• TSO background — the string BATCH
• RRSAF — RRSAF
• Call attachment — DB2CALL
• CICS — CICS region name
• IMS — IMS region ID

Explicit API values overwrite the default. If no API is used, a SELECT can still show a default for some attachments; for others the SQL Reference notes an empty string when the register was never set. Always SELECT it in your environment rather than assuming the textbook default.

APPLCOMPAT and old lengths

Before Db2 11, client strings were shorter (examples: accounting 200 bytes, application name 32, user ID 16, workstation 18). With APPLCOMPAT V11R1 or higher, Db2 uses the longer VARCHAR(255) / 128 sizes, does not pad to the old maximum, and strips trailing blanks. Packages at older compatibility can still see truncated values. IFCID 376 function identifiers 1104 (ACCTNG), 1105 (APPLNAME), 1106 (USERID), and 1107 (WRKSTNNAME) flag applications affected by the length change.

RLF search also started using the full length of these registers at V11R1. A limit that used to match a truncated 16-byte CLIENT_USERID might miss the longer value.

Practical habits

• Set APPLNAME to a stable service name, USERID to the end user when you have one, WRKSTNNAME to host or channel, ACCTNG to chargeback tokens your shop already uses, and CORR_TOKEN to a unique request id.
• Do not overload APPLNAME with a unique UUID for every REST call if you also enable IFCID 411 — you will hit the 6000-name cap.
• Remember these strings are inherited into stored procedures; the routine cannot SET them with SQL, and on return the caller’s values are restored anyway.
• Client user ID is not CURRENT SQLID. GRANT still looks at authorization IDs.

Explain It Like I'm Five

When you walk into a library, the librarian already knows your library card (that is USER / RACF). Client registers are the extra stickers you can put on your shirt: “I came from the homework club” (APPLNAME), “I sit at table 4” (WRKSTNNAME), “my teacher’s charge code is 7B” (ACCTNG), “ticket number 9921” (CORR_TOKEN), and “please call me Sam even though the card says SAMUEL01” (CLIENT_USERID). The stickers help the librarian sort the noisy crowd. They do not magically give you keys to the rare-book room.

Exercises

1. Select all five client registers from SYSIBM.SYSDUMMY1 in SPUFI and in a JDBC tool. Compare defaults.
2. For a CICS transaction, predict WRKSTNNAME and APPLNAME from the defaults above, then confirm with your shop’s accounting trace or DISPLAY THREAD.
3. Explain why setting CURRENT CLIENT_USERID to a colleague’s id would not let you DROP their tables.
4. Design a naming scheme: one APPLNAME per microservice, CORR_TOKEN = request UUID. Write the SELECT you would use to stamp an audit table.
5. Find whether your JDBC URL or WebSphere data source sets clientInfo / specialRegisters today.