Understanding DB2: Learning Visually with Examples (2nd Edition)

5.2. The DB2 Environment

The DB2 environment consists of environment variables and DB2 profile registry variables. These manage, monitor, and control the behavior of a DB2 system.

5.2.1. Environment Variables

You set the environment variables at the operating system level. Most environment variables applicable to DB2 are set automatically during the DB2 installation. For example, the PATH environment variable is updated to point to the DB2 executable code directory.

The two most important environment variables applicable to DB2 are

• DB2INSTANCE, which determines the active instance in your machine, and

• DB2NODE, which determines the active database partition in a multi-partitioned environment.

To review the contents of an environment variable like DB2INSTANCE, you can do the following.

On Windows:

echo %DB2INSTANCE%

or

set DB2INSTANCE

On Linux/UNIX:

export | grep DB2INSTANCE

or

set | grep DB2INSTANCE

To change the value of an environment variable temporarily, use the set operating system command on Windows or export on Linux/UNIX as shown in the following examples.

On Windows:

set DB2INSTANCE=myinst

On Linux/UNIX it would depend on the shell that is used:

• For the Korn shell:

  DB2INSTANCE=myinst export DB2INSTANCE

• For the Bourne shell:

  export DB2INSTANCE=myinst

• For the C shell:

  setenv DB2INSTANCE myinst

This setting will be lost after you close the window or end the session.

NOTE

A common mistake when using the set (Windows) or export (Linux/UNIX) commands is to leave spaces before and/or after the equal sign (=). No spaces must be used!

To create a new or modify an existing environment variable permanently, you can do the following.

• On Windows platforms, use the Control Panel. Figure 5.2 shows an example using Windows 2000 and the environment variable DB2INSTANCE. In the figure, System was selected followed by the Advanced tab, and then the Environment Variables button.

  Figure 5.2. Setting an environment variable permanently in Windows

• On Linux and UNIX platforms you can permanently change an environment variable by adding the export command in the .login or .profile startup scripts. However, rather than making this change directly, edit the script that comes with DB2 to set up the default DB2 environment, and then invoke this script from .login or .profile. DB2 provides the scripts db2profile (for the Bourne and Korn shells) and db2cshrc (for the C shell), which contain all the required commands to set up this default DB2 environment. These script files are located under the INSTHOME/sqllib directory, where INSTHOME represents the instance owner's home directory.

Figure 5.3 shows part of the db2profile script. Note that the DB2INSTANCE environment variable is set in the script file.

Figure 5.3. Part of the db2profile script file for a Linux/UNIX machine

############################################################################# # NAME: db2profile # # FUNCTION: This script sets up a default database environment for # Bourne shell or Korn shell users. # # USAGE: . db2profile # This script can either be invoked directly as above or # it can be added to the user's .profile file so that the # database environment is established during login. # # #### DO NOT EDIT THIS FILE #### ############################################################################# # Default DB2 product directory DB2DIR="/opt/IBM/db2/V8.1" # Function to avoid repetitive environment variable entries AddtoString() { var=$1 addme=$2 awkval='$1 != "'${addme?}'"{print $0}' newval=`eval echo \\${$var} | awk "${awkval?}" RS=:` eval ${var?}=`echo $newval | sed 's/ /:/g'`:${addme?} unset var addme awkval newval } #----------------------------------------------------------------------- # DB2INSTANCE [Default null, values: Any valid instance name] # Specifies the instance that is active by default. #----------------------------------------------------------------------- DB2INSTANCE=db2inst1 export DB2INSTANCE INSTHOME=/home/db2inst1 #----------------------------------------------------------------------- # First remove any sqllib entries from the user's path. # Add the directories: # INSTHOME/sqllib/bin - database executables # INSTHOME/sqllib/adm - sysadm executables # INSTHOME/sqllib/misc - miscellaneous utilities # to the user's PATH. #----------------------------------------------------------------------- AddtoString PATH ${INSTHOME?}/sqllib/bin AddtoString PATH ${INSTHOME?}/sqllib/adm AddtoString PATH ${INSTHOME?}/sqllib/misc export PATH #----------------------------------------------------------------------- # UDB Extender initialization #----------------------------------------------------------------------- if [ -f ${INSTHOME}/dmb/dmbprofile ]; then . ${INSTHOME}/dmb/dmbprofile fi #----------------------------------------------------------------------- # The following variables are used for JDBC support #----------------------------------------------------------------------- CLASSPATH=${CLASSPATH:-""} if [ -f ${INSTHOME?}/sqllib/java/db2java.zip ]; then AddtoString CLASSPATH ${INSTHOME?}/sqllib/java/db2java.zip fi ...

For the DB2 instance owner, a line to invoke the db2profile/db2cshrc script file is automatically added during the instance creation. If you work with DB2 but are not the instance owner, add the following line to your .login or .profile startup scripts:

. INSTHOME/sqllib/db2profile    (for Bourne and Korn shells)

or

source INSTHOME/sqllib/db2cshrc (for C shell)

Executing the above commands will guarantee that your database environment is configured to use DB2.

5.2.2. DB2 Profile Registries

Most DB2-specific information is stored in a centralized repository called the DB2 profile registry. Depending on the operating system platform where DB2 is installed, variables stored in the DB2 profile registries may be different. The DB2 profile registry variables are commonly referred to as DB2 registry variables.

NOTE

The word "Registry" always causes confusion when working with DB2 on the Windows platform. The DB2 profile registry variables have no relationship to the Windows registry variables.

The DB2 profile registry consists of the following registries.

• The DB2 Instance-Level Profile Registry: Variables set at this level apply only to a specific instance.

• The DB2 Global-Level Profile Registry: Variables set at this level apply globally to all instances.

• The DB2 Instance Node-Level Profile Registry: Variables at this level apply to a specific partition in a multi-partitioned environment.

• The DB2 Instance Profile Registry: Contains a list of all instances in the system. The command db2ilist, which lists all instances in a system, uses this registry as input.

NOTE

All variables in the DB2 registries except those in the DB2 Instance Profile Registry are the same. The difference is at which level you set the variable. For example, you can set the DB2COMM registry variable at the instance-level, global-level, or node-level profile registries.

The DB2 registries are stored as binary or text files in different locations depending on the operating system. To modify these registries, do not edit these files directly; instead, use the db2set command. Figure 5.4 shows the db2set command with the -all option, which lists all of the currently set DB2 profile registry variables.

Notice that each registry variable is prefixed with a letter in square brackets. This indicates in which level the variable is set.

• [i] indicates the variable has been set at the DB2 Instance-Level Profile Registry using the -i option (which is the default). For example, in Figure 5.4 [i] DB2COMM= appc,tcpip,npipes was set using the following command in instance myinst:

  db2set DB2COMM=appc,tcpip,npipes -i myinst

• [g] indicates the variable has been set at the DB2 Global-Level Profile Registry using the -g option. This setting applies to all instances defined on the DB2 server. For example, in Figure 5.4, [g] DB2COMM=netbios was set using this command:

  db2set DB2COMM=netbios -g

• [e] indicates a DB2 registry variable has been set as an environment variable using the set command (Windows) or export command (Linux/UNIX). For example, in Figure 5.4 [e] DB2COMM=tcpip was set using this command:

  set DB2COMM=tcpip

Although most DB2 registry variables can be set as an environment variable, we recommend setting them as DB2 registry variables using the db2set command. Changes to DB2 registry variables do not require a server reboot, while changes to environment variables may require a reboot.

To set a registry variable at the DB2 instance node-level profile registry level, use a command with this syntax:

db2set registry_variable=value -i instance_name partition_number

NOTE

Like the set operating system command, do not leave spaces before and/or after the equal sign (=) when using the db2set command.

In Figure 5.4 the DB2COMM registry variable was set three times with different values each at the [e], [i], and [g] levels. When a registry variable is defined at different levels, DB2 will choose the value using this search order.

1. Environment variable set using the set/export operating system commands.

2. DB2 Instance Node-Level Profile Registry

3. DB2 Instance-Level Profile Registry

4. DB2 Global-Level Profile Registry

Based on this search order, for the example in Figure 5.4, the value tcpip for the DB2COMM registry variable is the one that takes precedence as it has been set temporarily at the environment level.

Table 5.1 summarizes other options commonly used with the db2set command.

Some registry variables require that you stop and start the instance (db2stop/db2start) for the change to take effect. Refer to the DB2 UDB Administration Guide: Performance for a list of variables that have this requirement.