DB2 – how to safely blow away database on linux

db2schema

I'm using (I believe) 10.1.2, on CentOS 6.5 (for sure).

The underlying problem I have is that the database upgrade package prepared in house isn't working for me, in at least the sense that the database schema is not changing as it is supposed to. Of course, no-one external can help me with that.

What I'd like to do is remove the existing database as completely as possible (preferably to the level of deleting/archiving files that might have records of it), so that there's no way the upgrade package can be reading old schema data. What's the safest way to do that?

I've tried doing a drop database in the db2 commandline as db2inst1, with no luck:

db2 => connect to xydb 

   Database Connection Information

 Database server        = DB2/LINUXX8664 10.1.2
 SQL authorization ID   = DB2INST1
 Local database alias   = XYDB

db2 => drop database
SQL0104N  An unexpected token "END-OF-STATEMENT" was found following 
"DATABASE".  Expected tokens may include:  "<identifier>".  SQLSTATE=42601
db2 => drop database xydb
SQL1035N  The operation failed because the specified database cannot be 
connected to in the mode requested.  SQLSTATE=57019
db2 =>

Any help much appreciated.

Best Answer

You cannot drop a database if someone is connected to it. You would need to force everyone off the database first (and to do that you need SYSCTRL authority for the instance, and I'm gonna guess DBADM at the database level if you don't have instance level authority).

db2 connect to xydb
--the next command forces off the users asynchronously
db2 quiesce database immediate force connections
--but you need to unquiesce to drop
db2 unquiesce database
db2 connect reset
-- hope you don't have a connection in between
-- also make sure the database isn't activated....
db2 deactivate db xydb
-- do this as a safeguard cause there is no going back once you drop it.....
db2 backup db xydb to '<path you provide>' compress
db2 drop db xydb

or if you want to go instance level and lock down the instance.....

db2 quiesce instance <instance name> restricted access immediate force connections
-- make sure the db isn't activated
db2 deactivate db xydb
-- do this as a safeguard cause there is no going back once you drop it......
db2 backup db xydb to '<path you provide>' compress
db2 drop db xydb
-- wait a few seconds
db2 unquiesce instance <instance name>

Then don't forget to backup logs and archived logs and then you can clean those out as well.

Now you can attempt to create a new database. However, I'm guessing you need to worry about migrating your data? Are you attempting to migrate to a new fix pack or version of DB2? Are you attempting to upgrade your schema? You can always test out the "upgrade" on another server first. I guess I'd like more information on your "upgrade" and I can tweak this answer to hopefully respond to that part rather than just giving you hints on how to drop a database.

Related Question