Thursday, November 6, 2014

How to find Java application that is using my DB2

The amount of DB2 workload that is coming from Java applications is growing. Java applications can run on z/OS but more often they run outside of z/OS. In this post we will cover how you can investigate DB2 threads that are processing SQL statements issued by a Java application.

We will cover what is possible to do with standard DB2 commands and we will cover best practices for using JDBC that will make easier for database administrators to quickly identify the application that is responsible for the request. In future posts we will cover techniques for doing it without need to change the application code and how to get full information that can help developer to debug the code that is issuing SQL statements with poor performance. It can be a difficult task for complex applications and applications that are developed by different company.

Let's have a simple Java application that connects to DB2. This application just issues a simple query that can take long time. You can increase the sleep value to make it run longer. It issues the query and it reads its result set slowly. If you want to try it, you have to replace userid, password and URL with values that are good for your system. Then you just need to compile it and run with the DB2 JDBC driver on your Java class path.

package com.ca.blog.sampledbapp;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

public class SimpleDbApp {
private final String USERID = "qadba01";
private final String PASSWORD = "password";
private final String URL = "jdbc:db2://system:port/LOCATION";
private final String QUERY = "SELECT * FROM SYSIBM.SYSTABLES";

public static void main(String[] args) throws InstantiationException,
IllegalAccessException, ClassNotFoundException, SQLException,
                        InterruptedException {
SimpleDbApp app = new SimpleDbApp();
app.run();
}

private void run() throws InstantiationException, IllegalAccessException,
ClassNotFoundException, SQLException, InterruptedException {
Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance();
Properties properties = new Properties();
properties.put("user", USERID);
properties.put("password", PASSWORD);
Connection connection = DriverManager.getConnection(URL, properties);
CallableStatement statement = connection.prepareCall(QUERY);
final boolean rowsReturned = statement.execute();
if (rowsReturned) {
int count = 0;
while (statement.getResultSet().next()) {
count++;
Thread.sleep(100);
}
System.out.printf("Count: %d", count);
}
statement.close();
}
}

This application can run for a while. Let's see what we can see in DB2 about it.

We can issue -DISPLAY THREAD (*) DETAIL command to see if the thread for this statement is active:
DSNV401I  !D10A DISPLAY THREAD REPORT FOLLOWS -                   
DSNV402I  !D10A ACTIVE THREADS -                                  
NAME     ST A   REQ ID           AUTHID   PLAN     ASID TOKEN     
SERVER   RA *  2032 db2jcc_appli QADBA01  DISTSERV 02F2 98403     
 V437-WORKSTATION=130.200.80.104, USERID=qadba01,                 
      APPLICATION NAME=db2jcc_application                         
 V441-ACCOUNTING=JCC04150130.200.80.104                           
       '                                                          
 V445-O2C85068.DE86.CE04632EDB52=98403 ACCESSING DATA FOR         
  (  1)::FFFF:130.200.80.104                                      
 V447--INDEX SESSID           A ST TIME                           
 V448--(  1) 5166:56966       W R2 1431014492448                  

I was able to find it because it was the only thread for userid QADBA01. In case when there are hundreds of threads it can be difficult to find it. Default application name for all Java applications is db2jcc_application and if the application does not set you will not get anything better. There are some information that can help you:
  • Authid - if your application is using single userid to access DB2 and no other applications are using it, you can find out what application it is if you keep track of it. In case when every user of the application is also connecting to DB2 under its userid, you cannot use it to identify the application.
  • Workstation - there is an IP address of the application server. This can be used to find the application but it is not easy for database administrator to find out what application is running on that server and there can be multiple applications on one server.
  • SQL text - you can figure out the application from the tables that are used. That usually works for one statement but you cannot effectively aggregate performance metrics based on SQL text.
This is not issue of -DISPLAY THREAD command. Even advanced performance monitoring tools like CA SYSVIEW for DB2 or CA Chorus for DB2 Database Management does not display more than just better formatted identification section:

Identification                              
  End User User ID    : qadba01             
  End User Workstation: 130.200.80.104      
  End User Transaction: db2jcc_application  

Active Threads in the Investigator of CA Chorus
So let's see what can be done by the Java application to identify itself better to DB2.

We will start with standard JDBC properties and then we will cover DB2 specific properties. These properties can be set by Connection.setClientInfo(name, value) method.

These three properties are recommended to be used by every Java application that is connecting to a database:
  • ApplicationName - The name of the application that is connected. DB2 will user first 32 characters.
  • ClientUser - The name of the user that the application is performing work for. This should be the real end user ID and not the user ID that was used to establish the connection.
  • ClientHostname - The hostname of the computer the application using the connection is running on. Hostname is better than just IP address. When the application is running on multiple servers it is good to provide different hostnames for each server so it is possible to identify the real server.
Let's see how we can set it in our sample program:
    connection.setClientInfo("ApplicationName", System.getProperty("sun.java.command"));
    connection.setClientInfo("ClientUser",      System.getProperty("user.name"));
    connection.setClientInfo("ClientHostname",  InetAddress.getLocalHost().getHostName());

Note: This is simple example that does not handle exceptions how it should.

The output for -DISPLAY THREAD has improved:

SERVER   RA *  2077 db2jcc_appli QADBA01  DISTSERV 02F2 99078
 V437-WORKSTATION=plape03mac739, USERID=plape03,             
      APPLICATION NAME=com.ca.blog.sampledbapp.SimpleDb      
 V441-ACCOUNTING=JCC04150130.200.80.104                      
       '                                                     
 V445-O2C85068.E276.CE04710F0A04=99078 ACCESSING DATA FOR    
  (  1)::FFFF:130.200.80.104                                 
 V447--INDEX SESSID           A ST TIME                      
 V448--(  1) 5166:57974       W R2 1431015452732             

You can see the computer host name (in this case my laptop), my real userid and the main class of the application. The same is true for other tools:
Identification                                                  
  End User User ID    : plape03                                 
  End User Workstation: plape03mac739                           
  End User Transaction: com.ca.blog.sampledbapp.SimpleDb        

DB2 has two more properties in the client info that can be used. See Client info properties support by the IBM Data Server Driver for JDBC and SQLJ for full documentation.
  • ClientAccountingInformation - This can be up to 200 characters long string with accounting information. The default is JCCversionclient-ip. You can see it in the output of -DISPLAY THREAD on line that starts with V441-ACCOUNTING=.
  • ClientCorrelationToken - Unique value that allows you to correlate the logical unit of work in DB2 to your application. It was added in DB2 and it seems to be read-only.
That is nice but as DB2 database administrator you cannot change the Java application code. In future post we will cover how to inject this information into any application without need to change the code and rebuild and redeploy the application. The identification of the application is a good first step but in reality you need more information about what part of the application was issuing the statement, for example full stack trace would be helpful. We will cover techniques for it as well in future.

If you are a developer you should be using mentioned client info properties to make your application more friendly to database administrators. 

No comments:

Post a Comment