Introduction

The purpose of this article is to provide background information about the DB2 for Linux, UNIX, and Windows tracing functionality and how you can use it along with the powerful Eclipse Modeling Framework (EMF) to detect application resource leaks. The article covers basic information on how to enable the DB2 mechanism that tracks access to database resources by Java® applications, and how how to develop a custom Eclipse plug-in that you can use to analyze the resulting trace files. By analyzing the trace files, you can detect abnormal application behavior, such as database resource leaks. The article also provides tips on how to develop Eclipse plug-ins in general and an EMF plug-in specifically.

This article is not intended to replace the "DB2 application development: Tracing with the DB2 Universal JDBC Driver" article (see Resources section for link) or any other articles related to the DB2 tracing mechanism — it simply describes a sample use case for an easy and efficient means of analyzing DB2 trace files.

For complete documentation on the Eclipse Modeling Framework, consult the EMF project site, which is also linked to from the Resources section.

A domain specific language (DSL) is used to describe the (possibly narrow) elements of a single domain. The domain may be, for example, the family tree or the .ini configuration file elements. In relation to this article, you can consider the language used to form the DB2 trace files to be a domain specific language.

In today's world, the use of DSLs is not that popular because of the already established position and general use of the Extensible Markup Language (XML). XML has the advantage of being universal, but in some areas XML becomes too "verbose" and too heavy to process. Those areas are the places where a DSL becomes the key player — usually a DSL contains everything that is needed to describe the functional domain and does not contain anything that is not needed or adds unnecessary language overhead.

The use of a DSL may have a significant impact on the tools that read the content written in the DSL — there is always a requirement to write a custom parser in this type of scenario. Often, you can find well-recognized applications that can help you with this type activity — for example, ANTLR-based tools and plug-ins.

To use this article, you should be aware of the basics of the IBM DB2 product, the Java programming language, JDBC technology, the Eclipse platform, and EMF.

Back to top

The DB2 tracing mechanism details

The built-in DB2 tracing for Java Database Connectivity (JDBC) connections is thoroughly described in the DB2 Information Center (see Resources section for link). This section focuses on just a few of the basic features.

The DB2 JDBC tracing mechanism enables you to collect detailed information about the database queries issued from an application to the database server. It also provides you with information about the results of the queries and other important metadata. DB2 trace files contain most of the DB2 server information, all the JDBC connection details, all the SQL queries, and more. When trying to detect resource leaks, some of the most relevant data provided by JDBC tracing tells you whether the SQL connection, the SQL statement, and the SQL result set were correctly closed by the connected application.

A database resource leak occurs when an application allocates a resource but then never declares the resource to be no longer used (for example, by issuing a close() operation). The DB2 trace file contains a list of the DB2 connections, statements, and result sets that have been opened, but never closed. These resources are either constantly in use or have leaked. Resource leaks can result in the DB2 server being overload, abnormal behavior within the application, or even in a crash of the DB2 server.

In Java applications that use JDBC technology for connecting to a DB2 server, it is easy to enable DB2 tracing. Simply append the following parameters onto the JDBC URL string used by the application:

• traceDirectory — the directory you want to contain the trace files.
• traceFile — the prefix you want to use for each trace file name.
• traceLevel — the level of tracing you want to use. You can choose to trace all possible invocations and receive all the trace data, or you may want to use filters to trace only the most relevant information. For details on different trace levels, refer to the DB2 Information Center.

Once the application is started and the tracing parameters are correctly passed on the JDBC URL, the trace files should begin to be created. A separate file is created for each opened SQL connection.

Listing 1 shows a sample of a DB2 trace file. Note that in order to display correctly in this article, the longer lines have been split.

[ibm][db2][jcc] BEGIN TRACE_DRIVER_CONFIGURATION
[ibm][db2][jcc] Driver: IBM DB2 JDBC Universal Driver Architecture 2.3.63
[ibm][db2][jcc] Compatible JRE versions: { 1.3, 1.4 }
…
[ibm][db2][jcc][Time:1250166693938][Thread:main][Connection@67df5ee8] 
    getAutoCommit () returned true
[ibm][db2][jcc][Time:1250166693938][Thread:main][Connection@67df5ee8] 
    clearWarnings () called
[ibm][db2][jcc][Time:1250166693938][Thread:main][Connection@67df5ee8] 
    getMetaData () returned DatabaseMetaData@721cdee9
[ibm][db2][jcc][Time:1250166693938][Thread:main][Connection@67df5ee8] 
    setTransactionIsolation (2) called
[ibm][db2][jcc][Time:1250166698906][Thread:main][Connection@67df5ee8] 
    getAutoCommit () returned true
[ibm][db2][jcc][Time:1250166698906][Thread:main][Connection@67df5ee8] 
    clearWarnings () called
[ibm][db2][jcc][Time:1250166698906][Thread:main][Connection@67df5ee8] 
    getMetaData () returned DatabaseMetaData@721cdee9
[ibm][db2][jcc][Time:1250166698906][Thread:main][Connection@67df5ee8] 
    setTransactionIsolation (2) called
