StarQuest Technical Documents

Sending Client Information to DB2 for OS/390 and z/OS using StarSQL for Java

Last Update: 06 January 2006
Product: StarSQL for Java
Version: 2.27 or later
Article ID: SQV00SJ002

Abstract

In 3-tier applications that access a DB2 server, important client information is not automatically sent to DB2. This can pose a problem for DB2 administrators who wish to monitor particular applications or to troubleshoot issues with specific DB2 threads. To solve this problem, StarSQL for Java provides a native application programming interface (API) that can be used to set additional information about the clients from the application. This API can be accessed from the JDBC programming interface. 

This technical document details how to use the native API in StarSQL for Java to pass client information with every DB2 connection request from an application server. This information can include the application name, user ID, workstation name, or an accounting string.

Solution

The StarSQL for Java API takes an array of SET statements and executes them with a single DRDA EXCSQLSET command. To use the interface you must:

1. Ensure that the location of the StarSQL_JDBC.jar file is included in the Java build path. If you are using IBM WebSphere, the location of the rsaexternal.jar file must also be in the Java build path.
2. Add the following import statement to the import section of the Java application:

import com.starsql.jdbc.api.SQStatement;

The following code samples illustrate how to use the API in both WebSphere and non-WebSphere environments.

Code Sample for WebSphere Applications

import com.starsql.jdbc.api.SQStatement;

Statement stmt = conn.createStatement();

String[] setStatements = {
    "SET CLIENT USERID 'DB2USER'",
    "SET CLIENT APPLNAME 'MyApp'",
    "SET CLIENT WRKSTNNAME 'MyWorkstation'",
    "SET CLIENT ACCTNG 'SIG0101','my_accounting_info',X'CAFE','ABCD'",
};
com.ibm.websphere.rsadapter.WSCallHelper.jdbcCall(null, stmt, "executeSet",
   new Object[] { setStatements }, new Class[] { String[].class });

stmt.close();

Code Sample for Non-WebSphere Applications

import com.starsql.jdbc.api.SQStatement;

SQStatement stmt = (SQStatement) mConnection.createStatement();

String[] setStatements = {
    "SET CLIENT USERID 'DB2USER'",
    "SET CLIENT APPLNAME 'MyApp'",
    "SET CLIENT WRKSTNNAME 'MyWorkstation'",
    "SET CLIENT ACCTNG 'SIG0101','my_accounting_info',X'CAFE','ABCD'",
};

stmt.executeSet(setStatements);

stmt.close();

Sample Output

Following is an example of the client information as it would appear in a DB2 console, regardless of whether the interface is called from a WebSphere or non-WebSphere application:

DSNV401I -DB8G DISPLAY THREAD REPORT FOLLOWS - DSNV402I -DB8G ACTIVE THREADS - NAME ST A REQ ID AUTHID PLAN ASID TOKEN SERVER RA * 4 V2.27.1302 QAUSER DISTSERV 0042 17 V437-WORKSTATION=MyWorkstation, USERID=DB2USER, APPLICATION NAME=MyApp
V445-AC1324AB.H0D8.BFF65855A846=17 ACCESSING DATA FOR 172.19.36.171 TSO T * 3 P390 P390 004D 18 DISPLAY ACTIVE REPORT COMPLETE DSN9022I -DB8G DSNVDT '-DISPLAY THREAD' NORMAL COMPLETION