How to monitor what SQL commands are being run against a DB2 database

Often times when troubleshooting an application, it is necessary to determine what DB2 tables are being modified or queried by an application. IBM DB2 has this functionality built in out of the box and it is called the event monitor.

I often use the DB2 event monitor to determine what tables are involved when troubleshooting some applications. This is especially useful when the developers of the application do not publish the database schema.

Usage

db2 connect to <DATABASE> user <DB USER> password <DB PASSWRORD>

Before starting the event monitor, we must establish a connection to the database.

db2 "create event monitor db2out for statements write to file '/tmp/db2out' APPEND"

This command will create an event monitor and write the output to /tmp/db2out.

db2 set event monitor db2out state 1

This command starts the monitoring. You should run this right before you kick off the action that you want to capture DB2 queries/inserts/deletes for.

db2 set event monitor db2out state 0

This command stops the monitoring. It’s best to have the monitor running for as little time as possible so that you don’t have to go thru a lot of data.

db2evmon -path /tmp/db2out > db2out.txt

This writes the monitor output into a text file.

db2 DROP EVENT MONITOR db2out

This drops the monitor when you are done.