How to locate the local database directory?

Body

I came across a situation where one of my client recreated an instance but were not able to catalog the database successfully.

> db2 catalog db sample
SQL6028N  Catalog database failed because database "SAMPLE" was not found in the local database directory.

They were running this command from instance home directory /home/db2inst1 .

So it is clear that the local database directory does not exist under instance home directory. We need to specify the correct database directory in 'CATALOG DATABASE' command. The client was not aware of exact location where database was created.

Now the challenge is to find the correct database directory location. 

I asked him to look for file SQLSPCS.1 file on complete system:

find / -name SQLSPCS.1

It returned following location:

/data1/db2data/db2inst1/NODE0000/SQL00001/SQLSPCS.1

This path is interpreted as follows:

<local_db_directory>/<instance_name>/NODE<number>/SQL<number>/SQLSPCS.1

Here, local_db_directory is /data1/db2data .

So it explains that the local database directory for database 'SAMPLE' resides under /data1/db2data .

Finally we are able to catalog database successfully:

> db2 catalog db sample on /data1/db2data

DB20000I  The CATALOG DATABASE command completed successfully.
DB21056W  Directory changes may not be effective until the directory cache is refreshed.

Please note: In case of more than one SQLSPCS.1 files, you may need to be careful with correct local database directory location. In that case, move to <local_db_directory>/<instance_name>/NODE<number> and look for your database name.

For eg. 

> cd /data1/db2data/db2inst1/NODE0000

> ls

SQL00001  SAMPLE      sqldbdir

Here, database SAMPLE is found. Hence '/data1/db2data' is the correct database directory corresponding to database 'SAMPLE'.

Hope this helps!

Thanks.