• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Treasury of New DB2 6.1 (V6R1) Features, Part 3: Client Special Registers

    March 18, 2009 Michael Sansoterra

    This article briefly explores the new client special registers that are now available in V6R1 and how they benefit client/server development. In case you missed them, Part 1 of this series discussed several query enhancements and explained how IBM is packing more functionality into a single statement. Part 2 of this series discussed the concept of super groups and grouping sets, which are very useful features that allow developers to add various aggregations to a result set.

    Now back to the topic at hand. Since so much new DB2 development is client/server based, IBM created a useful set of special registers that allow developers to track information about who is connecting to the database. The new special registers are as follows:

    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

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    WorksRight Software

    Do you need area code information?
    Do you need ZIP Code information?
    Do you need ZIP+4 information?
    Do you need city name information?
    Do you need county information?
    Do you need a nearest dealer locator system?

    We can HELP! We have affordable AS/400 software and data to do all of the above. Whether you need a simple city name retrieval system or a sophisticated CASS postal coding system, we have it for you!

    The ZIP/CITY system is based on 5-digit ZIP Codes. You can retrieve city names, state names, county names, area codes, time zones, latitude, longitude, and more just by knowing the ZIP Code. We supply information on all the latest area code changes. A nearest dealer locator function is also included. ZIP/CITY includes software, data, monthly updates, and unlimited support. The cost is $495 per year.

    PER/ZIP4 is a sophisticated CASS certified postal coding system for assigning ZIP Codes, ZIP+4, carrier route, and delivery point codes. PER/ZIP4 also provides county names and FIPS codes. PER/ZIP4 can be used interactively, in batch, and with callable programs. PER/ZIP4 includes software, data, monthly updates, and unlimited support. The cost is $3,900 for the first year, and $1,950 for renewal.

    Just call us and we’ll arrange for 30 days FREE use of either ZIP/CITY or PER/ZIP4.

    WorksRight Software, Inc.
    Phone: 601-856-8337
    Fax: 601-856-9432
    Email: software@worksright.com
    Website: www.worksright.com

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    New Generation Software:  Watch a demo, then test drive NGS-IQ
    Northeast User Groups Conference:  19th Annual Conference, April 6 - 8, Framingham, MA
    COMMON:  Join us at the 2009 annual meeting and expo, April 26-30, Reno, Nevada

    IT Jungle Store Top Book Picks

    Easy Steps to Internet Programming for AS/400, iSeries, and System i: List Price, $49.95
    The iSeries Express Web Implementer's Guide: List Price, $49.95
    Getting Started with PHP for i5/OS: List Price, $59.95
    The System i RPG & RPG IV Tutorial and Lab Exercises: List Price, $59.95
    The System i Pocket RPG & RPG IV Guide: List Price, $69.95
    The iSeries Pocket Database Guide: List Price, $59.00
    The iSeries Pocket SQL Guide: List Price, $59.00
    The iSeries Pocket Query Guide: List Price, $49.00
    The iSeries Pocket WebFacing Primer: List Price, $39.00
    Migrating to WebSphere Express for iSeries: List Price, $49.00
    Getting Started With WebSphere Development Studio Client for iSeries: List Price, $89.00
    Getting Started with WebSphere Express for iSeries: List Price, $49.00
    Can the AS/400 Survive IBM?: List Price, $49.00
    Chip Wars: List Price, $29.95

    Mid-Market Companies Want to Be Green, Too Wall Street Makes IBM, Sun Strange Bedfellows?

    Leave a Reply Cancel reply

Volume 9, Number 10 -- March 18, 2009
THIS ISSUE SPONSORED BY:

Help/Systems
COMMON
System i Developer

Table of Contents

  • Looking for Commitment, Part 2
  • Treasury of New DB2 6.1 (V6R1) Features, Part 3: Client Special Registers
  • Admin Alert: Changing your SMTP Server is Easy-ish

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • IBM i Has a Future ‘If Kept Up To Date,’ IDC Says
  • When You Need Us, We Are Ready To Do Grunt Work
  • Generative AI: Coming to an ERP Near You
  • Four Hundred Monitor, March 22
  • IBM i PTF Guide, Volume 25, Number 12
  • Unattended IBM i Operations Continue Upward Climb
  • VS Code Is The Full Stack IDE For IBM i
  • Domino Runs on IBM i 7.5, But HCL Still Working on Power10
  • Four Hundred Monitor, March 6
  • IBM i PTF Guide, Volume 25, Number 11

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2023 IT Jungle