DB2 SQL Statement Trace Analyzer
Original Publication Date: 2001-Nov-01
Included in the Prior Art Database: 2003-Jun-20
DB2 SQL Statement Trace Analyzer DB2/UDB SQL Statement Analyzer Disclosed is a process for determining the DB2/UDB SQL statements that are consuming server resources. This process takes the output of the db2evmon utility using the statement event monitor trace. DB2/UDB customers have large numbers of SQL statements that are delivered to the database. Turning on Event Monitors aids, but it does not describe which SQL statement is the most expensive within the application. The event monitor merely spits out the SQL statement metrics (start, stop time, CPU seconds, sort activity, rows reads and written). It ignores the fact that most SQL statements are executed within loops, or by many applications. Statements that seem to be fast, but that are executed a tremendous amount of time can easily become the statement that would provide the best results from tuning. First thing that needs to be done is to read the output and calculate the elapsed time and write out the detail record. After all of the records have been written, sort the detail data by SQL and Operation. Read the sorted data and obtain the minimum, average, maximum, total values for the various data elements. Keep a running total of the number of executions. Print these records out when the Operation or SQL statement changes values, and reset the accumulators.