Administration Guide

Details on Creating a Database

Before creating a database, you should consider or carry out the following tasks:

Designing Logical and Physical Database Characteristics

You must make logical and physical database design decisions before you create a database. To find out more about logical database design, see Chapter 7, Logical Database Design. To find out more about physical database design, see Chapter 8, Physical Database Design.

Creating an Instance

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:

It should be noted that multiple instances have some minor disadvantages:

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:

On UNIX operating systems, the instance directory is located in the INSTHOME/sqllib directory, where INSTHOME is the home directory of the instance owner.

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:

On UNIX, the default can be called anything you want within the naming rules guidelines.

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, and db2cshrc 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:

Setting the DB2 Environment Automatically

Note:This discussion only applies to the UNIX operating system environments.

By default, the scripts 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.

Add one of the following statements to the .profile or .login script files:

Setting the DB2 Environment Manually

Note:This discussion only applies to the UNIX operating system environments.

To choose which instance that you want to use, enter one of the following statements at a command prompt. The period (.) and the space are required.

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 called test and prod, and their home directories are /u/test and /u/prod.

In window 1:

In window 2:

Use window 1 to work with the test instance and window 2 to work with the prod 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.

Multiple Instances on a System

It is possible to have more than one instance on a system. However, you may only work within one instance of DB2 at a time.

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.

Add an Instance

If you have Administrative authority on OS/2, or you belong to the Administrative group on Windows NT, 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 an Administration Server resides.

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:
    1. Expand the object tree until you find the Instances folder.
    2. Right-click the instance folder, and select Add from the pop-up menu.
    3. Complete the information, and click Apply.

    To add an instance using the command line, enter: 

       db2icrt <instance_name>
  3. Create an Administration Server.

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. For more information on authentication, see Chapter 16, Controlling Database Access.
Note:You can choose to update your instance configuration using the db2iupdt command.

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.

DB2 Enterprise - Extended Edition Details When Adding Instances

When working with DB2 Universal Database Enterprise - Extended Edition, you will also need to declare that you are adding a new instance that is a partitioned database system. This is done using -s eee on the command line.

UNIX Details When Creating Instances

When working with UNIX operating systems, the db2icrt command has the following optional parameters:

Examples:

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.

Windows NT Details When Creating Instances

When working with the Windows NT operating system, the db2icrt command has the following optional parameters:

For example, on DB2 for Windows NT Enterprise - Extended Edition, you could have the following example: 

   db2icrt inst1 -s eee
      /p:\\machineA\db2mpp
      /u:yourname,yourpwd /r:9010,9015
Note:The db2icrt command grants to the username used to create the instance:
  • Act as a part of the operating system
  • Create a token object
  • Increase quota
  • Log on as a service
  • Replace a process level token
The instance requires these user rights to access the shared drive, authenticate the user account, and run DB2 as a Windows NT service.

Listing Instances

To get a list of all the instances that are available on a system using the Control Center:
  1. Expand the object tree until you find the Instances folder.
  2. Right-click the instance folder, and select Add from the pop-up menu.
  3. On the "Add Database" window, click Refresh.
  4. Click the drop-down arrow to see a list of remote database instances.
  5. Click Cancel to exit the window.

To list all instances that are available on a system using the command line, enter: 

   db2ilist

To determine which instance applies to the current session, enter: 

   set db2instance

Note:On UNIX operating systems, enter: 
   db2 get instance

Setting the Current Instance

When you run commands to start or stop an instance's database manager, DB2 applies the command to the current instance. DB2 determines the current instance as follows:

Auto-Starting Instances

On UNIX operating systems, to enable an instance to auto-start after each system restart, enter the following command: 

   db2iauto -on InstName

where InstName is the login name of the instance.

On UNIX operating systems, to prevent an instance from auto-starting after each system restart, enter the following command: 

   db2iauto -off InstName

where InstName is the login name of the instance.

Running Multiple Instances Concurrently

You can start multiple DB2 instances as long as they use the same level of code.

To run multiple instances concurrently using the Control Center:
  1. Expand the object tree until you find the Databases folder.
  2. Right-click an instance, and select Start from the pop-up menu.
  3. Repeat Step 2 until you have started all the instances that you want to run concurrently.

To run multiple instances concurrently using the command line, enter:

License Management

The management of licenses for your DB2 products is done primarily through the License Center within the Control Center of the online interface to the product. From the License Center you can check the license information, statistics, registered users, and current users for each of the installed products.

Establish Environment Variables and the Profile Registry

Environment and registry variables control your database environment.

