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 1. "Designing Your Logical Database". To find out more about physical database design, see Chapter 2. "Designing Your Physical Database".
As part of your installation procedure, you create an instance of DB2. It is possible to have more than one instance on a system. You may only work within one instance of DB2 at a time.
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 4. "Controlling Database Access". For more information on the db2icrt command, see the Command Reference manual.
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. Use the db2set command to update registry variables without rebooting; this information is stored immediately in the profile registries.
| 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. |
Using the profile registry allows for centralized control of the environment variables. Appendix F. "DB2 Registry and Environment Variables" 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 (4) 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 show the current session value of a registry variable, use:
db2set registry_variable_name
To delete the current session value of a registry variable, use:
db2set registry_variable_name=
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
| Note: | The two parameters "-I" and "-G" cannot be used at the same time in the same command. |
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
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.
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
On the Windows NT and Windows 95 operating systems, all DB2 environment values should be defined in the profile registries using the db2set command, except for those that are true environment variables. For Windows NT, you should not have the DB2 environment variables defined in either your machine's user or system environment variables sections. On Windows 95, you should not have DB2 environment variables defined in your autoexec.bat file.
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: 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:
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.
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:
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
DB2 Administration Server (DAS) is a special DB2 administration control point used only to assist with administration tasks on other DB2 servers. DAS assists the Control Center (CC) and Client Configuration Assistant (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 DAS is created during DB2 installation. Refer to the Quick Beginnings for details.
As an overview of what occurs during the installation process as it relates to DAS, consider the following:
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. 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.
dasicrt ASNamewhere ASName is the instance name of the Administration Server.
Once you create an Administration Server, you should use it to establish directory structures and access permissions.
To start the DAS, enter db2admin start
To stop the DAS, enter db2admin stop
| Note: | For both cases under Windows NT, the person using these commands must have SYSADM, SYSCTRL, or SYSMAINT authority. |
| Note: | For both cases under UNIX, the person using these commands must have logged on with the authorization ID of the DAS owner. |
To see the current values for those administration configuration parameters relevant to the DAS, enter:
db2 get admin cfg
To update individual entries in the database manager configuration file relevant to the DAS, enter:
db2 update admin cfg using ...
See 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 dbadmin start; or, in the case of a Windows NT platform, stopping and starting the service.)
To set up the communications protocols for the DAS, see the Quick Beginnings for your platform.
On OS/2 or Windows NT only, use the following command to associate a user ID with the DAS:
db2admin setid userid password
| Note: | Do not use the Windows NT operating system to set the user ID for the DAS. There is no guarantee that the user will receive all required privileges. |
It is recommended that the user ID has SYSADM authority on each of the servers within the environment so that it can start or stop other instances if required.
To remove the DAS:
dasidrop ASNamewhere the ASName is the instance name of the Administration Server.
The following information shows the steps necessary to configure DB2 EEE servers (Sun, NT, and AIX) for remote administration using the Control Center (CC).
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 |
The DAS is an administrative control point which performs certain tasks on behalf of the Command 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:
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.
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.
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. |
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.
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.
To enable discovery of other Administration Servers from the Control Center (CC), ensure that the following parameters are set by issuing the db2 get admin cfg command from the db2as ID:
DISCOVER=SEARCH DISCOVER_COMM=TCPIP
To ensure that db2inst can be discovered, ensure that the following parameters are set by issuing the db2 get dbm cfg command from the db2inst ID:
DISCOVER_INST=ENABLE
| Note: | This is the default value for the DISCOVER_INST parameter. |
Similarly, to ensure that the database db2instDB can be discovered, ensure that the following parameter is set by issuing the db2 get db cfg for db2instDB command from the db2inst ID:
DISCOVER_DB=ENABLE
| Note: | This is the default value for the DISCOVER_DB parameter. |
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 Extended Enterprise Edition on Windows NT, the node configuration file is created for you when you create the instance. Refer to the DB2 Extended Enterprise Edition for Windows NT Quick Beginnings for complete instructions on how to set up a partitioned database system. |
| 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, see "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:
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 Chapter 20. "Configuring DB2", and throughout this book.
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.
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.
| Windows NT Considerations: | If you are using DB2 Extended Enterprise 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 Extended Enterprise 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. |