This article is written for IBM® DB2® Universal Database^TM for Linux, UNIX®, and Windows®.

Introduction

Tuning databases is a constant challenge to database administrators. Application tuning is one approach, but in most production systems DBAs can make few or no changes to the source code, and thus are limited in their ability to tune applications. This is especially true when they are using third party tools. So, often the most effective approach to tuning is to attack the problem at its root, the SQL statements themselves. You can frequently achieve the greatest performance gains by finding which SQL statements are consuming the most resources and then determine what can be done to bring down resource consumption.

As a general rule, a database will be tuned for optimal performance when it is installed for the first time, but frequently things start to get slower as time goes by. This is especially true in systems with high volumes of data, such as decision support systems. Users begin to see slow-downs caused by such factors as lock escalations, full table scans, and sorts that are forced to disk rather than occurring in memory. When that occurs, it's a good time to look at your SQL and see what can be improved.

This article addresses the issue of tuning the SQL being used to access a database for a typical activity. To simplify the problem of how to monitor the SQL statements in an application, I have written a Java program called DB2Trace.java that assists you in using the DB2 Universal Database event monitor to identify which SQL statements are consuming the most resources.

The DB2 event monitor is a facility that comes with DB2 Universal Database for Linux, UNIX and Windows. It writes data to a file or a named pipe whenever a specified event occurs in the database, and is a way to trace a particular activity on a database. The DB2Trace.java program takes the output from a statements event monitor and inserts it into a table called DB2TRACE in your database. You can then use SQL queries to analyze that data and create the appropriate indexes for problem SQL statements, or make other changes to improve performance.

When you're ready, download the code to try it yourself.

Back to top

Prerequisites

The program described in this article was tested using DB2 Universal Database Enterprise Edition Version 7.2.2 and JAVA 1.2.2. The JAVA should be 1.2 or higher to support JDBC 2.0.

Although the program was tested on UNIX, it should run on Windows without any change. Note that the commands illustrated below are for a UNIX command shell environment and would need to be modified appropriately for Windows.

Back to top

Running the Event Monitor

First, you must create the event monitor and run the monitor to collect the data you will analyze. In the steps below, substitute your own database name for dbname, your own user ID for username, and your own password for password. Note that rkmon is the name of the event monitor used in this example this could be replaced with any other name.

1. Open a new DB2 Command Line Processor session and execute the following DB2 UDB commands:
   
   

   db2 => connect to dbname user username using password db2 => update monitor switches using statement on db2 => create event monitor rkmon for statements write to file '/tmp' db2 => set event monitor rkmon state=1

   
   

   Keep this session open until the database activities are completed. Make sure that /tmp directory is sufficiently large to hold the trace files. The /tmp directory is chosen as all the users have access to this directory; however this directory could be replaced with any other directory (make sure access permissions are given to DB2). The size of the directory depends upon the number of SQL statements that the user wants to capture. A good starting point would be 500 MB.

2. Perform your normal database activities until completion of the period you wish to monitor. The monitored period could during a problem operation or a typical database activity process. You should see a group of files in the /tmp directory with a ".evt" extension. These are your event monitor files.
3. Go to the session you opened in Step1 and issue the statements below:
   
   

   db2 => set event monitor rkmon state=0 db2 => terminate

   
   
4. Execute the following command from a normal command prompt:
   
   

   $ db2evmon -path /tmp > sqltrace.txt

   
   
   All the captured SQL statements and their details will be captured in the single file sqltrace.txt.

Back to top

Running DB2Trace.java

You have collected your data in the sqltrace.txt file. The next steps will enable you to analyze that data.

