The DB2 Configuration Files Revealed

Tuesday Jun 29th 2004 by Marin Komadina
Share:

Everything you ever wanted to know about DB2 configuration files, but were afraid to ask.

IBM has provided us with an automated installation process. As a result, many DBAs do not have any overview of where DB2 has stored important configuration information. The DB2 configuration files contain security setting parameters, system resource allocation, backup environment variables and DB2 instance configuration settings. In the event of even a small problem with configuration data, a full database recovery procedure is needed. For huge databases, this could be a time costly procedure and in some cases not acceptable. Just the ability to recognize a problem with a specific configuration file and the opportunity to rebuild a damaged file manually, in certain situations might save valuable time. This article will uncover important DB2 database configuration files and related SUN Solaris system files, walking through the directory hierarchy.

This article covers:

  • The Structure Tree
  • Sun Solaris Configuration Files
  • The DB2 Instance Configuration Files
  • Conclusion

The Structure Tree

The settings in the operating system configuration files and DB2 database instance (database manager) configuration files are prerequisites for proper DB2 instance functionality. An instance and the database information is stored in a directory structure. The hierarchical directory structure is created by the installation procedure at a customized location. A hard coded database instance directory-structure is created according following schema:

 <home_dir>/<instance>/<partition>/<database>

My demo DB2 database instance, used in this article, was running on the SUN Solaris operating system, under instance owner db2inst1, hosting only one DB2 database "ARTIST" and consisting of the three database partitions. The instance was created at the /db2/db2inst1/NODE0000/SQL00001 installation path, symbolically describing instance db2inst1, partition NODE0000 and the fist database directory SQL00001.

SUN Solaris Configuration Files

The most important operating system configuration files related to the DB2 installation and configuration are displayed in Picture 1.

Certain files need modification while others are automatically adjusted by the DB2 installation procedure.

passwd - user configuration file (UNIX)

File Name

Default Location

passwd

/etc

The UNIX administrator maintains the user configuration file "passwd". Before DB2 software installation, two new entries were entered identifying the instance owner (db2inst1) and the fenced user (db2fenc1).

$ ls -lrt /etc/passwd
-r--r--r--   1 root     sys         1095 Sep 12  2003 /etc/passwd

$ cat  /etc/passwd

root:x:0:1:Super-User:/:/sbin/sh
daemon:x:1:1::/:
bin:x:2:2::/usr/bin:
sys:x:3:3::/:
adm:x:4:4:Admin:/var/adm:
lp:x:71:8:Line Printer Admin:/usr/spool/lp:
smtp:x:0:0:Mail Daemon User:/:
uucp:x:5:5:uucp Admin:/usr/lib/uucp:
nuucp:x:9:9:uucp Admin:/var/spool/uucppublic:/usr/lib/uucp/uucico
db2fenc1:x:59555:103::/export/home/db2fenc1:/bin/ksh
db2inst1:x:59556:102::/export/home/db2inst1:/bin/ksh
Listing 1: passwd configuration file

Performing the installation with a GUI, using the appropriate root administrative right on the machine, automatically causes changes in the passwd file .

group - group configuration file (UNIX)

File Name

Default Location

group

/etc

Every system user belongs to a specific UNIX group. Similar to the passswd file, group file may be managed manually or automatically. The DB2 groups hold only DB2 accounts and they are placed at the end of the group configuration file.

$ ls -lrt /etc/group
-rw-r--r--   1 root     sys          399 Sep 12  2003 /etc/group

$ cat /etc/group

root::0:root
other::1:
bin::2:root,bin,daemon
sys::3:root,bin,sys,adm
adm::4:root,adm,daemon
uucp::5:root,uucp
mail::6:root
tty::7:root,tty,adm
lp::8:root,lp,adm
nuucp::9:root,nuucp
staff::10:
daemon::12:root,daemon
db2dba::102:
db2fadm1::103:
Listing 2: group UNIX configuration file

services - port reservation file (UNIX)

File Name

Default Location

services

/etc

Communication between an application process (remote client) and database coordinator agent are carried over the listener process (db2tcpcm). The TCP/IP, as the communication protocol, uses predefined ports reserved in the services configuration file. DB2 ESE installation, configured with several database partitions, requires additional port reservation, used by the fast communication manager (FCM). FCM is responsible for handling data transport between the database partitions.

$ ls -lrt /etc/services
lrwxrwxrwx   1 root     root          15 Feb 28  2002 /etc/services -> ./inet/services

