db2 drop db error !

I was trying to drop database but was getting following error,

SQL1035N  The database is currently in use.  SQLSTATE=57019

So, while I logged in as instance owner, I tried following.

-- first attempt

db2 list applications global

No sessions were listed except my own local connections. Then I tried following set of statements.

-- second attempt

db2 deactivate db myDB

db2 drop database myDB

Still no luck, I kept getting the same message. Then I tried another variation

-- third attempt

db2 connect to myDB

db2 quiesce database immediate force connections

db2 connect reset

db2 terminate

db2 drop database myDB

Finally I tried this,

-- final attempt

db2 terminate

db2 drop database myDB

I was puzzled, why my third attempt did not work and the final one worked?

I thought it might be the sequence of statements. I looked into the "db2 terminate" command documentation on db2 information center, and her is what it says,

Although TERMINATE and CONNECT RESET both break the connection to a database, only TERMINATE results in termination of the back-end process.

It is recommended that TERMINATE be issued prior to executing the db2stop command. This prevents the back-end process from maintaining an attachment to a database manager instance that is no longer available.

What all this means is always use "db2 terminate" if you really want to disconnect your connections/sessions before doing db level options like db drop etc.

db2 luw 9.7 - Event monitors

Quick steps to setup event monitoring to capture the db2 sql activity and get to the output to some readable format.

-- db2 event monitoring for tracking sql statements etc.

-- first created for troubleshooting PowerDesigner issue.

-- sql statement to create evt monitor

CREATE EVENT MONITOR evtmon_pd_issue

FOR STATEMENTS

WHERE AUTH_ID = 'USER_ID'

WRITE TO FILE '/home/db2admin' -- this is a directory path

MAXFILES 5

MAXFILESIZE 1024

NONBLOCKED

APPEND;

db2 "set event monitor evtmon_pd_issue state 1"

db2 "set event monitor evtmon_pd_issue state 0"

--After stopping the evt monitor you will see a file in "/home/db2admin" which has word "evt" in its name and has sequential number at the end if there are many files created

-- this will format out the evt monitor captured data

db2evmon -path '/home/db2admin' >> evtmon_pd_issue.out

-- in *.out file you see output in readable format (txt)

-- ones done with your experiements, drop the evt monitor(s)

db2 "drop event monitor evtmon_pd_issue"

For more information on event monitors go to

http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=/com.ibm.db2.luw.admin.mon.doc/doc/c0055386.html