1. Download the DB2Ttrace.java program into the /tmp directory. Listing 1 below is a copy of the DB2Trace.java program.
   
   

   Listing 1. DB2Trace.java /*(c) Copyright IBM Corp. 2003 All rights reserved. */ /* */ /*This sample program is owned by International Business Machines */ /*Corporation or one of its subsidiaries ("IBM") and is copyrighted */ /*and licensed, not sold. */ /* */ /*You may copy, modify, and distribute this sample program in any */ /*form without payment to IBM, for any purpose including developing, */ /*using, marketing or distributing programs that include or are */ /*derivative works of the sample program. */ /* */ /*The sample program is provided to you on an "AS IS" basis, without */ /*warranty of any kind. IBM HEREBY EXPRESSLY DISCLAIMS ALL */ /*WARRANTIES EITHER EXPRESS OR IMPLIED, INCLUDING, BUT NOT LIMITED TO*/ /*THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTIC-*/ /*ULAR PURPOSE. Some jurisdictions do not allow for the exclusion or */ /*limitation of implied warranties, so the above limitations or */ /*exclusions may not apply to you. IBM shall not be liable for any */ /*damages you suffer as a result of using, modifying or distributing */ /*the sample program or its derivatives. */ /* */ /*Each copy of any portion of this sample program or any derivative */ /*work, must include a the above copyright notice and disclaimer of */ /*warranty. */ /* */ import java.io.*; import java.sql.*; /** * DB2Trace program reads the output from the db2event monitor * and inserts the data into a database. * * Creation Date: January 15 2003. */ class DB2Trace { /** * Usage: java DB2Trace sqltrace.txt jdbc:db2:db2mwh userid * password * sqltrace.txt is the input file * jdbc:db2:db2mwh is the database jdbc url connection * username is the user ID * password is the password * @param args[] * @exception IOException, SQLException */ public static void main (String args[]) throws IOException, SQLException { FileWriter fw = new FileWriter("DB2Trace.sql"); PrintWriter pw = new PrintWriter(fw); BufferedReader in = new BufferedReader(new FileReader(args[0])); String s = ""; String sqlString = ""; boolean textYes = false; int exists = 0; COM.ibm.db2.jdbc.app.DB2Driver driver = new COM.ibm.db2.jdbc.app.DB2Driver(); java.sql.DriverManager.registerDriver(driver); Connection conn = java.sql.DriverManager.getConnection(args[1], args[2], args[3]); Statement stmt = conn.createStatement(); ResultSet rset = stmt.executeQuery("SELECT COUNT(*) FROM SYSCAT.TABLES WHERE TABSCHEMA=USER AND " + "TABNAME='DB2TRACE'"); while (rset.next()) { exists = rset.getInt(1); } if (exists > 0) stmt.executeUpdate("DROP TABLE DB2TRACE"); stmt.executeUpdate("CREATE TABLE DB2TRACE( OPERATION VARCHAR(30),SQLTXT VARCHAR(32000)," + "STARTTIME VARCHAR(30),STOPTIME VARCHAR(30) ," + "EXECTIME VARCHAR(20)," + "USRCPU VARCHAR(20)," + "SORTS VARCHAR(20)," + "TOTSORTTIME VARCHAR(20)) IN TEST "); stmt.executeUpdate("CREATE INDEX DB2TRACE_CPU ON DB2TRACE(USRCPU)"); PreparedStatement p = conn.prepareStatement("INSERT INTO DB2TRACE(OPERATION,SQLTXT,EXECTIME," +" STARTTIME,STOPTIME,USRCPU,SORTS,TOTSORTTIME) VALUES(?,?,?,?,?,?,?,?)"); while ((s = in.readLine()) != null) { if (s.startsWith(" Operation: ")) { p.setString(1, s.substring(13, s.length())); } if (s.startsWith(" Text :")) { textYes = true; p.setString(2, s.substring(13, s.length())); } if (s.startsWith(" Exec Time:")) { p.setString(3, s.substring(14, s.length() - 7)); } if (s.startsWith(" Start Time: ")) { p.setString(4, s.substring(14, s.length())); } if (s.startsWith(" Stop Time: ")) { p.setString(5, s.substring(14, s.length())); } if (s.startsWith(" User CPU:")) { p.setString(6, s.substring(13, s.length() - 7)); } if (s.startsWith(" Sorts:")) { p.setString(7, s.substring(8, s.length())); } if (s.startsWith(" Total sort time:")) { p.setString(8, s.substring(18, s.length())); if (textYes == true) { p.executeUpdate(); textYes = false; } } } pw.close(); stmt.close(); p.close(); } }

   
   
2. Compile the program as follows:
   
   

   $ javac -classpath /export/home/seecomm/sqllib/java/db2java.zip:. DB2Trace.java

   
   

   NOTE: In this example JDBC 2.0 is used. Use the db2java.zip from the sqllib/java12 directory. DB2 V8 uses JDBC 2.0 as default; however for DB2 V7 run the file usejdbc2.bat (Windows) and usejdbc2(UNIX).

3. In this example the application driver is used which requires the database to be cataloged in the DB2 database directory. The application driver or thick client is a type 2 driver. This means that the Application Development Client or CAE has to be installed on the client's workstation.
4. Make sure that 32 KB page tablespace TEST is created, 32 KB page system temporary tablespace for sorts and 32 KB page bufferpool associated to these tablespaces. The tablespaces and bufferpool should be created before the program is run. These could be created with following DB2 commands:
   
   

   CREATE Bufferpool TESTBP SIZE 1000 PAGESIZE 32 K

   
   

   (Stop and start the instance so that the bufferpool can be activated and associated to a tablespace.)

   CREATE REGULAR TABLESPACE TEST PAGESIZE 32 K MANAGED BY SYSTEM USING ('/export/seecomm/') EXTENTSIZE 16 OVERHEAD 24.1 PREFETCHSIZE 32 TRANSFERRATE 0.9 BUFFERPOOL TESTBP

   
   
