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
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
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:
The syntax for event switches has an optional <<where>> conditional parameter, for further restricting data collection. Possible conditions are:
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.