Despite the fact that db2set has been around for a while, it seems that db2set is not always well understood. According to IBM’s documentation, the IBM DB2 Profile Registry command (db2set) allows for centralized control of environmental variables. Read on to learn more…
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 FilesIBMSQLLIB [i] DB2PROCESSORS=0,1 [i] DB2INSTPROF=C:ProgramDataIBMDB2DB2COPY1 [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 FilesIBMSQLLIB E DB2ADMINSERVER DB2DAS00 G DB2INSTDEF DB2 G DB2SYSTEM LOCKSMITH G DB2_COMPATIBILITY_VECTOR MYS G DB2_EXTSECURITY YES G DB2INSTPROF C:ProgramDataIBMDB2DB2COPY1 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.