5. Now you're ready to run the program:
   
   

   $cd /tmp $ java -classpath /export/home/seecomm/sqllib/java/db2java.zip:. DB2Trace sqltrace.txt jdbc:db2:db2mwh username password

   
   

   sqltrace.txt is the input file.
   jdbc:db2:db2mwh is the database JDBC URL connection.
   username is the user ID.
   password is the password.

   In this step, the SQL text, execution time, start time, stop time, user CPU time, sorts, and total sort time for each SQL statement are captured in the database table DB2TRACE.

Back to top

Analyzing the output

Since the DB2Trace program has stored the needed information in a database table (DB2TRACE), you can run queries against it to determine the offensive time-consuming SQL statements.

Four types of SQL statements need to be identified. Before running the queries below to identify these statements, assign at least 256 pages for Application Heap Size (applheapsz) in the database configuration parameters.

• SQL statements consuming the most execution time, ordered by execution time, descending order. In order to identify these statements, use the following SQL select statement:
  
  

  select sqltxt, exectime "ExecutionTime(sec)" from db2trace where operation not in ('Static Commit', 'Static Rollback', 'Prepare', 'Open', 'Describe', 'Compile') order by decimal (exectime) desc fetch first 10 rows only

  
  
• Most frequently run SQL statements, ordered by frequency, descending order. These statements can be determined with the following query:
  
  

  select distinct(sqltxt),count(*) Count from db2trace where operation not in ('Static Commit', 'Static Rollback', 'Prepare', 'Open', 'Describe', 'Compile') group by sqltxt order by count(*)desc fetch first 10 rows only

  
  
• Most CPU-consuming SQL statements, ordered by CPU time, in descending order. These can be determined with the following query:
  
  

  select sqltxt ,usrcpu "UserCPU(sec)" from db2trace where operation not in ('Static Commit', 'Static Rollback', 'Prepare', 'Open', 'Describe', 'Compile') order by usrcpu desc fetch first 10 rows only

  
  
• SQL statements consuming the most sort time, ordered by total sort time, descending order. Find these statements with the following query:
  
  

  select sqltxt ,totsorttime "TotalSortTime(ms)" from db2trace where operation not in ('Static Commit', 'Static Rollback', 'Prepare', 'Open', 'Describe', 'Compile') order by decimal(totsorttime) desc fetch first 10 rows only

  
  

Capture the SQL statements in each category and place them in a file called tune.sql. The frequency at which each statement in the workload is to be executed can be changed by inserting the following line into the file:

 
--#SET FREQUENCY <x> 

where <x> is the number of times the subsequent SQL statement is to be executed. Your tune.sql file will look something like this:

 
--#SET FREQUENCY 100 
SELECT COUNT (*) FROM EMPLOYEE; 
SELECT * FROM EMPLOYEE WHERE LASTNAME='HAAS'; 
--#SET FREQUENCY 1 
SELECT AVG (BONUS), AVG (SALARY) FROM EMPLOYEE 
GROUP BY WORKDEPT ORDER BY WORKDEPT; 

After these SQL statements are copied to tune.sql, check to see if any of the SQL statements have parameter markers (?) in the WHERE clauses. Change the parameter markers to appropriate data type values so that the SQL statement can be run without any errors.

To determine what indexes might improve performance, execute the index advisor as follows:

 
$cd /tmp 
$db2advis -d test -i tune.sql -t 0 -o tuneidx.sql 

All the recommended indexes will be placed in the file tuneidx.sql. Edit the file to add a connect statement at the beginning:

 
connect to dbname user userid using password; 

Add the following line to the end of the file:

 
terminate; 

Now you can run the file to create the recommended indexes:

 
$db2 -tf tuneidx.sql -z tuneidx.log 

where tuneidx.log captures all the output of the tuneidx.sql.

Back to top

Conclusion

There should be significant difference in the performance of the database now. The statistics collected in the DB2TRACE table could be used to as input to a spreadsheet, and will assist you in gaining a better understanding of the exactly what resources are being used by the SQL statements. After tuning the SQL statements, the database and database manager parameters can be tuned for better performance.

Back to top

Download

Information about download methods

About the author

Ramakrishna Kolluru is a Senior Database Engineer with SeeCommerce.com in Palo Alto, California. He is a certified Oracle 9i and DB2 7.1 DBA. SeeCommerce is a leader in Supply Chain Performance Management. You can reach him at rkolluru@seecommerce.com.

Comments (Undergoing maintenance)

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• Introduction
• Prerequisites
• Running the Event Monitor
• Running DB2Trace.java
• Analyzing the output
• Conclusion
• 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

New Offerings

Speed innovation with an affordable, dynamic infrastructure for software delivery

Special offers