The DB2 UDB command line processor (CLP) represents an interface through which you can conveniently access DB2 UDB functions. The CLP accepts commands or SQL statements from a DB2 command line. On Linux and UNIX®-based systems, this is the command line of a DB2 instance. On Windows® operating systems, this is the command line of a CLP-enabled command window; in this case, the db2cmd command must be run first (from an ordinary command window) to initialize the DB2 command line environment.

The db2 command starts the command line processor. The CLP includes both a front-end process (representing the user interface) and a back-end process to maintain a database connection. Each db2 invocation starts a new front-end process; the first db2 invocation starts the back-end process. All front-end processes with the same parent are serviced by a single back-end process, and therefore share a single database connection. The QUIT command stops the command line processor, but does not remove the associated back-end process; to do that, you need to issue the TERMINATE command. It is recommended that you issue a TERMINATE command before stopping your instance (STOP DATABASE MANAGER or db2stop); otherwise, the back-end process maintains an attachment to an instance that is no longer available.

The db2 command

The CLP can be started in one of three modes:

• Command mode. In this mode, each command or SQL statement must be prefixed by db2. On Windows operating systems, the DOSKEY command lets you use the Up Arrow key to scroll through the command history for your session and to re-issue a command without having to type it again. You can also create a command file containing DB2 commands or SQL statements and run it as you would any other command file. For example, a file named myfile.cmd, containing the following:

  db2start db2 connect to sample db2 select * from org db2 terminate db2stop

  
  can be run by issuing myfile from a Windows DB2 command prompt.
• Interactive input mode. You can launch this mode by issuing the db2 command, specifying nothing else. The interactive input mode is characterized by the default db2 =>command prompt. Commands and statements issued from this prompt are not prefixed by db2:

  elk /home/melnyk>db2 ... db2 => connect to sample Database Connection Information Database server = DB2/6000 8.2.0 SQL authorization ID = MELNYK Local database alias = SAMPLE db2 => select * from org DEPTNUMB DEPTNAME MANAGER DIVISION LOCATION -------- -------------- ------- ---------- ------------- 10 Head Office 160 Corporate New York 15 New England 50 Eastern Boston 20 Mid Atlantic 10 Eastern Washington 38 South Atlantic 30 Eastern Atlanta 42 Great Lakes 100 Midwest Chicago 51 Plains 140 Midwest Dallas 66 Pacific 270 Western San Francisco 84 Mountain 290 Western Denver 8 record(s) selected. db2 => terminate DB20000I The TERMINATE command completed successfully. elk /home/melnyk>

  
  It is now possible to customize this prompt and to access the command history for the current session; see New usability features in the interactive CLP for details. To exit interactive input mode and return to the operating system prompt, issue the QUIT command or the TERMINATE command.
• Batch mode. This mode lets you run DB2 commands or SQL statements from a command script, a text file that contains the commands and statements in the order in which they are to be executed. In this case, you use the -f option flag (see Table 1) on the db2 command to specify the name of the command script. For example, a file named myscript.db2, containing the following:

  !db2start; connect to sample; select * from org; terminate;

  
  can be run by issuing db2 -tf myscript.db2 from a DB2 command prompt. Notice the differences between the content of this file and that of myfile.cmd shown above. In this case, commands and statements in the file are not prefixed by db2. Each command or statement is terminated by the semicolon character; in fact, by specifying the -t option flag on the db2 command, we directed the CLP to use a semicolon as the statement termination character. You might have also noticed the exclamation mark in front of the db2start command, which is not a CLP command, but a system command that you issue from an operating system prompt. The exclamation mark is an escape character that lets you issue operating system commands from either interactive input mode or batch mode.

Figure 1 shows the complete syntax of the db2 command. Options include the specification of one or more option flags to configure your CLP session (Table 1), a DB2 command or SQL statement to execute, a request for general CLP help (?), or help text associated with a specific command (? phrase), SQLCODE (? message), SQLSTATE (? sqlstate), or class code (? class-code; for more information about class codes, see SQLSTATE Messages). You can also request the current settings of the CLP options (? options), or information about reading the online help syntax diagrams (? help). The following examples illustrate some of these options:

db2 -tvf myscript.db2     => option flags
db2 connect to sample     => SQL statement
db2 list tables for user  => DB2 command
db2 ?                     => general CLP help
db2 ? options             => current CLP options settings
db2 ? help                => help for syntax online help
db2 ? backup              => help for a specific DB2 command
db2 ? sql0117N            => help for a specific SQLCODE
db2 ? 42501               => help for a specific SQLSTATE
db2 ? 01                  => help for a specific class code

