Results 1 to 14 of 14
  1. #1
    Join Date
    Dec 2007
    Posts
    288

    Unanswered: Cannot recreate DB after dropping

    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?????

  2. #2
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    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>

  3. #3
    Join Date
    Dec 2007
    Posts
    288
    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???

  4. #4
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    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).

  5. #5
    Join Date
    Dec 2007
    Posts
    288
    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?

  6. #6
    Join Date
    Dec 2007
    Posts
    288
    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.

  7. #7
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    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.

  8. #8
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    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.

  9. #9
    Join Date
    Dec 2007
    Posts
    288
    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..

  10. #10
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    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

  11. #11
    Join Date
    Dec 2007
    Posts
    288
    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?

    Code:
    43855	entry DB2 UDB oper system services sqloopenp cei (1.3.15.814.2)
    	pid 6736 tid 4616 cpid -1 node 0
    	bytes 65
    	Data1 	(PD_TYPE_FILE_NAME,33) File name:
    	C:\DB2\NODE0000\SQLDBDIR\SQLDBDIR
    	Data2 	(PD_TYPE_UINT,4) unsigned integer:
    	844
    	Data3 	(PD_TYPE_SINT,4) signed integer:
    	1636
    when i search the dump for dutdm i get this:



    Code:
    	Data1 	(PD_TYPE_DEFAULT,98) Hexdump:
    	5351 4C31 3031 334E 2020 5468 6520 6461    SQL1013N  The da
    	7461 6261 7365 2061 6C69 6173 206E 616D    tabase alias nam
    	6520 6F72 2064 6174 6162 6173 6520 6E61    e or database na
    	6D65 2022 6475 7464 6D22 2063 6F75 6C64    me "dutdm" could
    	206E 6F74 2062 6520 666F 756E 642E 200D     not be found. .
    	0A53 514C 5354 4154 453D 3432 3730 350D    .SQLSTATE=42705.
    this is beyond frustrating!

  12. #12
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    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.

  13. #13
    Join Date
    Dec 2006
    Posts
    25
    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.

  14. #14
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    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
    Last edited by DBFinder; 07-22-09 at 17:27.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •