Understanding IBM DB2 DB2SET

December 9, 2010

One of the first troubleshooting opportunities I ever had with DB2 LUW involved db2set. That was with DB2 UDB 5.0 and was more than a decade ago. Now I’m using DB2 9.7 and I’m still using db2set, so it certainly has some staying power. Despite the fact that db2set has been around for a while, in talking to other DBAs it seems that db2set is not always well understood.

According to IBM’s documentation, the DB2 Profile Registry command (db2set) allows for centralized control of environmental variables. There are multiple levels of the Profile Registry.

• Instance Level [i}: The values defined in this registry (for the instance level) override the settings at the global level.
• Global Level [g}: This registry’s variable value is used if the corresponding environment variable has not been set for the specific instance.
• Instance Node {n}: Applies to DPF (partitioned) databases enabling variables to be set specific to the partition.
• Instance Profile (different from Instance Level). Contains a list of instance names. Each installation has its own list. The db2ilist command uses this to enumerate the instances.

DB2Set and the DB2Locksmith

From personal experience, I have found some interesting things about db2set. Once, when multiple DBAs were trying to use db2set at the same time to update values for the same instance, my db2set -all output displayed inconsistent values. This was easily fixed by simply determining the full set of updates we wanted to make and then having one person do all the db2set commands. Of course, that was on a previous release of DB2 so it may not be an issue with DB2 9.7.

I also discovered that DB2SYSTEM and DB2INSTDEF, which are set at the global level, can provide some good information. Just by looking at the DB2SYSTEM variable, I was once able to verify that an entire environment had been set up incorrectly using a file system copy approach versus a standard and supported DB2 approach. That one discovery saved me a lot of troubleshooting time.

Are you and DB2SET Acquainted?

Most of us have had at least some exposure to db2set. Remember this?

$> db2set db2comm=tcpip

Or maybe if your shop uses SAP, you have used this special aggregate registry variable which sets numerous values via one profile registry update.

$> db2set DB2_WORKLOAD=SAP

But have you used this one?

$> db2set -lr

If not, you might be missing some good information. When using the –lr option with db2set, you can see all the supported registry variables that are available for us with the product version for this installation.

Who can update the DB2 profile registry? The answer depends on the operating system. On a UNIX platform, someone who holds SYSADM for the instance can update registry values for that instance. On Windows, when extended security is enabled, a SYSADM who belongs to the DB2ADMNS group can set and update the registry values.

When using the DB2 Command Line to change a DB2 Profile variable value at the local or default instance level, the command is simply:

db2set <variable>=<value> 

Example:

db2set DB2COMM=tcpip
db2stop
db2start

(While the new value for DB2COMM is immediately stored in the profile registry, the change is not applied until a restart.)

DB2SET Helps Itself

If you want more information, it is easy to get it. DB2SET provides a command line help option that is actually helpful! Just adding a question mark to the end of the db2set command returns a full screen of information.

$> db2set ?

$> db2set  ?
DBI1300N  db2set displays, sets, or removes DB2 profile variables.
 
 db2set [variable=[value]]
        [-g|-i instance [db-partition-number]]
        [-all]
        [-null]
        [-r [instance] [node-number]]
        [-n DAS node[-u user[-p password]]]
        [-l|-lr]
        [-v]
        [-ul|-ur]
        [-?|-h]

Explanation:

The command options are:

-g       Access the global profile variables.
-i       Specifies the instance profile to use instead of the current or
         default.
-n       Specifies the remote DB2 Administration Server node name.
-u       Specifies the user ID to use for the Administration Server
         attachment.
-ul      Access the user profile variables
-ur      Refreshes user profile variables
-p       Specifies the password to use for the admin server attachment.
-r       Resets the profile registry for the given instance. The
         default/current instance will be used if none is provided.
-l       Lists all instance profiles.
-lr      Lists all supported registry variables.
-v       Verbose mode.
-?       Displays the command help message.
-h       Same as -? option.
-all     Displays all occurrences of the local environment variables as defined in:
         *  The environment, denoted by [e]
         *  The user level registry, denoted by [u]
         *  The node level registry, denoted by [n]
         *  The instance level registry, denoted by [i] and
         *  The global level registry, denoted by [g]
-null    Sets the variables value to null at the specified registry
         level to prevent looking up the value in the next registry
         level as defined in the variable value search order.
 
Notes:
*  db2set with no variable name displays all defined variables.
*  db2set <variable> displays <variable>'s value.
*  db2set <variable>= (nothing) deletes the <variable>.
*  db2set <variable>=<value> modifies the <variable>'s value.
*  db2set <variable> -null sets <variable>'s value to NULL.
*  db2set <variable> -all displays all defined <variable>'s values.
*  db2set -ur refreshes the current user profile.
*  db2set <variable> -ul displays defined <variables> at the user level.
*  db2set -all displays all defined variables in all registry levels.

The most common way to determine the set of values stored in the DB2 Profile Registry is to use the db2set –all command. Here’s an example.

db2set -all
[e] DB2PATH=C:\Program Files\IBM\SQLLIB
[i] DB2PROCESSORS=0,1
[i] DB2INSTPROF=C:\ProgramData\IBM\DB2\DB2COPY1
[g] DB2_COMPATIBILITY_VECTOR=MYS
[g] DB2_EXTSECURITY=YES
[g] DB2SYSTEM=LOCKSMITH
[g] DB2INSTDEF=DB2
[g] DB2ADMINSERVER=DB2DAS00

Provided you have the proper authority, you can also use the SYSIBMADM.REG_VARIABLES administrative view, which can be queried to get the registry settings from all database partitions. The result set might be different from what you would see when using the db2set -all. The mismatch can happen if a DB2 registry variable is configured using the db2set command after the instance has been started. REG_VARIABLES only returns the values that were in effect when the instance was started.

Here’s an example:

db2 "SELECT CHAR(REG_VAR_NAME,35) AS REGVAR, CHAR(REG_VAR_VALUE,35) AS VALUE, 
	LEVEL FROM TABLE(REG_LIST_VARIABLES())  AS REG ORDER BY 3,1"
 
 
REGVAR                     VALUE                                LEVEL
-------------------------  -----------------------------------  -----
DB2PATH                    C:\Program Files\IBM\SQLLIB          E
DB2ADMINSERVER             DB2DAS00                             G
DB2INSTDEF                 DB2                                  G
DB2SYSTEM                  LOCKSMITH                            G
DB2_COMPATIBILITY_VECTOR   MYS                                  G
DB2_EXTSECURITY            YES                                  G
DB2INSTPROF                C:\ProgramData\IBM\DB2\DB2COPY1      I
DB2PROCESSORS              0,1                                  I

Thinking about the Future

With each new version of DB2, the profile registry options are subject to change. So prior to upgrading, or performing a new install, one investigation I undertake is to learn about the various options for db2set as they apply to that particular product level. The IBM DB2 Information Center is always my resource for that. For DB2 9.7, you can find information for the db2set profile registry options starting at http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=/com.ibm.db2.luw.admin.regvars.doc/doc/c0004934.html. Drilling down from that link will give you all the details.

Final Thoughts

Sometimes there are some very valuable Profile Registry variables that can be set to improve performance or help DBAs solve application issues. It’s like opening a surprise gift, you know it might be something good, but you’re just not sure how good it might be until you look. Once you unwrap the information about db2set, you will understand how to use that knowledge as a gift for your DB2 environment.

» See All Articles by Columnist Rebecca Bond