Here are some additional points to remember about the db2 command:

• At least one blank space must separate the question mark (?) from the text string that follows.
• The CLP interprets any text string that is prefixed by a double hyphen (--) as a comment.
• You can use the backslash character (\) as the line continuation character in CLP command mode or interactive input mode. When the CLP encounters the line continuation character, it reads the next line and concatenates both lines. On Windows operating systems, the line continuation character generates a temporary db2 (cont.) => prompt.
• If message text returned by the db2 command is lengthy, consider using the "more" pipe (if available on your operating system), or redirecting the output to a file. For example:

  db2 "? sql0510" | more db2 "? SQL0510" > sql0510n.out

  
  Alternatively, use the -r or the -z CLP option to redirect output to a file (see Table 1). For example:

  db2 -r mydata\orglist.txt "select * from org"

  
  
• Use of double quotation mark delimiters on Linux and UNIX-based systems is recommended. If special characters are used in CLP command mode, they are interpreted by the operating system shell. This will likely produce unexpected results, unless you use double quotation marks or escape characters (such as the backslash character, \). For example, when executed inside an AIX® Korn shell environment, the following command:

  db2 select * from employee where edlevel > 18

  
  is interpreted as "select the names of all files from employee where edlevel and redirect the output to a file named '18'". The following commands return the correct output:

  db2 "select * from employee where edlevel > 18" db2 select \* from employee where edlevel \> 18

  
  
• The CLP recognizes 'NULL' (specified in uppercase characters) as a null string. For example:

  db2 update database configuration using mirrorlogpath NULL

  
  

Table 1. CLP option flags

