Tuning DB2 Universal Database Using the Statement Event Monitor

This article describes a technique for analyzing output from the DB2 Universal Database statement event monitor in order to tune your SQL and determine what indexes should be created to improve performance. A sample program is provided.

Ramakrishna Kolluru, Senior Database Engineer, SeeCommerce.com

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.



13 March 2003

This article is written for IBM® DB2® Universal DatabaseTM 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.


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.


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.

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.


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.


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.


Download

DescriptionNameSize
Code sampleDB2Trace.java5 KB

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=13799
ArticleTitle=Tuning DB2 Universal Database Using the Statement Event Monitor
publish-date=03132003