2

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

I am an accidental DB2 LUW on linux administrator; primary background in MSSQL and Oracle. We have an application that has an integrated DB2 Enterprise server as part of the stack. Within that, the application creates databases based on user defined workloads. When those workloads are dropped, the databases are removed as well. Unfortunately, I've reached a scenario where i need to reuse database names. For example, the databases are named like WORKOB0[0-9][0-9] and we've exceeded 99, historically.

If I manually create database WORKOB01, the create is successful. However, when I attempt to catalog it, I get the error message above. The databases do not exist in the user directory, no question about that. However, there is a twist. The application creates databases AT NODE 'HOMEDB01'. When I use commands like list database directory, no objects are present. If I use list node directory, it states that "the node directory cannot be found".

I'm lost. I've spent several hours on this and I'm in a position to say: I have no idea what I'm doing!

0

If this is a development environment I suggest you turn off the directory cache:

db2 update dbm cfg using DIR_CACHE NO
db2 stop force; db2start

I don't really understand why you need to create new databases though, can't you cope with a new schema in an existing database?

  • Just sounds like bad app design – Philᵀᴹ Oct 29 '16 at 20:46
  • Bad app design is a decent response - it's even an IBM app (with its identity protected by a hefty NDA). The entire schema is recycled per app processing iteration by an external, non-SQL data source. Thanks for the tip on dir_cache; see my updated response. – hekatonian Oct 30 '16 at 12:43
0

Update:

This was a classic case of looking at the obvious source of the problem instead of the actual source. The SQL1005N message was actually being generated by the client. By running db2 list db directory on the client, the entire list of previous database names were still in place. I wrote a quick sh script to run db2 uncatalog database $name for each one; and the problem was solved.

Thanks much to Lennart for your contribution; while it did not solve the riddle it was certainly helpful for future reference.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.