by Jagadish Ramu

Detecting performance bottlenecks is an important task in optimizing database operations in an application. One way to do that is to trace the database operations of the application. This information can help you fine tune the database calls that the application makes and in this way improve the application's performance. This Tech Tip will demonstrate how to use a tool called P6Spy to trace the database requests issued by an application running with the GlassFish v2 application server. Specifically, you'll learn how to trace SQL statements from an application that uses GlassFish's JDBC Connection Pool.

P6Spy is an open source Java tool that intercepts and logs all database statements in an application that uses Java Database Connectivity (JDBC). The tool can be used with any compliant JDBC driver. In fact, P6Spy itself is a JDBC-compliant driver that acts as wrapper for any JDBC-compliant driver. It can be used with a variety of application servers including GlassFish and with various databases such as Oracle, DB2, SQL Server, MySQL, and Java DB. The tool can be seamlessly integrated with GlassFish, that is, you don't need to add or change any source code. Also, the tool is highly configurable and you can switch it on only when you need to use it.

Using P6Spy With GlassFish

In this tip, you'll use P6Spy to trace JDBC-based database calls made by an application running with GlassFish v2. For the application, let's use CustomerCMP, which is available as a sample project in the NetBeans IDE. The application uses a Java DB database. It also uses a JDBC connection pool.

Download and Configure

If you haven't already done so, download and install the following:

• GlassFish v2
• P6Spy
• NetBeans IDE 6.1

Start GlassFish.

Next, you need to configure things so that P6Spy can be used with GlassFish. To do that:

• Create a directory named p6spy below the GlassFish v2 installation directory. For example if you installed GlassFish v2 in C:\Sun\AppServer, create a directory C:\Sun\AppServer\p6spy.
• Copy the spy.properties file from the P6Spy installation directory to the p6spy directory that you just created. The spy.properties file is used to control various functions of the P6Spy driver. For example, the logfile property in the spy.properties file specifies the name of the file in which P6Spy will log SQL statements. By default the file is specified as spy.log, but you can change it to another file name.
• Comment out all realdriver property assignments in the spy.properties file. The realdriver property specifies the name of the database driver that P6Spy will work with. Do this because GlassFish uses datasource which are defined in realDataSource property of the P6Spy connection pool.
• Add the location of the p6spy.jar file and the path to spy.properties to GlassFish's classpath -- for example, in the <java-config classpath-prefix> element in the GlassFish domain.xml file. You can use the GlassFish Admin Console to do that, as follows:
  • Login to the Admin Console.
  • Select Admin Server in the left navigation bar. This will display the Application Server page.
  • Select the JVM Settings tab then the Path Settings tab.
  • Enter the location of the p6spy.jar file and the path to spy.properties in the Classpath Prefix box. See for example, Figure 1.

    Figure 1. Adding the Location of the p6spy.jar File and the Path to spy.properties to GlassFish's Classpath

  • Click the Save button.

Restart GlassFish.

Create a JDBC Connection Pool and Resource

In order to use P6Spy with GlassFish, you need to create a JDBC resource and P6Spy-based connection pool. GlassFish v2 simplifies this task by providing templates to create a connection pool and resource for various databases and JDBC drivers. You can find the templates in the lib/install/templates/resources/jdbc directory below the GlassFish v2 installation directory. For example, javadb_type4_datasource.xml is the template for a Java DB Type 4 driver. All you need to do to use a template is specify appropriate values for properties such as user, password, databaseName, and serverName.

To simplify things even further, you can download and use the p6spy_datasource.xml file that accompanies this tip. The p6spy_datasource.xml file is based on the javadb_type4_datasource.xml template and specifies a P6Spy connection pool and a Java DB (Derby) datasource. To create the connection pool and resource using the p6spy_datasource.xml file, enter the following command on a command line:

   GF_INSTALL/bin/asadmin add-resources p6spy_datasource.xml_path

where GF_INSTALL is the directory where you installed GlassFish and p6spy_datasource.xml_path is the complete path to the p6spy_datasource.xml file.

Alternately, you can use the Admin Console to create the JDBC resource and connection pool as follows:

• Select Resources in the left navigation bar. This displays the Resources page.
• Select JDBC, then JDBC Resources. This displays the JDBC Resources page.
• Click the New button on the JDBC Resources page. This displays the New JDBC Resource page.
• Enter the resource-name as jdbc/p6spy-resource in the JNDI name field.
• Select the pool-name p6spy_pool in the Pool Name drop-down list.
• Click the OK button.

Now the p6spy-resource is ready to to intercept the database calls of resource jdbc/__default.

Note that you can use other types of datasources. To do that, keep the p6spy-resource's type as javax.sql.DataSource and the datasource-class as com.p6spy.engine.spy.P6DataSource. The realDataSourceName can be of type javax.sql.DataSource, javax.sql.ConnectionPoolDataSource, or javax.sql.XADataSource