Prior to the introduction of the DB2 profile registry, changing your environment variables on Windows or OS/2 workstations (for example) required you to change an environment variable and reboot. Now, your environment is controlled with a few exceptions by registry variables stored in the DB2 profile registries. Users with system administration (SYSADM) authority for a given instance can update registry values for that instance. Use the db2set command to update registry variables without rebooting; this information is stored immediately in the profile registries. The DB2 registry applies the updated information to DB2 server instances and DB2 applications started after the changes are made.

When updating the registry, changes do not affect the currently running DB2 applications or users. Applications started following the update use the new values.
Note:The DB2 environment variables DB2INSTANCE, DB2NODE, DB2PATH, and DB2INSTPROF may not, depending on the operating system, be stored in the DB2 profile registries. In order to update these environment variables, the set command must be used and the system rebooted. On UNIX platforms, the export command may be used instead of the set command, and a system reboot is not necessary.

Using the profile registry allows for centralized control of the environment variables. See Appendix D, DB2 Registry and Environment Variables for a list of many of the environment variables and registry variables. Different levels of support are now provided through the different environment profiles. Remote administration of the environment variables is also available when using the DB2 Administration Server.

There are four profile registries:

Users can override DB2 Instance Profile Registry environment variable settings for their session by changing session environment variable settings using the db2set command.

DB2 configures the operating environment by checking for registry values and environment variables and resolving them in the following order:

  1. Environment variables set with the set command. (Or the export command on UNIX platforms.)
  2. Registry values set with the instance node level profile (using the db2set -i command with a node number as shown below).
  3. Registry values set with the db2set command.
  4. Registry values set with the instance profile (using the db2set -i command as shown below).
  5. Registry values set with the global profile (using the db2set -g command as shown below).

Using the db2set Command

The db2set command supports the local declaration of the registry variables (and environment variables).

To display help information for the command, use: 

   db2set ?

To list the complete set of all supported registry variables, use: 

   db2set -lr

To list all currently defined registry variables for this session, use: 

   db2set

To list all defined registry variables in the profile registry, use: 

   db2set -all

To show the current session value of a registry variable, use: 

   db2set registry_variable_name

To show the value of a registry variable at all levels, use: 

   db2set registry_variable_name -all

To delete a variable's value at a specified level, you can use the same command syntax to set the variable but specify nothing for the variable value. For example, to delete the variable's setting at the node level, enter: 

   db2set registry_variable_name= -i instance_name
 node_number

To delete a variable's value and to restrict its use, if it is defined at a higher profile level, enter: 

   db2set registry_variable_name= -null instance_name

This command will delete the setting for the parameter you specify and restrict high level profiles from changing this variable's value (in this case, DB2 global-level profile). However, the variable you specify could still be set by a lower level profile (in this case, the DB2 node-level profile).

To change a registry variable for this session only, use: 

   db2set registry_variable_name=new_value

To change a registry variable default for all databases in the instance, use: 

   db2set registry_variable_name=new_value
      -i instance_name

To change a registry variable default for all instances in the system, use: 

   db2set registry_variable_name=new_value -g

To set registry variables at the user level, use: 

   db2set -ul

To set registry variables at the user level for a specific user, use: 

   db2set -ul user_name

Notes:

  1. The parameters "-i", "-g", and "-ul" cannot be used at the same time in the same command.

  2. Some parameters will always default to the global level profile. They cannot be set at the instance or node level profiles; for example, db2system and db2instdef.

  3. On UNIX, you must have system administration (SYSADM) authority to change registry values for an instance. Only users with root authority can change parameters in global-level registries.

When running in an LDAP environment, it is possible to set a DB2 registry variable value in LDAP such that its scope is global to all machines and all users that belong to a directory partition or to a Windows NT domain. Currently, the only DB2 registry variable that can be set at the LDAP global level is DB2LDAP_SEARCH_SCOPE.

To set this variable at the LDAP global level, use the -gl option for the db2set command.
Note:This is different from the -g option which is used to set DB2 registry variables at the machine global level. -gl is specific to the LDAP global level. Also, setting this DB2 registry variable in LDAP is only supported on Windows platforms.

To set the search scope value at the global level in LDAP, use: 

   db2set -gl db2ldap_search_scope = value

where the value can be "local", "domain", or "global".

To change a registry variable default for a particular node in an instance, use: 

   db2set registry_variable_name=new_value
      -i instance_name node_number

To reset all registry variables for an instance back to the defaults found in the Global Profile Registry, use: 

   db2set -r registry_variable_name

