Friday, November 28, 2014

Summary of IDUG EMEA 2014 Prague

IDUG hosted its annual
2014 EMEA conference in Prague a couple of weeks ago. The event was a real success:
·         Highest attendance since 2008
·         A significant amount of first-time attendees

The IDUG EMEA CPC Committee

Top 5 sessions (in my point of view):
  • Let’s talk recovery (Florence Dubois)
  • Understanding Query Transformation in DB2 for z/OS (Daniel Luksetich)
  • Hadoop and DB2 11 just got engaged! How long before the wedding? (Emil Kotrc)
  • Lost without a trace (Denis Tronin)
  • How to save REAL money with SQL Tuning (Phil Grainger)

Denis Tronin presenting DB2 Performance and IFCIDS

The only thing which did not work well was the Free Exams Certifications, many attendees were not able to benefit from this offer during the event, due to slow and/or malfunctioning internet connection in the certification room. In compensation, attendees were offered vouchers, to attempt the certifications at a later date.

The next IDUG EMEA conference will be held in Dublin on 15-20 November 2015. IBM Champion Steve Thomas is the chairman for this event.
Interested to attend? Act now! The call for presentation for next IDUG EMEA is opened! (if you are selected as a presenter, the conference fees are for free).

IBM recognized a handful of individuals and user groups for their DB2 activities and leadership, during the Mainframe50 celebration dinner: csDUG was rewarded as the new EMEA DB2 User Group!

csDUG awarded new EMEA DB2 User Group

Saturday, November 8, 2014

2nd csDUG Conference: A great success!

50 attendees from Czech Republic, Slovakia, and some other European countries attended the 2nd csDUG Conference earlier this week, in Prague. This one-day conference was held in the IBM lab in Prague, and sponsored by CA Technologies. Various famous international speakers made the trip to share their DB2 expertise and give presentations about DB2 for z/OS.

We also had the pleasure to see some representatives of other European DB2 User Groups (PDUG, SpDUG, BeLux, …)

Steen Rasmussen giving a presentation at csDUG

A big thank you to the presenters, who provided great DB2 technical content:

Steve Thomas giving a presentation at csDUG

The agenda of the conference is available [here].

A Mainframe ?

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.


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();;

private void run() throws InstantiationException, IllegalAccessException,
ClassNotFoundException, SQLException, InterruptedException {
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()) {
System.out.printf("Count: %d", count);

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=, USERID=qadba01,                 
      APPLICATION NAME=db2jcc_application                         
 V445-O2C85068.DE86.CE04632EDB52=98403 ACCESSING DATA FOR         
  (  1)::FFFF:                                      
 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:

  End User User ID    : qadba01             
  End User Workstation:      
  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(""));
    connection.setClientInfo("ClientUser",      System.getProperty(""));
    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,             
 V445-O2C85068.E276.CE04710F0A04=99078 ACCESSING DATA FOR    
  (  1)::FFFF:                                 
 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:
  End User User ID    : plape03                                 
  End User Workstation: plape03mac739                           
  End User Transaction:        

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.