Treasury of New DB2 6.1 (V6R1) Features, Part 3: Client Special Registers

Register	Description
CURRENT CLIENT_ACCTNG	Client accounting code
CURRENT CLIENT_APPLNAME	Application name
CURRENT CLIENT_PROGRAMID	Program id
CURRENT CLIENT_USERID	User Id
CURRENT CLIENT_WRKSTNNAME	Workstation (host) name

These registers contain completely user-defined values set by an application. Their values are designed to be set in the connection string of a client application that uses a JDBC, OLE DB, CLI, or .NET data provider. They can also be set by calling a special stored procedure. Each of these registers is defined with a data type of VARCHAR(255) and returns an empty string by default.

As mentioned already, these registers are meant to provide the DB2 server with information that can be used by a developer to provide, among other things, custom audit and application security features. Until now, this client side information (such as a Windows user name or Windows workstation name) was passed to a DB2 application via stored procedure parameters or an “active job” table, which was cumbersome at best. Now, however, this information can be set in the connection string and referenced within a stored procedure or trigger without having to arrange for parameters to be passed every time the client does something to the database.

This provides a benefit for legacy applications that are being client/server enabled because the ability to check the special registers for client information negates the need to enable older programs to process an additional set of parameters. If the data in these registers is not present, then it can be assumed that the current application is not a client application and, in some cases, can use an acceptable substitute such as the CURRENT_USER special register:

/* User Client User if available, else use job's current user */
Select Case When CLIENT_USERID='' Then Current_User Else CLIENT_USERID End
  From SysIBM/SysDummy1

How Do You Set The Registers?

As mentioned earlier, the registers are set in the connection string from the IBM JDBC, OLE DB, or .NET data providers. The next few paragraphs will give brief examples of how to set the connection information in each of these environments as well as explain how to handle the ODBC environment.

Using the IBMDASQL (or IBMDA400) OLE DB Providers

Assuming you have System i Access V6R1 installed and a reference set to the ActiveX Data Objects library, here is VBA code that uses the IBMDASQL provider to set the registers and then retrieve them. For the record, the IBMDA400 provider can be substituted in this example, as it uses the same connection string keywords as shown below in bold:

Sub TestClientRegisters()

Dim cmd As ADODB.Command
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset

Set conn = New ADODB.Connection

conn.Open "Provider=IBMDASQL; Data Source=MyAS400;" & _
"Client Accounting=1234567;" & _
"Client User ID=WINUSER; Client WorkStation Name=PCNAME; " & _
"Client Program ID=AUDIT_REVIEW; Application Name=Audit Application"

Set cmd = New ADODB.Command
With cmd
    Set .ActiveConnection = conn
    .CommandText = _
    "Select * " & _
    "  From (Values(CURRENT CLIENT_ACCTNG," & _
    "  CURRENT CLIENT_APPLNAME,CURRENT CLIENT_PROGRAMID," & _
    "  CURRENT CLIENT_USERID,CURRENT CLIENT_WRKSTNNAME)) As ClientInfo
	" & _
    " (Accounting,Application,Program,UserId,Workstation)"
    Set rs = .Execute
    If Not rs.EOF Then
        Debug.Print rs!Accounting, rs!Application, rs!Program, _
                    rs!UserId, rs!Workstation
    End If
    rs.Close
End With
conn.Close
Set conn = Nothing
Set cmd = Nothing
Set rs = Nothing

End Sub

Of course, these registers should normally be set in the connection string by variables that contain information such as the desktop computer name or Web server host name, windows user name or windows application user name, etc.

Partial Java Code

Below is a snippet of Java code showing how to set the client registers when connecting to the database server. The latest version of the jt400 toolbox driver is required. The setClientInfo method of the Connection object is used to pass a name value pair (register name/register value) to set the appropriate DB2 register.

