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.

Marin Komadina
Marin Komadina
Marin was born June 27, 1968 in Zagreb, Croatia. He graduated in 1993 form The Faculty for Electrotechnology and Computer Sciences, University of Zagreb in Croatia. He started his professional career as a System specialist and DBA for the Croatian company Informatika System. His most important project was the development and implementation of the enterprise, distributed point of sales solution, based on the Oracle technology. In 1999, Marin became the company CTO, where he played an active role in company development and technical orientation. After Informatika System, Marin worked as an IT Manager Assistant for the Austrian international retail company "Segro," on location in Graz (Austria) and Zagreb (Croatia). He was responsible for the company's technical infrastructure and operational support. Segro used IBM technology, OS/400 operating system and DB2 database. In 1998, Marin joined the international telecommunication company VIPNet GSM that was a part of greater concern, Mobilkom Austria& Western Wireless Int. USA. After one year, Marin took over the IT System Manager position, where he managed many multi-platform, telecommunication projects and was leading the IT system department. In 2001, Marin started to work in Germany as a senior system architect. He is currently working for German banks on different banking projects.

Latest Articles