DB2 Storage

z/OS DB2 system files

The Catalog Database DSNDB06

The system catalog describes the logical and physical structure of the data. It contains sets of tables, including DB2 system tables, data definitions, statistics, security and recovery information. Aa a set of catalog tables and views is created whenever a database is created, this database can grow. In the z/OS world, do not confuse the DB2 catalog with ICF catalogs, its just another DB2 database. You get information from it using SQL queries, typically to find backup information or maybe inconsistencies between the BOOTSTRAP and DSNZPARM parameters that affect any DB2 recovery.
Table SYSIBM.SYSCOPY, stored in DSNDB06.SYSCOPY, contains information needed by DB2 to manage recoveries, specifically image copy information, and also quiesce points.

The Directory Database DSNDB01

contains a subset of the catalog database, mainly recording the state of active processes. Specific tables include
Table SYSIBM.SYSLGRNX, which identifies log records for updates made to table spaces. Used to select specific log records, and so speed up recovery.

The Work Database DSNDB07

Used for workspace (that was easy!). At least 2 tablespaces will be defined, and they will be very busy. If you are managing DB2 backups, then don't worry about backing up DSNDB07.
As Work databases will be very active, they could be excellent candidates for Flash Storage

DB2 Recovery datasets

Bootstrap datasets BSDS

DB2 uses the BSDS to recover from error situations. It is updated every time DB2 issues a checkpoint, and every time DB2 allocates an archive log. The bootstrap dataset is a VSAM KSDS, and its availability is absolutely critical, DB2 won't start up, without 2 of them! They are called
hlq.BSDS01 and BSDS02
where hlq is site dependent. They contain inventories of log files, checkpoint availability and buffer pool information.

Active logs

DB2 can have up to 93 active log datasets from DB2 version 8 onwards. These record all updates to DB2 data, and are used for recovery and rollforward. When an active log fills, it is copied off to an archive log, then the active log is overwritten again. There is some delay built into this process, so some log data will be held on both active and archived logs. Generally, archived logs are held on slower medium than active logs. The overall size of the active log set, must be enough to cater for fast recovery in a reasonable time frame. Active logs are called
hlq.LOGCOPYn.DSxx
where xx is 01-93, and n is 1 or 2 for active log duplexing.

Archive logs

We already know all about these, almost. Archive logs are physical sequential datasets, and can be held on either disk or tape. DB2 will track up to 10,000 archive logs and the naming standard is
hlq.ARCHLOGn.Dyyyyddd.Thhmmsst.Afileseq (or Bfileseq for a BSDS backup)

DB2 UDB system files

As DB2 UDB installation is now an automated installation process, it is very possible that your DBAs do not know where to look, to find the physical location of the various important DB2 configuration files. As they care critical to the running of the DB2 system, it is important to know where they are if things go wrong.

As discussed on the naming standards page, a db2 database will be found in a directory structure like this: home_dir/db2inst1/NODE0000/SQL00001/
The database directory will contain several files once the CREATE DATABASE command runs, some of which are:-

SQLSGF.1 and SQLSGF.2 files are used if db2 is configured for automatic storage. These files are duplicates of each other. If automatic storage databases is not configured then three SQLT* subdirectories will contain the following 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.

The SQLDBCONF file contains database configuration information. This file should not be edited directly, but maintained by DBAs with the UPDATE DATABASE CONFIGURATION and RESET DATABASE CONFIGURATION commands.

The DB2RHIST.ASC file contains history information about backups and restores, as well as table maintenance data, it has a backup file called DB2RHIST.BAK
The DB2TSCHG.HIS file is used to determine which log files to process during table space recovery.
Both these files can be read with a text editor.

The log control files, SQLOGCTL.LFH.1, its mirror copy SQLOGCTL.LFH.2, 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. You should ensure the log subdirectory is mapped to different disks than those used for your data.

Another useful file is .profile in the db2 user's home directory. It is executed every time the db2 user connects to the system and holds default user environment settings. If you use TSM for db2 backups, then the TSM environment variables DSMI_DIR, DSMI_CONFIG and DSMI_LOG should be set in this file. It is typically found in db2inst1/db2dba