public static void main(String[] args) {

try {
    Class.forName("com.ibm.as400.access.AS400JDBCDriver");
    Connection conn=DriverManager.getConnection("jdbc:as400://MyAS400/i3;
	transaction isolation=none;driver=native;naming=system;","MyUser",
	"MyPassword");

    conn.setClientInfo("ApplicationName","LinuxApp");
    conn.setClientInfo("ClientUser","LinuxUser");
    conn.setClientInfo("ClientProgramID","ProgramName");
    conn.setClientInfo("ClientAccounting","Accounting");
    conn.setClientInfo("ClientHostname","WorkstationName");

    Statement stmt=conn.createStatement();
    ResultSet rs=stmt.executeQuery("Select * From (Values(CURRENT 
	CLIENT_APPLNAME,CURRENT CLIENT_PROGRAMID,CURRENT CLIENT_USERID,
	CURRENT CLIENT_WRKSTNNAME,CURRENT CLIENT_ACCTNG)) As ClientInfo 
	(Application,Program,UserId,Workstation,Accounting)");
     if (rs.next()) {
        System.out.println("Application:"+rs.getString(1)+
                           "   Program:"+rs.getString(2)+
                           "   Accounting:"+rs.getString(5)+
                           "   User:"+rs.getString(3)+"  Workstation:"
                           +rs.getString(4));
    }
    rs.close();

iSeries Access ODBC Driver/CLI/Legacy Applications

For the record, I could not find any information on how to set these registers in the connection string using the System i Access ODB C driver (formerly iSeries Access ODBC). Further, all my tinkering proved fruitless in this area. However, ODBC users can still make use of the client registers by calling IBM-supplied stored procedure SYSPROC.WLM_SET_CLIENT_INFO. The procedure’s parameter list is shown below.

CREATE PROCEDURE SYSPROC.WLM_SET_CLIENT_INFO ( 
	IN CLIENT_USERID     VARCHAR(255),
	IN CLIENT_WRKSTNNAME VARCHAR(255),
	IN CLIENT_APPLNAME   VARCHAR(255),
	IN CLIENT_ACCTSTR    VARCHAR(255),
	IN CLIENT_PROGRAMID  VARCHAR(255))
LANGUAGE C
SPECIFIC SYSPROC.WLM_SET_CLIENT_INFO
NOT DETERMINISTIC
READS SQL DATA
CALLED ON NULL INPUT
EXTERNAL NAME 'QSYS/QSQAPIS(SETCLIENT)'
PARAMETER STYLE DB2SQL;

If you have a third-party ODBC application that connects to your database, you’re currently out of luck in setting these registers since these values cannot be provided in the connection string (unless you get fancy by using a database user exit to set these values).

For developers using the CLI environment, the WLM_SET_CLIENT_INFO stored procedure or the SQLSetConnectAttr function can be used to set these registers.

Finally, even legacy programs can make use of these registers if there is a need to have them set in all possible application environments (including client/server jobs and traditional i OS jobs). Legacy programs capable of using embedded SQL can call the aforementioned stored procedure. The Set Client Information (SQLESETI) API can also be used to set these registers within high level language programs (C, RPG, COBOL) without using embedded SQL. The documentation for this API is particularly useful in that it demonstrates how to use the API with a sample C program.

.NET Example Using IBM’s Managed Provider

Finally, here is a .NET C# example that uses the IBM .NET managed provider. A minimum version of System i Access V6R1 is required. Again the connection string name/value pairs intuitively map to the corresponding DB2 special registers. In this sample, I simply read the connection properties instead of submitting a query against the special registers:

using System;
using IBM.Data.DB2.iSeries;

namespace DB2Test
{
    class Program
    {
        static void Main(string[] args)
        {
            string connDB2 = "DataSource=MyAS400;
			ApplicationName=FHGDEMO;" +
                             "ClientAccounting=User-Defn;
ClientProgramId=MyProgram;" + 
                             "ClientUserId=Me;
ClientWorkstation=MyWS";
            iDB2Connection conn = new iDB2Connection(connDB2);
            conn.Open();
            System.Console.WriteLine( 
            conn.ApplicationName + "t" +
            conn.ClientAccounting + "t" +
            conn.ClientProgramID + "t" +
            conn.ClientUserID + "t" +
            conn.ClientWorkstation + "t");
            Console.ReadLine();
        }
    }
}

Incidentally, the IBMDASQL and .NET providers automatically default the application name. I ran the VBA code within Microsoft Word and unless I explicitly set the register value, the application name register returned a default of WINWORD.EXE. Likewise, the .NET program (called DB2Test) returned a default value of DB2Test for the application name.

How you set them depends largely on the type of application. For a client/server application you can call the appropriate platform specific API to return the current user name and workstation host name, for example. For a Web application, however, it may be a little trickier to determine who the user is if your Web site does not allow anonymous connections. In this case the code may simply need to pass the Web server’s information or may need to rely on a sophisticated approach to determine what user is logged in (either by application security or, in the case of an ASP.NET site using integrated windows authentication, etc.)

When recording the values in these registers for auditing purposes, it is a good idea for your organization to set standards for how these registers will be used and what their maximum size should be. By default each of these can hold 255 characters but most developers probably won’t use that much information from each register. By pre-defining how they’ll be used, intelligent decisions can be made on how to define columns to hold their values within a table.

Summary

I believe these new registers will be useful to developers for the following reasons:

• Legacy applications can use them with only minor modifications.
• They provide powerful auditing features for client/server applications.
• They can be used to pass all kinds of information to the server for custom processing or security. For example, you can pass a special token value via the client accounting register that the server application can verify is an acceptable value before executing any code.
• They provide compatibility with other DB2 versions.

Gone are the days of the standalone AS/400 that processed its entire workload within the confines and safety of a single box. Here to stay are the days of heterogeneous, multi-platform client/server or “distributed” computing. Thankfully, IBM has made it easier to manage and track these remote applications via the special registers.

Michael Sansoterra is a programmer/analyst for i3 Business Solutions, an IT services firm based in Grand Rapids, Michigan. Send your questions or comments for Mike to Ted Holt via the IT Jungle Contact page.

RELATED STORIES

Treasury of New DB2 6.1 Features, Part 1: Query Enhancements

Treasury of New DB2 6.1 Features, Part 2: Grouping Sets and Super Groups

---

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot