Browse Prior Art Database

DB2 SQL Statement Trace Analyzer

IP.com Disclosure Number: IPCOM000015293D
Original Publication Date: 2001-Nov-01
Included in the Prior Art Database: 2003-Jun-20
Document File: 2 page(s) / 67K

Publishing Venue

IBM

Abstract

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.

This text was extracted from a PDF file.
At least one non-text object (such as an image or picture) has been suppressed.
This is the abbreviated version, containing approximately 81% of the total text.

Page 1 of 2

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.

The two files that are generated can be imported into a variety of display tools that allow for easy manipulation of the data (i.e. Spreadsheet progr...