DB2 10.5 for Linux, UNIX, and Windows

Database directories and files

When you create a database, information about the database including default information is stored in a directory hierarchy.

The hierarchical directory structure is created for you. You can specify the location of the structure by specifying a directory path or drive for the CREATE DATABASE command; if you do not specify a location, a default location is used.

In the directory that you specify as the database path in the CREATE DATABASE command, a subdirectory that uses the name of the instance is created.

Within the instance-name subdirectory, the partition-global directory is created. The partition-global directory contains global information associated with your new database. The partition-global directory is named NODExxxx/SQLyyyyy, where xxxx is the data partition number and yyyyy is the database token (numbered >=1).

Under the partition-global directory, the member-specific directory is created. The member-specific directory contains local database information. The member-specific directory is named MEMBERxxxx where xxxx is the member number.

In a DB2® pureScale® environment, there is a member-specific directory for each member, called MEMBER0000, MEMBER0001, and so on.
In a partitioned database environment, member numbers have a one-to-one mapping with their corresponding partition number, therefore there is one NODExxxx directory per member and partition. Member-specific directories are always named MEMBERxxxx and they always reside under the partition-global directory.
An Enterprise Server Edition environment runs on a single member, and has one member-specific directory, called MEMBER0000.

Partition-global directory

The partition-global directory has the path: your_instance/NODExxxx/SQLxxxxx.

The partition-global directory contains the following files:

Global deadlock write-to-file event monitor files that specify either a relative path or no path at all.
Table space information files.
The files SQLSPCS.1 and SQLSPCS.2 contain table space information. These files are duplicates of each other for backup purposes.
Storage group control files.
The files SQLSGF.1 and SQLSGF.2 contain storage group information associated with the automatic storage feature of a database. These files are duplicates of each other for maintenance and backup purposes. The files are created for a database when you create the database using the CREATE DATABASE command or when you upgrade a nonautomatic storage database to DB2 V10.1 or later.
Temporary table space container files.
The default directory for new containers is instance/NODExxxx/<db-name>. The files are managed locally by each member. The table space file names are made unique for each member by inserting the member number into the file name, for example: /storage path/SAMPLEDB/T0000011/C0000000.TMP/SQL00002.MEMBER0001.TDA
The global configuration file.
The global configuration file, SQLDBCONF, contains database configuration parameters that refer to single, shared resources that must remain consistent across the database. Do not edit this file. To change configuration parameters, use the UPDATE DATABASE CONFIGURATION and RESET DATABASE CONFIGURATION commands.
History file.
The DB2RHIST.ASC history file and its backup DB2RHIST.BAK contain history information about backups, restores, loading of tables, reorganization of tables, altering of a table space, and other changes to a database.
Logging-related files.
The global log control files, SQLOGCTL.GLFH.1, SQLOGCTL.GLFH.2, contain recovery information at the database level, for example, information related to the addition of new members while the database is offline and maintaining a common log chain across members. The log files themselves are stored in the LOGSTREAMxxxx directories (one for each member) in the partition-global directory.
Locking files.
The instance database lock files, SQLINSLK,and SQLTMPLK, help to ensure that a database is used by only one instance of the database manager.
Automatic storage containers

Member-specific directory

The member-specific directory has the path: /NODExxxx/SQLxxxx/MEMBERxxxx

This directory contains objects associated with the first database created, and subsequent databases are given higher numbers: SQL00002, and so on. These subdirectories differentiate databases created in this instance on the directory that you specified in the CREATE DATABASE command.

The member-specific directory contains the following files:

Buffer pool information files.
The files SQLBP.1 and SQLBP.2 contain buffer pool information. These files are duplicates of each other for backup purposes.
History file.
The DB2TSCHG.HIS file contains a history of table space changes at a log-file level. For each log file, DB2TSCHG.HIS contains information that helps to identify which table spaces are affected by the log file. Table space recovery uses information from this file to determine which log files to process during table space recovery. You can examine the contents of history files in a text editor.
Local event monitor files.
Logging-related files.
The log control files, SQLOGCTL.LFH.1, its mirror copy SQLOGCTL.LFH.2, and SQLOGMIR.LFH, contain information about the active logs. In a DB2 pureScale environment, each member has its own log stream and set of local LFH files, which are stored in each member-specific directory.

Tip: Map the log subdirectory to disks that you are not using for your data. By doing so, you might restrict disk problems to your data or the logs, instead of having disk problems for both your data and the logs. Mapping the log subdirectory to disks that you are not using for your data can provide a substantial performance benefit because the log files and database containers do not compete for movement of the same disk heads. To change the location of the log subdirectory, use the newlogpath database configuration parameter.
The local configuration file.
The local SQLDBCONF file contains database configuration information. Do not edit this file. To change configuration parameters, use the UPDATE DATABASE CONFIGURATION and RESET DATABASE CONFIGURATION commands.

At the same time a database is created, a detailed deadlocks event monitor is also created. In an Enterprise Server Edition environment and in partitioned database environments, the detailed deadlocks event monitor files are stored in the database directory of the catalog node. In a DB2 pureScale environment, the detailed deadlocks event monitor files are stored in the partition-global directory. When the event monitor reaches its maximum number of files to output, it will deactivate and a message is written to the notification log. This prevents the event monitor from using too much disk space. Removing output files that are no longer needed allows the event monitor to activate again on the next database activation.

Note: If you are upgrading your existing DB2 Version 9.5 or Version 9.7 databases to Version 10.5, the database log directories have changed. If you use a user defined log directory, such as /usr/logpath, the location of the log files is /usr/logpath/NODE0000/LOGSTREAM0000 after you upgrade. If you are using the default database directory, such as /home/db2user/db2inst/NODE0000/SQL00001/SQLOGDIR, the location of the log files is /home/db2user/db2inst/NODE0000/SQL00001/LOGSTREAM0000 after the upgrade. The old log directory only contains renamed log files after the upgrade. You can view information about the new log directories in the db2diag.log file.

Additional information for SMS database directories in non-automatic storage databases

In non-automatic storage databases, the SQLT* subdirectories contain the default System Managed Space (SMS) table spaces:

SQLT0000.0 subdirectory contains the catalog table space with the system catalog tables.
SQLT0001.0 subdirectory contains the default temporary table space.
SQLT0002.0 subdirectory contains the default user data table space.

Each subdirectory or container has a file created in it called SQLTAG.NAM. This file marks the subdirectory as being in use so that subsequent table space creation does not attempt to use these subdirectories.

In addition, a file called SQL*.DAT stores information about each table that the subdirectory or container contains. The asterisk (*) is replaced by a unique set of digits that identifies each table. For each SQL*.DAT file there might be one or more of the following files, depending on the table type, the reorganization status of the table, or whether indexes, LOB, or LONG fields exist for the table:

SQL*.BKM (contains block allocation information if it is an MDC or ITC table)
SQL*.LF (contains LONG VARCHAR or LONG VARGRAPHIC data)
SQL*.LB (contains BLOB, CLOB, or DBCLOB data)
SQL*.XDA (contains XML data)
SQL*.LBA (contains allocation and free space information about SQL*.LB files)
SQL*.INX (contains index table data)
SQL*.IN1 (contains index table data)
SQL*.DTR (contains temporary data for a reorganization of an SQL*.DAT file)
SQL*.LFR (contains temporary data for a reorganization of an SQL*.LF file)
SQL*.RLB (contains temporary data for a reorganization of an SQL*.LB file)
SQL*.RBA (contains temporary data for a reorganization of an SQL*.LBA file)