Create the CMP Project

Recall that this tip uses CustomerCMP as the sample application and that the application uses a Java DB database and a JDBC connection pool. The CustomerCMP application is available as a sample project in the NetBeans IDE. To use the application, you need to create the project as follows:

1. Start the NetBeans IDE.
2. Select New Project from the main menu, then select Enterprise below Samples in the Categories list. Then select CustomerCMP in the Projects list. See Figure 2.

   Figure 2. Creating the CustomerCMP Project

3. Click the Next button.
4. Specify a location for the project and click the Finish button.

One other thing you need to do before you can use the application with the P6Spy-based resource is to point to the resource in the persistence.xml file. To do that, expand Configuration files below CustomerCMP-ejb in the NetBeans Projects list. You should see the persistence.xml file in the expanded list of Configuration files. Open persistence.xml and change the setting for <jta-data-source> to jdbc/p6spy_resource, as shown in Figure 3.

Figure 3. Setting the jta-data-source

Last, build the project by right-clicking on the CustomerCMP project in the project list and selecting Build. Then deploy the project by right-clicking the CustomerCMP project in the project list and selecting undeploy and deploy.

Run the Application

To run the CustomerCMP application, point your browser to http://localhost:8080/customer/. You should see a page that contains the contents shown in Figure 4.

Figure 4. CustomerCMP Page

Click the Create New Customer link and enter the data for a new customer, Jagadish Prasath, as shown in Figure 5. Then click the Submit button

Figure 5. Creating a New Customer

This request uses the P6Spy connection pool to persist the customer details in the Java DB database. P6Spy logs the SQL statements used for the database operations. You can find the spy log in the domains\domain1\config directory below the GlassFish installation directory. If you examine the log file, you'll see the following entry , which corresponds to the actions required to create the new user Jagadish Prasath:

   1215548015250|47|7|statement|INSERT INTO CUSTOMER (customerid, FIRSTNAME, LASTNAME) VALUES (?, ?, ?)| INSERT INTO CUSTOMER (customerid, FIRSTNAME, LASTNAME) VALUES ('1', 'Jagadish', 'Prasath')

Let's look at some more P6Spy logging by creating another user and then searching for users. First create a new user in the same was as you did previously. Name the new user Arun Prasath. Then return to the initial page of the customerCMP application and click the Search for Customer link. You should see the search page. Do a search on the last name Prasath as shown in Figure 6.

Figure 6. Searching for Customers

You should now see the following entries added to the spy log file:

   1215549723390|15|11|statement|INSERT INTO CUSTOMER (customerid, FIRSTNAME, LASTNAME) VALUES (?, ?, ?)|INSERT INTO CUSTOMER (customerid, FIRSTNAME, LASTNAME) VALUES ('2', 'Arun', 'Prasath')
   1215549975390|125|12|statement|SELECT customerid, FIRSTNAME, LASTNAME FROM CUSTOMER WHERE (LASTNAME = CAST (? AS VARCHAR(32672) ))|SELECT customerid, FIRSTNAME, LASTNAME FROM CUSTOMER WHERE (LASTNAME = CAST ('Prasath' AS VARCHAR(32672) ))
   1215549975390|-1||resultset|SELECT customerid, FIRSTNAME, LASTNAME FROM CUSTOMER WHERE (LASTNAME = CAST ('Prasath' AS VARCHAR(32672) ))|CUSTOMERID = 1, FIRSTNAME = Jagadish, LASTNAME = Prasath
   1215549975390|-1||resultset|SELECT customerid, FIRSTNAME, LASTNAME FROM CUSTOMER WHERE (LASTNAME = CAST ('Prasath' AS VARCHAR(32672) ))|CUSTOMERID = 2, FIRSTNAME = Arun, LASTNAME = Prasath

Note that P6Spy gives you flexibility in what you trace. For example, you can trace a second datasource by creating another p6spy-based resource and then referring to the second datasource that you want to trace. Also, you can customize which tables to trace by specifying appropriate values for the filter, include, and exclude properties in the spy.properties file. You can also request verbose logging through the excludecategories property in the spy.properties file.

In addition, the P6Spy properties are dynamically reconfigurable. Simply set the reloadproperties property in the spy.properties file to true and the reloadpropertiesinterval property to the number of seconds you want as the time interval between property reloads.

Further Reading

• Templates to create JDBC Connection Pool & Resource
• GlassFish Project - Connection Pooling

About the Author

Jagadish Ramu is is an engineer in the GlassFish Application Server team. He works in the areas of JDBC, connection pooling, and connectors. He has been involved with the connectors team at Sun since mid-2005. Jagadish holds an M.Tech degree from BITS Pilani, India.