Reporting Bad SQL Code in DB2

June 27, 2003

A production DBA often faces the problem of a slow database caused by badly written SQL code. Even when tested by developers, SQL code can generate many performance problems. A regular pre-production test made on a manually generated, small dataset, when run on the real, big production dataset, can cause system breakdown. Some complex executions need hours to complete. In that situation the DBA needs a way to collect and identify slow-running SQL statements, and send the most critical statements back to developers for additional change.

This article covers:

• Database and Event Monitor Switches
• Describing a Problem
• Finding a Top Sessions
• Reporting a Bad SQL Code
• Conclusion

Database and Event Monitor Switches

There are many different troubleshooting techniques for discovering and diagnosing problems caused by badly written SQL statements. One of them is enabling special monitor switches.

Monitor switches are used to instruct the database manager to collect applications statistics and performance. The Monitoring period is defined by starting and stopping the event monitor switch. Collected information is then written into a dedicated serial file, with full details of each SQL call that was executed. The Event monitor statistic files has to be further processed using the db2trace database utility and the result file is readable ASCI file.

Monitor switches can be controlled at the database manager level (database monitor switches) for the whole database or at the individual session level (event monitor switches).

Database manager monitor switches

Displaying the current status of the database manager monitor switches:

# db2 "get dbm monitor switches"
 
 Default database monitor switches
   Buffer pool                         (DFT_MON_BUFPOOL) = OFF
   Lock                                   (DFT_MON_LOCK) = OFF
   Sort                                   (DFT_MON_SORT) = OFF
   Statement                              (DFT_MON_STMT) = OFF
   Table                                 (DFT_MON_TABLE) = OFF
   Unit of work                            (DFT_MON_UOW) = OFF

• DFT_MON_BUFFERPOOL - collecting number of reads and writes to the database
• DFT_MON_LOCK - collecting locks wait statistics and deadlock occurrence
• DFT_MON_SORT - collecting sort statistics
• DFT_MON_STMT - collecting SQL statements information
• DFT_MON_TABLE - collecting table statistics, rows written and rows read
• DFT_MON_UOW - collecting timing information about Unit of Works

DB2 database version 8 has one new monitor switch DFT_MON_TIMESTAMP for collecting timestamps of monitored data. Database monitor switches are by default disabled and database manager will not collect any statistics.

Event monitor switches

On an application level, we can create private monitor switches independent of the database manager and other applications. Applications inherit their monitor switch settings from the database manager when connecting to a database.

Displaying current application event monitor switches:

db2 => get monitor switches
 
            Monitor Recording Switches
 
Switch list for node 0
Buffer Pool Activity Information  (BUFFERPOOL) = OFF  
Lock Information                        (LOCK) = OFF
Sorting Information                     (SORT) = OFF
SQL Statement Information          (STATEMENT) = OFF
Table Activity Information             (TABLE) = OFF
Unit of Work Information                 (UOW) = OFF

List of event monitor filters:

• DATABASE - logging database deactivation
• TABLES - logging changes for each accessed tables
• DEADLOCKS - logging deadlock occurance
• TABLESPACES - logging tablespace activity
• BUFFERPOOLS - logging bufferpool activity
• CONNECTIONS - logging connection activity
• STATEMENTS - logging finished SQL statement information
• TRANSACTIONS - logging commit and rollback activity

The syntax for event switches has an optional <<where>> conditional parameter, for further restricting data collection. Possible conditions are:

• APPL_ID - application marker
• AUTH_ID - authorization marker
• APPL_NAME - application program name
• LOCAL - Event monitor reports activity only for the node where it is running
• GLOBAL - Event monitor reports activity from all nodes

Activating SQL statement collecting for a specified user will help us to find potential problems with application SQL code. The typical reason for system slowdown is long-running jobs that contain one or more expensive un-tuned SQL statement.