create table t1 (c1 varchar(30));
insert into t1 values ('123   
789');
-- three blanks spaces follow '123
update command options using n on;
insert into t1 values ('123   
789');
select * from t1;

C1
------------------------------
123 789
123   789

  2 record(s) selected.

CLP options can generally be specified in any sequence and combination. To turn an option on, prefix the corresponding option letter with a minus sign (for example, -f). To turn an option off, prefix the option letter with a plus sign or wrap the option letter with two minus signs (for example, +c is equivalent to -c-; for an example of a scenario in which you might not want to automatically commit SQL statements, see Enhanced Savepoints).

You can use the LIST COMMAND OPTIONS command to see the current settings for the CLP options. You can change these settings from CLP interactive input mode or batch mode by issuing the UPDATE COMMAND OPTIONS command. For example:

db2 => update command options using c off p off

The settings revert to their original values when the interactive session or batch input ends.

You can also set the CLP options for a session globally using the DB2OPTIONS registry variable. For example:

export DB2OPTIONS='+c -p-'  => Linux and UNIX-based systems
set DB2OPTIONS=+c -p-       => Windows operating systems

This command turns off the automatic commitment of SQL statements and removes the prompt that is displayed in CLP interactive input mode.

Back to top

New usability features in the interactive CLP

DB2 UDB Version 8.1 introduced significant ease-of-use enhancements to the DB2 CLP. You can now customize the prompt that appears when you use the CLP in interactive mode. You can also exploit a new command cache to run previously executed commands, or to edit those commands using your operating system's integrated command editor or an editor of your choice.

The customized CLP prompt can show the current user, instance attachment, or database connection. To customize the command prompt, you will need to set a new DB2 UDB registry variable called DB2_CLPPROMPT. The DB2_CLPPROMPT registry variable can be set to any text string up to a maximum of 100 characters in length. The customized string can contain optional tokens that will be replaced with actual values at run time. The sidebar describes the tokens that you can set.

Listing 1 shows how you use the db2set command to set the value of the DB2_CLPPROMPT registry variable. (Be sure not to include blank spaces around the equals sign!) When the CLP interactive mode is invoked by issuing the db2 command, the resulting command prompt reflects the changes that were made. And once a database connection is established, the customized prompt also shows the alias of the connected database. The customized prompt can be altered by exiting the CLP interactive mode (issue the QUIT command), and then issuing the db2set command again, specifying a different string. In this example, the newline character token is used to generate a kind of "multi-line" prompt.

elk /home/melnyk>db2set db2_clpprompt="(Instance: %i, Database: %d):"

elk /home/melnyk>db2
(c) Copyright IBM Corporation 1993,2002
Command Line Processor for DB2 SDK 8.2.2
...
(Instance: MELNYK, Database: ):connect to sample

   Database Connection Information

 Database server        = DB2/6000 8.2.2
 SQL authorization ID   = MELNYK
 Local database alias   = SAMPLE

(Instance: MELNYK, Database: SAMPLE):quit
DB20000I  The QUIT command completed successfully.

elk /home/melnyk>db2set db2_clpprompt="(Instance:%i%n Database:%d):"

elk /home/melnyk>db2
(c) Copyright IBM Corporation 1993,2002
Command Line Processor for DB2 SDK 8.2.2
...
(Instance:MELNYK
 Database:):connect to sample

...

(Instance:MELNYK
 Database:SAMPLE):

DB2 UDB Version 8 also includes a handy command cache for the CLP interactive runtime environment. This feature lets you recall (view, edit, or run) any command that was entered during an interactive session.

The new HISTORY command lets you access the contents of this cache, which are returned with sequence numbers for each command in the history; these numbers show you the order in which the commands were run. The most recent command is listed last. The HISTORY command allows you to specify the REVERSE keyword if you want to list the commands in reverse chronological order. By default, the command cache holds up to twenty commands that were run during the current interactive CLP session. The command history does not persist from session to session; the cache is cleared when the session ends, which normally happens after you run the QUIT command. If you start two separate interactive CLP sessions, each session's command history is stored in a separate cache.

The real benefit of having the command cache comes from the fact that you can use the new RUNCMD (or R) command to execute any command in the cache. Not only can you execute a command, but you can also use the EDIT (or E) command to edit a cached command "on the fly" and then run it in a virtually seamless sequence of operations that is sometimes referred to as "roundtrip editing."

Both RUNCMD and EDIT let you specify a command sequence number to identify the cached command that you want to edit or run. If you do not specify a sequence number, the system retrieves the most recently run command.

Listing 2 demonstrates this feature. The listing continues from the end of Listing 1; it begins with a simple SELECT statement run against the ORG table in the SAMPLE database that comes with DB2 UDB. The HISTORY command invocation that follows shows that three commands or statements have been run in this session so far: CONNECT, SELECT, and HISTORY. Let's add a WHERE clause to that SELECT statement by invoking the EDIT command and specifying statement number 2 in the sequence. In this example running on an AIX system, we're also explicitly specifying the vi editor. We could have left out the optional EDITOR clause and, for example, set the value of the DB2_CLP_EDITOR registry variable to "vi" instead:
db2set db2_clp_editor="vi"

Because we have chosen to save our changes when closing the editor, the updated statement appears after the interactive CLP prompt, and we have the option of executing the statement immediately. Invoking the HISTORY command again shows that there are now five commands or statements in the cache, including the modified SELECT statement.

(Instance:MELNYK
 Database:SAMPLE):select * from org

DEPTNUMB DEPTNAME       MANAGER DIVISION   LOCATION
-------- -------------- ------- ---------- -------------
      10 Head Office        160 Corporate  New York
      15 New England         50 Eastern    Boston
      20 Mid Atlantic        10 Eastern    Washington
      38 South Atlantic      30 Eastern    Atlanta
      42 Great Lakes        100 Midwest    Chicago
      51 Plains             140 Midwest    Dallas
      66 Pacific            270 Western    San Francisco
      84 Mountain           290 Western    Denver

  8 record(s) selected.

(Instance:MELNYK
 Database:SAMPLE):history
1    connect to sample
2    select * from org
3    history
(Instance:MELNYK
 Database:SAMPLE):edit editor vi 2...
select * from org where location = 'Chicago'
~
~
~
...
~
~
~
:q
(Instance:MELNYK
 Database:SAMPLE):select * from org where location = 'Chicago'
Do you want to execute the above command ? (y/n) y

DEPTNUMB DEPTNAME       MANAGER DIVISION   LOCATION
-------- -------------- ------- ---------- -------------
      42 Great Lakes        100 Midwest    Chicago

  1 record(s) selected.

(Instance:MELNYK
 Database:SAMPLE):history reverse
5    history reverse
4    select * from org where location = 'Chicago'
3    history
2    select * from org
1    connect to sample
(Instance:MELNYK
 Database:SAMPLE):

Back to top

Sample CLP scripts

DB2 UDB provides sample CLP scripts that you can run against the SAMPLE database that comes with DB2. These scripts are located in the /sqllib/samples/clp directory on Linux and UNIX-based systems, and the \SQLLIB\samples\clp directory on Windows operating systems. The accompanying README file describes the command scripts and how to run them. For the latest samples updates, see Sample Programs.

Back to top

The Command Editor

The Command Editor is the graphical user interface (GUI) to the CLP. It appears as a notebook that can be opened from the Windows Start menu (Start → Programs → IBM DB2 → Command Line Tools → Command Editor), by issuing the db2ce command, or from the Control Center's toolbar (Figure 2).

When the Command Editor first opens, the Commands page is immediately available for input (Figure 3). You can begin typing in (or paste text into) the Command Input window, making sure that each command or SQL statement ends with a statement termination character. The default character, a semicolon, appears in a field that you can modify. You can even import a previously saved script from the file system or from the Task Center (Selected → Open). When you are ready to run your commands or SQL statements, you can click the Execute button.

If you select a target database, the Query Results and Access Plan pages of the Command Editor notebook become available. The Query Results page lets you view and work with the results of a single SQL query. You can even change the value of a field, or delete or insert an entire row on this page, and then commit or roll back the changes that you have made! After running such a query, the Command Output window (the lower part of the Commands page) echos the query and shows you the query status. The Access Plan page provides you with a graphical representation of the access plan (the order of operations for accessing the requested data) for explainable SQL statements (such as SELECT, INSERT, or UPDATE).

You can retrieve the command history for the session by selecting Selected → History, or by clicking the History icon: . If a command or an SQL statement that you select in the Command History window is particularly long and has been truncated in the Commands pane, the full command or statement appears in the Details pane (Figure 4).

The SQL statement in Figure 3 or Figure 4 creates an SQL procedure, which is a stored procedure whose body is written in SQL. SQL procedures are a handy way to define more complex queries or tasks that can be called whenever they are needed. If we had included this statement in a CLP script named createSQLproc.db2, for example, we could have created the procedure by issuing the following commands:

db2 connect to sample

db2 -td@ -vf createSQLproc.db2

db2 connect reset

This db2 command specifies the -td option, which tells the command line processor to define and to use @ as the statement termination character (the procedure body is already using the semicolon as its statement termination character); the -v option, which tells the CLP to echo command text to standard output; and the -f option, which tells the CLP to read input from the specified file instead of from standard input. The SALES_STATUS procedure accepts an input parameter called quota and returns an output parameter called sql_state. The procedure body consists of a single SELECT statement that returns the name and the total sales figures for each sales person whose total sales exceed the specified quota.

Figure 5 shows you how to use the Command Editor to call this stored procedure, using a quota value of 25.

You might also be interested in saving this procedure call as a script that can be run later, or that can be scheduled to run regularly. You can do this by saving the statement to the file system or to the Task Center (Selected → Save; see Figure 6).

The easiest way to open the Task Center at this point is to click the Task Center icon in the Command Editor's toolbar.

SALES STATUS appears in the task list (Figure 7). Right-clicking this task or opening the Selected pull-down menu gives you a list of possible actions, including running the task now (Figure 8) or scheduling it to run later.

Once the task has run, you can examine the execution results by right-clicking the task or opening the Selected pull-down menu and selecting the Show Results action. The Show Results notebook that opens gives you execution and script details, including output and error messages, if any (Figure 9).

Back to top

Summary

We have seen that the DB2 UDB command line processor is a convenient and versatile interface for accessing DB2 functions. The DB2 CLP can be invoked from the DB2 command line, through command scripts, and even through a graphical user interface. Numerous examples were used to illustrate the different ways in which the CLP can be used and customized according to your preferences. I encourage you to explore further, and to develop your own scripts as a way to increase your understanding of DB2 and effectively manage your databases.

Resources

• DB2 Universal Database for Linux, UNIX and Windows Support is the ideal place to locate resources such as the Version 8.2 Information Center and PDF product manuals.
  
  
• The IBM DB2 Universal Database Command Reference provides detailed information about the DB2 command line processor and all of the DB2 UDB CLP commands.
  
  

About the author

Roman B. Melnyk , Ph.D., is a senior member of the DB2 Information Development team, specializing in database administration, DB2 utilities, and SQL. During more than ten years at IBM, Roman has written numerous DB2 books, articles, and other related materials. Roman coauthored DB2 Version 8: The Official Guide (Prentice Hall Professional Technical Reference, 2003), DB2: The Complete Reference (Osborne/McGraw-Hill, 2001), DB2 Fundamentals Certification for Dummies (Hungry Minds, 2001), and DB2 for Dummies (IDG Books, 2000).

Comments (Undergoing maintenance)

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• The db2 command
• New usability features in the interactive CLP
• Sample CLP scripts
• The Command Editor
• Summary
• Resources
• About the author
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: DB2 serving ATOM feed
• Webcast: A smarter way to successful SOA - Information as a trusted strategic asset
• Briefing: Information on Demand
• Buy: DB2 Enterprise

My developerWorks community

Dig deeper into Information Management on developerWorks

DB2 pureScale

See how true transparent scaling architecture can be achieved on distributed platforms

Special offers