Comment lines by Soloman Barghouthi: A technique for cancelling rogue or unwanted database queries

Consistent and accurate request accountability in a J2EE™ application server environment could be the key to dealing with events such as rogue or unwanted database queries. This article describes a simple technique for identifying and handling roque queries in an IBM® WebSphere® application Server environment -- without needing to restart the application server or the database. This content is part of the IBM WebSphere Developer Technical Journal.

Soloman Barghouthi (soloman@us.ibm.com), Senior Software Engineer, IBM

Author photoSoloman Barghouthi is a Senior Software Engineer at the IBM Rochester Lab. Soloman is the architect and leader of the WebSphere Application Server EJBContainer team. Soloman is a database expert and has published many articles on database/WebSphere Application Server interaction.


developerWorks Contributing author
        level

27 January 2010

Also available in Chinese Russian Portuguese

Stop that query

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
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
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
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:

  1. 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
    Figure 4. Get snapshot command
  2. 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.
  3. 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:

    db2 force application (78)

    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.

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.

Resources

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into WebSphere on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=WebSphere, Information Management
ArticleID=464149
ArticleTitle=Comment lines by Soloman Barghouthi: A technique for cancelling rogue or unwanted database queries
publish-date=01272010