Important: Read the disclaimer before reading the article.

Introduction

In today's fast-paced world, just getting the job done often isn't enough. Getting the job done and doing it with the utmost expedience is key. The database world is by far not spared from such performance hunger. Customers want their data stored and retrieved quickly and efficiently. IBM® DB2® Universal Database^TM has a performance track record that in many cases sets it ahead of the pack of competing databases.

Unfortunately, many DB2 users might not be reaping the full benefit of DB2 performance because of some design flaw in the database or in the configuration. There are many articles and papers on DB2 performance tuning, and there are many good tools in DB2 for doing in depth analysis. The tool I'm going to describe in this article, Apache's JMeter, is another tool you can add to your toolbox to help you keep you an eye on your database's performance and to do simulation testing.

Back to top

About Apache JMeter

JMeter is an offering under the Apache Jakarta Project umbrella. JMeter is a Java^TM Swing-based desktop application designed to load-test and measure system performance. It was originally designed to test Web applications. However, it has been extended so that you can now test relational databases (via JDBC^TM). You can learn more about JMeter at its official site: http://jakarta.apache.org/jmeter/

Using JMeter, you can make sure that your database meets certain criteria such as being able to handle a certain number of simultaneous users. Simulation testing is key to a successful project. JMeter can simulate heavy loads against DB2. This is all made possible via JMeter's multithreaded framework. The framework allows for concurrent sampling by many threads. As you will see later in this article, JMeter can provide you with graphical feedback.

Back to top

Getting JMeter and setting up your testing environment

You can download the latest release of JMeter at http://jakarta.apache.org/builds/jakarta-jmeter/release/. At the time I wrote this article, the current JMeter version was 1.8. JMeter requires Sun's JDK1.4 (or higher) environment to work. The IBM JDK (in its current incarnation) does not seem to be compatible with JMeter. JMeter has been tested under various UNIX® flavors (including Solaris and Linux) and Windows® (98®, NT®, 2000®).

To facilitate our JDBC testing with DB2, copy the DB2 JDBC driver to the <jmeter install dir>\jakarta-jmeter\lib directory. For DB2 8.1, by default installation, the DB2 driver is located at: c:\program files\ibm\sqllib\java\db2java.zip. Oddly enough, JMeter will not pick up DB2's JDBC driver if it is named as a zip file. To get things to register, you must rename the file as a .JAR file. You might rename the file in the lib directory to db2java.jar. On the other hand, if you want to test the DB2 8.1 Java Common Client (JCC) driver, you simply need to copy the db2jcc.jar file to the JMeter lib directory (no renaming necessary).

Back to top

Putting some sand in our sandbox

Before we start playing with JMeter, we need to create a database with some test data.

1. From the DB2 command line processor, enter:

   create db loaddb

   
   
2. Connect to the loaddb database, as follows:

   connect to loaddb user db2admin using db2admin

   
   
3. Create a table that will hold our test data:

   create table loadtable (col1 varchar(25) not null, col2 varchar(25) not null, col3 varchar(25) not null)

   
   
4. Populate the table with many rows so that we can test our query performance using JMeter. To do this, I used a small JBDC program whose code can be seen in Listing 1 and which inserts 30,000 records into the loadtable table. Go ahead and compile and execute the code to populate loadtable with test data.

Listing 1. Inserting records into the database

 
package com.ibm.jmetertest; 
import java.sql.*; 
 
public class JMeterPopulator 
{ 
  public static void main(String[] args) 
  { 
    try 
    { 
      Class.forName("COM.ibm.db2.jdbc.app.DB2Driver"); 
	String dbName = "loaddb"; 
	String url = "jdbc:db2:" + dbName; 
	String userName = "db2admin"; 
	String password = "db2admin"; 
	Connection con 
          = DriverManager.getConnection(url,userName, password); 
	int numOfTestRecords = 30000; 
	PreparedStatement statement 
          = con.prepareStatement("INSERT INTO LOADTABLE VALUES(?,?,?)"); 
	for (int i =0; i<numOfTestRecords; i++) 
	{ 
	  statement.setString(1,"Col1Test-" + i); 
	  statement.setString(2,"Col2Test-" + i); 
	  statement.setString(3,"Col3Test-" + i); 
	  statement.executeUpdate(); 
	  System.out.println(i); 
	} 
    } 
    catch(Exception e) 
    { 
       System.out.println("An error has occurred: " + e.toString()); 
       e.printStackTrace(); 
    } 
  } 
} 
 

Now that we have a bunch of records, let's test query performance with JMeter.

Back to top

Enter JMeter

To fire up JMeter, run the jmeter.bat file in the <jmeter install dir>\jakarta-jmeter\bin directory.

When JMeter starts, you see the main screen with a pane on the left hand side that has no test plans defined (see Figure 1). In JMeter, a test plan consists of one or more thread groups. Threads represent simulated users and a thread group is a list of simulated users.

Let's start by creating a thread group. Do this by right clicking on the TestPlan node and choosing Add => Thread Group. See Figure 2.

In the next screen (see Figure 3), you will be able to define the number of threads or simulated users that you want to be part of your test. The Ramp-Up Period specifies how long JMeter will wait before exposing a simulated user to the database. For example, if we have 30 simulated users and a ramp-up period of 15 seconds, we will be exposing the database to 2 users per second (30 users/15 seconds). If you specify a ramp-up period of 0, JMeter automatically starts all of the simulated users at once. The Loop Count defines how many times JMeter will repeat our test. In this screen, we can also rename our thread group. Go ahead and enter the settings you see in Figure 3, which gives us 30 users who all start simultaneously. The test will run 100 times.

