You are currently on IBM Systems Media’s archival website. Click here to view our new website.


Bookmark and Share
RSS

Recent Posts

i Can ... Use Client Special Registers

October 9, 2009

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!

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:

  1. Set Client Information (SQLESETI) API
  2. 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)
  3. ODBC - SQLSetConnectAttr() 
    (ODBC_ATTR_INFO_ACCTSTR, ODBC_ATTR_INFO_APPLNAME, ODBC_ATTR_INFO_PROGRAMID, ODBC_ATTR_INFO_USERID, ODBC_ATTR_INFO_WRKSTNNAME connection attributes)
  4. JDBC - setClientInfo() connection method 
    (ClientAccounting, ApplicationName, ClientProgramID, ClientUser, ClientHostName JDBC 4.0 connection properties)
  5. 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

 

System i Navigator 6.1 users will observe client special values woven 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 'System i Navigator - 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)

 

Best Practice

When upgrading to IBM i 6.1, be sure to upgrade your System i Navigator clients to 6.1, to be positioned to extract the full value from the Client Special Register support.


Related Articles and Resources

SOX Auditing of STRSQL and RUNSQLSTM Commands 

IBM DB2 for i 6.1 -- Sophistication simplified 

DB2 for i SQL Reference (Version 6 Release 1) 

Set Client Information (sqleseti() API) 


Viewlet Tour

The following executables provide a visual tour of where to find the client special registers.

Linux (you many need to add execute authority to the downloaded file to run it) 

MacOS 

Windows 

  

Posted October 9, 2009| Permalink

-->