8

I've never got this working the first time, but now I can't seem to do i at all.

There is a connection pool somewhere using the database, so trying to drop the database when an application is using the database should give this error. The problem is there are no connection to the database when I issue these commands:

db2 connect to mydatabase
db2 quiesce database immediate force connections
db2 connect reset
db2 drop database mydatabase

This allways give: 

SQL1035N  The database is currently in use.  SQLSTATE=57019

running this command shows no connections/applications

DB2 list applications

I can even deactivate the database, but still can't drop it. 

db2 => deactivate database mydatabase
DB20000I  The DEACTIVATE DATABASE command completed successfully.
db2 => drop database mydatabase
SQL1035N  The database is currently in use.  SQLSTATE=57019
db2 =>

Anyone got any clues? I'm running the cmd-windows as the local administrator (windows 2008) and this is also the admin for DB2. The connectionpool-user cannot connect during quiesce-state.

Tommy
  • 195
  • 1
  • 2
  • 9

7 Answers7

9

Just as a tip if Arun's tip does not work, because an remote app is instantly reconnecting to the db.

db2 force applications all 
db2 terminate 

db2set DB2COMM= 
db2stop 
db2start 

db2 force applications all 
db2 terminate 

... do your stuff here ...

db2set DB2COMM=TCPIP 
db2stop 
db2start

Not sure if the db2 force and the db2 terminate is necessary, though. It's just our best practice.

Peter Schuetze
  • 1,231
  • 10
  • 17
  • I don't get why remote connections are allowed when the database is "quiesce"'ed and the remote users don't have that authority... – Tommy Feb 12 '10 at 22:59
  • From IBMs documentation: http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin/r0000908.htm -- RESET - Equivalent to an explicit connect to the default database if it is available. If a default database is not available, the connection state of the application process and the states of its connections are unchanged. -- Looks like you connected to the database by calling `DB2 connect reset` – Peter Schuetze Feb 15 '10 at 15:57
  • Setting DB2COMM= was the only way to kick out all the connection pools connecting to the database. I just love DB2... – Tommy Feb 20 '10 at 15:21
2

Your problem might occur because of the differences of connect reset and terminate. Have a look at this discussion. You can also have a look at the IBM documentation. So it might work for you if you run a db2 terminate instead of a db2 connect reset.

Peter Schuetze
  • 1,231
  • 10
  • 17
2

I just use 'db2stop force', then 'db2start' , then drop db. This way is better than quiesce command which is asynchronous by nature.

Arun Srini
  • 43
  • 6
  • I must correct my accepted answer. This worked one time, but it must have been luck. Had to turn to Peter's "trick" – Tommy Feb 20 '10 at 15:22
1

The quiesce will prevent the drop from working. We were involved in a large cleanup effort and after running revoke/connect and quiescing the databases we waited a few months to drop the test dbs. Our drop command wouldn't work unless we unquiesced the db then dropped.

Lisa
  • 11
  • 1
0

Do these steps from command line. This will drop the db:

Force applications all;

deactivate db ;

drop db ;

0

When a db is quiesced it is still on the chain of databases with the db manager, albeit marked as quiesced. When you try to drop it, we will check to see if it is on this chain; if so, then it is still in use ==> You cant drop it.

You have to issue an unquiesce db or issue a db2stop and db2start and then try to drop it. It should work.

Marko
  • 227
  • 4
  • 7
  • 15
John
  • 1
0

I have similar problem but no matter how I force application to stop, it will automatically come back.

In my case, it's a Service that's locking the database.

I was able to solve this by terminate PID service from Windows Task Manager with PID found in DB2 Application List.

To find PID, open to DB2 Control Center, All Databases, DATABASE (the one you are trying to drop), Application List, Show Lock Chain, Show Lock Details and scroll down to find Client Process ID and terminate this PID and try again.

Hope this helps people in the future.

Simon
  • 1