DB2 Command Line Processor Tips and Tricks

by Paul C. Zikopoulos
and Roman B. Melnyk

If you are an experienced database administrator (DBA) you
likely use a mix of graphical user interface (GUI) tools and the IBM® DB2®
Universal Database™ (DB2 UDB) command line processor (DB2 CLP) to accomplish
your day-to-day tasks. In some cases, the difference between using a GUI or the
CLP is a cultural one: Windows®-based DBAs prefer to use a GUI interface (for
example, the DB2 Control Center), whereas Linux and UNIX®-based DBAs often
choose the DB2 CLP.

When business catch phrases like “time to value” or
“flattened learning curves” find their way into every aspect of a vendor’s
marketing campaign, they always seem to revolve around the product’s GUI.
Sometimes lost, though, are the little things that could help increase the
productivity and efficiency of DB2 CLP users.

Since the release of DB2 UDB Version 8.1, there have been
some ease-of-use enhancements to the DB2 CLP. The ability to customize the DB2
CLP (interactive mode) was added in FixPak 1. The DB2 CLP was further enhanced
in Version 8.1.2 by adding plug-ins for your favorite text editors, as well as
a command cache. This article will detail these new features.

Customizing the DB2 CLP in Interactive Mode

DB2 UDB Version 8.1 with FixPak 1 adds the ability to have
customizable DB2 CLP interactive prompts. You can use this feature to reflect
the context of the current instance attachment and database connection, and is
a neat way for DBAs to quickly and easily identify characteristics of their
current attachment. In the past, DBAs would have to repeatedly invoke CLP
commands (such as GET INSTANCE) to recall this information. The DB2 CLP
interactive prompt was hard coded and could not be changed, as shown below.

The new customization feature allows you to have the prompt
reflect the current user, current instance attachment, and database connection,
among others. The following example shows a customized interactive DB2 CLP
prompt that identifies user PAULZ, who is attached to the DB2 instance
and connected to the SAMPLE database.

To customize the command prompt, a new DB2 UDB registry
variable called DB2_CLPPROMPT was introduced. The DB2_CLPPROMPT
registry variable can be set to any text string that is 1 to 100 characters
long. The customized string can contain optional tokens that will be replaced
at run time. The information in the prompt is always displayed in uppercase
characters, regardless of how the options are configured, and if you change the
settings, the new values will not take effect until you restart the CLP.

You can set the DB2_CLPPROMPT registry variable in
the DB2 UDB Profile Registry using the DB2SET command, as shown below:

The following table lists the tokens that you can set:

The DB2 CLP Command Cache

In DB2 UDB Version 8.1.2, a DB2 CLP command cache was added
to the interactive runtime environment. This neat feature gives you the ability
not only to recall commands that you have entered in an interactive DB2 CLP
session, but to run them as well.

This feature is implemented through two new DB2 commands:
HISTORY and RUNCMD.

Latest Articles