DB2 Basics: Getting to know the DB2 UDB command line processor

The IBM® DB2® Universal Database™ (UDB) command line processor (CLP) is a convenient interface for accessing DB2 functions. Explore the three modes in which the CLP operates, get a detailed explanation of the db2 command, and learn some recent usability enhancements that are sure to please command line users. And, for those who prefer to work with graphical tools, see an overview of the Command Editor.

Roman Melnyk (roman_b_melnyk@hotmail.com), DB2 Information Development, IBM Canada Ltd.

Roman Melnyk photoRoman 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).



10 March 2005

IBM DB2 e-kit for Database Professionals

Learn how easy it is to get trained and certified for DB2 for Linux, UNIX, and Windows with the IBM DB2 e-kit for Database Professionals. Register now, and expand your skills portfolio, or extend your DBMS vendor support to include DB2.

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
Figure 1. Syntax of the db2 command
db2 command
Table 1. CLP option flags
OgptionDescription (default)
-aDisplay SQLCA data. (OFF)
-cCommit SQL statements automatically. (ON)
-e{c|s}Write SQLCODE or SQLSTATE data to standard output. (OFF)
-f filenameAccept input from the specified file (command script). If other options are specified in combination with the -f option, the -f option must be specified last. (OFF)
-l filenameWrite log records to the specified file. This file contains a history of executed commands and statements. If the file already exists, new information is appended. If other options are specified in combination with the -l option, the -l option must be specified last. (OFF)
-nPreserve any trailing blank space characters that precede a newline character within a delimited token. For example:
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.

The -n option must be used with the -t option. (OFF)
-oWrite both data and messages to standard output. (ON)
-pDisplay a prompt when in CLP interactive mode. (ON)
-r filenameRedirect output (excluding messages and error codes) to the specified file. If the file already exists, new information is appended. If the -a option is specified in combination with the -r option, SQLCA data is also written to the file. The -r option does not affect the -e option. If the -e option is specified, SQLCODE or SQLSTATE data is written to standard output, not to the file. (OFF)
-sStop processing if errors occur while executing a command script or when in CLP interactive mode; write error messages to standard output. If this option is disabled, error messages are displayed, and execution of the remaining commands or statements continues; execution only stops if a system error occurs. (OFF)
-tUse a semicolon (;) as the statement termination character. This option disables the backslash (\) line continuation character. (OFF)
-tdxUse x as the statement termination character. (OFF)
-vEcho input text to standard output. (OFF)
-wDisplay SQL statement warning messages. (ON)
-xReturn data without any headers, including column names. (OFF)
-z filenameRedirect all output (including any messages or error codes) to the specified file. If the file already exists, new information is appended. If the -a option is specified in combination with the -z option, SQLCA data is also written to the file. The -z option does not affect the -e option. If the -e option is specified, SQLCODE or SQLSTATE data is written to standard output, not to the file. (OFF)

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.


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.

Listing 1. Customizing the CLP's interactive 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):

Valid tokens for the DB2_CLPPROMPT registry variable

This DB2 registry variable lets you define a new prompt for the CLP interactive mode. You do this by setting the variable to a text string whose length is not greater than 100 characters. The string includes one or more of the following tokens:

  • %i. The local alias of the current instance attachment. If no instance attachment exists, %i is replaced by the value of the DB2INSTANCE registry variable. If the DB2INSTANCE registry variable is not set (Windows operating systems only), %i is replaced by the value of the DB2INSTDEF registry variable. If neither of these variables is set, %i is replaced by an empty string.
  • %d. The local alias of the current database connection. If no database connection exists, %d is replaced by the value of the DB2DBDFT registry variable. If the DB2DBDFT variable is not set, %d is replaced by the empty string.
  • %ia. The authorization ID of the current instance attachment. If no instance attachment exists, %ia is replaced by the empty string.
  • %da. The authorization ID of the current database connection. If no database connection exists, %da is replaced by the empty string.
  • %n. A newline character.

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.

Listing 2. Using the CLP command cache and "roundtrip editing"
(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):

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.


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).

Figure 2. The Command Editor notebook can be opened from the Control Center
Opening the Command Editor

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).

Figure 3. The Command Editor is a three-page notebook for entering commands and retrieving results
The Command Editor is a three-page notebook

You can retrieve the command history for the session by selecting Selected → History, or by clicking the History icon: history. 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).

Figure 4. The Command History window
The Command History window

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.

Figure 5. Using the Command Editor to call an SQL procedure
Using the Command Editor to call an SQL procedure

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).

Figure 6. Saving a command script to the Task Center
Saving a command script to the Task Center

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

Figure 7. The Task Center
The Task Center

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.

Figure 8. Running a command script from the Task Center
Running a command script from the Task Center

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).

Figure 9. Viewing the results of a command script execution from the Task Center
Viewing the results of a command script execution from the Task Center

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

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=52707
ArticleTitle=DB2 Basics: Getting to know the DB2 UDB command line processor
publish-date=03102005