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