IBM Destination Z - Group home

Getting Client Accounting Methods Information

By Destination Z posted Mon December 23, 2019 03:26 PM


With today's complicated access to data from distributed infrastructures, it is sometimes difficult to identify source of access into DB2 for z/OS databases. New client accounting methods in various environments assist us and give us an option to identify individualized user access or transactional access into the structures.

Here, we will discuss the capability of the DB2 client accounting string in the IBM Data Server Driver. This client information can be used in conjunction with several other methodologies such as trusted context and RACF control in concert to provide what is known as z/OS identity propagation. The importance of identity propagation is to provide capability for audit, regulatory and credentialing from end-user client processes. Without this capability, DB2 administrators are left with little information to determine the source of distributed access.

With the set client info process, the capability now exists to classify workloads, identify client information, provide accounting unique information, and utilize this information and various reporting tools and diagnostic procedures. This set client info process can easily be placed into a reference architecture or used by an application developer as one of their common routines for each application program, just as one would have for such items as error handling, messaging, etc.

Depending on the programming environment, this information can be set in various ways. The following references indicate how this can be accomplished for usage in the IBM Data Server Driver. However, this capability exists as well in Resource Recovery Services attachment facility environments and Enterprise workload manager classification. You should be aware that, depending on the DB2 for z/OS version in your environment, the length for client fields would be different. Please consult the appropriate documentation for your version of DB2 for z/OS.

Java and SQLJ

A set of IBM Data Server Driver for JDBC and SQLJ-only methods (see Table 1) provide extra information about the client to the server. This information can be used for accounting, workload management or debugging.

Extended client information is sent to the database server when the application performs an action that accesses the server, such as executing SQL.
In the IBM Data Server Driver for JDBC and SQLJ version 4.0 or later, the IBM Data Server Driver for JDBC and SQLJ-only methods are deprecated. You should use java.sql.Connection.setClientInfo instead.

Table 1: Methods That Provide Client Information to the Data Server

Information provided
Accounting information
Name of the application that is working with a connection
The CLIENT DEBUGINFO connection attribute for the Unified debugger
A caller-specified string that helps the caller identify which program is associated with a particular SQL statement. setDB2ClientProgramId does not apply to DB2 for Linux, UNIX, and Windows data servers.
User name for a connection
Client workstation name for a connection

To set the extended client information:
  1. Create a Connection.
  2. Cast the java.sql.Connection object to a
  3. Call any of the methods shown in Table 1
  4. Execute an SQL statement to cause the information to be sent to the data server
The following code performs the previous steps to pass a user name and a workstation name to the data server. The numbers to the right of selected statements correspond to the previously described steps.
public class ClientInfoTest {
 public static void main(String[] args) {
    String url = "jdbc:db2://";
    try {
      String user = "db2adm";
      String password = "db2adm";
      Connection conn = DriverManager.getConnection(url,           1 
        user, password);
      if (conn instanceof DB2Connection) {
        DB2Connection db2conn = (DB2Connection) conn;            2 
        db2conn.setDB2ClientUser("Michael L Thompson");          3 
        // Execute SQL to force extended client information to be sent
        // to the server
        conn.prepareStatement("SELECT * FROM SYSIBM.SYSDUMMY1"
          + "WHERE 0 = 1").executeQuery();                                   4 
    } catch (Throwable e) {

Getting to Work

Hopefully, you can place this type of standardized client accounting code into your reference architecture and utilize this capability to more accurately classify your workload and assist in problem determination and performance monitoring, as well as providing you the capability for greater granularity in auditing and access.
Kevin Harrison is an IBM Sr. Certified IT Architect/Sr. Software Engineer in DB2 z/OS Development at the IBM Silicon Valley lab. He is a member of the WW DB2 SWAT team dedicated to supporting large-scale DB2 customers. He has been working with DB2 z/OS since V1 and focuses on database and application design, and application/systems performance and tuning.
1 view