We're getting error sql10004c and can't connect to database. LIST DB DIRECTORY ON <path> gave us the same error. User response tells us to restore database. This is a very large database. Can we fix this error without restore. Please help.

What had been done to the database immediately before this started?

If the documentation says to restore, you probably should be get ready to do so.

Before restoring over the problem, i would back up what you have and then restore. If you cannot run a "real" backup due to the corruption, i'd suggest a physical copy of the dasd. Might be useful for diagnosing after the system is back up and running.

What DB2 version and OS are you using? How many local databases do you have on the same <path>? Can you connect to them? Please provide "db2 list db directory" output. If you can't restore, it's best to open a pmr to address this error.

We didn't do anything to this database. This is DB2 9.1 running on AIX 6. We're going to migrate to DB2 9.7 but can't connect. One database exists

System Database Directory

Number of entries in the directory = 1

Database 1 entry:

Database alias = WAREHOUS
Database name = WAREHOUS
Local database directory = /db/dba
Database release level = b.00
Comment =
Directory entry type = Indirect
Catalog database partition number = 0
Alternate server hostname =
Alternate server port number =

I'll show you what you can try, but if this is not clear, it's best to restore or try to get support from IBM. Please be careful when renaming/copying files.

There are 2 database directories: system and local. It looks like your local db directory has a problem.

Local db dir is stored in /db/dba/<instance name>/NODE0000/sqldbdir (if this is a DPF env, then NODE00## where ## is the node #)
System db dir is stored in $HOME/sqllib/sqldbdir


Here is what you can try:

- Check permissions on /db/dba/<instance name>/NODE0000/sqldbdir, sqldbdir should have 755 and owned by the instance owner.
- If permissions are ok, copy sqldbdir from $HOME/sqllib to /db/dba/<instance name>/NODE0000 and try to connect (rename the original sqldbdir in /db/dba/<instance name>/NODE0000 prior to copying).
- If this doesn't work, then you need to rebuild sqldbdir as follows:

db2 uncatalog db warehous
cd /db/dba/<instance name>
mv NODE0000 NODE0000.old
db2 create db warehous on /db/dba
cd /db/dba/<instance name>/NODE0000 (this is a newly created dir - got created during the previous step)
rename all dir except for sqldbdir [ rename SQL00001, WAREHOUS (if it exists) ]
copy [ SQL00001, WAREHOUS (if it exists) ] from /db/dba/<instance name>/NODE0000.old to /db/dba/<instance name>/NODE0000

Now, you should have the following in /db/dba/<instance name>/NODE0000
- original [ SQL00001, WAREHOUS (if it exists) ]
- new sqldbdir

Try to connect to db.

If this DPF, you have to repeat the same steps for all NODE00## (uncatalog db once, mv NODE00## for each node, create db once, rename/copy for all NODE00##).

We can connect, rebuild sqldbdir. We migrated to 9.7 ok, Can we delete renamed directories?

Another question is how to migrate back to 9.1?

before migration you should have taken offline backup
drop 9.7 instance - create 9.1 instance if code still present
restore db
all changes executed in 9.7 are lost !!

Please provide ls -l output of what you have in /db/dba/<instance name> and /db/dba/<instance name>/NODE0000

db2girl!!!!!

THANK YOU!!!!!!!!!!!!!!!!!!!! This has saved me from a bunch of work I did not want to do. It is much appreciated.