DB2 Version 10.1 for Linux, UNIX, and Windows

db2set - DB2 profile registry command

Displays, sets, or deletes the values of DB2®profile variables. The db2set command is an external environment registry command that supports local and remote administration through the DB2 administration server (DAS).

You can also use the ENV_GET_REG_VARIABLES table function to retrieve the values of the registry variables that the instance is using and the values that are stored in the registry.

Authorization

SYSADM and, for the -g command parameter, root access on Linux and UNIX operating systems or Local Administrator authority on Windows operating systems.

Required connection

A local instance attachment is required when updating variables immediately, if there is no instance attachment, a local instance attachment is created. Otherwise, no connection is required. A remote attachment is not supported.

Command syntax


>>-db2set--+----------------------+----------------------------->
           '-variable=--+-------+-'   
                        '-value-'     

>--+----------------------------------------+--+-------+-------->
   +- -g------------------------------------+  '- -all-'   
   +- -i--instance--+---------------------+-+              
   |                +-db-partition-number-+ |              
   |                '-member_number-------' |              
   '- -gl-----------------------------------'              

>--+-----------------------------+--+--------+------------------>
   '- -gd--agg-registry-variable-'  '- -null-'   

>--+--------------------------------------------+--------------->
   '- -r--+----------+--+---------------------+-'   
          '-instance-'  +-db-partition-number-+     
                        '-member_number-------'     

>--+-------------+--+------------------+------------------------>
   '- -immediate-'  '- -info--variable-'   

>--+-------------------------------------------------+---------->
   '- -n--DAS node--+------------------------------+-'   
                    '- -u--user--+---------------+-'     
                                 '- -p--password-'       

>--+------+--+-----+--+------+--+-----+------------------------><
   +- -l--+  '- -v-'  +- -ul-+  +- -h-+   
   '- -lr-'           '- -ur-'  '- -?-'

Command parameters

variable=

Displays the value of the specified variable.

value: Sets the specified variable to the specified value. If the value has space(s), make sure to enclose the value in quotes. If you do not specify a value after the equal sign (=), the current value of the variable is deleted. There are a set of registry variables which are immediate by default and another set of registry variables that are immediate if you specify the -immediate parameter. Both of these sets of registry variables take effect the next time the SQL statement is compiled. Otherwise, changes take effect after you restart the instance.

-g

Accesses the global profile registry variables for all instances pertaining to a particular DB2 copy. This allows you to view or change the values of these variables at this level.

-i instance

Specifies the instance profile to use. If you do not specify an instance profile, the current, or default one is used.

db-partition-number or member-number: Specifies a number in the db2nodes.cfg file.
If you use the -immediate parameter with the member-number option, only the specific member sees the update the next time the SQL statement is compiled.

-gl

Accesses the global profile variables stored in LDAP. This parameter is effective only if you set the DB2_ENABLE_LDAP registry variable to YES.

-all

Displays all occurrences of the local environment variables as defined in the following places:

The operating system environment, denoted by [e]
The node-level registry, denoted by [n]
The instance-level registry, denoted by [i]
The global-level registry, denoted by [g]

-gd agg-registry-variable

Displays the group definition of an aggregate registry variable. .

-null

Sets the value of the variable to NULL at the registry level you specified or at the default level, which is the instance level, if you did not specify a registry level. This means that DB2 considers the variable as being not set and will not search for a value for the variable in the next available registry levels, as defined by the order of precedence of the registry levels.

-r

Resets the profile registry for a particular instance.

instance: Specifies the instance for which you want to reset the profile. If you do not specify an instance and an instance attachment exists, this option resets the profile for the current instance. If you do not specify an instance and no attachment exists, this option resets the profile for the instance that is specified by the DB2INSTANCE environment variable.
db-partition-number or member-number: Specifies a number in the db2nodes.cfg file.
If you use the -immediate parameter with the member-number option, the value is reset only for the specified member.

-immediate | -im

Specifies that the update takes effect the next time an SQL statement is compiled for registry variables that support this feature.

Immediate changes to registry variables that affect the SQL compiler will take effect the next time that you compile an SQL statement. There are two types of SQL statements:

Dynamic SQL statements: If a dynamic SQL statement is already present in the package cache, the statement will not be invalidated and therefore the statement will not be recompiled with new settings. In order for the -immediate parameter to take effect, you must issue the FLUSH PACKAGE CACHE statement in order to remove previous statements from the package cache so that your SQL statement can be recompiled without having to restart the instance.
Static SQL statements: If a static SQL statement is already present in a package, the statement will not be invalidated and therefore the statement will not be recompiled with new settings. In order for the -immediate parameter to take effect, you must issue the BIND command or the REBIND command in order for the package to be recompiled without having to restart the instance

You cannot combine this parameter with either the -g, -gl, or -n parameters.

-info variable

Returns the properties of the specified variable. The properties state whether an immediate change is supported by the variable and whether the change is immediate by default.

-n DAS node

Specifies the remote DAS node name.

-u user

Specifies the user ID to use for the administration server attachment.

-p password

Specifies the password to use for the administration server attachment.

-l

Lists all instance profiles for the DB2 product installation.

-lr

Lists all supported registry variables.

-v

Specifies that verbose output is to be used while the command is running.

-ul

Accesses the user profile variables. This parameter is supported on Windows operating systems only.

-ur

Refreshes the user profile variables. This allows multiple users to have different variable settings under the same instance or under the same environment settings. This parameter is supported on Windows operating systems only.

-h | -?

Displays help information. If you specify this parameter, all other parameters are ignored.

Examples

The following examples show how to issue the various parameters with the db2set command:

Display all defined instant profiles pertaining to a particular installation:
```
   db2set -l
```
Display all supported registry variables:
```
   db2set -lr
```
Display all defined global variables that are visible to all instances pertaining to a particular installation:
```
   db2set -g
```
Display all defined variables for the current instance:
```
   db2set
```
Display all defined values for the current instance:
```
   db2set -all
```
Display all defined values for the DB2COMM registry variable for the current instance:
```
   db2set -all DB2COMM
```
Reset all defined variables for the instance INST on member 3:
```
   db2set -r -i INST 3
```
Delete the value of the DB2CHKPTR registry variable on the remote instance RMTINST through the DAS node RMTDAS, using user ID MYID and password MYPASSWD:
```
   db2set -i RMTINST -n RMTDAS -u MYID -p MYPASSWD DB2CHKPTR=
```
Set the DB2COMM registry variable to TCPIP for all instances pertaining to a particular installation:
```
   db2set -g DB2COMM=TCPIP
```
Set the DB2COMM registry variable to TCPIP only for instance MYINST:
```
   db2set -i MYINST DB2COMM=TCPIP
```
Set the DB2COMM registry variable to null at the default level. The default level is the instance level:
```
   db2set -null DB2COMM
```
Delete the current value of the registry variable DB2_ANTIJOIN so that it takes effect the next time the SQL statement is compiled:
```
   db2set DB2_ANTIJOIN= -immediate
```
Set a registry variable to a value containing space(s) by enclosing the value in quotes:
```
db2set DB2_LOAD_COPY_NO_OVERRIDE='COPY YES TO /var/db2/loadcopy/sales'
```
Note: The above command overrides the setting of load copy specified on the LOAD command such that it is always YES, and places the copy file in the directory /var/db2/loadcopy/sales. The quotes are needed because of the space between COPY and YES and again before /var/db2/loadcopy/sales.

Usage notes

You can set a variable at one or more of four levels: operating-system environment, node instance, instance, and global. The DB2 database system uses this order of precedence to access and resolve variable settings. If you already set the value of a variable at the operating-system-environment level, an update to the value of the variable does not take effect immediately, even if you use the -immediate parameter, because the operating-system-environment level takes precedence over the registries.

If you do not specify a variable name, the values of all defined variables are displayed. If you specify a variable name, the value of only that variable is displayed. To display all the defined values of a variable, specify the variable and -all parameters. To display the values of all the defined variables in all registries, specify the -all parameter.

To make registry variable changes for remote registries on Windows operating systems, issue the db2_all or rah command with the db2set command.

Although the command behaves the same way for root and for non-root installations of the DB2 product, not all parameters are available, such as the -n parameter, which specifies the DAS node name.

The db2set command must not be run concurrently or continuously in a script.