StarQuest Technical Documents

Sending Client Information to DB2 for z/OS & DB2 LUW Using the StarSQL ODBC Driver

Last Update: 16 June 2016
Product: StarSQL for Windows
Version: 5.5 or later
Article ID: SQV00SQ049

Abstract

In 3-tier applications that access a DB2 server, client information is not automatically sent to DB2. This can pose a problem for DB2 administrators who want to monitor particular applications or to troubleshoot issues with specific DB2 threads. To solve this problem, the StarSQL ODBC driver allows an application to send additional information about the client connections.  

When a server application uses the SET CLIENT statements to pass client information to a DB2 z/OS or DB2 LUW host, StarSQL sets the DB2 special registers by issuing the DRDA EXCSQLSET command. The DB2 administrator can then use the database host commands to display the client information.

Solution

The StarSQL ODBC driver supports setting multiple DB2 registers using a single DRDA EXCSQLSET command. An application passes the client information with a string that contains a batch of SET statements. The client information can include the application name (CLIENT APPLNAME), user ID (CLIENT USERID), workstation name (CLIENT WRKSTNNAME), or an accounting string (CLIENT ACCTNG). Following is an example string that contains a batch of SET statements to send the client information.

SET CLIENT USERID 'DB2UserID'; SET CLIENT APPLNAME 'yourApplication'; SET CLIENT WRKSTNNAME 'clientWorkstation'; SET CLIENT ACCTNG 'SIG0101','yourAccountingInfo',X'CAFE','ABCD'

The following code samples illustrate how to include the SET statements from an ODBC, VB/ADO, or VB.NET application. Following the code samples are instructions for displaying the client information from a DB2 LUW or DB2 for z/OS host.

Code Sample for ODBC Applications

HSTMT hstmt = NULL;

RETCODE rc = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);

if (rc == SQL_SUCCESS)
{

rc = SQLExecDirect(hstmt, "SET CLIENT USERID 'DB2UserID';"\
        "SET CLIENT APPLNAME 'yourApplication';"\
        "SET CLIENT WRKSTNNAME 'clientWorkstation';"\
        "SET CLIENT ACCTNG 'SIG0101','yourAccountingInfo',X'CAFE','ABCD'", SQL_NTS);

        // error check ...
} else {
       // error check ...
}

if (hstmt != NULL) {
        rc = SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
}

Code Sample for VB/ADO Applications

Dim adoConn As New ADODB.Connection
Dim adoCmd As New ADODB.Command
Dim BatchSql As String

'Build a batch SQL statement that calls the DB2 special registers
BatchSql = "SET CLIENT USERID 'DB2UserID';" & _
            "SET CLIENT APPLNAME 'yourApplication';" & _
            "SET CLIENT WRKSTNNAME 'clientWorkstation';" & _
            "SET CLIENT ACCTNG 'SIG0101','yourAccountingInfo',X'CAFE','ABCD'"

'Connection string using StarSQL DSN
adoConn.Open "DSN=DB2DSN;UID=db2user;PWD=secret"

'Set command properties
adoCmd.Name = "SendClientInfo"
Set adoCmd.ActiveConnection = adoConn
adoCmd.CommandText = BatchSql
adoCmd.CommandType = adCmdText
adoCmd.CommandTimeout = 15

'Execute command
adoCmd.Execute

'Close connection and destroy objects
Set adoCmd = Nothing
adoConn.Close
Set adoConn = Nothing

Code Sample Using VB.NET

The following example illustrates how to send client information from a VB.NET application using the .NET Framework Data Provider for ODBC.

Dim cn As OdbcConnection

'Build a batch SQL statement that calls the DB2 special registers
Dim mystring As String = "SET CLIENT USERID 'DB2UserID';" & _
                         "SET CLIENT APPLNAME 'yourApplication';" & _
                         "SET CLIENT WRKSTNNAME 'clientWorkstation';" & _
                         "SET CLIENT ACCTNG 'SIG0101','yourAccountingInfo',X'CAFE','ABCD'"

'Connection string using StarSQL DSN
cn = New OdbcConnection("DSN=DB2DSN;UID=db2user;PWD=secret")
cn.Open()

'Create command object and set property
Dim cmd As OdbcCommand = New OdbcCommand(mystring, cn)
cmd.CommandType = CommandType.Text

'Execute command
cmd.ExecuteNonQuery()

'Close connection
cn.Close()

Displaying the Client Information on a DB2 LUW Host

Enter the following command to display the client information that is set in the special registers of a DB2 LUW host.

db2 get snapshot for applications on <database>

The client information is displayed similar to the following output.

TP Monitor client user ID = DB2UserID
TP Monitor client workstation name = yourApplication
TP Monitor client application name = clientWorkstation
TP Monitor client accounting string = yourAccountingInfo

Displaying the Client Information on a DB2 z/OS Host

Enter the following command to display the client information that is set on a DB2 for z/OS host.

-DISPLAY THREAD(*) DETAIL

Following is an example of the client information as it would appear in the DB2 console:

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 DB2USER DISTSERV 0042 17 V437-WORKSTATION=clientWorkstation, USERID=DB2UserID, APPLICATION NAME=yourApplication
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

Note: It is necessary to perform some database activity (e.g. SQLTables() or a SELECT) after connecting before the client information is displayed by -DISPLAY THREAD(*) DETAIL. Just connecting to the database is insufficient.