Procedure:
It is strongly recommended that all DB2-specific registry variables be defined in the DB2 profile registry. If DB2 variables are set outside of the registry, remote administration of those variables is not possible, and the workstation must be rebooted in order for the variable values to take effect.
Windows operating systems have one system environment variable,
DB2INSTANCE, that can only be set outside the profile registry; however, you are not required to set DB2INSTANCE. The DB2 profile registry variable DB2INSTDEF may be set in the global level profile to specify the instance name to use if DB2INSTANCE is not defined.
DB2 Enterprise Server Edition servers on Windows have two system environment variables, DB2INSTANCE and DB2NODE, that can only be set outside the profile registry. You are not required to set DB2INSTANCE. The DB2 profile registry variable DB2INSTDEF may be set in the global level profile to specify the instance name to use if DB2INSTANCE is not defined.
The DB2NODE environment variable is used to route requests to a target logical node within a machine. This environment variable must be set in the session in which the application or command is issued and not in the DB2 profile registry. If this variable is not set, the target logical node defaults to the logical node which is defined as zero (0) on the machine.
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 9x:Edit theautoexec.bat file, and reboot the system to have the change take effect.
On Windows:You can set the DB2 environment variables DB2INSTANCE and DB2NODE as follows (using DB2INSTANCE in this description):
v (On Windows NT and Windows 2000) Select Start, Settings, Control Panel.
(On Windows XP and Windows .NET) Select Start —> Control Panel.
v (On Windows NT and Windows 2000) Double-click on the System icon.
(On Windows XP and Windows .NET) Depending on the Windows theme and the currently selected view type, you may have to select Performance and Maintenance before you can select the System icon.
v (On Windows NT)In the System Control Panel, in the System Environment Variables section, do the following: (On Windows 2000, Windows XP, and Windows .NET) From the System Properties window you have to select the Advancedtab and then click on the Environment Variables button and do the following:
1. If the DB2INSTANCE variable does not exist:
a. (On Windows NT) Select any system environment variable. (On Windows 2000, Windows XP, and Windows .NET) Click on the New button.
b. (On Windows NT) Change the name in the Variable field to DB2INSTANCE. (On Windows 2000, Windows XP, and Windows .NET) Fill in the Variable Name field with DB2INSTANCE.
c. (On Windows NT) Change the Value field to the instance name, for example db2inst. (On Windows 2000, Windows XP, and Windows .NET) Fill in the Variable Value field with the instance name, for example db2inst.
2. If the DB2INSTANCE variable already exists, append a new value:
a. Select the DB2INSTANCE environment variable.
b. Change the Value field to the instance name, for example db2inst.
3. (On Windows NT) Select Set. (On Windows 2000, Windows XP, and Windows .NET) Select OK.
4. Select OK.
5. Reboot your system for these changes to take effect.
Note: The environment variable DB2INSTANCE can also be set at the session (process) level. For example, if you want to start a second DB2 instance called TEST, issue the following commands in a command window:
setDB2INSTANCE=TEST db2start
When working in C Shell, issue the following commands in a command window:
setenv DB2INSTANCE TEST
The profile registries are located as follows:
v The DB2 Instance Level Profile Registry in the Windows operating system registry, with the path:
\HKEY_LOCAL_MACHINE\SOFTWARE\IBM\DB2\PROFILES\instance_name
Note: The instance_name is the name of the DB2 instance.
v The DB2 Global Level Profile Registry in the Windows registry, with the path:
\HKEY_LOCAL_MACHINE\SOFTWARE\IBM\DB2\GLOBAL_PROFILE
v The DB2 Instance Node Level Profile Registry in the Windows registry, with the path:
...\SOFTWARE\IBM\DB2\PROFILES\instance_name\NODES\node_number
Note: The instance_name and the node_number are specific to the database partition you are working with.
v There is no DB2 Instance Profile Registry required. For each of the DB2 instances in the system, a key is created in the path:
\HKEY_LOCAL_MACHINE\SOFTWARE\IBM\DB2\PROFILES\instance_name
The list of instances can be obtained by counting the keys under the PROFILES key.
Related concepts:
v “DB2 Administration Server” on page 44 Related tasks:
v “Setting environment variables on UNIX systems” on page 37
Setting environment variables on UNIX systems
Procedure:
It is strongly recommended that all DB2-specific registry variables be defined in the DB2 profile registry. If DB2 variables are set outside of the registry, remote administration of those variables is not possible.
On UNIX operating systems, you must set the system environment variable DB2INSTANCE.
The scriptsdb2profile (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 ininsthome/sqllib, where insthome is the home directory of the instance owner.
These scripts include statements to:
v Update a user’s path with the following directories:
– insthome/sqllib/bin – insthome/sqllib/adm – insthome/sqllib/misc
v Set DB2INSTANCE to the default local instance_name for execution.
Note: Except for PATH and DB2INSTANCE, all other DB2-supported variables must be set in the DB2 profile registry. To set variables that are not supported by DB2, define them in your script files,userprofile andusercshrc.
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:
v For Korn shell:
DB2INSTANCE=inst1 exportDB2INSTANCE v For Bourne shell:
exportDB2INSTANCE=<inst1>
v For C shell:
setenv DB2INSTANCE <inst1>
In order for the DB2 profile registry to be administered properly, the following file ownership rules must be followed on UNIX operating systems.
v The DB2 Instance Level Profile Registry file is located under:
INSTHOME/sqllib/profile.env
The access permissions and ownership of this file should be:
-rw-rw-r-- <db2inst1> <db2iadm1> profile.env
where<db2inst1> is the instance owner, and <db2iadm1> is the instance owner’s group.
TheINSTHOME is the home path of the instance owner.
v The DB2 Global Level Profile Registry is located under:
– /var/db2/<version_id>/default.env for AIX, Solaris, and Linux operating systems (where<version_id> is the current version).
– /var/opt/db2/<version_id>/default.env for the HP-UX operating system (where<version_id> is the current version).
The access permissions and ownership of this file should be:
-rw-rw-r-- <Instance_Owner> <Instance_Owner_Group> default.env
In order to modify a global registry variables, a user must be logged on as:
root.
v The DB2 Instance Node Level Profile Registry is located under:
INSTHOME/sqllib/nodes/<node_number>.env
The access permissions and ownership of the directory and this file should be:
drwxrwsr-w <Instance_Owner> <Instance_Owner_Group> nodes
-rw-rw-r-- <Instance_Owner> <Instance_Owner_Group> <node_number>.env
TheINSTHOME is the home path of the instance owner.
v The DB2 Instance Profile Registry is located under:
– /var/db2/<version_id>/profiles.reg for AIX, Solaris, and Linux operating systems (where<version_id> is the current version).
– /var/opt/db2/<version_id>/profiles.reg for the HP-UX operating system (where<version_id> is the current version).
The access permissions and ownership of this file should be:
-rw-r--r-- rootsystem profiles.reg
Related concepts:
v “DB2 Administration Server” on page 44 Related tasks:
v “Setting environment variables on Windows” on page 34
Creating a node configuration file
Procedure:
If your database is to operate in a partitioned database environment, you must create a node configuration file calleddb2nodes.cfg. This file must be located in thesqllib 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 Considerations
If you are using DB2 Enterprise - Server Edition on Windows, the node configuration file is created for you when you create the instance. You should not attempt to create or modify the node
configuration file manually. You can use the db2ncrt command to add a database partition server to an instance. You can use the db2ndrop command to drop a database partition server to an instance. You can use the db2nchg command to modify a database partition server configuration including moving the database partition server from one machine to another; changing the TCP/IP host name; or, selecting a different logical port or network name.
Note: You should not create files or directories under thesqllib 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. The other exception is when user-defined 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:
dbpartitionnum hostname [logical-port [netname]]
Tokens are delimited by blanks. The variables are:
dbpartitionnum
The database partition number, which can be from 0 to 999, uniquely defines a node. Database partition numbers must be in ascending sequence. You can have gaps in the sequence.
Once a database partition 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 database partition number can be used again for any new node that you add.
The database partition number is used to generate a node name in the database directory. It has the format:
NODEnnnn
The nnnn is the database partition number, which is left-padded with zeros. This database partition number is also used by the CREATE DATABASEand DROP DATABASE commands.
hostname
The hostname of the IP address for inter-partition communications.
(There is an exception whennetname is specified. In this situation, netname is used for most communications, with hostname only being used for db2start, db2stop, and db2_all.)
logical-port
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 theetc/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 eachhostname, one logical-port must be either 0 (zero) or blank (which defaults to 0). The node associated with thislogical-port is the default node on the host to which clients connect. You can override this with the DB2NODE environment variable indb2profile script, or with the sqlesetc() API.
If you have multiple nodes on the same host (that is, more than one dbpartitionnum for a host), you should assign the logical-port numbers to the logical nodes in ascending order, from 0, with no gaps.
netname
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 partitions, and uses SP2SW1 as the DB2 Universal Database interface.
It also shows the partition numbers starting at 1 (rather than at 0), and a gap in thedbpartitionnum sequence:
Table 1. Database partition number example table.
dbpartitionnum hostname logical-port netname
1 SP2EN1 0 SP2SW1
2 SP2EN1 1 SP2SW1
4 SP2EN2 0
5 SP2EN3
You can update thedb2nodes.cfg file using an editor of your choice. (The exception is: an editor should not be used on Windows.) You must be careful, however, to protect the integrity of the information in the file, as data
partitioning requires that the database partition number not be changed. The node configuration file is locked when you issue db2start and unlocked after db2stopends 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.
Related concepts:
v “Guidelines for stored procedures” in the Administration Guide: Performance Related reference:
v “db2start - Start DB2” in the Command Reference v “db2stop - Stop DB2” in the Command Reference v “CREATE DATABASE” in the Command Reference v “DROP DATABASE” in the Command Reference
v “db2nchg - Change Database Partition Server Configuration” in the Command Reference
v “db2ncrt - Add Database Partition Server to an Instance” in the Command Reference
v “db2ndrop - Drop Database Partition Server from an Instance” in the Command Reference