$ cat /etc/services | grep db2
db2cdb2inst1    50000/tcp   # Connection port for DB2 instance db2inst1
db2idb2inst1    50001/tcp   # Interrupt  port for DB2 instance db2inst1
DB2_db2inst1    60000/tcp   # db2 fast communication manager start 
DB2_db2inst1_END  60004/tcp # db2 fast communication manager end 
Listing 3: services - UNIX port reservation file

.profile - user profile configuration file (UNIX)

File Name

Default Location

.profile

$HOME

The instance owner, (db2inst1), has a specific configuration file (.profile) in the user home directory associated with it by default. That user profile executes every time that the user connects to the system. Profile holds default user environment settings.

$ ls -lrt .profile
-rw-r--r--   1 db2inst1 db2dba     331 Dec 12  2003 .profile

$ cat .profile
#       This is the default standard profile provided to a user.
#       They are expected to edit it to meet their own needs.
MAIL=/usr/mail/${LOGNAME:?}
# The following three lines have been added by UDB DB2.
if [ -f sqllib/db2profile ]; then
    . sqllib/db2profile
fi
Listing 4: .profile - the DB2 instance owner default profile

The DB2 installation procedure customizes UNIX profile execution, by redirecting logon execution to the db2profile environment file. Db2profile contains all of the necessary settings for executing DB2 database software. A user can change this default behavior, customizing entries according specific needs.

.rhosts - remote execution configuration file (UNIX)

File Name

Default Location

.rhosts

$HOME

The .rhosts file contains a list of the remote hosts having permissions to invoke certain commands on the local host without supplying a password. The DB2 database uses this file for allowing remote command execution for the multi-partition configuration database system. Each DB2 ESE database partition must have authority to perform remote commands on all of the other database partitions.

$ ls -lrt .rhosts
-rw-------   1 db2inst1 db2dba      18 Feb  6  2003 .rhosts

$ cat .rhosts
ARTIST001 db2inst1
Listing 5: .rhosts - UNIX trusted remote hosts configuration file

In the displayed example, we had setup with one physical machine, hosting multiple DB2 ESE partitions in MDP configuration.

The DB2 Instance Configuration Files

In contrast to the operating system configuration files, a DB2 database instance has numerous configuration points. Under normal conditions, a DBA does not need to intervene and update any of them manually. The next picture displays the DB2 instance configuration files related to a single machine running different DB2 database software installations, (DB2 version 7.1 and version 8.1), simultaneously.

Click for larger image

Each configuration file has a relationship to the previous one in the tree. All files do not necessarily exist on every installed DB2 system. Search order over configuration files follows a strict order:

  • session environment variables
  • global DB2 profile registry (global.reg)
  • DB2 software version profile registry (profiles.reg)
  • global instance profile registry (default.env)
  • local instance profile registry (profile.env)
  • node level profile registry (node_num.env)
  • instance owner profile (db2profile)
  • custom configuration profile (userprofile)
  • partition configuration file (db2nodes.cfg)

global.reg - global DB2 profile registry

File Name

Default Location

global.reg

/var/db2

A global.reg configuration file, acts as a root configuration file for all DB2 installations on the machine. A UNIX system with only one database version installed does not have this file. My demonstration installation has installed DB2 versions 7 and 8, where the global.reg was located on the highest directory structure position. It is used as a general, system DB2 configuration file.

$ ls -lrt
total 6
drwxr-xr-x   2 root     other        512 Oct 16  2002 v71
drwxr-xr-x   2 root     other        512 May 25 13:37 v81
-rw-r--r--   1 root     other        251 May 26 15:39 global.reg

$ file global.reg
global.reg:     data

$ strings global.reg
/opt/IBM/db2/V8.1
db2inst1
/home/db2inst1/sqllib
/opt/IBM/db2/V8.1
/opt/IBM/db2/V8.1
Listing 6: global.reg - global DB2 profile registry

profiles.reg - DB2 software version profile registry

File Name

Default Location

profiles.reg

/var/db2/v71

/var/db2/v81

The profiles.reg configuration file holds a list of all instance profiles running under the same DB2 software version. In the other words, a list of DB2 instances running the same code version. On a UNIX machine having only one DB2 software version installed, profiles.reg is used as the global profile registry file. The profiles.reg file becomes the second configuration file in the hierarchy on a machine with several installed DB2 versions, and is read accordingly--after the global.reg configuration file.

$ ls -lrt profiles.reg
-rw-r--r--   1 root     other          8 Jul 26  2002 profiles.reg
 
$ cat profiles.reg
db2inst1
 
$ db2set -l
db2inst1
Listing 7: profiles.reg - DB2 version profile registry

