Before creating a database, you should consider or carry out the following tasks:
You must make logical and physical database design decisions before you create a database. To find out more about logical database design, see Chapter 2, Designing Your Logical Database. To find out more about physical database design, see Chapter 3, Designing Your Physical Database.
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 a default instance of DB2 called "DB2". On UNIX, the default 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:
| 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:
. INSTHOME/sqllib/db2profile (for Bourne or Korn shell) source INSTHOME/sqllib/db2cshrc (for C shell)where INSTHOME is the home directory of the instance that you wish to use.
. USERHOME/db2profile (for Bourne or Korn shell) source USERHOME/db2cshrc (in C shell)where USERHOME is the home directory of the user.
| 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.
. INSTHOME/sqllib/db2profile (for Bourne or Korn shell) source INSTHOME/sqllib/db2cshrc (for C shell)where INSTHOME is the home directory of the instance that you wish to use.
. USERHOME/db2profile (for Bourne or Korn shell) source USERHOME/db2cshrc (in C shell)where USERHOME 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 called test and prod, and their home directories are /u/test and /u/prod.
In window 1:
. /u/test/sqllib/db2profile
source /u/test/sqllib/db2cshrc
In window 2:
. /u/prod/sqllib/db2profile
source /u/prod/sqllib/db2cshrc
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. |
It is possible to have more than one instance on a system. 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.
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 create additional DB2 instances using the db2icrt command. The machine that you run the command on becomes the instance-owning machine (node zero/0). Ensure that you create instances on a machine where an Administration Server resides.
| Note: | You can choose to update an existing singe-partition instance to the multi-partition format using the db2iupdt command. |
Use the db2icrt command to create an instance of DB2. When using this command, 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 6, Controlling Database Access.
To create an instance, perform the following steps:
db2icrt <instance_name>
When working with DB2 Extended Enterprise Environment, you will also need to declare that you are creating an instance that is a partitioned database system. This is done using -s eee on the command line.
When working with UNIX operating systems, the db2icrt command has the following optional parameters:
This parameter is used to display a help menu for the command.
This parameter sets the debug mode for use during problem determination.
This parameter specifies the authentication type for the instance. Valid authentication types are SERVER, CLIENT, DCS, or DCE. If not specified, the default is SERVER, if a DB2 server is installed. Otherwise, it is set to CLIENT.
Notes:
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 Software's Developer Kit. For other DB2 products, this is a required parameter.
| Note: | FencedID may not be "root" or "bin". |
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.
Allows different types of instances to be created. Valid instance types are: ee, eee and client.
Examples:
db2icrt -u db2fenc1 db2inst1
db2icrt -u db2inst1 db2inst1
db2icrt db2inst1 -s client
Optionally on Windows NT, you may also want 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.
When working with DB2 for Windows NT, you will also need to declare the logon and account name and password of the DB2 Service. This is done using /u: followed by the username and password (comma separated) on the command line.
Optionally on Windows NT, you may also want to specify the TCP/IP port range for the FCM. This is done using /r: followed by the base port and end port numbers (comma separated) on the command line.
For example, on DB2 for Windows NT Extended Enterprise 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 the username used to create the
instance the following Windows NT user rights:
|
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.
To get a list of all the instances that are available on a system, enter:
db2ilist
To determine which instance applies to the current session,
set db2instance
Or, on UNIX operating systems,
db2 get 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:
set db2instance=<new_instance_name>
To set the DB2INSTDEF registry variable at the global level of the registry, enter:
db2set db2instdef=<new_instance_name> -g
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.
You can start multiple DB2 instances as long as they belong to the same level of code.
To run multiple instances concurrently, use one of the following methods:
set db2instance=<another_instName>
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.
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. "DB2 Registry and Environment Variables" in the Administration Guide, Performance lists 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. They are:
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:
The db2set command supports the local declaration of the registry variables (and environment variables) to a particular setting.
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:
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
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:
%DB2INSTPROF%\instance_name\PROFILE.ENV
| Note: | The instance_name is specific to the database partition you are working with. |
%DB2INSTPROF%\DEFAULT.ENV
%DB2INSTPROF%\instance_name\NODES\node_number.ENV
| Note: | The instance_name and the node_number are specific to the database partition you are working with. |
There is an additional registry file that keeps track of all defined nodes. The information in this file is roughly equivalent to what is kept in the db2nodes.cfg file.
%DB2INSTPROF%\instance_name\NODES.CFG
%DB2INSTPROF%\PROFILES.REG
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 Extended Enterprise 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:
\HKEY_LOCAL_MACHINE\SOFTWARE\IBM\DB2\PROFILES\instance_name
| Note: | The instance_name is specific to the database partition you are working with. |
\HKEY_LOCAL_MACHINE\SOFTWARE\IBM\DB2\GLOBAL_PROFILE
...\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. |
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.
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:
db2instance=inst1 export db2instance
set 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. (For information on DB2 Administration Server (DAS), see DB2 Administration Server (DAS).)
$INSTHOME/sqllib/profile.env
The access permissions and ownership of this file should be:
-rw-r--r-- Instance_Owner DAS_Instance_Group profile.env
The $INSTHOME is the home path of the instance owner.
The access permissions and ownership of this file should be:
-rw-r--r-- DAS_Instance_Owner DAS_Instance_Group default.env
$INSTHOME/sqllib/nodes/node_number.env
The access permissions and ownership of the directory and this file should be:
drwxrwxr-x Instance_Owner DAS_Instance_Group nodes -rw-r--r-- Instance_Owner DAS_Instance_Group node_number.env
| Note: | The Instance_Owner and the DAS_Instance_Owner should both be members of the DAS_Instance_Group. |
The $INSTHOME is the home path of the instance owner.
The access permissions and ownership of this file should be:
-rw-r--r-- root system profiles.reg
The DB2 environment on Windows 3.x is not controlled by profile registries. Instead, Windows 3.x clients define environment keywords in the db2.ini file (typically found in the C:\windows directory).
On Windows 3.x, the parameters that control the DB2 environment are called environment keywords. However, because many Windows 3.x keywords are also used on operating systems that use the DB2 profile registries, environment keywords may also be referred to as registry variables.
The db2.ini initialization file is an ASCII file that stores values for the Windows 3.x client environment keywords. Within this file, there is just one section header.
The parameters are set by specifying a keyword with its associated keyword value in the form:
KEYWORD=keywordValue
For example, here is what would appear in a sample db2.ini file following the section header:
DB2PATH=C:\SQLLIB\WIN DB2INSTANCE=DB2 DB2INSTPROF=C:\SQLLIB DB2TRACEON=N
Notes:
The db2.ini file is located in the Windows product directory.
On Windows 3.x, the Client for DB2 Version 4, Version 5, and Version 6 must set this information only in the db2.ini file.
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 (CCA) or the Control Center (CC). DAS assists the CC and CCA 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 tasks or operations desired by the administrator, but provide sufficient authority to carry out all desired commands. Generally, the tasks or operations required include:
For more information on setting up DAS communications, refer to the Quick Beginnings for your platform.
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:
Log on to the machine you want to create the DAS on using an account that has local Administrator authority. If a specific user is to be identified, create a user with local Administrator authority. Enter db2admin create. If a specific user account is desired, you must use "/USER:" and "/PASSWORD:" when issuing db2admin create.)
When creating the DAS, you can optionally provide a user account name and a user password. If valid, the user account name and password will identify the owner of the DAS. Do not use the user ID or account name created for the DAS as a User Account. Set the password for the account name to "Password Never Expires". After you create the DAS, you can establish or modify its ownership by providing a user account name and user password with the db2admin setid command. Refer to the Command Reference for more information on this command.
On DB2 UDB for Windows NT Extended Enterprise Edition, if you are using the CCA or the Control Center to automate connection configuration to a DB2 server, the database partition server that is on the same machine as the DAS will be the co-ordinator node. This means that all physical connections from the client to the database will be directed to the database partition server on the instance-owning machine before being routed to other database partition servers.
On DB2 UDB for Windows NT Extended Enterprise Edition, creating additional Administration Servers on other machines allows the CCA or Control Center to configure other systems as co-ordinator nodes using DB2 Discovery. To do this, perform the following:
db2admin create /user:username
/password:passwrd
where username and passwrd are the username and
password for the DAS.
dasicrt ASName
/usr/lpp/db2_05_00&/instance/
dasicrt ASname
/opt/IBMdb2/V5,0/instance/
dasicrt ASname
| Note: | If you are running NIS and NIS+, you need to set up the user and group names
in such a way that:
|
Because a user ID can only own one instance, you must have a separate user ID to own each DB2 Administration Server (DAS) that you create.
Once you create an Administration Server, you should use it to establish directory structures and access permissions.
To manually start or stop the DAS, you must first log on to the machine using an account or user ID that has local Administrator 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:
. INSTHOME/sqllib/db2profile (for Bourne or Korn shell) source INSTHOME/sqllib/db2cshrc (for C shell)where INSTHOME is the home directory of the instance.
db2admin start
| Note: | The DAS is automatically started after each system reboot. |
. INSTHOME/sqllib/db2profile (for Bourne or Korn shell) source INSTHOME/sqllib/db2cshrc (for C shell)where INSTHOME is the home directory of the instance.
db2admin stop
| Note: | For both cases under UNIX, the person using these commands must have logged on with the authorization ID of the DAS owner. |
To obtain the name of the DAS on your machine, use:
dasilist
This command is found in the bin subdirectory under the subdirectory specific to the installed DB2 version and release.
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.
You must first logon 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 has SYSADM authority on each of the servers within the environment so that it can start or stop other instances if required.
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 exiting 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 logon to the machine using an account or user ID that has local Administrator 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:
Displays a help menu for this command.
Sets the debug mode, which is used for problem analysis.
You must first logon to the machine using an account or user ID that has local Administrator authority.
To remove the DAS:
| Note: | This example assumes db2das00 is the name of the DAS to be removed. |
| Note: | Under Windows NT, the person using this command must have SYSADM, SYSCTRL, or SYSMAINT authority. |
. INSTHOME/sqllib/db2profile (for Bourne or Korn shell) source INSTHOME/sqllib/db2cshrc (for C shell)where INSTHOME is the home directory of the instance.
db2admin stop
dasidrop ASNamewhere the ASName is the instance name of the Administration Server. This command is found in the instance subdirectory under the subdirectory specific to the installed DB2 version and release.
| Note: | The dasidrop command removes the sqllib directory under the home directory of the DB2 Administration Server (DAS). |
The following information shows the steps necessary to configure DB2 EEE servers (Sun, NT, and AIX) for remote administration using the Control Center (CC).
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 (CC) or the Client Configuration Assistant (CCA) 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:
There are two (2) 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.
DB2 Instance:
DAS:
| Note: | Please substitute site-specific values for the above fields. For example, the following table contains example pathnames for each supported EEE platform: |
Table 20. Example Pathnames for Each Supported EEE Platform
| Paths | DB2 UDB EEE V5.2 for AIX | DB2 UDB EEE V5.2 for Solaris | DB2 UDB EEE V5.2 for Windows NT |
|---|---|---|---|
| install_path | /usr/lpp/db2_05_00 | /opt/IBMdb2/V5.0 | 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 |
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 (CC) or the Client Configuration Assistant (CCA).
The DAS is an administrative control point which performs certain tasks on behalf of the Control Center (CC). There can be at most one (1) 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 CC can administer the EEE instance. This DAS (db2as) "represents" the system that is present in the CC 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:
Control Center Communications with DAS: Service Ports:
The Control Center (CC) 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, db2cclist, 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:
rah 'install_path/bin/db2cclst'
For example, on AIX this command invocation would appear as:
rah '/usr/lpp/db2_05_00/bin/db2cclst'
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/db2_05_00/bin/db2cclst"
Every time either machine boots, db2cclist is invoked without user intervention.
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 syslogdThen, 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, one could create a domain ID for db2as and add this domain ID to the Local Administrators group on each host.
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 (CC), ensure that the DB2COMM 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 CC 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 a DNS is not 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:
Allows the CCA to refresh systems in the known list, and to add new systems to the list by using the Add Systems push button. When the discover parameter is set to KNOWN, the CCA will not be able to search the network.
Enables all of the facilities of Known Discovery, and enables network searching.
Disables Discovery. In this case, the Search the network option is not available in the "Add Database SmartGuide".
| 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 Extended Enterprise 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 parameter is set in the DB2 registry using the db2set command. It is not recommended that you use any other method to set the db2comm parameter. |
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 use the following:
db2set db2comm
In addition, there are two DB2 profile registry variables that can be used to tune Search Discovery via NetBIOS on the client: db2discoverytime and db2nbdiscoveryrcvbufs. the default values should be suitable in most cases.
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 discovery 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.
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:
Update the DAS configuration file using the command process:
update admin cfg using discover [ DISABLE | KNOWN |
SEARCH ]
update admin cfg using discover_comm [ NETBIOS | TCPIP ]
Stop and restart the DAS by entering the following commands:
db2admin stop db2admin start
| Note: | Search Discovery will only operate on NetBIOS and TCPIP. |
| 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. Set the parameters as follows:
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:
db2set db2discoverytime=60
This specifies that Search Discovery should wait 60 seconds for a response from servers.
db2set db2nbdiscoverrcvbufs=20
This specifies the number of NetBIOS buffers that will be allocated for concurrent response messages from discovered servers.
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 (CCA) 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.
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:
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. |
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. |
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 co-ordinator nodes when connections are established.
There are two ways of updating a DAS configuration:
When there are multiple DAS present, the same instance may appear in more than one system on the CCA or Control Center's interface; however, each system will have a different communications access path to instances. Users can select different DB2 systems as co-ordinator nodes for communications and thereby redistribute the workload.
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. (For information on stored procedures, refer to "Stored Procedures" in Administration Guide, Performance.) 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:
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.
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.
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. 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. |
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 "Configuring DB2" found in Administration Guide, Performance.
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 create the same configuration file on all database partitions.
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.
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 directory in the partition's /etc/services file 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.
For additional information, refer to the DB2 Enterprise - Extended Edition for Windows NT Quick Beginnings.
The syntax of a service entry is as follows:
DB2_instance port/tcp #comment
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. |