![Close [x]](http://dw1.s81c.com/i/c.gif){kind=small}
A three-tier enterprise architecture consists of a client tier, middle tier, and a data tier. In many Java™ 2 Platform, Enterprise Edition (J2EE) environments, IBM WebSphere Application Server provides the middle tier. WebSphere Application Server processes requests from the client tier and accesses the data tier (for example, IBM DB2®) to retrieve or update the data tier based on client requests and input. In many cases, an authentication data alias is often used in client requests for database interaction. This could result in diminished accountability due to loss of caller identity. Accountability is very critical, not only to identify who is doing what from a tracking standpoint, but also to enable WebSphere Application Server and database administrators to react to unexpected events, such as the need to identify and cancel unwanted and rogue database queries.
Figure 1. Three-tiered architecture
This article explains how to use a feature in WebSphere Application Server to tag database connections with client information, and how you can apply this tagging feature to interesting situations, like for identifying and canceling rogue queries.
Tagging database connections with client information
A feature introduced in WebSphere Application Server V6.0 enables you to tag database connections with certain information. That information is then passed down by the application server to the database layer (if the database supports passing such information) where it can be used to identify who is doing work on the connection. The information you can set on a database connection includes:
- CLIENT_ACCOUNTING_INFO: Specifies accounting information for the connection. This information is used for client accounting purposes and is used primarily with DB2 for z/OS®.
- CLIENT_LOCATION: Specifies the location of the client that is driving the request.
- CLIENT_ID: Specifies the current client user name for the connection. This name is for client accounting purposes and is not the user value for the JDBC connection; that is, this is not an authentication data alias.
- CLIENT_APPLICATION_NAME: Specifies the name of the application that is using the database connection.
This information can be set on a connection in a WebSphere Application Server V6.0 application using the com.ibm.websphere.rsadapter.WSConnection API, shown in Figure 2.
Figure 2. WSConnection API
Starting with WebSphere Application Server V7, the above API is deprecated in favor of the standard JDBC 4.0 API, as shown in Figure 3.
Figure 3. JDBC 4.0 Connection API
Putting the tagging feature to good use
Suppose you have an application that assembles database queries dynamically based on user requests, and then submits these queries to a DB2 database using WebSphere Application Server. During testing, you realize that there are some use cases for which you want the ability to cancel queries that might be incorrectly specified by the client. How do you do that?
Listing 1 shows a simple example that executes a database query that you will want to identify and cancel.
Listing 1
.....
javax.sql.DataSource ds = (javax.sql.DataSource)ctx.lookup("jdbc/myDS");
conn=ds.getConnection();
stmt=conn.createStatement();
rs=stmt.executeQuery("Select * from myEmpTable");
while(rs.next()){
out.println(rs.getString(1)); |
To leverage the tagging feature here, you will need to tag the database connection with client information. Listing 2 shows how you can do this.
Listing 2
import com.ibm.websphere.rsadapter.WSConnection
public void displayInfo()
{…..
Properties props = new properties();
props.setProperty(WSConnection.CLIENT_APPLICATION_NAME,
"myspecialAppappname_displayInfo");
javax.sql.DataSource ds = (javax.sql.DataSource)ctx.lookup("jdbc/myDS");
conn=ds.getConnection();
((WSConnection)conn).setClientInformation(prop);
stmt=conn.createStatement();
rs=stmt.executeQuery("Select * from myEmpTable");
while(rs.next()){
out.println(rs.getString(1));
} |
The application now annotates the connection it uses with myspecialAppappname_displayInfo. Now that the database connection is marked with the client information, the database administrator can identify and cancel the work associated with the application by simply looking for connections with the annotated value. For example, a DB2 database administrator can perform these steps to identify and cancel such a query:
- From a DB2 command window, issue this command to display all the
database connection information (Figure 4):
db2 get snapshot for applications on sample
Figure 4. Get snapshot command
- As shown in Figure 4, the TP Monitor client application name has a
value of
myspecialAppappname_displayInfo, which is the value set by the application to identify the connection. - You can destroy the connection that is being used to execute the query
ny using the Application handle value (which is 78 here) and running the DB2 force
application command:
This application command causes the connection running the bad query to be interrupted and throws a StaleConnectionException to the application. The application must then deal with the exception just like it would any other StaleConnectionException.db2 force application (78)
Although ending a hung or long-running query might seem like a complex task, the WebSphere Application Server tagging feature provides an easy way to simplify the task. The technique described here can be applied to any database, provided the database has tools similar to DB2 for displaying and cancelling a given query.
- IBM WebSphere Application Server
Information Center
-
IBM developerWorks
WebSphere
Table of contents
Next steps from IBM
Tags
Use the slider bar to see more or fewer tags.
For articles in technology zones (such as Java technology, Linux, Open source, XML), Popular tags shows the top tags for all technology zones. For articles in product zones (such as Info Mgmt, Rational, WebSphere), Popular tags shows the top tags for just that product zone.
For articles in technology zones (such as Java technology, Linux, Open source, XML), My tags shows your tags for all technology zones. For articles in product zones (such as Info Mgmt, Rational, WebSphere), My tags shows your tags for just that product zone.
Popular article tags |
My article tagsSkip to tags list
Popular article tags |
My article tags