IBM has provided the db2set system command for displaying and changing the profiles.reg file content.

default.env - global instance profile registry

File Name

Default Location

default.env

/var/db2/v71

/var/db2/v81

The local instance profile holds certain important information related to the local instance settings, such as license information or default host name. Profile content is normally managed using the DB2 system command 'db2set -g'.

$ ls -lrt default.env
-rw-rw-r--   1 dbinst1  db2dba         44 Feb  6  2003 default.env
 
$ cat default.env
DB2_EEE_LICENSE_POLICY='34512834284444'
DB2SYSTEM='ARTIST001'
 
>> db2set -g
DB2_EEE_LICENSE_POLICY='34512834284444'
DB2SYSTEM='ARTIST001'
Listing 8: default.env - local instance profile registry

This profile is also called global, generating a misunderstanding to the overall perception of the IBM configuration. Even though the content handling is managed with db2set global command switch "-g", this setting represents real, local instance registry settings.

profile.env - local instance profile registry

File Name

Default Location

UNIX default

profile.env

$HOME/sqllib

Yes

This is a central DB2 configuration file. According to IBM, it is strongly recommended that all DB2-specific registry variables be defined through this registry file. The explanation lays in the remote administration of these variables over Control Center, which would not be possible if registry variables were defined elsewhere. Only the PATH and DB2INSTANCE variables should be defined outside of the DB2 local instance profile.

$ ls -lrt profile.env
-rw-rw-r--   1 db2inst1  db2dba       318 Oct 22  2002 profile.env
 
$ cat profile.env
DB2AUTOSTART='TRUE'
DB2_EXTENDED_OPTIMIZATION='ON'
DB2_DISABLE_FLUSH_LOG='ON'
DB2_STRIPED_CONTAINERS='ON'
DB2_HASH_JOIN='YES'
DB2DBDFT='artist'
DB2COMM='TCPIP'
DB2_PARALLEL_IO='*'
 
$ db2set -i
DB2AUTOSTART='TRUE'
DB2_EXTENDED_OPTIMIZATION='ON'
DB2_DISABLE_FLUSH_LOG='ON'
DB2_STRIPED_CONTAINERS='ON'
DB2_HASH_JOIN='YES'
DB2DBDFT='artist'
DB2COMM='TCPIP'
DB2_PARALLEL_IO='*'
Listing 9: profile.env - local instance profile

userprofile - custom configuration profile

File Name

Default Location

userprofile

$HOME/sqllib

Userprofile is a custom configuration file that is supposed to hold all non-standard db2profile variables. This file is of high importance during fix pack installations, because of the possibility that every new fix pack might clear all non-standard settings from db2profile. The db2profile file, by default, calls the userprofile after the DB2 environment is initialized.

$ ls -lrt userprofile
-rwxr-xr-x   1 db2inst1 db2dba       123 Feb  6  2003 
 
$ cat /export/home/db2inst1/sqllib/userprofile
export DSMI_CONFIG=/opt/tivoli/tsm/client/api/bin/dsm.opt
export DSMI_DIR=/opt/tivoli/tsm/client/api/bin
export DSMI_LOG=/home/db2inst1 
 
$ tail -f db2profile
 
if [ -f ${INSTHOME?}/sqllib/userprofile ]
then
    . ${INSTHOME?}/sqllib/userprofile
fi
Listing 10: userprofile - custom configuration profile


node_num.env - DB2 instance node level profile registry



File Name Default Location
"node_num".env $HOME/sqllib/nodes


An SDP configured database system, with the DB2 database divided across different physical database partitions, has a node_num.env registry file on every database partition. My demo DB2 database ARTIST was running on only one physical machine and did not have a node-level profile registry file.



db2profile - DB2 instance owner profile



File Name Default Location
db2profile /opt/IBM/db2/V7.1 (template)
/opt/IBM/db2/V8.1 ( template)
$HOME/sqllib

The dB2profile is the most important DB2 configuration file. It is created automatically by the DB2 installation, and has all of the needed settings to activate DB2 software properly.

$ ls -lrt $HOME/sqllib/db2profile
-rwxr-xr-x   1 db2inst1  db2dba      4906 May 26 15:39 
 
