Master all aspects of IBM DB2 database administration and prepare to take and pass IBM's Certification Exams 611 and 311: Certified Database Administrator.

Editor's note: This article is an excerpt from the new book DB2 10.1/10.5 for Linux, UNIX, and Windows Database Administration (Exams 611 and 311): Certification Study Guide.

During normal operation, the DB2 database manager's behavior is controlled, in part, by a collection of values that defines the DB2 operating environment. Some of these values are operating system environment variables, and others are special DB2-specific system-level values known as environment or registry variables. Registry variables provide a way to centrally control the database environment. Four different registry profiles are available, and each controls the database environment at a different level. The registry profiles are as follows:

• The DB2 global-level profile registry—All machine-wide environment variable settings reside in this registry; one global-level profile registry exists on each DB2 workstation. To set an environment variable for all instances, use this profile registry.
• The DB2 instance-level profile registry—The environment variable settings for a particular instance are kept in this registry; this is where you set the majority of the DB2 environment variables. The values defined in this profile registry override any corresponding settings in the global-level profile registry.
• The DB2 instance node-level profile registry—This profile registry level contains variable settings that are specific to a partition (node) in a multi-partitioned database environment. The values defined in this profile registry override any corresponding settings in the global-level and instance-level profile registries.
• The DB2 user-level profile registry—This profile registry level contains variable settings that are specific to each user and takes higher precedence over other registry settings.

Table 2.3 shows the order in which DB2 resolves the registry settings and the environment variables when configuring the system.

You can use the db2greg command to view and alter the global registry settings, which then modifies the file /var/db2/global.reg in root installations and $HOME/sqllib/global.reg in nonroot installations.

The command output in a root installation looks like this:

db2greg -dump

V,DB2GPRF,DB2SYSTEM,gb01qa,/opt/ibm/db2/V10.1,

S,TSA,3.2.2.5,/opt/IBM/tsamp,-,-,0,0,-,1389969042,0 S,DB2,10.1.0.3,/opt/ibm/db2/V10.1,,,3,0,,1389969093,0

I,DB2,10.1.0.3,db2inst1,/home/db2inst1/sqllib,,1,0,/opt/ibm/db2/V10.1,, V,DB2GPRF,DB2INSTDEF,db2inst1,/opt/ibm/db2/V10.1,

The global registry consists of three record types:

• Service (S)—This records product-level information, such as version and install path.
• Instance (I)—This records instance-level information, such as instance name, instance path, DB2 version, and the start-at-boot flag.
• Variables (V)—This records variables and the value settings.

You can edit the global registry setting by using the db2greg command; editing in root installation needs a root privilege.

A wide variety of registry variables are available, and they vary depending on the operating system being used. Chapter 22 of the Database Administration Concepts and Configuration Reference manual contains a complete listing.

So how do you determine which registry variables have been set and what they have been set to? Or more important, how do you assign values to one or more registry variables? One way is by executing the db2set system command. The syntax for this command is:

db2set [variable=[value]]

[-g|-i instance [member-number]]

[-all]

[-null]

[-r [instance] [member-number]]

[-im|-immediate]

[-info]

[-n DAS Node [-u user [-p password]]]

[-l|-lr]

[-v]

[-ul|-ur]

[-?|-h]

where:

Variable--Identifies the registry variable whose value is to be displayed, set, or removed

Value--Identifies the value to assign to the specified registry variable; if no value is provided but a registry variable is specified, the specified registry variable is deleted

Instance--Identifies the instance profile with which to associate the specified registry variable

Member-number--Identifies the node number of the instance in cases regarding the use of a DPF database

DAS Node--Identifies the name of the node where the DAS instance resides (this is deprecated in DB2 9.7 and is no longer required to be created)

User--Identifies the authentication ID to use to attach to the DAS instance

Passwords--Identifies the password (for the authentication ID) to use to attach to the DB2 Administration Server instance

Table 2.4 describes all other options with this command.

It is important to note that if you execute the db2set command without options, a list containing every registry variable that has been set for the current (default) instance, along with its value, will be returned.

Thus, if you want to determine which registry variables have been set for each profile, execute the db2set command:

db2set -all

And the resulting output might look something like this:

[i] DB2FCMCOMM=TCPIP4

[i] DB2_SKIPINSERTED=ON

[i] DB2_OBJECT_TABLE_ENTRIES=10000

[i] DB2_USE_ALTERNATE_PAGE_CLEANING=ON

[i] DB2_LOAD_COPY_NO_OVERRIDE=nonrecoverable

[i] DB2_INLIST_TO_NLJN=YES

[i] DB2_REDUCED_OPTIMIZATION=ON

[i] DB2_EVALUNCOMMITTED=ON

[i] DB2_EXTENDED_OPTIMIZATION=Y

[i] DB2_ANTIJOIN=Y

[i] DB2TCPCONNMGRS=16

[i] DB2_SKIPDELETED=ON

[i] DB2DBDFT=SAMPLE

[i] DB2COMM=TCPIP

[i] DB2_PARALLEL_IO=*:5

[i] DB2AUTOSTART=YES

[g] DB2SYSTEM=prodbcuapp001

Alternatively, to see the current value of the DB2COMM registry variable for all DB2 instances, execute a db2set command that looks something like this: 

db2set -l DB2COMM

And finally, if you want to assign a value to the DB2COMM registry variable for all DB2 instances on a server, you can do so by executing a db2set command that looks something like this:

db2set -g DB2COMM=[Protocol, ...]

where:

Protocol Identifies one or more communications protocols to start when the DB2 database manager for the instance is started; any combination of the following values is valid: NPIPE, TCPIP, and SSL

Thus, to set the DB2COMM instance-level registry variable such that the DB2 database manager will start the TCP/IP communication manager each time any instance is started, execute a db2set command that looks like this:

/home/db2inst1/sqllib/adm/db2set -g DB2COMM=TCPIP

 You can unset the value assigned to any registry variable by providing just the variable name and the equal sign as input to the db2set command. Thus, if you want to disable the DB2COMM instance-level registry variable for an instance named db2inst1, you can do so by executing a db2set command that looks like this:

db2set -i DB2INST1 DB2COMM=

A Word About Aggregate Registry Variables

An aggregate registry variable is a group of several registry variables as a configuration that is identified by one registry variable name. Each registry variable that is part of the group has a predefined setting. The purpose of an aggregate registry variable is to ease registry configuration for broad operational objectives.

In DB2 10.1, the only valid aggregated registry variable is DB2_WORKLOAD, and the valid values for this variable are:

Value                        Description

                           1C                           1C application-specific workload setting

CM                          Content Manager–specific workload setting

COGNOS_CS        Cognos® Content Server–specific workload setting FILENET_CM         FileNet® Content Manager–specific workload setting INFOR_ERP_LN    Infor ERP Baan–specific workload setting

                           MAXIMO                 Maximo®-specific workload setting

                           MDM                       Master Data Management–specific workload setting

                          SAP                        SAP application–specific workload setting

                          TPM                        Tivoli® Provisioning Manager–specific workload setting

                          WAS                        WebSphere® Application Server–specific workload setting

                          WC                          WebSphere Commerce–specific workload setting

                          WP                          WebSphere Portal–specific workload setting

You can use an aggregate registry variable to explicitly define any registry variable that is implicitly configured, which in a way overrides the aggregated registry variable implicit value.

If you attempt to modify an explicitly set registry variable by using an aggregate registry variable, a warning is issued and the explicitly set value is kept. This warning tells you that the explicit value is maintained and will override the implicit value. For example, setting DB2_ REDUCED_OPTIMIZATION to YES and then setting the DB2_WORKLOAD to SAP will generate a warning message something like the following:

db2set DB2_REDUCED_OPTIMIZATION=YES

db2set DB2_WORKLOAD=SAP

DBI1319W The variable "DB2_REDUCED_OPTIMIZATION" has been explicitly set and

will not be affected by the configuration of the aggregate variable "DB2_

WORKLOAD".

If the aggregate registry variable is used first, and then you specify an explicit registry variable, no warning is given. To identify the override settings, use the db2set –all command and check for [O] displayed next to its value, as follows:

[i] DB2_INLIST_TO_NLJN=YES [O]

[i] DB2_REDUCED_OPTIMIZATION=YES [O]

[i] DB2COMM=TCPIP [O]

And wherever the DB2_WORKLOAD setting value is active, you will see [DB2_WORKLOAD] appear next to its value something like this:

[i] DB2_ROWCOMPMODE_DEFAULT=STATIC [DB2_WORKLOAD]

[i] DB2_INDEX_PCTFREE_DEFAULT=0 [DB2_WORKLOAD]

[i] DB2_SKIP_VIEWRECREATE_SAP=TRUE [DB2_WORKLOAD]