[ibm][db2][jcc][Time:1250166698906][Thread:main][Connection@67df5ee8] 
    prepareStatement (select OBJ_ID from SCHEMA_NAME.OBJECT_CLASS 
    where CLASSNAME=?) called
[ibm][db2][jcc][Time:1250166698906][Thread:main][Connection@67df5ee8] 
    prepareStatement () returned PreparedStatement@6d0f1ee0
[ibm][db2][jcc][Time:1250166698906][Thread:main][PreparedStatement@6d0f1ee0] 
    setObject (1, java.lang.String) called

Back to top

Eclipse Modeling Framework fundamentals

As described on EMF project's Web site, EMF is "a modeling framework and code generation facility for building tools and other applications based on a structured data model." In addition to a number of utilities to manage models, EMF also provides a fairly complex and advanced set of capabilities for creating Eclipse plug-ins that provide a user-friendly graphical interface for viewing and manipulating the content of the modeled domain.

EMF can use a model to generate the following set of Eclipse plug-ins:

• model plug-in — contains the model definition
• model edit plug-in — enables manipulation of model instances
• model viewer plug-in — provides views that you can use to easily navigate and see details about the content of a model instance

The complete set of generated EMF plug-ins allow you to open, view, and even modify the modeled data files. The following sections describe how to create a model for the DB2 trace files, generate a set of EMF plug-ins based on that model, and use the plug-ins to help detect resource leaks.

Back to top

EMF model for DB2 trace files

Before you can create the EMF plug-ins that read and visually render the content of the DB2 trace files, you first need to create the corresponding EMF model. The model defines the relationships between elements of the domain (the domain in this scenario is the DB2 trace file elements) and the elements by themselves. You can generate the EMF model either from the UML model, or you can create it from scratch. Because the model for this scenario is not complicated and does not involve a long modeling approach, this article just covers how to create the model from scratch.

Before you can create the EMF model, you need at least one DB2 trace file. You need the trace file so that you can review its contents and identify the elements that it contains. Following are the elements that would be of most interest in regard to discovering resource (connections, statements, or result sets) leaks in an application:

• DB2 Server metadata — contains the DB2 Server product name and version, and the JDBC driver name and version
• DB2 connection details — contains the JDBC URL used for the connection and the username
• Operating system details — verbose information about the operating system
• JVM details — verbose information about the JVM used by the traced application
• The list of statements and result sets used by the traced application

The DB2 trace files also contain other elements, besides those listed above, that are important for the complete understanding of how the traced application is using the database server.

The scope of this article does not include details on creating EMF models, but you can find comprehensive instructions from the documentation link on the Eclipse Modeling Framework project's Web site (see Resources section).

Figure 1 shows an example of an EMF model created for the DB2 trace file elements that you need to search for a resource leak. These are the same trace file elements described in the above list.

Generate EMF plug-ins based on the EMF model

After you create the EMF model, you can use EMF to automatically generate a set of EMF plug-ins. These plug-ins contain code for reading, manipulating, and saving the content of the modeled trace files. One of these plug-ins is the Eclipse view that renders a visual representation of the content of the modeled DB2 trace files. You can use this plug-in to inspect the DB2 trace files and detect resource leaks.

To generate the EMF plug-ins from the EMF model file, do the following:

1. Open the EMF model file in your Eclipse environment.
2. Right click on the model.
3. From the context menu, select Generate All.

   This creates the following plug-ins:

   • edit
   • editor
   • tests

Customize the EMF editor plug-in to use a resource factory

By default, the instances of the EMF models are serialized into the files using the XML Metadata Interchange (XMI) serialized format. This means that the modeled files are interpreted as XML files. The EMF provides a feature to support files in this format.

You can also provide your own collection of Java classes that are able to read and save files in a customized format. These classes represent the EMF resource factory.

The base EMF framework class for the resource factory is the org.eclipse.emf.ecore.resource.Resource.Factory interface and its basic implementation is org.eclipse.emf.ecore.resource.impl.ResourceFactoryImpl. The factory is responsible for providing an instance of the org.eclipse.emf.ecore.resource.Resource class. The Resource class is the base class that contains methods for EMF resource manipulation, including methods for reading and saving content. To read the DB2 trace files, you only need to provide the custom implementation that reads the trace file format, not the XMI format.

To read the DB2 trace format, you must provide the parser for the DB2 trace file format (the domain specific language). Two of the options you have are:

• Write a parser from scratch.
• Use a parser generator framework (for example, ANTLR).

Regardless of the way you decide to create the parser, you need to understand the syntax of the DB2 trace files and design the grammar for this syntax.

DB2 trace files basic syntax

For this scenario, you are most concerned with the trace file elements that are used in your EMF model for detecting database resource leaks.

The following EMF elements are constructed from the corresponding DB2 trace file lines:

• The DB2 server metadata is represented by the sections:
  • Database product version
  • Driver name
  • Driver version
• DB2 connection details are in lines containing:
  • Successfully connected to server
  • User:
• Operating system details are contained in lines starting with:
  • Operating system
• JVM details are included in lines starting with:
  • Java
• The JDBC statements are listed in lines containing:
  • [Connection@<Connection ID>] createStatement
• The PreparedStatements are listed in lines containing:
  • [Connection@<Connection ID>] prepareStatement
• The result set details are listed in lines containing:
  • [ResultSet@<ResultSet ID>]

In order to detect which resources have been closed, the parser should look into the DB2 trace file to find the lines containing:

• [<Resource ID>] close () called

  Where, <Resource ID> is one of the following:

  • Statement
  • PreparedStatement
  • ResultSet

Create a custom EMF resources factory

Use the DB2 trace file elements outlined in the previous section to either design the grammar for use within ANTLR, or to write the custom parser that reads the content of the DB2 trace file and creates the instances of the EMF modeled classes. Those classes are generated by the EMF framework. They are within the EMF plug-in that contains the EMF model and extends the org.eclipse.emf.ecore.EObject base class. The following lines create an instance of the PreparedStatement modeled class and modify its properties:

PreparedStatement pStmt = traceFactory.createPreparedStatement();
pStmt.setID(statementID);
pStmt.setQuery(queryString);

Once the parser is implemented and creates the valid EMF model instance, it must be bound into the EMF resources factory.

Use the default instance of the org.eclipse.emf.ecore.resource.Resource.Factory.Registry class to register your custom EMF resource factory implementation as shown below:

Resource.Factory.Registry.INSTANCE.getExtensionToFactoryMap()
    .put(resourceURI.fileExtension(), new MyCustomDB2TraceResourceFactoryImpl());

Note that the code shown above is actually only a single line. It had to be split in order to display properly in this article.

Once the above line is present, all the resources are handled by your custom resources class. This means that you can open and analyze any of your DB2 trace files using your set of EMF plug-ins.

Back to top

Use the EMF plug-ins to detect resource leaks

Once you have executed your Java application with the DB2 JDBC option enabled, you can analyze the trace files with your new EMF plug-ins. To do so, simply start your Eclipse environment with your EMF plug-ins installed and then open one or more of the DB2 trace files.

When you open a DB2 trace file, the EMF plug-ins visually render its content in a tree view. When you select an element in the tree view, the properties view is activated and shows the details of what you have selected.

Figure 2 shows an example of the EMF tree view with the EMF element properties view below it.

You can use the tree view to navigate through the DB2 trace file. You can view when relevant elements such as JDBC connections, statements, prepared statements, and result sets, were created and which queries were issued.

When looking for resource leaks, one of the key things to look for is SQL queries associated with the JDBC object Statement/PreparedStatement, and whether the close() method has been invoked. The close() method is responsible for releasing all the database resources associated with the JDBC element that the method is invoked for. Well-written Java applications that use the JDBC framework should always release database resources as soon as they are no longer needed.

If the application does not close the JDBC connection, usually you need to investigate further to discover why. There are cases when a JDBC connection must not be closed before the application terminates (for example, the need to provide the JDBC connection pool). Therefore, you need to analyze each particular case one-by-one.

The DB2 tracing mechanism makes the analysis somewhat easier, but the trace files are complicated and hard to analyze manually. Using the EMF plug-ins to read the DB2 trace files and present the content visually, makes it much easier to isolate and identify the resources that are not being handled properly.

Figure 3 shows an example of using the EMF plug-ins to view the details of a JDBC connection with an ID of 174. The detailed properties view shows that the Close Called property for the connection is false. This indicates that the connection has not been closed by the application. So in this case, you would want to investigate why this happened. If you determine that the JDBC connection was not handled properly and the owning application "forgot" to close it, you have discovered a resource leak that needs to be fixed.

Back to top

Conclusion

This article showed how to use the IBM DB2 tracing mechanism together with the Eclipse Modeling Framework. It explained how you can generate EMF plug-ins that let you visually explore the DB2 trace files and quickly detect abnormal behavior in Java database-enabled applications. This saves you valuable time you might otherwise have to spend manually analyzing the DB2 trace files.

Resources

• DB2 JDBC tracing documentation in the DB2 Version 9 Information Center.
  
  
• "DB2 application development: Tracing with the DB2 Universal JDBC Driver" (developerWorks, June 2005).
  
  
• The Eclipse Modeling Framework project Web site.
  
  

About the author

Piotr Godowski is a software engineer for Tivoli in the IBM Software Group. He joined IBM in 2007 and works at IBM Poland. He works with IBM DB2® databases and is a passionate advocate of using the Eclipse platform to make software engineering tasks a bit easier.

Comments (Undergoing maintenance)

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• Introduction
• The DB2 tracing mechanism details
• Eclipse Modeling Framework fundamentals
• EMF model for DB2 trace files
• Use the EMF plug-ins to detect resource leaks
• Conclusion
• Resources
• About the author
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: DB2 serving ATOM feed
• Webcast: A smarter way to successful SOA - Information as a trusted strategic asset
• Briefing: Information on Demand
• Buy: DB2 Enterprise

My developerWorks community

Dig deeper into Information Management on developerWorks

DB2 pureScale

See how true transparent scaling architecture can be achieved on distributed platforms

Special offers