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 variables and registry values 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 values stored in the DB2 profile registries. Use the db2set command to update registry values without rebooting; this information is stored immediately in the profile registries.
| Note: | The DB2 environment variables db2instance, 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 E. "DB2 Registry Values and Environment Variables" lists many of the environment variables and registry values. 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 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 for your platform, 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 variable_name
To delete the current session value of a registry variable, use:
db2set variable_name=
To change a registry variable for this session only, use:
db2set variable_name=new_value
To change a registry variable default for all databases in the instance, use:
db2set variable_name=new_value
-I instance_name
To change a registry variable default for all instances in the system, use:
db2set 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 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 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 variable_name node_number
On OS/2, you should have no environment variables defined in config.sys apart from DB2PATH and DB2INSTPROF. All values 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 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%\PROFILES.REG
%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
Remote registry support is allowed: Only the global level profile registry key, GLOBAL_PROFILE, must be local to a machine. The system profile registry key, PROFILES, may reside on a cluster or a remote machine registry. To specify the remote registry, set the DB2 system variable, DB2REMOTEPREG, to the remote machine name. For example,
db2set DB2REMOTEPREG=\\rmtwkstnwhere \\rmtwkstn is the remote workstation name.
| Note: | Care should be taken in setting this option since all the 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.
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. |
\HKEY_LOCAL_MACHINE\SOFTWARE\IBM\DB2\PROFILES
Remote registry support is allowed: Only the global level profile registry key, GLOBAL_PROFILE, must be local to a machine. The system profile registry key, PROFILES, may reside on a cluster or a remote machine registry. To specify the remote registry, set the DB2 system variable, DB2REMOTEPREG, to the remote machine name. For example,
db2set DB2REMOTEPREG=\\rmtwkstnwhere \\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 DB2 instance that enables remote administration of DB2 servers. The Administration Server instance is created and used in a similar fashion to any other DB2 instance. You can only have one DAS on a machine.
For more information on setting up DAS communications, see the Quick Beginnings for your platform.
Enter db2admin create. (If this command returns an error stating that a DAS already exists, issue a "db2admin drop", then re-issue "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.
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 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 database manager 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:
db2reset admin cfgChanges to the database manager configuration file become effective only after they are loaded into memory (that is, when db2start is executed).
To set up the communications protocols for the DAS, see the Quick Beginnings for your platform.
The DB2 Administration Server must be cataloged before the user can attach to the DAS. The only difference from the normal CATALOG NODE command is the addition of the keyword ADMIN after the keyword CATALOG. For more information on using the CATALOG command for nodes, including the differences for the various communication protocols, see the Command Reference.
When cataloging locally, you must know the instance name of the DAS. Use the db2set command to view the instance name:
db2set db2adminserver
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.
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.
| 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. 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 19. "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.
On the Windows NT and OS/2 platforms, use the Performance Configuration SmartGuide, which helps you tune performance and balance memory requirements for a single database per instance by suggesting which configuration parameters to modify and providing suggested values for them. To use this SmartGuide:
In most cases the values recommended by the Performance Configuration SmartGuide will provide better performance than the default values, because they are based on information about your workload and you own particular server. However, note that the values are designed to improve the performance of, though not necessarily optimize, your database system. They should be thought of as a starting point on which you can make further adjustments to obtain optimized performance.
For details on how to refine your system by benchmarking, and to configure your system, see Chapter 18. "Benchmark Testing" and Chapter 19. "Configuring DB2".
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. 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. |