I am in kind of a bind. Every week I restore our production Database into a different database on a different Server for viewing by the users. Both Server are Windows boxes running V9.5

Usually a just do a redirected restore but the datbase is growing by eaps and bounds so this week I decided to drop the target database and then recreate it fresh, set the tablespace and then restore.

I dropped the target database and now I cannot recreate it in that instance. when i try and recreate it I get this error:

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

I have tried catloging and uncataloging the target database in the instance.. whatever I try it just says the database does not exist. Can anyone help?????

Check the local db directory on all drives (C:, D:, etc...). The command is:
db2 list db directory on <drive>

For example,
db2 list db directory on d:


If you find it on some drive, catalog it using:
db2 catalog db <db name> on <drive>

I have C: E: F: G: H: and I: drives on this Server. I tried the

db2 list db directory on <drive> command for each drive letter - database not found on any of them???

If you know the drive where your database was originally created, go to that drive, then cd to a directory with your instance name, then NODE0000 and SQLDBDIR.

Here is an example:

D:\DB2\NODE0000\SQLDBDIR>dir
Volume in drive D has no label.
Volume Serial Number is 5817-8201

Directory of D:\DB2\NODE0000\SQLDBDIR

07/19/2009 10:27 AM <DIR> .
07/19/2009 10:27 AM <DIR> ..
07/19/2009 10:27 AM 212 SQLDBBAK
07/19/2009 10:27 AM 212 SQLDBDIR
07/19/2009 10:27 AM 540 SQLDBINS


You should see 3 files as listed above. Rename them (don't delete just in case) and then try creating the db again (after uncataloging from system db directory).

Thank you so much for your Help!!!

I found the three files in this directory. C:\DB2_02\NODE0000\SQLDBDIR

How do I know these file aren't associated with one of the other Databases in my DB2_02 instance?

I renamed the 3 files and tried to uncatalog the database
DB2 uncatalog database DUTDM
- database does not exist -

I tried to recreate the database and I got the same error as above .. SQL1005N The database alias "dutdm" already exists in either the
local database directory or system database directory.

Do a search for sqldbdir and rename all files that are found under the instance name you're working with. Search will also find the system db directory files (they won't be under the instance name), but it's ok if you rename them as well. Don't rename the files belonging to other instances.

Note that renaming these files will "erase" all databases stored in the local db directory. So, if you have other databases on the drive (in this instance), don't perform this action.

I renamed the files and tried to recreate the database and I got the same error as above .. SQL1005N The database alias "dutdm" already exists in either the local database directory or system database directory.

I just don't know what to do..

The db still exists in some local db directory. At this point, I'd suggest to take a trace. First, uncatalog the db from the system db directory using "db2 uncatalog db <db name>". Then, take a trace using:

db2trc on -f db2trc.dmp
db2 create db <db name> -> this should generate the same error
db2trc off
db2trc fmt db2trc.dmp db2trc.fmt

Open db2trc.fmt and search for sqldbdir. You'll find several matches. Look for the one that exists under your instance name. In the example I provided earlier, it should find: D:\DB2\NODE0000\SQLDBDIR\SQLDBDIR

I started the trace and then tried to catalog , uncatalog, drop my database.

db2 attach to kocdevu2
catalog database dutdm
uncatalog dutdm
drop database dutdm

The only entries i find when I search for SQLDBDIR are from the DB2 instance on this box, not the kocdevu2 instance?

when i search the dump for dutdm i get this:



this is beyond frustrating!

Take a trace of the "create db" command (uncatalog the db first). If the trace doesn't help you to resolve the error, consider opening a pmr.

1) DO
db2 list db directory
You will get something like this
Database alias = SAMPLE
Database name = SAMPLE
Local database directory = C:

2) then run
db2 list db directory on C:\
If you get the following
Database alias = SAMPLE
Database name = SAMPLE
Database directory = SQL00011

Then delete the directory listed for 'Database directory'. In the above example it is SQL00011.
eg, C:\DB2\NODE0000\SQL00011
Rather than delete just move it to a diff directory and rename it.

3) Look at the old containers. Sometimes even if you drop it they dont get deleted. I m not sure that should be the reason for your error. But still check that. And if they exists then move and rename them too.

For such situation, I used CA (db2ca) to add DB entry from the DRIVE in question. this means that you CATALOGed local Database via CA.

Then Drop Database works.

I had same problem and simple 2 step solution.


Yes , if you have gone few steps deleting folders manually already ; you may not be able to do this 2 step solution. Then you have to delete all container in the old database