DB2: Database alias already exists when cataloging

aliasdb2db2-luw

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!

Best Answer

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?

Related Solutions

Db2 – Problem installing DB2Express

If you built your database at C:\Daten\db\db2\IBM\DB2, then that should be your path for your database directory. (ie, it should be one higher than where your database resides).

Also, just wondering what command you used when you built your database? Where did you specify that it lives?

Disable Security in DB2 Express on Windows 7

The short answer to this problem is that you need to set the DB2 registry variable DB2_GRP_LOOKUP to local.

As a local administrator on your PC (i.e. not using your domain account), start a DB2 Command Window and issue the following:

db2set DB2_GRP_LOOKUP=local
db2stop
db2start

The reason this occurs has to do with how DB2 enumerates which groups a user belongs to upon successful authentication. In a Windows Domain environment, the default group enumeration behavior is to ask the system that authenticated the user: If a user is authenticated by the local machine, DB2 asks the local machine what groups belongs to; if the user is authenticated by the domain, DB2 will ask the domain what groups the user belongs to - and it will not ask the local machine.

So, when you connect to DB2 using your domain ID, DB2 asks the domain controller what groups you belong to, so it does not see that your ID belongs to the Administrators group on your local PC.

When you set DB2_GRP_LOOKUP=local, this modifies this behavior: It tells DB2 to always enumerate groups from the local machine, regardless of whether the user was authenticated by the domain or the local machine.

After doing this, DB2 will see that your Domain ID belongs to the local Administrators group, and you'll end up with full SYSADM privileges (and the ability to create a database).

Related Question