Custom DB2 Universal Database command line processor

Introduction

The Ibm®db2®universal Database™linux, UNIX®, and Windows® Editions (hereinafter called DB2 UDB) provide two ways to enter commands from the command line interface. When using the DB2 UDB command-line processor in interactive (Interactive) mode (DB2 udb command line processor,db2 udb CLP), you do not have to add keyword DB2 before the DB2 UDB command or SQL query. If you are unfamiliar with the different operating modes of the DB2 UDB CLP, check out the DB2 planning-DB2 V8.1 Family Fundamentals Certification Preparation Tutorial, part 1th (total 6).

Take a look at Figure 1, in which the SELECT * is entered in the DB2 UDB CLP running in interactive mode ... Statement. Do you know which database or instance this particular table (STAFF) is located in? You probably don't know it (though for this particular case, you can guess); but DB2 UDB knows!

Figure 1. The default CLP view does not tell you which instance is connected to

Now take a look at the exact same query in Figure 2 below. Can you answer my question now? Notice what's different?

Figure 2. The CLP is customized to show you the instance and database that you are connecting to

The DB2 UDB V8.1 with Revision Pack 1 (Fix Pack 1,FP1) provides a great new feature that allows you to customize the DB2 udb CLP prompt when you are running in interactive mode (the DB2 udb CLP in the latter context is the DB2 UDB C running in interactive mode) LP).

You can use this new customization feature to add your own text and variables that reflect the context (contexts) of the current instance connection (instance attachment) and/or database connection (DB connection) to the output display. This article will introduce you to this new customization feature.

DB2 UDB CLP in custom interactive mode

DB2 V8.1 with FP1 has new customizable DB2 UDB CLP interactive prompt capabilities. The DB2 UDB CLP prompt can now reflect the context of the current instance connection and the database connection, and can also display specific character messages. Without this feature, using the DB2 CLP in interactive mode displays hard-coded prompts, as shown in Figure 3.

Figure 3. Hard-coded prompts in CLP

Before going on: 20-second course on DB2 UDB Registry variables

You can use the DB2 UDB registry variable to change the environment. These variables can be set this way because they are dynamic in nature and can be applied to the current run-time environment without having to be included in the system startup profile. There are some DB2 UDB registry variables can be set only as part of system startup.

The DB2 UDB V8.1 provides a DB2 overview registry (DB2 profile Registry) to control the environment. In previous distributions, if you changed the environment level, you would need to reboot the DB2 UDB. With some exceptions, the DB2 UDB V8.1 environment can be specifically controlled by registry variables stored in the DB2 profile registry.

You can use the Db2set command to update the DB2 UDB registry variable, which is immediately stored in the profile registry. The DB2 Profile Registry applies these updated information to DB2 UDB server instances and applications that are started after the changes have been made.

You can also assign a value to an environment-level variable using the SET command for a temporary session (most of the variables set in this way appear in the DB2 UDB registry; However, some do not, such as db2instance).

If you need to permanently set an environment variable, you should set it in the workstation's startup profile using the Db2set command. The environment variable set by the SET command is valid only during the command session, and the Db2set command line permanently sets the environment variable in the DB2 profile registry.

To view a list of all supported registry variables, enter the following command:

Db2set-lr

To change the value of the DB2 UDB registry variable, enter the following command:

Db2set Registry_variable_name=new_value

To view a list of all DB2 UDB registry variables that are set, enter the following command:

Db2set-all

The output of this command is similar to Figure 4.

Figure 4. DB2 the output of the Set-all command

Note that there is an [E] in front of the environment-level variable, and the instance and global variables are preceded by [i] and [G].