An instance is a logical database manager environment where you catalog databases and set configuration parameters. Depending on your needs, you can create more than one instance. You can use multiple instances to do the following:
v Use one instance for a development environment and another instance for a production environment.
v Tune an instance for a particular environment.
v Restrict access to sensitive information.
v Control the assignment of SYSADM, SYSCTRL, and SYSMAINT authority for each instance.
v Optimize the database manager configuration for each instance.
v Limit the impact of an instance failure. In the event of an instance failure, only one instance is affected. Other instances can continue to function normally.
It should be noted that multiple instances have some minor disadvantages:
v Additional system resources (virtual memory and disk space) are required for each instance.
v More administration is required because of the additional instances to manage.
The instance directory stores all information that pertains to a database instance. You cannot change the location of the instance directory once it is created. The directory contains:
v The database manager configuration file v The system database directory
v The node directory
v The node configuration file (db2nodes.cfg)
v Any other files that contain debugging information, such as the exception or register dump or the call stack for the DB2® processes.
On UNIX® operating systems, the instance directory is located in the INSTHOME/sqllib directory, where INSTHOME is the home directory of the instance owner.
On Windows® operating systems, the instance directory is located in the /sqllib sub-directory, in the directory where DB2 was installed.
In a partitioned database system, the instance directory is shared between all database partition servers belonging to the instance. Therefore, the instance directory must be created on a network share drive that all machines in the instance can access.
As part of your installation procedure, you create an initial instance of DB2 called “DB2”. On UNIX, the initial instance can be called anything you want within the naming rules guidelines. The instance name is used to set up the directory structure.
To support the immediate use of this instance, the following are set during installation:
v The environment variable DB2INSTANCE is set to “DB2”.
v The DB2 registry variable DB2INSTDEF is set to “DB2”.
On UNIX, the default can be called anything you want within the naming rules guidelines.
On Windows, the instance name is the same as the name of the service, so it should not conflict. You must have the correct authorization to create a service.
These settings establish “DB2” as the default instance. You can change the instance that is used by default, but first you have to create an additional instance.
Before using DB2, the database environment for each user must be updated so that it can access an instance and run the DB2 programs. This applies to all users (including administrative users).
On UNIX operating systems, sample script files are provided to help you set the database environment. The files are:db2profile for Bourne or Korn shell, anddb2cshrc for C shell. These scripts are located in the sqllib subdirectory under the home directory of the instance owner. The instance owner or any
user belonging to the instance’s SYSADM group can customize the script for all users of an instance. Alternatively, the script can be copied and customized for each user.
The sample script contains statements to:
v Update a user’s PATH by adding the following directories to the existing search path: thebin, adm, and misc subdirectories under the sqllib subdirectory of the instance owner’s home directory.
v Set the DB2INSTANCE environment variable to the instance name.
Related concepts:
v “Multiple instances on a UNIX operating system” on page 22 v “Multiple instances on a Windows operating system” on page 23 Related tasks:
v “Add an Instance” on page 27
v “UNIX Details When Creating Instances” on page 25 v “Windows Details When Creating Instances” on page 26 v “Setting the current instance” on page 28
v “Auto-starting instances” on page 29
v “Running multiple instances concurrently” on page 29 v “Listing instances” on page 28
v “Creating additional instances” on page 24
Setting the DB2 environment automatically on UNIX
By default, the scripts that set up the database environment when you create an instance affect the user environment for the duration of the current session only. You can change the.profile file to enable it to run the db2profile script automatically when the user logs on using the Bourne or Korn shell.
For users of the C shell, you can change the.login file to enable it to run the db2shrc script file.
Procedure:
Add one of the following statements to the.profile or .login script files:
v For users who share one version of the script, add:
. INSTHOME/sqllib/db2profile (for Bourne or Korn shell) source INSTHOME/sqllib/db2cshrc (for C shell)
whereINSTHOME is the home directory of the instance that you wish to use.
v For users who have a customized version of the script in their home directory, add:
. USERHOME/db2profile (for Bourne or Korn shell) source USERHOME/db2cshrc (in C shell)
whereUSERHOME is the home directory of the user.
Related tasks:
v “Setting the DB2 Environment Manually on UNIX” on page 21
Setting the DB2 Environment Manually on UNIX
Procedure:
To choose which instance you want to use, enter one of the following statements at a command prompt. The period (.) and the space are required.
v For users who share one version of the script, add:
. INSTHOME/sqllib/db2profile (for Bourne or Korn shell) source INSTHOME/sqllib/db2cshrc (for C shell)
whereINSTHOME is the home directory of the instance that you wish to use.
v For users who have a customized version of the script in their home directory, add:
. USERHOME/db2profile (for Bourne or Korn shell) source USERHOME/db2cshrc (in C shell)
whereUSERHOME is the home directory of the user.
If you want to work with more than one instance at the same time, run the script for each instance that you want to use in separate windows. For example, assume that you have two instances calledtest and prod, and their home directories are/u/test and /u/prod.
In window 1:
v In Bourne or Korn shell, enter:
. /u/test/sqllib/db2profile v In C shell, enter:
source /u/test/sqllib/db2cshrc
In window 2:
v In Bourne or Korn shell, enter:
. /u/prod/sqllib/db2profile v In C shell, enter:
source /u/prod/sqllib/db2cshrc
Use window 1 to work with thetest instance and window 2 to work with theprod instance.
Note: Enter the which db2 command to ensure that your search path has been set up correctly. This command returns the absolute path of the DB2 CLP executable. Verify that it is located under the instance’s sqllib directory.
Related tasks:
v “Setting the DB2 environment automatically on UNIX” on page 20
Multiple instances on a UNIX operating system
It is possible to have more than one instance on a UNIX®operating system.
However, you may only work within one instance of DB2® at a time.
Note: To prevent environmental conflicts between two or more instances, you should ensure that each instance has its own home filesystem. Errors will be returned when the home filesystem is shared.
The instance owner and the group that is the System Administration
(SYSADM) group are associated with every instance. The instance owner and the SYSADM group are assigned during the process of creating the instance.
One user ID or username can be used for only one instance. That user ID or username is also referred to as the instance owner.
Each instance owner must have a unique home directory. All of the files necessary to run the instance are created in the home directory of the instance owner’s user ID or username.
If it becomes necessary to remove the instance owner’s user ID or username from the system, you could potentially lose files associated with the instance and lose access to data stored in this instance. For this reason, it is
recommended that you dedicate an instance owner user ID or username to be used exclusively to run DB2.
The primary group of the instance owner is also important. This primary group automatically becomes the system administration group for the instance and gains SYSADM authority over the instance. Other user IDs or usernames that are members of the primary group of the instance owner also gain this level of authority. For this reason, you may want to assign the instance owner’s user ID or username to a primary group that is reserved for the administration of instances. (Also, ensure that you assign a primary group to the instance owner user ID or username; otherwise, the system-default primary group is used.)
If you already have a group that you want to make the system administration group for the instance, you can simply assign this group as the primary group when you create the instance owner user ID or username. To give other users administration authority on the instance, add them to the group that is assigned as the system administration group.
To separate SYSADM authority between instances, ensure that each instance owner user ID or username uses a different primary group. However, if you choose to have a common SYSADM authority over multiple instances, you can use the same primary group for multiple instances.
Related tasks:
v “UNIX Details When Creating Instances” on page 25
Multiple instances on a Windows operating system
It is possible to run multiple instances of DB2®on the same machine. Each instance of DB2 maintains its own databases and has its own database manager configuration parameters.
An instance of DB2 consists of the following:
v A Windows®service that represents the instance. The name of the service is same as the instance name. The display name of the service (from the Services panel) is the instance name, prefixed with the “DB2 - ” string. For example, for an instance named DB2, there exists a Windows service called
“DB2” with a display name of “DB2 - DB2”.
Note: A Windows service is not created for Windows 98, for Windows ME, or for client instances.
v An instance directory. This directory contains the database manager configuration files, the system database directory, the node directory, the DCS database directory, all the diagnostic log and dump files that are associated with the instance. The instance directory is by default a sub-directory inside the SQLLIB directory and has the same name as the instance name. For example, the instance directory for instance “DB2” is C:\SQLLIB\DB2, where C:\SQLLIB is where DB2 is installed. You can use the registry variable DB2INSTPROF to change the default location of the instance directory. If the DB2INSTPROF registry variable is set to another location, then the instance directory is created under the directory pointed to by DB2INSTPROF. For example, ifDB2INSTPROF=D:\DB2PROFS, then the instance directory will beD:\DB2PROFS\DB2.
v A registry key under HKEY_LOCAL_MACHINE\SOFTWARE\
IBM\DB2\PROFILES\<instance_name>. All the instance level registry variables are created here.
You can run multiple DB2 instances concurrently. To work with an instance, you need to set the DB2INSTANCE environment variable to the name of the instance before issuing commands against that instance.
To prevent one instance from accessing the database of another instance, the database files for an instance are created under a directory that has the same name as the instance name. For example, when creating a database on drive C: for instance DB2, the database files are created inside a directory called C:\DB2. Similarly, when creating a database on drive C: for instance TEST, the database files are created inside a directory calledC:\TEST.
Related concepts:
v “High Availability” in the Data Recovery and High Availability Guide and Reference
Related tasks:
v “Windows Details When Creating Instances” on page 26
Creating additional instances
Although an instance is created as part of the installation of DB2 UDB, your business needs may require you to create additional instances.
Prerequisites:
If you belong to the Administrative group on Windows, or you have root authority on UNIX platforms, you can add additional DB2 instances. The machine where you add the instance becomes the instance-owning machine (node zero). Ensure that you add instances on a machine where a DB2 administration server resides.
Procedure:
To add an instance using the command line, enter:
db2icrt<instance_name>
When using the db2icrt command to add another instance of DB2, 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.
You can change the location of the instance directory from DB2PATH using the DB2INSTPROF environment variable. You require write-access for the
instance directory. If you want the directories created in a path other than DB2PATH, you have to set DB2INSTPROF before entering the db2icrt command.
For DB2 Universal Database Enterprise – Server Edition, you also need to declare that you are adding a new instance that is a partitioned database system.
Related concepts:
v “Authentication methods for your server” on page 227
v “Authentication considerations for remote clients” on page 232 Related reference:
v “db2icrt - Create Instance” in the Command Reference
UNIX Details When Creating Instances
When working with UNIX operating systems, the db2icrt command has the following optional parameters:
v –h or –?
This parameter is used to display a help menu for the command.
v –d
This parameter sets the debug mode for use during problem determination.
v –a AuthType
This parameter specifies the authentication type for the instance. Valid authentication types are SERVER, SERVER_ENCRYPT, or CLIENT. If not specified, the default is SERVER, if a DB2 server is installed. Otherwise, it is set to CLIENT.
Notes:
1. The authentication type of the instance applies to all databases owned by the instance.
2. On UNIX operating systems, the authentication type DCE is not a valid choice.
v –u FencedID
This parameter is the user under which the fenced user-defined functions (UDFs) and stored procedures will execute. This is not required if you install the DB2 client or the DB2 Application Development Client. For other DB2 products, this is a required parameter.
Note: FencedID may not be “root” or “bin”.
v –p PortName
This parameter specifies the TCP/IP service name or port number to be used. This value will then be set in the instance’s database configuration file for every database in the instance.
v –s InstType
Allows different types of instances to be created. Valid instance types are:
ese, wse, client, and standalone.
Examples:
v To add an instance for a DB2 server, you can use the following command:
db2icrt-u db2fenc1 db2inst1
v If you installed the DB2 Connect Enterprise Edition only, you can use the instance name as the Fenced ID also:
db2icrt -u db2inst1 db2inst1
v To add an instance for a DB2 client, you can use the following command:
db2icrtdb2inst1 –s client–u fencedID
DB2 client instances are created when you want a workstation to connect to other database servers and you have no need for a local database on that workstation.
Related reference:
v “db2icrt - Create Instance” in the Command Reference
Windows Details When Creating Instances
When working with the Windows operating systems, the db2icrt command has the following optional parameters:
v –s InstType
Allows different types of instances to be created. Valid instance types are:
ese, wse, client, and standalone.
v –p:InstProf_Path
This is an optional parameter to specify a different instance profile path. If you do not specify the path, the instance directory is created under the SQLLIB directory, and given the shared name DB2 concatenated to the instance name. Read and write permissions are automatically granted to everyone in the domain. Permissions can be changed to restrict access to the directory.
If you do specify a different instance profile path, you must create a shared drive or directory. This will allow the opportunity for everyone in the domain to access the instance directory unless permissions have been changed.
v –u:username,password
When creating a partitioned database environment, you must declare the domain/user account name and password of the DB2 service.
v –r:base_port,end_port
This is an optional parameter to specify the TCP/IP port range for the Fast Communications Manager (FCM). If you specify the TCP/IP port range, you must ensure that the port range is available on all machines in the partition database system.
For example, on DB2 for Windows Enterprise Server Edition, you could have the following example:
db2icrtinst1 –s eee –p:\\machineA\db2mpp
–u:<user accountname>,<password> –r:9010,9015
Note: If you change the service account; that is, if you no longer use the default service created when the first instance was created during product installation, then you must grant the domain/user account name used to create the instance the following advanced rights:
v Act as a part of the operating system v Create a token object
v Increase quota v Log on as a service
v Replace a process level token v Lock page in memory
The instance requires these user rights to access the shared drive, authenticate the user account, and run DB2 as a Windows service. The
″Lock page in memory″ right is needed for Address Windowing Extensions (AWE) support.
Related reference:
v “db2icrt - Create Instance” in the Command Reference
Add an Instance
Procedure:
Once you have created an additional instance, you will need to add a record of that instance within the Control Center to be able to work with that instance from the Control Center.
To add another instance, perform the following steps:
1. Log on under a user ID or name that has Administrative authority or belongs to the local Administrators group.
2. To add an instance, use one of the following methods:
To use the Control Center:
a. Expand the object tree until you find the Instances folder of the system that you want.
b. Right-click the instance folder, and select Add from the pop-up menu.
c. Complete the information, and click Apply.