IBM Books

Administration Guide

Before Creating a Database

Before creating a database, you should consider or carry out the following tasks:

Design Logical and Physical Database Characteristics

You must make logical and physical database design decisions before you create a database. To find out more about logical database design, see Chapter 1. "Designing Your Logical Database". To find out more about physical database design, see Chapter 2. "Designing Your Physical Database".

Create an Instance

As part of your installation procedure, you create an instance of DB2. It is possible to have more than one instance on a system. You may only work within one instance of DB2 at a time.

Use the db2icrt command to create an instance of DB2. When using this command, you should provide the login name of the instance owner and optionally specify the authentication type of the instance. The authentication type applies to all databases created under that instance. The authentication type is a statement of where the authenticating of users will take place. For more information on authentication, see Chapter 4. "Controlling Database Access". For more information on the db2icrt command, see the Command Reference manual.

Establish Environment Variables and the Profile Registry

Environment and registry variables control your database environment.

Prior to the introduction of the DB2 profile registry, changing your environment variables on Windows or OS/2 workstations (for example) required you to change an environment variable and reboot. Now, your environment is controlled with a few exceptions by registry variables stored in the DB2 profile registries. Use the db2set command to update registry variables without rebooting; this information is stored immediately in the profile registries.
Note:The DB2 environment variables db2instance, db2node, db2path, and db2instprof may not, depending on the operating system, be stored in the DB2 profile registries. In order to update these environment variables, the set command must be used and the system rebooted.

Using the profile registry allows for centralized control of the environment variables. Appendix F. "DB2 Registry and Environment Variables" lists many of the environment variables and registry variables. Different levels of support are now provided through the different environment profiles. Remote administration of the environment variables is also available when using the DB2 Administration Server.

There are four (4) profile registries. They are:

Users can override DB2 Instance Profile Registry environment variable settings for their session by changing session environment variable settings using the db2set command.

DB2 configures the operating environment by checking for registry values and environment variables and resolving them in the following order:

  1. Environment variables set with the set command.
  2. Registry values set with the instance node level profile (using the db2set -I command with a node number as shown below).
  3. Registry values set with the db2set command.
  4. Registry values set with the instance profile (using the db2set -I command as shown below).
  5. Registry values set with the global profile (using the db2set -G command as shown below).

Using the db2set Command

The db2set command supports the local declaration of the registry variables (and environment variables) to a particular setting.

To display help information for the command, use:

   db2set ?

To list the complete set of all supported registry variables, use:

   db2set -lr

To list all currently defined registry variables for this session, use:


To show the current session value of a registry variable, use:

   db2set registry_variable_name

To delete the current session value of a registry variable, use:

   db2set registry_variable_name=

To change a registry variable for this session only, use:

   db2set registry_variable_name=new_value

To change a registry variable default for all databases in the instance, use:

   db2set registry_variable_name=new_value
      -I instance_name

To change a registry variable default for all instances in the system, use:

   db2set registry_variable_name=new_value -G
Note:The two parameters "-I" and "-G" cannot be used at the same time in the same command.

To change a registry variable default for a particular node in an instance, use:

   db2set registry_variable_name=new_value
      -I instance_name node_number

To reset all registry variables for an instance back to the defaults found in the Global Profile Registry, use:

   db2set -r registry_variable_name

To reset all registry variables for a node in an instance back to the defaults found in the Global Profile Registry, use:

   db2set -r registry_variable_name node_number

Setting Environment Variables on OS/2

On OS/2, you should have no environment variables defined in config.sys apart from DB2PATH and DB2INSTPROF. All variables should be defined in the profile registries using the db2set command except for those that remain true environment variables.

DB2INSTANCE also remains a true environment variable, however, it is not required if you make use of the DB2INSTDEF registry variable. This registry variable defines the default instance name to use if DB2INSTANCE is not set.

To set system environment variables, do the following: Edit the config.sys file, and reboot the system to have the change take effect.

The different profile registries are located according to the following:

Setting Environment Variables on Windows NT and Windows 95

On the Windows NT and Windows 95 operating systems, all DB2 environment values should be defined in the profile registries using the db2set command, except for those that are true environment variables. For Windows NT, you should not have the DB2 environment variables defined in either your machine's user or system environment variables sections. On Windows 95, you should not have DB2 environment variables defined in your autoexec.bat file.

To determine the settings of an environment variable, use the echo command. For example, to check the value of the db2path environment variable, enter:

   echo %db2path%

To set system environment variables, do the following:

On Windows 95: Edit the autoexec.bat file, and reboot the system to have the change take effect.

