Level: Introductory Rahul Kitchlu, Software Engineer DB2 Integration team, IBM Toronto Software Lab
Jason Shayer, Information Developer DB2 Universal Database team, IBM Toronto Software Lab
05 Dec 2002 In 3-tier applications that access a DB2 server, important client information is not automatically sent. DB2 Connect provides a native API you can use to send detailed client information, such as application name, user ID, workstation name, and an accounting string, to the DB2 server. This API can be accessed from its C interface, or the JDBC programming interface, or directly from the WebSphere Admin Console.
©
2002 International Business Machines Corporation. All rights reserved.
Introduction
If you are responsible for developing and delivering three-tier applications that access data on DB2® servers, you may have found it difficult to pass along the information you need for proper monitoring and accounting of the clients. In a typical scenario, applications running on WebSphere® Application Server that access data on DB2 servers are indistinguishable to DB2. Application servers such as WebSphere maintain a pool of database connections for improved response time. These incoming connections can be easily identified from DB2. However, since the applications using these connections run within the WebSphere Java™ Virtual Machine (JVM) they do not provide any additional information on connection by default.
For example,
Figure 1
shows the output from DISPLAY THREAD(*) command on DB2 for z/OS and OS/390 before we change the environment to set additional information about the incoming client. The application name is labeled as
java.exe
and the user ID and workstation defaults to the user ID and workstation that was used to get the database connection. This might not be accurate for all scenarios where further modular information about the application or the module currently running needs to be set.
Figure 1. Before we set the client information
Client connections on a DB2 for UNIX, Windows and Linux can be seen through a DB2 LIST APPLICATIONS command as shown below:
> db2 list applications
Auth Id Application Appl. Application Id DB # of
Name Handle Name Agents
-------- -------------- ---------- ------------------------------ -------- -----
DB2V71 java.exe 195 *LOCAL.db2v81.07B0C0003412 SAMPLE 1
DB2V71 java.exe 169 G91A59AF.O17A.0BA7C0210130 SAMPLE 1
|
To solve this problem, DB2 provides a native API (SQLESETI) that can be used to set additional information about the clients from the application. This API can be accessed from its C interface, or the JDBC programming interface, or directly from the WebSphere Admininstrative Console. This article details how to set up the environment so that more client information can be passed along with every DB2 connection request from a WebSphere Application Server This information could be the application name, user ID, workstation name, or an accounting string.
Our examples assume an environment consisting of the following products:
This article includes:
- A brief conceptual introduction of key architectural components.
- The process for setting the correct client information on DB2 to make the applications monitoring and resource accounting more efficient. To illustrate the strong integration between WebSphere and DB2, you can configure this directly from the Data Sources Tab in the WebSphere Application Server Console. This feature is only available when using DB2 with WebSphere Application Server.
-
Instructions on how to set client information with a Java program using DB2 APIs. The sample code for such applications is available for
download
.
The environment
The typical environment for which this article is appropriate is a three-tier setup in which an application, working through WebSphere, makes a connection to a DB2 database, in this case DB2 for OS/390, through an intermediate, DB2 Connect Enterprise Edition.
Figure 2. The three-tier environment described in this article
DB2 Universal Database (UNIX, Windows and Linux)
DB2 Universal Database is IBM's relational database management system for AIX
®
, Linux, HP-UX, Sun, and Windows
®
. DB2 database software marks the next stage in the evolution of the relational database. DB2 is the industry's first multimedia, Web-ready relational database management system delivering leading capabilities in reliability, performance, and scalability.
DB2 Universal Database or z/OS and OS/390
The DB2 database for OS/390 and z/OS, Version 7 program delivers improved performance, availability, and scalability for your e-business and data warehouse applications.
With DB2 UDB for OS/390 and z/OS, Version 7, your e-business and business intelligence applications can be synergized with the powerful, highly available environment provided by S/390
®
and zSeries
TM
servers running OS/390 and z/OS. You can leverage your existing applications while developing and expanding your electronic commerce for the future.
DB2 Connect
DB2 Connect provides a seamless access point to mainframe servers (S/390, zSeries, AS/400
®
, and iSeries
TM
) for your applications. Leverage your host data with DB2 and use DB2 Connect to enable a fast, secure access to that data from Windows
®
and UNIX
®
platforms.
WebSphere Application Server
IBM WebSphere Application Server, Version 5, and its development environment, WebSphere Studio, Version 5, provide the standards-based infrastructure to integrate business processes across the enterprise and with partners, suppliers and customers.
Setting client information - An overview
You can set client information either at the connection boudndary or the at the transaction boundary.
-
At connection boundary
Using the JDBC interface you can set additional client information when you first get a database connection. You can access this feature from your application's database connection code or directly from the WebSphere Administrative console. Setting default client information at connection boundary from console is simpler, because you can use the graphical user interface offered with WebSphere without any additional overhead.
-
At transaction boundary
If you need to change the client information after a connection has been made, you can manually invoke a DB2 SQLESETI API either from the C interface (an example for this scenario is available in the DB2 Samples Directory) or from the Java interface, using a JNI wrapper to call the necessary C code. You may need to set client information at transaction boundary rather than at connection boundary in certain situations. For example, if your application contain multiple methods within the same connection module, you might have need to identify each method or process, and thus would need to set client information at each transaction.
 |
Setting client information at connection boundary
You can set client information either using the
WebSphere Administrative console
or by using a
Java program
.
Using the WebSphere Administrative console
You can configure the client information directly from the Data Sources Tab in the WebSphere Application Server (Advanced Edition for Multiplatforms, Version 4.1) Console:
-
Launch the WebSphere Administrative Console.
-
Click on
Resources
and
JDBC Provider
.
-
Right-click on
JDBC Provider
and click
New
. Refer to your WebSphere documentation for more information on creating a new driver.
-
Select the driver you created and select
Data Source
.
-
From the
General
tab, in the
Custom Properties
box, add any or all of following parameters (and their corresponding values):
ClientApplName ClientWrkstnName ClientUserid ClientAcctStr |
-
Click
Apply
to finish.
Using a Java program
The following code shows how you can use the DB2ConnectionPoolDataSource Class to flow client information to the server.
DB2ConnectionPoolDataSource ds = new DB2ConnectionPoolDataSource();
ds.setUser("myuser");
ds.setPassword("mypass");
ds.setDatabaseName("mydb");
ds.setConnectionAttribute
("ClientApplName=WebSphere-Samples;
ClientWrkstnName=WebSphere-Wkstn;
ClientUserid=WebSphere-User
ClientAcctStr=WebSphere-Acctstr");
PooledConnection poolconn = ds.getPooledConnection();
con = poolconn.getConnection();
|
Setting client information at transaction boundary
As an alternative to setting client information at connection boundary, you may choose to set the information at transaction boundary. This section describes how to set or reset client information at transaction boundary in your application code written in Java. To do this, manually invoke a JNI wrapper to call the necessary C code. For this discussion, we provide a typical Java class that calls JNI wrapper class that in turn calls the native APIs. You may use the code provided for your applications or develop your own JNI bridge.
Prerequisites
Ensure that you:
- Call this JNI wrapper after the connection is established.
- Call this JNI wrapper before any transaction.
- Call this JNI wrapper for each application that needs to set client information.
- Properly set DB2 and Java environment variable.
Restrictions
SQLESETI API is supported on all DB2 for UNIX, Windows and Linux platforms however, the Java interface and the JNI wrapper we provide with this article have been developed and tested using:
-
IBM
®
DB2 Connect Enterprise Edition 7.2
- JDK (1.2.2 and 1.3)
- AIX 4.3.3
- WebSphere Application Server Version 4.0.1
- DB2 for OS/390 and z/OS Version 6 and 7
Procedure
This procedure describes a step-by-step way to implement a Java interface (
seti.java
) to the SQLESETI API using a JNI wrapper.
-
First we need to write a Java class that defines the "native" methods. Let us call this interface
seti.java
-
We compile this Java class to get the JNI header file:
-
javac seti.java
is the command to compile the code.
-
javah -jni seti
is the command to generate the JNI header files.
-
seti.h
is the the-machine generated JNI header file.
-
Extend the JNI header file to implement the code in C to call the API.
sapi.c
is the C code that calls the SQLESETI API.
-
Compile the C code with the correct compiler settings based on the operating system and the correct DB2 include files. We implemented this code on an IBM AIX machine, so here is the command to compile the C code on AIX:
xlc -I$DB_HOME/include -I$JAVA_HOMEinclude -c sapi.c -O |
The output of this step is
sapi.o
.
-
Generate the shared library from the compiled code. Here is the command to generate the shared library on AIX:
cc -o libseti.so sapi.o -I$DB_HOME/include -I$JAVA_HOME/include -
L$DB_HOME/lib -bE:seti.exp -bM:SRE -e Java_seti_app -ldb2 -lc |
libseti.so
is the output of this step.
Hint:
Steps 4 and 5 can be achieved on other platforms as follows:
Linux
gcc -o libseti.so -shared -Wl, -soname,libseti.so -I$DB_HOME/include -
I$JAVA_HOME/include -I$JAVA_HOME/include/linux sapi.c -static -lc |
Windows
cl -I%JAVA_HOME%/include -I$DB_HOME/include -
I%JAVA_HOME%/include/win32 -LD sapi.c -Felibseti.dll |
Solaris
cc -G -I$JAVA_HOME/include -I$DB_HOME/include -
I$JAVA_HOME/include/solaris \ sapi.c -o libseti.so |
The Java applications are now ready to call this Java interface (
seti.java
), which loads the shared library (from step 5) to call the JNI wrapper to call the native methods (
test.java
, for example).
All of the code mentioned in the above procedure is available for
download
.
Implications of transaction processor monitors
In a transaction monitor or application server (multi-tier) environment, application users do not issue SQL requests directly. Instead, they make a request to the transaction processor monitor (for example, CICS
®
, TUXEDO, or ENCINA running on a UNIX, OS/2
®
, or Windows NT
®
server) or application server to execute a business transaction. Each business transaction has an application part that issues SQL requests to the database server. Because the SQL requests are issued by an intermediate server, the database server has no information about the original client that caused the execution of the SQL request.
For further information on transaction processor monitoring, see the
DB2 Connect User's Guide
.
Developers of transaction processor monitor (TP monitor) transactions or application server code can use the SQLESETI API to provide information about the original client to the database server.
You can use the
seti.java
(see
download
) or JDBC Connection attributes to flow this client info to the database server.
Let us now take a look at monitoring these application clients on DB2 for UNIX, Windows and Linux. Assume that we catalog a remote database (AIX 5.1, DB2 Version 7.2) on an AIX 4.3.3 machine running DB2 Version 7.2 and run our test application using
seti.java
to set the client information. On the DB2 server, we can see this information being sent using the following commands:
-
On the server, switch on DB2 monitoring:
db2 update monitor switches using table on bufferpool
on uow on sort on lock on statement on |
-
On the server, reset the monitor switches for your database
db2 reset monitor for database <dbname> |
- On the client, run the application.
-
On the server, take an application level snapshot:
db2 get snapshot for applications on <dbname> |
The snapshot should have content similar to the following:
...
...
TP Monitor Client User ID:WebSphere-Userid
TP Monitor Client Workstation Name:WebSphere-Wrkstn
TP Monitor Client Application Name:WebSphere-AppName
TP Monitor Client Accounting String:WebSphere-AccStr
...
... |
These TP Monitor elements can be used to identify WebSphere Application Server connections.
The results shown on DISPLAY THREAD
Figure 4
shows the output of a DISPLAY THREAD(*) command running on DB2 for OS/390 and z/OS server after the client information has been set. Notice that the application name, user ID and workstation name have been set to the values we specified earlier.
Figure 4. The client information now appears in DISPLAY THREAD
More about the SQLESETI C API
SQLESETI is a C API that is provided with DB2 to set client information specific to a connection, provided the connection already exits. This API is only used to pass information to DB2 UDB Version 6 or later and DB2 OS/390 Version 5 or later.
Types of information you can specify using the SQLESETI API
Applications can specify the following types of information:
- Client user ID being set or queried. A maximum of 255 characters can be set, although servers can truncate this to some platform-specific value. This user ID is for identification purposes only, and is not used for any authorization.
- Client workstation name being set or queried. A maximum of 255 characters can be set, although servers can truncate this to some platform-specific value.
- Client application name being set or queried. A maximum of 255 characters can be set, although servers can truncate this to some platform-specific value.
- Client accounting string being set or queried. A maximum of 200 characters can be set, although servers can truncate this to some platform-specific value.
The valid entries for the SQLE-CLIENT-INFO TYPE element and the associated descriptions for each entry are listed below:
Table 1. Client information settings
|
Type
|
Data type
|
Description
| | SQLE_CLIENT_INFO_USERID | CHAR(255) | The user ID for the client. Some servers may truncate the value. For example, DB2 for OS/390 servers support up to length 16. This user ID is for identification purposes only, and is not used for any authorization. | | SQLE_CLIENT_INFO_WRKSTNNAME | CHAR(255) | The workstation name for the client. Some servers may truncate the value. For example, DB2 for OS/390 servers support up to length 18. | | SQLE_CLIENT_INFO_ APPLNAME | CHAR(255) | The application name for the client. Some servers may truncate the value. For example, DB2 for OS/390 servers support up to length 32. | | SQLE_CLIENT_INFO_ ACCTSTR | CHAR(200) | The accounting string for the client. Some servers may truncate the value. For example, DB2 for OS/390 servers support up to length 200. |
Conclusion
Monitoring and auditing applications that use databases as backend is becoming an increasingly difficult task. Applications that access DB2 servers can use the SQLESETI API to set relevant client information (such as application name, user id, workstation name, account string) at either the database connection or transaction level. At connection boundary this information can be set using the JDBC interface in you Java applications, or directly from the WebSphere Administrative Console. At transaction boundary, you can code your own Java interface using a JNI wrapper class to call the SQLESETI API.
Acknowledgements
The authors recognize the help, guidance and expertise of the following people: Grant Hutchison, the DB2-WebSphere Integration Team, and many others at the IBM Toronto Software Lab.
Download | Description | Name | Size | Download method |
|---|
| Sample code file | SampleCode.zip | 10KB | HTTP |
|---|
Resources Learn
Get products and technologies
-
Build your next development project with
IBM
trial software, available for download directly from developerWorks.
Discuss
About the authors  | 
| |
Rahul Kitchlu
is a Software Engineer with the DB2 Integration team at the IBM Toronto Software Lab. Rahul earned his Bachelor of Computer Science Degree from the University of New Brunswick and is an IBM Certified Solutions Expert. His current focus is DB2-WebSphere Integration. You can reach Rahul at
rkitchlu@ca.ibm.com
.
|
| 
| |
Jason Shayer
is an Information Developer with the DB2 Universal Database team at the IBM Toronto Software Lab. Jason earned his Bachelor of Mathematics and English Degree from the University of Waterloo and has been a part of DB2 Information Development for 5+ years. He's currently team lead for the Quick Beginnings component of DB2 documentation. You can reach Jason at
shayer@ca.ibm.com
.
|
Rate this page
| 
