Client special registers are a database-specific resource that can be optionally used by application providers to imprint identity or any other useful character-based detail for an application environment. Using client special registers doesn’t affect or impact the execution of SQL statements. Db2 for IBM i users can see the client special register values when using Db2 for i tools. When an application or client places meaningful values in the registers, database analysts and database administrators will have a better awareness of the application context.
IBM i 6.1 includes Db2 for i support of client special registers. There are five registers in all and they can be used to brand identity to SQL applications and connections. The register values surface to the user in many places. Through the instrumentation of meaningful, descriptive register values, an application provider will observe improved serviceability and accountability.
The register values are changed using any of the following interfaces. In general, its best to assign the register values during start-up application processing. The assigned values will be inherited across SQL Server mode jobs and across DRDA connections. The registers are different from other SQL special registers in that you aren’t allowed to use the SQL SET statement to change the register values. Instead, you need to use one of the following interfaces:
- Set Client Information (SQLESETI) API
- SQL Call Level Interface (SQL CLI) – SQLSetConnectAttr()
(SQL_ATTR_INFO_ACCTSTR, SQL_ATTR_INFO_APPLNAME,
SQL_ATTR_INFO_PROGRAMID, SQL_ATTR_INFO_USERID,
SQL_ATTR_INFO_WRKSTNNAME connection attributes)
- ODBC – SQLSetConnectAttr()
(ODBC_ATTR_INFO_ACCTSTR, ODBC_ATTR_INFO_APPLNAME,
ODBC_ATTR_INFO_PROGRAMID, ODBC_ATTR_INFO_USERID,
ODBC_ATTR_INFO_WRKSTNNAME connection attributes)
- JDBC – setClientInfo() connection method
(ClientAccounting, ApplicationName, ClientProgramID, ClientUser,
ClientHostName JDBC 4.0 connection properties)
- SYSPROC.WLM_SET_CLIENT_INFO procedure
Note: The setClientInfo() method is part of the JDBC 4.0 standard. If the application code executes on many databases, beginning to use this method for added value on IBM i won’t cause problems for the application when running outside of Db2 for i, as long as those databases support JDBC 4.0.
Similar to other special registers, the client special register values can be queried using SQL. The register values also appear in Database Monitor records and within Database Server joblogs.
| Register Name | SQL Special Register Name | Datatype | Database Monitor Column (where QQRID=1000) |
| Client Application Name | CURRENT CLIENT_APPLNAME | VARCHAR(255) | QVC3001 |
| Client Accounting | CURRENT CLIENT_ACCTNG | VARCHAR(255) | QVC3005 |
| Client Program ID | CURRENT CLIENT_PROGRAMID | VARCHAR(255) | QVC3006 |
| Client User ID | CURRENT CLIENT_USERID | VARCHAR(255) | QVC3002 |
| Client Workstation | CURRENT CLIENT_WRKSTNNAME | VARCHAR(255) | QVC3003 |
Navigator for i as well as Access Client Solutions SQL Performance Center externalize client special values through the many database features. SQL Details for Jobs, Show Plan Cache Statements, Analyze SQL Performance Monitors and Database Health Center display the register values. IBM clients utilize default values for the client special registers. For example, Run SQL Scripts usage has Client Application Name set to ‘Access Client Solutions – Run SQL Scripts’. Since these values are defaults, any user specified values will take precedence.
Practical Application
The Start Database Monitor (STRDBMON) command includes the capability to pre-filter database monitor records based upon a client special register value. The Interactive SQL (STRSQL) command uses a default value of ‘STRSQL’ for the Client Program ID. The following command could be used to collect a single database monitor output file of all the SQL executed from within STRSQL. For installations that “lock down” the use of STRSQL, they could use client special register support to review and archive all activity in a seldom-used interface.
STRDBMON OUTFILE(QGPL/STRSQLMON1) OUTMBR(*FIRST *REPLACE) JOB(*ALL/*ALL/*ALL) TYPE(*DETAIL) COMMENT('FTRCLTPGM(STRSQL)')
< … later … >
ENDDBMON JOB(*ALL/*ALL/*ALL)
Related Articles and Resources
SOX Auditing of STRSQL and RUNSQLSTM Commands
Set Client Information (sqleseti() API)
This week’s blog post on client special registers was written by Scott Forstie. Scott is a senior software engineer at IBM, and he’s the SQL development leader for Db2 for IBM i in Rochester, Minn. Before working on Db2, he worked on UNIX enablement for the AS/400 and S/390 systems. Thanks, Scott!
This blog post was edited for currency on January 26, 2020.
This blog post was originally published on IBMSystemsMag.com and is reproduced here by permission of IBM Systems Media.