On Windows NT 4.x: You can set the DB2 environment variables db2instance, db2path, and db2instprof as follows:

The profile registries are located as follows:

DB2 UDB provides the capability of accessing DB2 UDB registry variables at the instance level on a remote machine. Currently, DB2 UDB registry variables are stored in three different levels: machine or global level, instance level, and node level. The registry variables stored at the instance level (including the node level) can be redirected to another machine by using DB2REMOTEPREG. When DB2REMOTEPREG is set, DB2 UDB will access the DB2 UDB registry variables from the machine pointed to by DB2REMOTEPREG. For example,

   db2set DB2REMOTEPREG=rmtwkstn

where rmtwkstn is the remote workstation name.
Note:Care should be taken in setting this option since all DB2 instance profiles and instance listings will be located on the specified remote machine name.

This feature may be used in combination with setting DBINSTPROF to point to a remote LAN drive on the same machine that contains the registry.

Setting Environment Variables on UNIX Systems

The scripts db2profile (for Korn shell) and db2cshrc (for Bourne shell or C shell) are provided as examples to help you set up the database environment. You can find these files in insthome/sqllib, where insthome is the home directory of the instance owner.

These scripts include statements to:

An instance owner or SYSADM user may customize these scripts for all users of an instance. Alternatively, users can copy and customize a script, then invoke a script directly or add it to their .profile or .login files.

To change the environment variable for the current session, issue commands similar to the following:

In order for the DB2 profile registry to be administered properly, the following file ownership rules must be followed on UNIX operating systems. (For information on DB2 Administration Server (DAS), see "DB2 Administration Server (DAS)".)

DB2 Administration Server (DAS)

DB2 Administration Server (DAS) is a special DB2 administration control point used only to assist with administration tasks on other DB2 servers. DAS assists the Control Center (CC) and Client Configuration Assistant (CCA) when working on the following administration tasks:

You can only have one DAS on a machine. DAS is configured during installation to start when the operating system is booted.

DAS is used to perform remote tasks on the host system on behalf of a client request from the Control Center or the Client Configuration Assistant. Authorized access to DAS requires clients with SYSADM authority. All of the clients can be part of the SYSADM_GROUP configuration parameter.

Some of the requested tasks may require specific authority to run. The DAS runs under the identifier of a specific user. The privileges granted to that user must be restricted to only those tasks or operations desired by the administrator, but provide sufficient authority to carry out all desired commands. Generally, the tasks or operations required include:

For more information on setting up DAS communications, refer to the Quick Beginnings for your platform.

Creating the DAS

Typically, the DAS is created during DB2 installation. Refer to the Quick Beginnings for details.

As an overview of what occurs during the installation process as it relates to DAS, consider the following:

Once you create an Administration Server, you should use it to establish directory structures and access permissions.

Starting and Stopping the DAS

To start the DAS, enter db2admin start

To stop the DAS, enter db2admin stop
Note:For both cases under Windows NT, the person using these commands must have SYSADM, SYSCTRL, or SYSMAINT authority.
Note:For both cases under UNIX, the person using these commands must have logged on with the authorization ID of the DAS owner.

Configuring the DAS

To see the current values for those administration configuration parameters relevant to the DAS, enter:

   db2 get admin cfg

To update individual entries in the database manager configuration file relevant to the DAS, enter:

   db2 update admin cfg using ...

See the Command Reference for more information on which database manager configuration parameters can be modified.

To reset the configuration parameters to the recommended database manager defaults, enter:

   db2 reset admin cfg

Changes to the database manager configuration file become effective only after they are loaded into memory (that is, when a db2admin stop is followed by a dbadmin start; or, in the case of a Windows NT platform, stopping and starting the service.)

To set up the communications protocols for the DAS, see the Quick Beginnings for your platform.

Security Considerations for the DAS

On OS/2 or Windows NT only, use the following command to associate a user ID with the DAS:

   db2admin setid userid password
Note:Do not use the Windows NT operating system to set the user ID for the DAS. There is no guarantee that the user will receive all required privileges.

It is recommended that the user ID has SYSADM authority on each of the servers within the environment so that it can start or stop other instances if required.

Removing the DAS

To remove the DAS:

Setting Up DAS with EEE Systems

The following information shows the steps necessary to configure DB2 EEE servers (Sun, NT, and AIX) for remote administration using the Control Center (CC).

There are two (2) aspects to configuration: That which is required for the DB2 Administration Server (DAS), and that which is recommended for the target, administered DB2 instance. In the three sections which follow, a section is devoted to each of the two configuration topics. Each of the configuration topics is preceded by a section describing the assumed environment.

Example Environment


Install path:

TCP services file:

DB2 Instance:


owner ID:

instance path:

3 nodes, db2nodes.cfg:

DB name:



owner/user ID:

instance path:

install/run host:

internode communications port:
16000 (unused port for hostA and hostB)
Note:Please substitute site-specific values for the above fields. For example, the following table contains example pathnames for each supported EEE platform:

Table 20. Example Pathnames for Each Supported EEE Platform
Paths DB2 UDB EEE V5.2 for AIX DB2 UDB EEE V5.2 for Solaris DB2 UDB EEE V5.2 for Windows NT
install_path /usr/lpp/db2_05_00 /opt/IBMdb2/V5.0 C:\sqllib
instance_path /home/db2inst/sqllib /home/db2inst/sqllib C:\profiles\db2inst
das_path /home/db2as/sqllib /home/db2as/sqllib C:\profiles\db2as
tcp_services_file /etc/services /etc/services C:\winnt\system32\drivers\ etc\services

DAS Configuration

The DAS is an administrative control point which performs certain tasks on behalf of the Command Center (CC). There can be at most one (1) DAS per physical machine. In the case of an EEE instance which consists of several machines, at least one of the machines must be running a DAS so that the CC can administer the EEE instance. This DAS (db2as) "represents" the system that is present in the CC navigator tree as the parent of the target DB2 instance (db2inst).

For example, db2inst consists of three nodes distributed across two physical machines or hosts. The minimum requirement can be fulfilled by running db2das on either hostA or hostB.


  1. The number of partitions present on hostA does note have any bearing on the number of DASes that can be run on that host. You can run only one copy of db2as on hostA regardless of the multiple logical nodes (MLN) configuration for that host.

  2. It is not necessary to create the DAS ID, db2as, on all hosts. Rather, it is necessary for it to exist only on the host upon which it is running. As well, it is not necessary for the home directory of the DAS ID to be mounted on all hosts. In particular with this example, the ID db2as must exist on hostA, is not required on hostB, and db2as's home directory does not need to be mounted on hostB.

Control Center Communications with DAS: Service Ports

The Control Center (CC) communicates with the DAS using a TCP service port, 523. Since this port is reserved for exclusive use by DB2 UDB, it is not necessary to insert new entries into the tcp_services_file.

Internode Administrative Communications: Service Ports

For some administrative tasks, the DAS must establish communications with all nodes. In order to do so, a named TCP port must be defined in the tcp_services_file for each host which participates in the instance.
Note:Windows NT EEE will attempt to add the TCP port entry into the tcp_services_file for you.

For example, db2inst is defined across two hosts, hostA and hostB. As specified in "Example Environment", port 16000 is unused on both hosts. Therefore, the following line must be inserted into the tcp_services_file for both hostA and hostB.

   db2ccmsrv  16000/tcp

The db2ccmsrv port name must be present, spelled exactly as shown above, and the same port number selected must be used on all hosts.

Internode Administrative Communications: UNIX DB2 EEE Servers

Once the TCP port line is inserted into the tcp_services_file on hostA and hostB, it is necessary to start an administrative listener process or daemon, db2cclist, on all hosts that participate in the instance. You can do so manually from the command line, or configure the system to automatically invoke db2cclst every time the system boots:

From the ID of the instance you wish to administer, db2inst, invoke the following command from either hostA or hostB:
   rah 'install_path/bin/db2cclst'

For example, on AIX this command invocation would appear as:

   rah '/usr/lpp/db2_05_00/bin/db2cclst'

From an ID with Superuser privileges (like root) execute the following command on hostA and hostB:
   mkitab "db2cclst::once:su - db2inst -c install_path /bin/db2cclst"

For example, on AIX this command invocation would appear as:

   mkitab "db2cclst::once:su - db2inst -c install_path

Every time either machine boots, db2cclist is invoked without user intervention.

To verify that the listener daemon is active on each host, the following command can be invoked from the instance ID, db2inst:

   rah 'ps -ef ] grep db2cclst'

If you do not find the db2cclst process running on each host, additional diagnostic information can be obtained by adding the following line to /etc/syslog.conf on each host:

   *.info  /tmp/db2/

where the file /tmp/db2/ can be replaced with a more appropriate file.
Note:The file must exist and the SYSLOG daemon must be asked to re-read its configuration file after the changes are made:
   kill -1 <syslogd PID>
where syslogd PID can be obtained by executing
   ps -ef ] grep syslogd
Then, after manually invoking the listener as described above, you can view the syslog file /tmp/db2/user.infoon the failing host for error messages generated by db2cclst.

Internode Administrative Communications: Windows NT DB2 EEE Servers

The DB2 Remote Command Service (db2rcmd.exe) automatically handles internode administrative communications. In the event that a failure does occur, the Windows NT registry will contain diagnostic information.


In order for the DAS to perform some administrative tasks against an instance, it must possess sufficient authority. In particular, the DAS must be a System Administrator (SYSADM) for the target, administered instance.

It is necessary to grant the DAS such authority for all DB2 instances that it will administer. Candidate instances are those which are installed on the same machine as the DAS. For a DB2 EEE instance, at least one database partition server must be present on the same machine as the DAS for it to be eligible as described above.

For example on UNIX, one way in which db2as can be granted the required authority to administer db2inst is to ensure that the primary groups of db2inst and db2as are identical. Alternatively, it is sufficient to make the primary group of db2inst a secondary group of db2as, and the primary group of db2as a secondary group of db2inst. Finally, another option would be to set the SYSADM_GROUP database administration configuration parameter for db2inst to the primary group of db2as.

On Windows NT, db2as must be a member of the Local Administrators group on hostA and hostB. In addition to the option of creating the db2as ID and adding it to the Local Administrators group on both hosts, one could create a domain ID for db2as and add this domain ID to the Local Administrators group on each host.


Installation for the DAS should configure certain registry variables that are necessary for proper operation. To verify the current values for these variables, execute the following command from either the DB2 instance ID, db2inst, or the DAS ID, db2das:

   db2set -g

At least the following parameters must be defined with the following values:


As well, in order to communicate with the DAS from the Control Center (CC), ensure that the DB2COMM variable is set to TCPIP. To verify this setting, execute the following command from the DAS ID, db2as, and check at the global (-g) and instance (-i) levels (only one need be set):

   db2set -all

Along the same lines, verify that the DB2COMM parameter is set to TCPIP for the DB2 instance to enable communications between the CC and db2inst by issuing the following command from the db2inst ID:

   db2set -all

If you modify this parameter for the DAS, then you must restart the DAS for the change to take effect. Restart of the DB2 instance is also required if this parameter is modified for the DB2 instance. For db2inst, you would issue a db2stop followed by a db2start, whereas db2admin stop and db2admin start would be issued for the DAS.

Discovery of Administration Servers, Instances, and Databases

To enable discovery of other Administration Servers from the Control Center (CC), ensure that the following parameters are set by issuing the db2 get admin cfg command from the db2as ID:


To ensure that db2inst can be discovered, ensure that the following parameters are set by issuing the db2 get dbm cfg command from the db2inst ID:

Note:This is the default value for the DISCOVER_INST parameter.

Similarly, to ensure that the database db2instDB can be discovered, ensure that the following parameter is set by issuing the db2 get db cfg for db2instDB command from the db2inst ID:

Note:This is the default value for the DISCOVER_DB parameter.

Create a Node Configuration File

If your database is to operate in a partitioned database environment, you must create a node configuration file called db2nodes.cfg. This file must be located in the sqllib subdirectory of the home directory for the instance before you can start the database manager with parallel capabilities across multiple partitions. The file contains configuration information for all database partitions in an instance, and is shared by all database partitions for that instance.
Windows NT Considerations:If you are using DB2 Extended Enterprise Edition on Windows NT, the node configuration file is created for you when you create the instance. Refer to the DB2 Extended Enterprise Edition for Windows NT Quick Beginnings for complete instructions on how to set up a partitioned database system.
Note:You should not create files or directories under the sqllib subdirectory other than those created by DB2 to prevent the loss of data if an instance is deleted. There are two exceptions. If your system supports stored procedures, put the stored procedure applications in the function subdirectory under the sqllib subdirectory. (For information on stored procedures, see "Stored Procedures".) The other exception is when user-defined distinct functions (UDFs) have been created. UDF executables are allowed in the same directory.

The file contains one line for each database partition that belongs to an instance. Each line has the following format:

   nodenum hostname [logical-port [netname]]

Tokens are delimited by blanks. The variables are:

The node number, which can be from 0 to 999, uniquely defines a node. Node numbers must be in ascending sequence. You can have gaps in the sequence.

Once a node number is assigned, it cannot be changed. (Otherwise the information in the partitioning map, which specifies how data is partitioned, would be compromised.)

If you drop a node, its node number can be used again for any new node that you add.

The node number is used to generate a node name in the database directory. It has the format:


The nnnn is the node number, which is left-padded with zeros. This node number is also used by the CREATE DATABASE and DROP DATABASE commands.

The hostname of the IP address for inter-partition communications. (There is an exception when netname is specified. In this situation, netname is used for most communications, with hostname only being used for DB2START, DB2STOP, and db2_all.)

This parameter is optional, and specifies the logical port number for the node. This number is used with the database manager instance name to identify a TCP/IP service name entry in the etc/services file.

The combination of the IP address and the logical port is used as a well-known address, and must be unique among all applications to support communications connections between nodes.

For each hostname, one logical-port must be either 0 (zero) or blank (which defaults to 0). The node associated with this logical-port is the default node on the host to which clients connect. You can override this with the DB2NODE environment variable in db2profile script, or with the sqlesetc() API.

If you have multiple nodes on the same host (that is, more than one nodenum for a host), you should assign the logical-port numbers to the logical nodes in ascending order, from 0, with no gaps.

This parameter is optional, and is used to support a host that has more than one active TCP/IP interface, each with its own hostname.

The following example shows a possible node configuration file for an RS/6000 SP system on which SP2EN1 has multiple TCP/IP interfaces, two logical nodes, and uses SP2SW1 as the DB2 Universal Database interface. It also shows the node numbers starting at 1 (rather than at 0), and a gap in the nodenum sequence:

nodenum   hostname   logical-port   netname
1         SP2EN1     0              SP2SW1
2         SP2EN1     1              SP2SW1
4         SP2EN2     0
5         SP2EN3

You can update the db2nodes.cfg file using an editor of your choice. You must be careful, however, to protect the integrity of the information in the file, as data partitioning requires that the node number not be changed. The node configuration file is locked when you issue DB2START and unlocked after DB2STOP ends the database manager. The DB2START command can update the file, if necessary, when the file is locked. For example, you can issue DB2START with the RESTART option or the ADDNODE option.
Note:If the DB2STOP command is not successful and does not unlock the node configuration file, issue DB2STOP FORCE to unlock it.

Creation of the Database Configuration File

A database configuration file is also created for each database. The creation of this file is done for you. This file contains values for various configuration parameters that affect the use of the database, such as:

These parameters are described in detail in Chapter 20. "Configuring DB2", and throughout this book.

Performance Tip: Many of the configuration parameters come with default values, but may need to be updated to achieve optimal performance for your database.

For multiple partitions: When you have a database that is partitioned across more than one partition, the configuration file should be the same on all database partitions. Consistency is required since the SQL compiler compiles distributed SQL statements based on information in the local node configuration file and creates an access plan to satisfy the needs of the SQL statement. Maintaining different configuration files on database partitions could lead to different access plans, depending on which database partition the statement is prepared. Use db2_all to create the same configuration file on all database partitions.

Enable FCM Communications

In a partitioned database environment, most communication between database partitions is handled by the Fast Communications Manager (FCM). To enable the FCM at a database partition and allow communication with other database partitions, you must create a service directory in the partition's /etc/services file as shown below. The FCM uses the specified port to communicate. If you have defined multiple partitions on the same host, you must define a range of ports as shown below.
Windows NT Considerations:If you are using DB2 Extended Enterprise Edition in the Windows NT environment, the TCP/IP port range is automatically added to the services file by:
  • The install program when it creates the instance or addes a new node
  • The DB2ICRT utility when it creates a new instance
  • The DB2NCRT utility when it adds the first node on the machine.

For additional information, refer to the DB2 Extended Enterprise Edition for Windows NT Quick Beginnings.

The syntax of a service entry is as follows:

   DB2_instance port/tcp #comment

The value for instance is the name of the database manager instance. All characters in the name must be lowercase. Assuming an instance name of db2puser, you would specify DB2_db2puser

The TCP/IP port that you want to reserve for the database partition.

Any comment that you want to associate with the entry. The comment must be preceded by a pound sign (#).

If the /etc/services file is shared, you must ensure that the number of ports allocated in the file is either greater than or equal to the largest number of multiple database partitions in the instance. When allocating ports, also ensure that you account for any processor that can be used as a backup.

If the /etc/services file is not shared, the same considerations apply, with one additional consideration: you must ensure that the entries defined for the DB2 instance are the same in all /etc/services files (though other entries that do not apply to your partitioned database do not have to be the same).

If you have multiple database partitions on the same host in an instance, you must define more than one port for the FCM to use. To do this, include two lines in the etc/services file to indicate the range of ports you are allocating. The first line specifies the first port, while the second line indicates the end of the block of ports. In the following example, five ports are allocated for the instance sales. This means no processor in the instance has more than five database partitions.

   DB2_sales         9000/tcp
   DB2_sales_END     9004/tcp
Note:You must specify END in uppercase only. Also you must ensure that you include both underscore (_) characters.

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]