Wednesday, June 16, 2010

DB2 Database Directories


When the database is created and/or catalogued in an instance, it record its details in three directory. They are System Database directory, Local database directory and Node directory

The System database directory
contain all the database information. The database may be a local database or it can also show the remote database details. Local database is represented with keyword “INDIRECT” and remote database is represented by “REMOTE”.

When the connection to the DB2 Database is made, DB2 first check the details in the System Database directory. If the connection is to the “INDIRECT” database then it check the local database directory. If the connection is to the “REMOTE” database then it check the Node directory.

System Database directory is a binary file with a name SQLDBDIR. On Windows, the file can be located in DB2INSTPROF/SQLDBDIR/SQLDBDIR. On Linux, the file is located in INSTHOME/sqllib/sqldbdir/sqldbdir. The content of this directory is displayed by using the command “list db directory”. The purpose of the System database directory is to record all the database (local or remote) that are accessible.

DB2INSTPROF = DB2 Registry variable. If not set then the default value is the DB2 Installed directory path.

INSTHOME = Is the user profile variable which is set to the Instance home directory.

Local Database Directory is a binary file with the name SQLDBDIR. However, this file is different from the System Database directory binary file and also reside in different storage path. On Windows and Linux, the file can be located in DFTDBPATH/NODEXXXX/sqldbdir/sqldbdir which will list all database created in the default directory. If we need to check database in non-default directory then it can be located in /NODEXXXX/sqldbdir/sqldbdir.The content of this directory is displayed by using the command “list db directory on ”. The purpose of the local database directory is to record all the database (local only) created in specific directory. This directory also give the SQLXXXXX directory name of the database.

DFTDBPATH = DB2 Database Manager variable which show the default path for creating a new database.

Node Directory is a binary file with the name SQLNODIR. On Windows, the file can be located in DB2INSTPROF/SQLNODIR/SQLNODIR. On Linux, the file is located in INSTHOME/sqllib/sqlnodir/sqlnodir. The content of this directory is displayed by using the command “list node directory”. The purpose of the Node Directory is it record how to communicate with the remote DB2 instance.





Database Manager Configuration File (DBM CFG)

The DBM CFG files store all the Database Manager (Instance) parameter values. Each Instance has its own DBM CFG file. This is a binary file. The file is called as "db2systm". On Linux the file is located at 
INSTHOME/sqllib/db2systm





Database Configuration File (DB CFG)


The DB CFG files stores all the Database parameter values. Each database has it unique DB CFG file. This is a binary file. The file is called as "SQLDBCONF" starting from v8.2 and later.
To locate the file on Linux following step are performed
1) Get the database path
db2 list active databases



2) Change to "Database Path" directory returned from the output of previous command. The file will be located under that path





Useful Reference



1) DB2 directory Structure on Linux

2) Database directories and files


No comments: