IBM Books

Administration Guide

Administering DB2 in an MSCS Environment

If you are using MSCS clusters, your DB2 instance requires additional planning with regards to daily operation, database deployment, and database configuration. For DB2 to execute transparently on any MSCS node, additional administrative tasks must be performed. All DB2 dependent operating system resources must be available on all MSCS nodes. Some of these operating system resources fall outside the scope of MSCS. That is, they cannot be defined as an MSCS resource. You must ensure that each system is configured such that the same operating system resources are available on all MSCS nodes. The sections that follow describe the additional work that must be done.

Starting and Stopping DB2 Resources

You must start and stop DB2 resources from the Cluster Administrator tool. Several mechanisms are available to start a DB2 instance such as the db2start command, and the Services option from the Control Panel. However, if DB2 is not started from the Cluster Administrator, the MSCS software will not be aware of the state of DB2 instance. If a DB2 instance is started using the Cluster Administrator and stopped using the db2stop command, the MSCS software will interpret the db2stop command as a software failure and attempt to restart DB2. (The current MSCS interfaces do not support notification of a resource state.)

Similarly, if you use db2start to start a DB2 instance, MSCS cannot detect that the resource is online. If a database server failed, MSCS would not bring the DB2 resource online on the failover machine in the cluster.

Three operations can be applied to a DB2 instance:

This operation is equivalent to using the db2start command. If DB2 is already active, this operation can be used simply to notify MSCS that DB2 is active. Any errors during this operation will be written to the Windows NT Event Log.

This operation is equivalent to using the db2stop command. If there are any active connections to an instance, this operation will fail. This is consistent with the behavior of db2stop.

Fail resource
This operation is equivalent to using the db2stop command with the force option specified. DB2 will disconnect all applications off the DB2 system and stop all database servers.

Running Scripts

You can execute scripts both before and after a DB2 resource is brought online. These scripts must reside in the instance profile directory that is specified for the DB2INSTPROF environment variable. This directory is the directory path that is specified by the -p parameter of the db2icrt command. You can obtain this value by issuing the following command:

   db2set -i:instance_name DB2INSTPROF

This file path must be on a clustered disk so that the instance directory is available on all cluster nodes.

These script files are not required, and are only executed if they are found in the instance directory. They are launched by the MSCS Cluster Service in the background. The script files must redirect standard output to record any output as a result of commands within the script file. The output is not displayed to the screen.

In a partitioned database environment, by default, the same script will be used by every database partition server in the instance. If you need to distinguish among the different database partition servers in the instance, use different assignments of the DB2NODE environment variable to target specific node numbers (for example, use the IF statement in the db2cpre.bat and db2cpost.bat files).

Running Scripts Before Bringing DB2 Resources Online

If you want to run a script before you bring a DB2 resource online, the script must be named db2cpre.bat. DB2 calls functions that will launch this batch file from the Windows NT command line processor and wait for the command line processor to complete execution before the DB2 resource is brought online. You can use this batch file for tasks such as modifying the DB2 database manager configuration. You may want to change some database manager parameter values if the failover system is constrained, and you must reduce the system resources consumed by DB2.

The commands placed in the db2cpre.bat script should execute synchronously. Otherwise the DB2 resource may be brought online before all tasks in the script are completed, which may result in unexpected behavior. Specifically, db2cmd should not be executed in the db2cpre.bat script, because it, in turn, launches another command processor, which will execute DB2 commands asynchronously to the db2cmd program.

If you want to use DB2 CLP commands in the db2cpre.bat script, the commands should be placed in a file and executed as a CLP batch file from within a program that initializes the DB2 environment for the DB2 command line processor, then waits for the completion of the DB2 command line processor. For example:

#include <windows.h>
int WINAPI DB2SetCLPEnv_api(DWORD pid);
void main (int argc, char *argv [ ] )
      STARTUPINFO         startInfo   = {0};
      PROCESS_INFORMATION pidInfo     = {0};
      char     title  [32]   = "Run Synchronously";
      char     runCmd [64]  =
                             "DB2 -z c:\\run.out -tvf c:\\run.clp";
/* Invoke API to setup a CLP Environment */
      if ( DB2SetCLPEnv_api (GetCurrentProcessId ()) == 0 )(1 - see notes below)
         startInfo.cb          = sizeof(STARTUPINFO);
         startInfo.lpReserved  = NULL;
         startInfo.lpTitle     = title;
         startInfo.lpDesktop   = NULL;
         startInfo.dwX         = 0;
         startInfo.dwY         = 0;
         startInfo.dwXSize     = 0;
         startInfo.dwYSize     = 0;
         startInfo.dwFlags     = 0L;
         startInfo.wShowWindow = SW_HIDE;
         startInfo.lpReserved2 = NULL;
         startInfo.cbReserved2 = 0;
               if ( CreateProcessA( NULL,
                              runCmd, (2)
                              NORMAL_PRIORITY_CLASS | CREATE_NEW_CONSOLE,
            WaitForSingleObject (pidInfo.hProcess, INFINITE);
            CloseHandle (pidInfo.hProcess);
            CloseHandle (pidInfo.hThread);


  1. The API DB2SetCLPEnv_api is resolved by the import library DB2API.LIB. This API sets an environment that allows CLP commands to be invoked. If this program is invoked from the db2cpre.bat script, the command processor will wait for the CLP commands to complete.

  2. runCmd is the name of the script file that contains the DB2 CLP commands.

A sample program called db2clpex.exe can be found in the MISC subdirectory of the DB2 install path. This executable is similar to the example provided, but accepts the DB2 CLP command as a command line argument. If you want to use this sample program, copy it to the BIN subdirectory. You can use this executable in the db2cpre.bat script as follows (INSTHOME is the instance directory).

  db2clpex "DB2 -Z INSTHOME\pre.log -tvf INSTHOME\pre.clp"

All DB2 attach commands or connect statements should explicitly specify a user, otherwise they will be executed under the user account associated with the cluster service. CLP scripts should also complete with the terminate command to end the CLP background process.

The following is an example of a db2cpre.bat file:

db2cpre.bat : (1 - see notes below)
db2clpex "db2 -z INSTHOME\pre-%DB2NODE%.log (2, 3)
   -tvf INSTHOME\pre.clp" (4, 5)
update dbm cfg using MAXAGENTS 200;
get dbm cfg;


  1. The db2cpre.bat script executes under the user account associated with the Cluster Service. If DB2 actions are required, the user account associated with the Cluster Service must be a valid SQL identifier, as defined by DB2.

  2. INSTHOME is the instance directory.

  3. The name of the log file must be different for each node to avoid file contention when both logical nodes are brought online at the same time.

  4. db2clpex.exe is the sample program previously provided using the command line argument to specify the CLP command to execute. (This line of the example has been split at -tvf for formatting reasons.)

  5. The db2clpex.exe sample program must be made available on all MSCS cluster nodes.

  6. The CLP commands in this example set a limit on the number of agents.

Running Scripts After Bringing DB2 Resources Online

If you want to run a script after you bring a DB2 resource online, it must be named db2cpost.bat. The script will be executed asynchronously from MSCS after the DB2 resource has been successfully brought online. The db2cmd command can be used in this script to execute DB2 CLP script files. Use the -c parameter of the db2cmd command to specify that the utility should close all windows on completion of the task. For example:

   db2cmd -c db2 -tvf mycmds.clp

The -c parameter must be the first argument to the db2cmd command, as it prevents orphaned command processors in the background.

The db2cpost.bat script is useful if you want to perform database activities immediately after the DB2 resource fails over and becomes active. For example, you can restart or activate databases in the instance so that they are primed for user access.

The following is an example of a db2cpost.bat script:

db2cpost.bat (1 - see notes below)
db2cmd -c db2 -z INSTHOME\post-%DB2NODE%.log (2, 3)
   -tvf INSTHOME\post.clp (4)
restart database SAMPLE;
connect reset;
activate database SAMPLE;


  1. The db2cpost.bat script executes under the user account associated with the Cluster Service. If DB2 actions are required, the user account associated with the Cluster Service must be a valid SQL identifier, as defined by DB2.

  2. INSTHOME is the instance directory.

  3. The name of the log file must be different for each node to avoid file contention when both logical nodes are brought online at the same time.

  4. The db2cmd command can be used because the db2cpost.bat script can execute asynchronously. The -c parameter must be used to terminate the command processor.

  5. The CLP script in this example contains commands to restart and activate the database. This script returns the database to an active state immediately after the database manager is started. In a partitioned database system you should remove the activate database command because multiple DB2 resources are brought online at the same time: the restart database command may fail because another node is activating the database. If this occurs, rerun the script to ensure that the database is restarted correctly.

Database Considerations

When you create a database, ensure that the database path refers to a share disk. This allows the database to be seen on all MSCS nodes. All logs and other database files must also refer to clustered disks for DB2 to failover successfully. If you do not perform these steps, a DB2 system failure will occur as it will seem to DB2 that files have been deleted or are unavailable.

Also ensure that the database manager and database configuration parameters are set so that amount of system resources consumed by DB2 is supported on either MSCS node. The autorestart database configuration parameter should be set to ON so that the first database connection on failover will bring the database to a consistent state. The default setting for autorestart is ON. The database can also be brought to a ready state by using the db2cpost.bat script to restart and activate the database. This method is preferred, because there will be no dependency on autorestart, and the database is brought to a ready state independent of a user connection request.

User and Group Support

DB2 relies on Windows NT for user authentication and group support. For a DB2 instance to fail over from one MSCS node to another in a seamless fashion, each MSCS node must have access to the same Windows NT security databases. You can achieve this by using Windows NT Domain Security.

Define all DB2 users and groups in a Domain Security database. The MSCS nodes must be members of this Domain or the Domain must be a Trusted Domain. DB2 will then use the Domain Security database for authentication and group support, independent of which MSCS node DB2 is executing on.

If you are using local accounts, the accounts must be replicated on each MSCS node. This approach is not recommended because it is error prone and requires dual maintenance.

DCE Security is also a supported authentication mode, providing that all MSCS nodes are clients in the same DCE cell.

You should associate the MSCS service with a user account that follows DB2 naming conventions. This allows the MSCS service to perform actions against DB2 that may be required in the db2cpre.bat and db2cpost.bat scripts.

Communications Considerations

DB2 supports two LAN protocols in an MSCS Environment:

TCP/IP is supported because it is a supported cluster resource type. To enable DB2 to use TCP/IP as a communications protocol for a partitioned database system, create an IP Address resource and place it in the same group as the DB2 resource that represents the database partition server that you want to use as a coordinator node for remote applications. Then create a dependency using the Cluster Administrator tool to ensure that the IP resource is online before the DB2 resource is started. DB2 clients can then catalog TCP/IP node directory entries to use this TCP/IP address.

The TCP/IP port associated with the svcename database manager configuration parameter must be reserved for use by the DB2 instance on all machines that participate in the instance. The service name associated with the port number must also be the same in the services file on all machines.

Although NetBIOS is not a supported cluster resource, you can use NetBIOS as a LAN protocol because the protocol ensures that NetBIOS names are unique on the LAN. When DB2 registers a NetBIOS name, NetBIOS ensures the name is not in use on the LAN. In a failover scenario, when DB2 is moved from one system to another, the nname used by DB2 will be deregistered from one partner machine in the MSCS cluster and registered on the other machine.

DB2 NetBIOS support uses NetBIOS Frames (NBF). This protocol stack can be associated with different logical adapter numbers (LANA). To ensure consistent NetBIOS access to the server, the LANA associated with the NBF protocol stack should be the same on all clustered nodes. You can configure this by using the Networks option from the Control Panel. You should associate NBF with LANA 0, as this is the default setting expected by DB2.

System Time Considerations

DB2 uses the system time to timestamp certain operations. All MSCS nodes that participate in DB2 failover must have the system time zone and system time synchronized to ensure DB2 behaves consistently on all machines.

Set the system time zone using the Date/Time option from the Control Panel dialog. MSCS has a time service that synchronizes the date and time when the MSCS nodes join to form a cluster. The time service, however, only synchronizes the time every 12 hours, which may result in problems if the time is changed on one system and DB2 fails over before the time is synchronized.

If the date/time is changed on one of the MSCS cluster nodes, the time should be manually synchronized on the other cluster nodes using the command:

  net time /set /y \\remote node

Where remote node is the machine name of the cluster node.

Administration Server and Control Center Considerations in a Partitioned Database Environment

The DB2 Administration Server is (optionally) created during the installation of DB2 Universal Database. It is not a partitioned database system. The Control Center uses the services provided by the Administration Server to administer DB2 instances and databases.

In a partitioned database system, a DB2 instance can reside on multiple MSCS nodes. This implies that a DB2 instance must be cataloged on multiple systems under the Control Center so that the instance remains accessible, regardless of which MSCS node the DB2 instance is active on.

The Administration Server instance directory is not shared. You must mirror all user-defined files in the Administration Server directory to all MSCS nodes to provide the same level of administration to all MSCS nodes. Specifically, you must make user scripts and scheduled executables available on all nodes. You must also ensure that scheduled activities are scheduled on all machines in an MSCS cluster.

Alternatively, instead of duplicating the Administration Server on all machines, you may want to have the Administration Server fail over. For the purposes of the following example, assume that you have two MSCS nodes in the cluster, and they are called MACH0 and MACH1. MACH0 has access to a cluster disk that will be used by the Administration Server. Also assume that both MACH0 and MACH1 have an Administration Server. You would perform the following steps to make the Administration Server highly available:

  1. Stop the Administration Server on both machines by executing the db2admin stop command on each machine.

  2. On all administration client machines, uncatalog all references to the Administration Servers on MACH0 and MACH1 using the db2 uncatalog node command. (You can use the db2 list node directory command on the client machine to determine if any references to the Adminstration Server exist.)

  3. Drop the Administration Server from MACH1 by executing the db2admin drop command from MACH1. (You would only perform this step if you had an Administration Server on both machines.)

  4. Determine the name of the Administration Server by issuing the db2admin command from MACH0. (The default name is DB2DAS00.)

  5. Use the DB2MSCS utility to set up fail-over support for the Administration Server. This entails creating a DB2 resource on MSCS named DB2DAS00 that has dependencies on the IP and disk resources. (If you have a mutual takeover configuration, you would put the resource in the group that holds the DB2 resource for NODE0.) This resource will be used as the MSCS resource that supports the Administration Server. The DB2MSCS.ADMIN file would be as follows:
       # db2mscs.admin for Administration Server
       # run db2mscs -f:db2mscs.admin
       # put Administration server in the same group as DB2 Node 0
       GROUP_NAME=DB2NODE0 (see note below)
       IP_NAME= IP Address for Administration Server
    Note:This group can be the same as the existing group. This way, you do not require an additional disk for the instance profile directory.

  6. On MACH1 execute the following command to set DB2DAS00 as the Administration Server:
       db2set -g db2adminserver=DB2DAS00

  7. On MACH0, modify the start-up properties of DB2DAS00 through the Services program so that it is brought up manually and not automatically, because DB2DAS00 is now controlled by MSCS.

When the Administration Server is enabled for failover, all remote access should use an MSCS IP resource for communicating with the Administration Server. The Administration Server will now have the following properties:

Limitations and Restrictions

When you run DB2 in an MSCS environment:

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

[ DB2 List of Books | Search the DB2 Books ]