To reset all registry variables for a node in an instance back to the defaults found in the Global Profile Registry, use: 

   db2set -r registry_variable_name node_number

Setting Environment Variables on OS/2

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.

On OS/2, you should have no environment variables defined in config.sys apart from DB2PATH and DB2INSTPROF. All variables should be defined in the profile registries using the db2set command except for those that remain true environment variables.

DB2INSTANCE also remains a true environment variable, however, it is not required if you make use of the DB2INSTDEF registry variable. This registry variable defines the default instance name to use if DB2INSTANCE is not set.

DB2INSTANCE and DB2PATH are set when DB2 is installed; DB2INSTPROF can be set after installation. The environment variable DB2PATH must be set; this environment variable is set during installation and you should not modify it. Setting DB2INSTANCE and DB2INSTPROF environment variables is optional.

To determine the setting of an environment variable, enter: 

   set variable

To change the setting of an environment variable, enter the following command: 

   set variable=value

To set system environment variables, do the following: Edit the config.sys file, and reboot the system to have the change take effect.

The different profile registries are located according to the following:

Setting Environment Variables on Windows NT and Windows 95

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 32-bit 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 - Extended Edition servers on Windows NT 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 with port 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 95 and Windows 98: Edit the autoexec.bat file, and reboot the system to have the change take effect.

On Windows NT 4.x: You can set the DB2 environment variables DB2INSTANCE, DB2PATH, and DB2INSTPROF as follows:

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: 
   set db2instance=TEST
   db2start

The profile registries are located as follows:

DB2 UDB provides the capability of accessing DB2 UDB registry variables at the instance level on a remote machine. Currently, DB2 UDB registry variables are stored in three different levels: machine or global level, instance level, and node level. The registry variables stored at the instance level (including the node level) can be redirected to another machine by using DB2REMOTEPREG. When DB2REMOTEPREG is set, DB2 UDB will access the DB2 UDB registry variables from the machine pointed to by DB2REMOTEPREG. For example, 

   db2set DB2REMOTEPREG=rmtwkstn

where rmtwkstn is the remote workstation name.
Note:Care should be taken in setting this option since all DB2 instance profiles and instance listings will be located on the specified remote machine name.

This feature may be used in combination with setting DBINSTPROF to point to a remote LAN drive on the same machine that contains the registry.

