Managing Multiple DB2 Instances

Managing Multiple DB2 Instances

Although it may not always be considered to be a best practice, I frequently find myself working on servers with multiple DB2 instances, and I need to switch between managing databases in the different instances quite frequently. I have seen some DBAs manage this by simply logging in to the various instance owner accounts. While this makes the process simple, I am not a proponent of this. From a security (auditing) perspective, I always recommend that DBAs use their own login for performing DBA tasks. It provides a better audit trail, as changes can be attributed directly to the person who made them, instead of the instance owner (e.g., db2inst1).

How your environment is configured

When you log in to a Linux or UNIX system, your shell will read the .profile file in your home directory (bash users can use .profile, or have bash-specific customizations in .bash_profile). In this file, you'll typically find the following lines:

if [[ -f /home/db2inst1/sqllib/db2profile ]] ; then
. /home/db2inst1/sqllib/db2profile
fi

This checks for the existence of the db2profile file for db2inst1, and then executes that script in your current environment. The db2profile makes a number of changes to your environment, but the most critical are setting the DB2INSTANCE environment variable and adding the locations of your instance's binaries to the PATH.

Once the db2profile has configured your shell's environment, you're ready to use the DB2 Command Line Processor (for the db2inst1 instance):

$ db2 "connect to I1DB"

Database Connection Information

Database server = DB2/AIX64 10.5.5
SQL authorization ID = IBJORHOV
Local database alias = I1DB

Managing a Second Instance

If you need to manage the databases in a different instance on the same server, you need to reconfigure your environment. As mentioned above, simply using su to log in as the second instance owner isn't a great solution, nor is editing your .profile. 

Typically you would need to do 2 things from the command line: Terminate the DB2 Backend Process (db2bp), and re-initialize your environment using the other instance's db2profile. Many people forget to perform the first step, and are confused when db2 commands don't see databases in the new instance.

$ db2 terminate
DB20000I The TERMINATE command completed successfully.

$ . /home/db2inst2/sqllib/db2profile

After these 2 steps, should be able to manage the second instance and it's databases.

$ db2 "connect to I2DB"

Database Connection Information

Database server = DB2/AIX64 10.5.5
SQL authorization ID = IBJORHOV
Local database alias = I2DB

This solution works, but it makes it a little more complicated than is necessary. We can certainly come up with a better solution.

Simplifying the Switch

Automating the commands to switch between DB2 instances should be pretty simple – you can write a simple shell script to do this or even create a shell function in your .profile, like this:

#
# Function to switch DB2 instance profiles
#
switchDB2instance() {
instance=${1:-null}
db=${2}

home=$(grep ^${instance}: /etc/passwd | cut -d: -f6)

if [[ ! -f ${home:-null}/sqllib/db2profile ]] ; then
print "Error: ${instance} is not a valid DB2 instance."
return
fi

# Switch Instance
if [[ ! -z "${DB2INSTANCE}" ]] ; then
db2 terminate
fi
. ${home}/sqllib/db2profile
export DB2DBDFT=${db}

unset instance db
}

You can execute this function from the command line, and provide the name of the instance as an argument. Not only does it take care of the 2 steps mentioned above, but it also takes care of validating that the instance exists on the server and where its db2profile is located. 

$ switchDB2instance db2inst2
DB20000I The TERMINATE command completed successfully.

It is also possible to place the contents of the switchDB2instance function in a standalone shell script, but I prefer to keep the function in my .profile since it's responsible for configuring my environment.

The switchDB2instance function also takes an optional second argument - the name of a database – to set the DB2DBDFT environment variable. If this environment variable is set, the DB2 CLP will implicitly connect to the database (avoiding the need to issue a db2 connect to I1DB statement).

Setting up your Environment

If you are using the switchDB2instance function, you can modify your .profile a little further to save a few keystrokes by using shell aliases.

#
# Set up shell aliases to quickly switch
#
alias i1='switchDB2instance db2inst1 I1DB'
alias i2='switchDB2instance db2inst2 I2DB'

Defining these aliases will let you type i1 instead of switchDB2instance db2inst1 I1DB and i2 instead of switchDB2instance db2inst2 I2DB.

Exercise Care

If you're switching between various DB2 instances on a server frequently, it is a very good idea that you have some visual indication of which DB2 instance is currently active. Without this, it can be quite simple to accidentally execute a command against the wrong DB2 instance.

I use the shell prompt to report which instance profile is currently active, via the $DB2INSTANCE environment variable. Here are examples for bash and ksh that you can add to your .profile, to get a more useful shell prompt.

bash

export PS1="\u@\h:\w (\$DB2INSTANCE) \$ "
export PS1

Will result in a prompt that shows your current username, the server's hostname, the current working directory (which may be abbreviated – the ~ represents your home directory), and finally the current value of the $DB2INSTANCE environment variable, shown in parenthesis. This looks like:

idbjorh@db2linux:~ (db2inst1) $

ksh

If you're on AIX, chances are that you are using ksh as your shell. To get a similar prompt, add the following lines to your .profile:

PS1=`echo '"\n"'``echo $USER@``hostname -s``echo ' ($DB2INSTANCE):"\n"'`'$PWD \$ '
export PS1

This will result in a 2-line shell prompt (because ksh doesn't abbreviate the current working directory):

idbjorh@db2aix (db2inst1):
/home/idbjorh $

In both cases, using a command like switchDB2instance db2inst2 will result in a modified shell prompt, like:

idbjorh@db2linux:~ (db2inst1) $ switchDB2instance db2inst2
idbjorh@db2linux:~ (db2inst2) $

Seeing the name of the "active" DB2 instance in parenthesis as part of your shell prompt should help provide you with a reminder of which instance you're currently using.

What about Windows?

If you're managing DB2 databases on a Windows server, the settings described here obviously won't work, but the concepts are similar. Windows is actually a little simpler, because the path to all of the executables does not change.


To change between DB2 instances (within a single copy of DB2), you'll need to perform similar steps. For example, if your Windows system has 2 instances, DB201 and DB202, you would need to do the following to change from the default instance (DB201) to the DB202 instance (within the DB2 Command Window):

  1. Issue db2 terminate to terminate the DB2 Backend Process (db2bp.exe)
  2. Issue the set DB2INSTANCE=DB2_02 command to change the DB2INSTANCE environment variable.

You should be able to write a .bat or perhaps even a PowerShell script to help automate this.


Conclusion

Managing multiple DB2 instances on a single server does not mean that you need to log in as each individual instance owner to support each instance. The shell function and aliases will help simplify the steps you need to follow to switch between managing different instances on a single server, and have the added benefit of helping to keep your security administrators happy.

 

Recent Stories
Managing Multiple DB2 Instances

Introduction to XML in DB2 11 for z/OS – Part 6 – XQuery basics

DB2 11 Fundamentals for z/OS