DB2 Restore – Unable to Restore Database Because It Exists, Unable to Drop Because It Does Not Exist

db2restore

I've seen this mentioned a couple of times but none of the suggested solutions worked for me, so I'm posting a question together with a solution in hope that it helps someone else.

During restore of a development database I encountered:

SQL1005N  The database alias "XXXX" already exists in either the local 
database directory or system database directory.

db2 drop db XXXX
SQL1031N  The database directory cannot be found on the indicated file system. 
SQLSTATE=58031

The solution suggested elsewhere is to catalog the database and then drop it:

db2 catalog db XXXX
DB20000I  The CATALOG DATABASE command completed successfully.
DB21056W  Directory changes may not be effective until the directory cache is 
refreshed.

db2 drop db XXXX
SQL1031N  The database directory cannot be found on the indicated file system. 
SQLSTATE=58031

So it appears as there's a zoombie database lurking around, that we can't get rid of. What to do?

Best Answer

Disclaimer, the following solution where for a development box. Use with caution. For a production like scenario, open a PMR.

It turned out that during a previous restore the file system got full and therefore not all files necessary got created. I cataloged the database:

db2 catalog db XXXX
DB20000I  The CATALOG DATABASE command completed successfully.
DB21056W  Directory changes may not be effective until the directory cache is 
refreshed.

and tried to connect to it (fails). In db2diag log there's an entry showing where he files where supposed to be located, in my case:

/data/db/db2/db2inst1/NODE0000/SQL00010

As it turned out this is the same location as shown in

db2 list db directory

So what I did was:

db2stop force

and then rsynced all files from another database:

#> cd /data/db/db2/db2inst1/NODE0000/SQL00010
#> rsync -av ../SQL00011/* .
#> db2start

Before dropping the database I tried connecting (for the fun of it) and got a new error:

#> db2 connect to XXXX
SQL0294N  The container is already in use.  SQLSTATE=42730

Dropping the database was possible though

#> db2 drop db XXXX
DB20000I  The DROP DATABASE command completed successfully.

As mentioned this was performed on a developer box with no important data, in a production environment I would have opened a PMR instead.

Related Question