Client Special Registers Are Special

Client special registers are very useful for understanding who requested work for database server jobs (QZDASOINIT, QSQSRVR, and QRWTSRVR)

An early iCan blog, written by Scott Forstie in October 2009, reviewed Client Special Registers.  I’m revisiting this topic since client special registers are very useful for understanding who requested work for the database server jobs (QZDASOINIT, QSQSRVR, and QRWTSRVR).

Client special registers are used to provide information about who issuing an SQL statement. In order to take advantage of the client special registers, they must be set in the requesting application.  

There are five possible values:

• Client application name
  This is the name of the application that is issuing the SQL request.
• Client program ID
  This is the name of the program that issues the SQL statement.
• Client accounting
  This is the accounting information for the SQL request.  For example, this could be the accounting code associated with the requesting user profile.
• Client user ID
  This is the user name. For example, this could be the user name of the client that originated the request.
• Client workstation name
  This could be the actual name of a workstation (PC) where a request came from, or it could be a job name. This is one way to pass the requesting job name to the database server job to link the two jobs together.

IBM i automatically sets these client special registers for a variety of interfaces, including Access Client Solution’s Run SQL Scripts, Db2 Web Query, STRSQL and others.

There are various interfaces that can be used to set the client special registers, which are listed below. Client special registers are inherited across SQL Server Mode jobs and DDM/DRDA connections.

1. SYSPROC.WLM_SET_CLIENT_INFO stored procedure.
   Call this SQL procedure prior to execution of your SQL statements.
2. Set Client Information (SQLESETI) API
3. SQL Call Level Interface (SQL CLI) – SQLSetConnectAttr()
4. ODBC – SQLSetConnectAttr()
5. JDBC – setClientInfo() connection method 

When set, client special registers are logged to the database server’s job log in SQL799C. For reference, see the blog on Prestart Job Messages.

Once you have set the client special registers, you can use these settings to better understand who is requesting work in your database servers. You can use the following interfaces to see SQL statements along with the client special registers. 

• QSYS2.ACTIVE_JOB_INFO() with DETAILED_INFO => ‘ALL’
• QSYS2.GET_JOB_INFO()
• Retrieve Job Information (QUSRJOBI) API
• SQL Performance Center
  • SQL Plan Cache Snapshots, SQL Statements
  • SQL Plan Cache, SQL Details for Jobs

---