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

Bookmark and Share

Recent Posts

Client Special Registers are Special

February 12, 2019

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. 

Posted February 12, 2019| Permalink