$ cat db2profile
DB2DIR="/opt/IBM/db2/V8.1"
AddtoString()
{
  var=$1
  addme=$2
  awkval='$1 != "'${addme?}'"{print $0}'
  newval=`eval /usr/bin/echo \\${$var} | /usr/bin/awk "${awkval?}" RS=:`
  eval ${var?}=`/usr/bin/echo $newval | /usr/bin/sed 's/ /:/g'`:${addme?}
  unset var addme awkval newval
}
DB2INSTANCE=instancename
export DB2INSTANCE
INSTHOME=instancehomedir
AddtoString PATH ${INSTHOME?}/sqllib/bin
AddtoString PATH ${INSTHOME?}/sqllib/adm
AddtoString PATH ${INSTHOME?}/sqllib/misc
export PATH
if [ -f ${INSTHOME}/dmb/dmbprofile ]; then
    . ${INSTHOME}/dmb/dmbprofile
fi
CLASSPATH=${CLASSPATH:-""}
if [ -f ${INSTHOME?}/sqllib/java/db2java.zip ]; then
    AddtoString CLASSPATH ${INSTHOME?}/sqllib/java/db2java.zip
fi
if [ -f ${INSTHOME?}/sqllib/java/db2jcc.jar ]; then
    AddtoString CLASSPATH ${INSTHOME?}/sqllib/java/db2jcc.jar
fi
if [ -f ${INSTHOME?}/sqllib/java/sqlj.zip ]; then
    AddtoString CLASSPATH ${INSTHOME?}/sqllib/java/sqlj.zip
fi
if [ -d ${INSTHOME?}/sqllib/function ]; then
    AddtoString CLASSPATH ${INSTHOME?}/sqllib/function
fi
 
if [ -f ${INSTHOME?}/sqllib/java/db2jcc_license_cisuz.jar ]; then
    AddtoString CLASSPATH ${INSTHOME?}/sqllib/java/db2jcc_license_cisuz.jar
fi
 
if [ -f ${INSTHOME?}/sqllib/java/db2jcc_license_cu.jar ]; then
    AddtoString CLASSPATH ${INSTHOME?}/sqllib/java/db2jcc_license_cu.jar
fi
AddtoString CLASSPATH .
export CLASSPATH
if [ -d ${INSTHOME?}/sqllib/templates ]; then
    VWS_TEMPLATES=${INSTHOME?}/sqllib/templates
    export VWS_TEMPLATES
fi
if [ -d ${INSTHOME?}/sqllib/logging ]; then
    VWS_LOGGING=${INSTHOME?}/sqllib/logging
    export VWS_LOGGING
fi
VWSPATH=${INSTHOME?}/sqllib
export VWSPATH
LD_LIBRARY_PATH=${LD_LIBRARY_PATH:-""}
AddtoString LD_LIBRARY_PATH ${INSTHOME}/sqllib/lib
export LD_LIBRARY_PATH
THREADS_FLAG=native
export THREADS_FLAG
if [ -f ${INSTHOME?}/sqllib/userprofile ]
then
    . ${INSTHOME?}/sqllib/userprofile
fi
Listing 11: db2profile - DB2 instance owner profile

db2nodes.cfg - partition configuration file

File Name

Default Location

db2nodes.cfg

$HOME/sqllib/

db2nodes.cfg is the DB2 ESE database partition (node) definition file. A standard, non-partitioned database instance does not use this configuration file.

$ ls -lrt db2nodes.cfg
-r--r--r--   1 db2inst1  db2dba        39 Dec 17  2002 
 
$ cat db2nodes.cfg
0 ARTIST001 0
1 ARTIST001 1
2 ARTIST001 2
Listing 12: db2nodes.cfg - DB2 node instance configuration file

Beginning with DB2 version 8, db2nodes.cfg is enhanced with the resourcename column. This new column provides the opportunity to direct one logical node to use a particular set of system resources. The new db2nodes.cfg file format has the following structure:

      nodenum    hostname   logical port   netname   resourcename  

Only Sun Solaris, version 9 is appropriate to use this new enhancement. For example, a UNIX machine with 8 CPUs could employ 4 CPUs for DB2, leaving the other CPUs for any applications running on the same machine.

Conclusion

Writing this article, one question crossed my mind.

Has anyone attempted to install DB2 software packages into any other directory than default provided by IBM (/var/db2), or has anyone read specific DB2 documentation describing how to organize the DB2 database directories on the server for the most optimal handling?

What I am definitely missing in the DB2 documentation, is a standardized database file structure recommendation. IBM has not published anything, such Oracle has, (an OFA "The Optimal Flexible Architecture"). An OFA is a set of standards that defines how to set up "complex Oracle systems at sites demanding high performance with low maintenance under continually evolving requirements."

Oracle kept this standard for internal use only for some time, but later this came out as a public standard. Thus far, DB2 has not published a similar standard, and we are forced to accept installation procedure defaults along with their consequences.

» See All Articles by Columnist Marin Komadina

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved