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 at a location
that is determined by the information you provide in the CREATE
DATABASE command. If you do not specify the location of
the directory path or drive when you create the database, the default
location is used.
In the directory you specify as the database path in the CREATE
DATABASE command, a subdirectory that uses the name of the instance is
created. This subdirectory ensures that databases created in different
instances under the same directory do not use the same path. Below
the instance-name subdirectory, a subdirectory named NODE0000 is
created. This subdirectory differentiates database partitions in a
logically partitioned database environment. Below the node-name directory,
a subdirectory named SQL00001 is created. This
name of this subdirectory uses the database token and represents
the database being created. SQL00001 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 directory structure appears as follows: your_database_path/your_instance/NODE0000/SQL00001/
The database directory contains the following files that are created
as part of the
CREATE DATABASE command.
- The files SQLBP.1 and SQLBP.2 contain
buffer pool information. These files are duplicates of each other
for backup purposes.
- The files SQLSPCS.1 and SQLSPCS.2 contain
table space information. These files are duplicates of each other
for backup purposes.
- The files SQLSGF.1 and SQLSGF.2 contain
storage path 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 databases when automatic
storage is enabled following a CREATE DATABASE dbname AUTOMATIC
STORAGE YES command or ALTER DATABASE dbname ADD STORAGE ON statement.
- The SQLDBCONF file contains database configuration
information. Do not edit this file.
Note: The SQLDBCON file was used in previous
releases.
To change configuration parameters, use the UPDATE
DATABASE CONFIGURATION and RESET DATABASE CONFIGURATION commands.
- 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.
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 both history files in a text editor.
- The log control files, SQLOGCTL.LFH.1, its
mirror copy SQLOGCTL.LFH.2, and SQLOGMIR.LFH,
contain information about the active logs.
Recovery processing uses
information from these files to determine how far back in the logs
to begin recovery. The SQLOGDIR subdirectory
contains the actual log files.
Note: You should ensure the log
subdirectory is mapped to different disks than those used for your
data. A disk problem could then be restricted to your data or the
logs but not both. This 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,
change the newlogpath database configuration
parameter.
- The SQLINSLK file helps to ensure that a
database is used by only one instance of the database manager.
At the same time a database is created, a detailed deadlocks event
monitor is also created. The detailed deadlocks event monitor files
are stored in the database directory of the catalog node. 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 consuming too much disk space. Removing
output files that are no longer needed will allow the event monitor
to activate again on the next database activation.
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 required for an operational database. Three
default table spaces are created:
- 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 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)
DB2 Version 9.7 for Linux, UNIX, and Windows