Setting Environment Variables on UNIX Systems

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 scripts db2profile (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 in insthome/sqllib, where insthome is the home directory of the instance owner.

These scripts include statements to:

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, db2profileand db2cshrc.

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:

In order for the DB2 profile registry to be administered properly, the following file ownership rules must be followed on UNIX operating systems. (For information on DB2 Administration Server (DAS), see DB2 Administration Server (DAS).)

DB2 Administration Server (DAS)

DB2 Administration Server (DAS) is a special DB2 administration control point used only to assist with administration tasks on other DB2 servers. You must have a running DAS if you want to use the Client Configuration Assistant or the Control Center. DAS assists the Control Center and Client Configuration Assistant when working on the following administration tasks:

You can only have one DAS on a machine. DAS is configured during installation to start when the operating system is booted.

DAS is used to perform remote tasks on the host system on behalf of a client request from the Control Center or the Client Configuration Assistant. Authorized access to DAS requires clients with SYSADM authority. All of the clients can be part of the SYSADM_GROUP configuration parameter.

Some of the requested tasks may require specific authority to run. The DAS runs under the identifier of a specific user. The privileges granted to that user must be restricted to only those commands associated with the tasks or operations to be carried out by the administrator. Generally, the tasks or operations required include:

For more information on setting up DAS communications, refer to the Quick Beginnings for your platform.

Creating the DAS

Typically, the setup program creates a DAS on the instance-owning machine during DB2 installation. If, however, the setup program failed to create it, you can manually create a DAS.

As an overview of what occurs during the installation process as it relates to DAS, consider the following:

Once you create an Administration Server, you should use it to establish directory structures and access permissions.

Starting and Stopping the DAS

To manually start or stop the DAS, you must first log on to the machine using an account or user ID that has local administrative authority.

When working on DB2 for OS/2 or DB2 for Windows NT, you must do the following:

Note:For both cases under Windows NT, the person using these commands must have SYSADM, SYSCTRL, or SYSMAINT authority.

When working on DB2 for any of the UNIX operating systems, you must do the following:

Note:For both cases under UNIX, the person using these commands must have logged on with the authorization ID of the DAS owner.

Listing the DAS

To obtain the name of the DAS instance on your machine, enter: 

   db2admin

This command is also used to start or stop the DAS, create a new user and password, drop a DAS instance, and establish or modify the user account associated with the DAS instance.

Configuring the DAS

To see the current values for those administration configuration parameters relevant to the DAS, enter: 

   db2 get admin cfg

This will show you the current values that were given as defaults during the installation of the product or those that were given during previous updates to the configuration parameters.

To update individual entries in the database manager configuration file relevant to the DAS, enter: 

   db2 update admin cfg using ...

Refer to the Command Reference for more information on which database manager configuration parameters can be modified.

To reset the configuration parameters to the recommended database manager defaults, enter: 

   db2 reset admin cfg

Changes to the database manager configuration file become effective only after they are loaded into memory (that is, when a db2admin stop is followed by a db2admin start; or, in the case of a Windows NT platform, stopping and starting the service.)

To set up the communications protocols for the DAS, refer to the Quick Beginnings for your platform.

Security Considerations for the DAS

You must first log on to the machine using an account or user ID that has local Administrator authority.
Note:On Windows NT, you should not use the Services utility in the Control Panel to change the logon account for the DAS since some of the required access rights will not be set for the logon account. Always use the db2admin command to set or change the logon account for the DB2 Administration Server (DAS).

After creating the DAS, you can set or change the logon account using the db2admin command as follows: 

   db2admin setid username password

where username and password are the username and password of an account that has local Administrator authority.

It is recommended that the user ID or the username have SYSADM authority on each of the servers within the environment so that it can start or stop other instances if required.

Updating the DAS

On UNIX operating systems, if DB2 is updated by installing a Program Temporary Fix (PTF) or a code patch, all DB2 Administration Servers (DAS) as well as all existing instances should be updated. To update a DAS, use the dasiupdt command available in the instance subdirectory under the subdirectory specific to the installed DB2 version and release.

You must first log on to the machine as "root" (on UNIX), using an account, or with a user ID that has local administrative authority.

The command is used as follows: 

   dasiupdt InstName

The InstName is the login name of the instance owner. There are also optional parameters for this command that can be placed before the InstName and separated by spaces:

Removing the DAS

You must first log on to the machine as "root" (on UNIX), using an account, or with a user ID that has local administrative authority.

To remove the DAS:

Setting Up DAS with EEE Systems

The following information shows the steps necessary to configure DB2 EEE servers (Solaris, NT, Sequent, HP-UX, and AIX) for remote administration using the Control Center.

During installation, the setup program creates a single DAS on the instance-owning machine. You may want to create additional DAS on other machines to allow the Control Center or the Client Configuration Assistant access to other coordinator nodes. The overhead of working as a coordinator node can then be spread to more than one node in an instance.

To distribute the coordinator function:

  1. Create a new DAS on the selected additional machines in the partitioned database system.
  2. Catalog each DAS as a separate system in the Control Center or Client Configuration Assistant.
  3. Catalog the same instance under each new system, and each time specify the same machine name used to catalog the DAS.

There are two aspects to configuration: That which is required for the DB2 Administration Server (DAS), and that which is recommended for the target, administered DB2 instance. In the three sections which follow, a section is devoted to each of the two configuration topics. Each of the configuration topics is preceded by a section describing the assumed environment.

Example Environment

product/version:
DB2 UDB EEE V7.1

install path:
install_path

TCP services file:
tcp_services_file

DB2 Instance:

name:
db2inst

owner ID:
db2inst

instance path:
instance_path

nodes:
3 nodes, db2nodes.cfg:

DB name:
db2instDB

DAS:

name:
db2as

owner/user ID:
db2as

instance path:
das_path

install/run host:
hostA

internode communications port:
16000 (unused port for hostA and hostB)
Note:Please substitute site-specific values for the above fields. For example, the following table contains example pathnames for each supported EEE platform:

Table 24. Example Pathnames for Supported EEE Platforms
Paths DB2 UDB EEE for AIX DB2 UDB EEE for Solaris DB2 UDB EEE for Windows NT
install_path /usr/lpp/<v_r_ID> /opt/IBMdb2/<v_r_ID> C:\sqllib
instance_path /home/db2inst/sqllib /home/db2inst/sqllib C:\profiles\db2inst
das_path /home/db2as/sqllib /home/db2as/sqllib C:\profiles\db2as
tcp_services_file /etc/services /etc/services C:\winnt\system32 \drivers\etc\services

In the table, <v_r_ID> is the platform-specific version and release identifier. For example in DB2 UDB EEE for AIX in Version 5.2, the <v_r_ID> is db2_05_00.

When installing DB2 UDB EEE, the setup program creates a DAS on the instance-owning machine. The database partition server resides on the same machine as the DAS and is the connection point for the instance. That is, this database partition server is the coordinator node for requests issued to the instance from the Control Center or the Client Configuration Assistant.

DAS Configuration

The DAS is an administrative control point which performs certain tasks on behalf of the Control Center. There can be at most one DAS per physical machine. In the case of an EEE instance which consists of several machines, at least one of the machines must be running a DAS so that the Control Center can administer the EEE instance. This DAS (db2as) "represents" the system that is present in the Control Center navigator tree as the parent of the target DB2 instance (db2inst).

For example, db2inst consists of three nodes distributed across two physical machines or hosts. The minimum requirement can be fulfilled by running db2das on either hostA or hostB.

Notes:

  1. The number of partitions present on hostA does not have any bearing on the number of DASes that can be run on that host. You can run only one copy of db2as on hostA regardless of the multiple logical nodes (MLN) configuration for that host.

  2. It is not necessary to create the DAS ID, db2as, on all hosts. Rather, it is necessary for it to exist only on the host upon which it is running. As well, it is not necessary for the home directory of the DAS ID to be mounted on all hosts. In particular with this example, the ID db2as must exist on hostA, is not required on hostB, and db2as's home directory does not need to be mounted on hostB.

Control Center Communications with DAS: Service Ports

The Control Center communicates with the DAS using a TCP service port, 523. Since this port is reserved for exclusive use by DB2 UDB, it is not necessary to insert new entries into the tcp_services_file.

Internode Administrative Communications: Service Ports

For some administrative tasks, the DAS must establish communications with all nodes. In order to do so, a named TCP port must be defined in the tcp_services_file for each host which participates in the instance.
Note:Windows NT EEE will attempt to add the TCP port entry into the tcp_services_file for you.

For example, db2inst is defined across two hosts, hostA and hostB. As specified in Example Environment, port 16000 is unused on both hosts. Therefore, the following line must be inserted into the tcp_services_file for both hostA and hostB. 

   db2ccmsrv  16000/tcp

The db2ccmsrv port name must be present, spelled exactly as shown above, and the same port number selected must be used on all hosts.

Internode Administrative Communications: UNIX DB2 EEE Servers

Once the TCP port line is inserted into the tcp_services_file on hostA and hostB, it is necessary to start an administrative listener process or daemon, db2cclst, on all hosts that participate in the instance. You can do so manually from the command line, or configure the system to automatically invoke db2cclst every time the system boots:

Manual:
From the ID of the instance you wish to administer, db2inst, invoke the following command from either hostA or hostB: 
   rah 'install_path/bin/db2cclst'

For example, on AIX this command invocation would appear as: 

   rah '/usr/lpp/<v_r_ID>/bin/db2cclst'

The rah command is found in the instance subdirectory in the version and release subdirectory. The exact name of the version and release subdirectory varies by operating system. instance is the home directory of the instance you wish to use.

In this case, <v_r_ID> is the platform-specific version and release identifier. For example in DB2 UDB EEE for AIX in Version 5.2, the <v_r_ID> is db2_05_00.

Automatic:
From an ID with Superuser privileges (like root) execute the following command on hostA and hostB: 
   mkitab "db2cclst::once:su - db2inst -c install_path /bin/db2cclst"

For example, on AIX this command invocation would appear as: 

   mkitab "db2cclst::once:su - db2inst -c install_path
      /usr/lpp/<v_r_ID>/bin/db2cclst"

Every time either machine boots, db2cclst is invoked without user intervention.

In the table, <v_r_ID> is the platform-specific version and release identifier. For example in DB2 UDB EEE for AIX in Version 5.2, the <v_r_ID> is db2_05_00.

To verify that the listener daemon is active on each host, the following command can be invoked from the instance ID, db2inst: 

   rah 'ps -ef | grep db2cclst'

If you do not find the db2cclst process running on each host, additional diagnostic information can be obtained by adding the following line to /etc/syslog.conf on each host: 

   *.info  /tmp/db2/user.info

where the file /tmp/db2/user.info can be replaced with a more appropriate file.
Note:The file must exist and the SYSLOG daemon must be asked to re-read its configuration file after the changes are made: 
   kill -1 <syslogd PID>
where syslogd PID can be obtained by executing 
   ps -ef | grep syslogd
Then, after manually invoking the listener as described above, you can view the syslog file /tmp/db2/user.infoon the failing host for error messages generated by db2cclst.

Internode Administrative Communications: Windows NT DB2 EEE Servers

The DB2 Remote Command Service (db2rcmd.exe) automatically handles internode administrative communications. In the event that a failure does occur, the Windows NT registry will contain diagnostic information.

Security

In order for the DAS to perform some administrative tasks against an instance, it must possess sufficient authority. In particular, the DAS must be a System Administrator (SYSADM) for the target, administered instance.

It is necessary to grant the DAS such authority for all DB2 instances that it will administer. Candidate instances are those which are installed on the same machine as the DAS. For a DB2 EEE instance, at least one database partition server must be present on the same machine as the DAS for it to be eligible as described above.

For example on UNIX, one way in which db2as can be granted the required authority to administer db2inst is to ensure that the primary groups of db2inst and db2as are identical. Alternatively, it is sufficient to make the primary group of db2inst a secondary group of db2as, and the primary group of db2as a secondary group of db2inst. Finally, another option would be to set the SYSADM_GROUP database administration configuration parameter for db2inst to the primary group of db2as.

On Windows NT, db2as must be a member of the Local Administrators group on hostA and hostB. In addition to the option of creating the db2as ID and adding it to the Local Administrators group on both hosts, you could create a domain ID for db2as and add this domain ID to the Local Administrators group on each host.

Environment

Installation for the DAS should configure certain registry variables that are necessary for proper operation. To verify the current values for these variables, execute the following command from either the DB2 instance ID, db2inst, or the DAS ID, db2das: 

   db2set -g

At least the following parameters must be defined with the following values: 

   DB2SYSTEM=hostA
   DB2ADMINSERVER=db2as

As well, in order to communicate with the DAS from the Control Center, ensure that the DB2COMM registry variable is set to "TCPIP". To verify this setting, execute the following command from the DAS ID, db2as, and check at the global (-g) and instance (-i) levels (only one need be set): 

   db2set -all

Along the same lines, verify that the DB2COMM parameter is set to "TCPIP" for the DB2 instance to enable communications between the Control Center and db2inst by issuing the following command from the db2inst ID: 

   db2set -all

If you modify this parameter for the DAS, then you must restart the DAS for the change to take effect. Restart of the DB2 instance is also required if this parameter is modified for the DB2 instance. For db2inst, you would issue a db2stop followed by a db2start, whereas db2admin stop and db2admin start would be issued for the DAS.

Discovery of Administration Servers, Instances, and Databases

Known Discovery allows you to discover instances and databases on systems that are known to your client, and add new systems so that their instances and databases can be discovered. Search Discovery provides all of the facilities of Known Discovery and adds the option to allow your local network to be searched for other DB2 servers.

To have a server support Known Discovery, set the discover parameter in the DAS configuration file to KNOWN. To have it support Search Discovery, set this parameter to SEARCH. To prevent discovery of a server, and all of its instances and databases, set this parameter to DISABLE.
Note:The TCP/IP host name returned to a client by Search Discovery is the same host name that is returned by your DB2 server system when you enter the hostname command. On the client, the IP address that this host name maps to is determined by either the TCP/IP domain name server (DNS) configured on your client machine or, if no DNS is configured, a mapping entry in the client's hosts file. If you have multiple adapter cards configured on your DB2 server system, you must ensure that TCP/IP is configured on the server to return the correct hostname, and that the DNS or local client's hosts file, maps the hostname to the IP address desired.

On the client, enabling Discovery is also done using the discover parameter; however, in this case, the discover parameter is set in the client instance (or server acting as a client) as follows:

Note:The discover parameter defaults to SEARCH on all client and server instances. The discover parameter defaults to SEARCH on all DB2 Administration Servers (DAS) except DAS installed in a UNIX Enterprise - Extended Edition environment, where discover defaults to KNOWN.

Additional Settings for Search Discovery

Search Discovery requires that the discover_comm parameter be set on both the server (in the DB2 Administration Server's configuration file) and the client (in the database manager configuration file).

The discover_comm parameter is used to control the communications protocols that the server will listen to for search requests from clients, and that clients will use to send out search requests. The discover_comm parameter can be set to TCP/IP or NetBIOS. Only these protocols are currently supported.

On the DAS, the values specified for discover_comm must be equal to, or a subset of, the values set for DB2COMM.
Note:To avoid problems with the Control Center and the Client Configuration Assistant, ensure that the DB2COMM registry variable is set in the DB2 registry using the db2set command. It is not recommended that you use any other method to set the DB2COMM registry variable.

On the server, the discover_comm parameter is set in the DAS configuration file. On the client (or a server acting as a client), discover_comm is set in the database manager configuration file.
Note:When using Search Discovery, at least one protocol specified by the discover_comm parameter on the client must match those specified by the discover_comm parameter on the DAS. If there is no match, the server will not respond to the client's requests.

To check the settings for the DB2COMM registry variable, enter: 

   db2set db2comm

In addition, two DB2 profile registry variables can be used to tune Search Discovery via NetBIOS on the client: DB2DISCOVERYTIME and DB2NBDISCOVERYRECVBUFS. The default values for these registry variables should be suitable in most cases.

The DB2DISCOVERYTIME and DB2NBDISCOVERRCVBUFS profile registry variables are set in the client instance (or a server acting as a client). Set the registry variables as follows:

Hiding Server Instances and Databases from Discovery

You may have multiple instances, and multiple databases within these instances, on a server. You may want to hide some of these from the Discovery process.

To allow clients to discover server instances on a system, set the discover_inst database manager configuration parameter in each server instance on the system to ENABLE (this is the default value). Set this parameter to DISABLE to hide this instance and its databases from Discovery.

To allow a database to be discovered from a client, set the discover_db database configuration parameter to ENABLE (this is the default value). Set this parameter to DISABLE to hide the database from Discovery.

Setting Discovery Parameters

The discover and discover_comm parameters are set in the DAS configuration file on the server system, and in the database manager configuration file on the client. Set the parameters as follows:

Note:If the discover_comm includes NETBIOS, you must ensure that the Workstation name (nname) parameter is set for both the client and the DAS. Also, you must ensure that the DB2NBADAPTERS registry variable is set to the Adapter number that you want to use.

Use the Control Center to set the discover_inst and discover_db parameters:
  1. Expand the object tree until you find the Instances folder.
  2. Right-click the instance, and select Configure from the pop-up menu.
  3. On the "Environment" page, select the discover_inst parameter.
  4. To allow the server instance to be discovered from a client, select Enable and click OK.
  5. Right-click on the database in the object tree, and select Configure from the pop-up menu.
  6. On the "Environment" page, select the discover_db parameter.
  7. To allow the database to be discovered from a client, select Enable and click OK.

To run multiple instances concurrently using the command line, enter:

Setting Up the DAS to Use the Client Configuration Assistant and the Control Center

You must configure DB2 Discovery to retrieve information about systems on your network. DB2 Discovery is a feature that is used by the Client Configuration Assistant and Control Center. Configuring for this feature may require you to update instance lists and the DB2 Administration Server (DAS) configuration to ensure that DB2 Discovery retrieves the correct information.

Update Instance Lists

A DB2 Administration Server (DAS) may not be aware of all the instances in a partitioned database system because initially when an instance is created, only the DAS on the instance-owning machine is aware of the instance.

If you created an instance on a machine that does not have a DAS, you can create a DAS on this machine to make the instance known.

Perform the following steps if you created more than one DAS, and you want each DAS to be aware of all the instances in your partitioned database system:

  1. For each DAS

    Run the db2ilist command on the Administration Server machine to display a list of instances known to this DAS.
    Note:If the list of instances is complete, you do not need to carry out the remaining steps but can proceed to the next section.

  2. For each instance that is missing from the instance list in the previous step

    On the instance-owning machine, run the db2nlist command to see if there is an entry for the machine that has the DAS. If there is not, you must run the db2ncrt command to add this machine to the instance.
    Note:The network shared drive for the instance must be available on the DAS machine.

Update the DAS Configuration

By default, the setup program sets the DB2SYSTEM registry variable to the Windows NT computer name. The system names that are retrieved by Discovery are the systems on which a DB2 Administration Server (DAS) resides. Discovery uses these systems as coordinator nodes when connections are established.

There are two ways of updating a DAS configuration:

Create a Node Configuration File

If your database is to operate in a partitioned database environment, you must create a node configuration file called db2nodes.cfg. This file must be located in the sqllib 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 NT Considerations:If you are using DB2 Enterprise - Extended Edition on Windows NT, the node configuration file is created for you when you create the instance.
Note:You should not create files or directories under the sqllib 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. (See Stored Procedures for information on stored procedures.) The other exception is when user-defined distinct 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: 

   nodenum hostname [logical-port [netname]]

Tokens are delimited by blanks. The variables are:

nodenum
The node number, which can be from 0 to 999, uniquely defines a node. Node numbers must be in ascending sequence. You can have gaps in the sequence.

Once a node 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 node number can be used again for any new node that you add.

The node number is used to generate a node name in the database directory. It has the format: 

   NODEnnnn

The nnnn is the node number, which is left-padded with zeros. This node number is also used by the CREATE DATABASE and DROP DATABASE commands.

hostname
The hostname of the IP address for inter-partition communications. (There is an exception when netname 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 the etc/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 each hostname, one logical-port must be either 0 (zero) or blank (which defaults to 0). The node associated with this logical-port is the default node on the host to which clients connect. You can override this with the DB2NODE environment variable in db2profile script, or with the sqlesetc() API.

If you have multiple nodes on the same host (that is, more than one nodenum 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 nodes, and uses SP2SW1 as the DB2 Universal Database interface. It also shows the node numbers starting at 1 (rather than at 0), and a gap in the nodenum sequence: 

nodenum   hostname   logical-port   netname
1         SP2EN1     0              SP2SW1
2         SP2EN1     1              SP2SW1
4         SP2EN2     0
5         SP2EN3

You can update the db2nodes.cfg file using an editor of your choice. (The exception is: an editor should not be used on Windows NT.) You must be careful, however, to protect the integrity of the information in the file, as data partitioning requires that the node number not be changed. The node configuration file is locked when you issue DB2START and unlocked after DB2STOP ends 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.

Creation of the Database Configuration File

A database configuration file is also created for each database. The creation of this file is done for you. This file contains values for various configuration parameters that affect the use of the database, such as:

These parameters are described in detail in Chapter 32, Configuring DB2.

Performance Tip: Many of the configuration parameters come with default values, but may need to be updated to achieve optimal performance for your database.

For multiple partitions: When you have a database that is partitioned across more than one partition, the configuration file should be the same on all database partitions. Consistency is required since the SQL compiler compiles distributed SQL statements based on information in the local node configuration file and creates an access plan to satisfy the needs of the SQL statement. Maintaining different configuration files on database partitions could lead to different access plans, depending on which database partition the statement is prepared. Use db2_all to keep the configuration files synchronized across all database partitions.

Replicating Configuration Information Using Response Files

A response-file generator utility called db2rspgn is available to create a response file that can be used when re-installing your system or when you wish to replicate to identical system the registry variables, database manager configuration parameters, and database administration configuration parameters of your current system.

After having installed a system with one or more DB2 products, and after tuning parameters for the environment, you can use db2rspgn to generate the required values into a response file. The response file can then be used to re-create the identical system.

The command line syntax declares the destination directory for the response file(s) and any supporting files. In addition, you can optionally specify the instances you wish copied; and, you can optionally disable the administration instance and/or the DataLinks server instance.

Refer to the appropriate Quick Beginnings to see the details on the syntax of this utility and a discussion on how to use the generated response files.

Enable FCM Communications

In a partitioned database environment, most communication between database partitions is handled by the Fast Communications Manager (FCM). To enable the FCM at a database partition and allow communication with other database partitions, you must create a service entry in the partition's services file of the etc directory as shown below. The FCM uses the specified port to communicate. If you have defined multiple partitions on the same host, you must define a range of ports as shown below.

Windows NT Considerations
If you are using DB2 Enterprise - Extended Edition in the Windows NT environment, the TCP/IP port range is automatically added to the services file by:

For additional information, refer to the DB2 Enterprise - Extended Edition for Windows Quick Beginnings.

The syntax of a service entry is as follows: 

   DB2_instance port/tcp #comment

DB2_instance
The value for instance is the name of the database manager instance. All characters in the name must be lowercase. Assuming an instance name of db2puser, you would specify DB2_db2puser

port/tcp
The TCP/IP port that you want to reserve for the database partition.

#comment
Any comment that you want to associate with the entry. The comment must be preceded by a pound sign (#).

If the /etc/services file is shared, you must ensure that the number of ports allocated in the file is either greater than or equal to the largest number of multiple database partitions in the instance. When allocating ports, also ensure that you account for any processor that can be used as a backup.

If the /etc/services file is not shared, the same considerations apply, with one additional consideration: you must ensure that the entries defined for the DB2 instance are the same in all /etc/services files (though other entries that do not apply to your partitioned database do not have to be the same).

If you have multiple database partitions on the same host in an instance, you must define more than one port for the FCM to use. To do this, include two lines in the etc/services file to indicate the range of ports you are allocating. The first line specifies the first port, while the second line indicates the end of the block of ports. In the following example, five ports are allocated for the instance sales. This means no processor in the instance has more than five database partitions. 

   DB2_sales         9000/tcp
   DB2_sales_END     9004/tcp
Note:You must specify END in uppercase only. Also you must ensure that you include both underscore (_) characters.

[ Top of Page | Previous Page | Next Page ]