Back to top

Talking to our database

Now, we specify how we will communicate with DB2 UDB. We will do this in the form of a JDBC Request. Right click on the Thread Group you created (DB2 Test) and choose Add => Sampler => JDBC Request.

Next you will see a screen in which we can define certain DB2-specific information that enables conversation with our DB2 database. This information includes our JDBC URL, JDBC driver, and our user name and password. On this screen, we can also specify information about pooling, as well as the SQL query which we want to apply to our database.

Go ahead and use the values shown in Figure 5 for our test. For those a bit unfamiliar with pooling, it is simply the maintenance of a group of database connections for reuse by applications. Database connection pooling is commonly seen in Web environments where Web users connect and disconnect frequently. In the database world, obtaining a database connection is an expensive operation. One must create the connection, maintain it, and then release it when one is done with the connection. Using JMeter, one can analyze the benefits of connection pooling.

Back to top

Listening to the results with listeners

At this point, we are actually done with defining a JMeter test that will test our DB2 database. The problem is, at this point, we have no way to see what our test results are. To do this, right click JDBC Request and choose Add => Listener => Graph Results as shown here:

Back to top

Putting your foot on the JMeter pedal

Now is the time to start your JMeter test. From the main menu choose Run and then Start, as shown in Figure 7.

Notice that a green square in the upper right hand corner of the screen lights up. This indicates that a test is running. At this point, you should see the graph being populated with information about how long (on average) it took to perform your designated query (see Figure 8).

You might consider playing around with the number of simultaneous users (threads in your ThreadGroup) to see how increasing the user load affects the average time it takes to execute the same query. You might try adding indexes to the columns touched by the query and see visually if they will benefit your particular database environment.

JMeter also allows you the ability to save your test plans by using File => Save Test Plan to specify where you want to save your JMeter test plan.

Back to top

Is it really working?

Unfortunately, the error reporting in JMeter isn't that great. In fact, when I was playing around the tool, I thought it was working, but it wasn't. Accordingly, you should consider using the assertions feature. Assertions allow JMeter users to scrutinize the responses from the database server and "assert" that certain data is resident in the response.

Before we do our assertion, we need to know what results we expect from our query. We can do this by running our query in the command line processor, as shown in Figure 9.

To add an assertion, right click on your JDBC Request and choose Add => Assertions => Response Assertion as shown in Figure 10.

In this screen, you can add text assertions. Here, click Add and then add the text Col1Test-20015 to the Patterns to Test field, because we know this text should be in our returned results (see Figure 11).

Now, we need to add a Listener to listen for our response assertion. Right click on the JDBC Request and choose Add => Listener => Assertion Results as shown in Figure 12.

You can then see an Assertion Results screen (Figure 13) where you can even specify a data file to write your assertion data to.

Now, when you run your test object, you should see the Assertions Results screen populated with either negative or positive assertions depending on if the conditions in your assertion were met.

Figure 14 shown below shows assertions that were successful.

If we change our assertion to look for Col1Test-40000 (which we know is not in our database data), we would get a similar screen to what is shown in Figure 15.

Back to top

Conclusion

JMeter can be used to simulate heavy loads on your system, server, and network. In this article, we used JMeter to analyze query performance of our DB2 database. We have barely scratched the surface of what JMeter can do. JMeter can be used to create some fairly complex and informative test plans. JMeter gives you instant visual feedback about your system's performance in the form of graphs and splines. The data provided by JMeter can then be used to make sure that what is under the hood of your enterprise operation is tuned for optimal performance.

Back to top

Acknowledgements

Thanks to Larry Menard and Robert Taniwa of the IBM Toronto labs for their JDBC expertise and their help with this article.

Back to top

Disclaimer

This article contains sample code. IBM grants you ("Licensee") a non-exclusive, royalty free, license to use this sample code. However, the sample code is provided as-is and without any warranties, whether EXPRESS OR IMPLIED, INCLUDING ANY IMPLIED WARRANTY OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE OR NON-INFRINGEMENT. IBM AND ITS LICENSORS SHALL NOT BE LIABLE FOR ANY DAMAGES SUFFERED BY LICENSEE THAT RESULT FROM YOUR USE OF THE SOFTWARE. IN NO EVENT WILL IBM OR ITS LICENSORS BE LIABLE FOR ANY LOST REVENUE, PROFIT OR DATA, OR FOR DIRECT, INDIRECT, SPECIAL, CONSEQUENTIAL, INCIDENTAL OR PUNITIVE DAMAGES, HOWEVER CAUSED AND REGARDLESS OF THE THEORY OF LIABILITY, ARISING OUT OF THE USE OF OR INABILITY TO USE SOFTWARE, EVEN IF IBM HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.

Top of page

Back to top

Download

Information about download methods

About the author

Kulvir Singh Bhogal works as an IBM consultant, devising and implementing Java-centric solutions at customer sites across the nation. You can reach Kulvir at kbhogal@us.ibm.com.

Comments

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• Introduction
• About Apache JMeter
• Getting JMeter and setting up your testing environment
• Putting some sand in our sandbox
• Enter JMeter
• Talking to our database
• Listening to the results with listeners
• Putting your foot on the JMeter pedal
• Is it really working?
• Conclusion
• Acknowledgements
• Disclaimer
• Download
• 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