Reporting Bad SQL Code in DB2

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.