THE COMMAND-LINE T OOLS

All DB2 operations are invoked by DB2 commands, SQL statements, or XQuery statements. For

example, to back up a database, you use the BACKUP DATABASE command. To create a table,

you use the CREATE TABLE SQL statement. To parse an XML document you use the FLWOR

expression. All of these commands, SQL statements, and XQuery statements can be entered using the command-line tools.

The command-line tools consist of the Command Line Processor (CLP), the Command Window (Windows platform only), and the Command Editor. Since they are command driven, you must have some knowledge of DB2 commands and SQL statements to use them.

Figure 4.1 The IBM DB2 menu

N O T E Some of the tools described in this section are deprecated in version 9.5, that is, they are still part of the product, but will not be further enhanced. This is done in favor of a new strategy with respect to tools, which is to provide them decoupled from the DB2 code, as separate downloadable Web-based tools. The tools will not only work with DB2, but also with other IBM Data servers; therefore, their name starts with “Data Server” rather than “DB2” as in the case of the Data Server Administration Console (DSAC) Web-based tool. DSAC is described in more detail in Section 4.4, General Administration Tools.

4.2.1 The Command Line Processor and the Command Window

The DB2 CLP and the DB2 Command Window are text-based tools used to interact with the DB2 engine. Figure 4.2 shows the relationship between the CLP and the Command Window. Compare each line in the Windows machine versus the Linux/UNIX machine. The equivalent line in each machine has been aligned in the figure.

Windows Machine

MS-DOS Command Prompt Window

C:> <Any operating system command> Example: dir

C:> <Any DB2 system command> Example: db2start

C:> db2cmd (to invoke the Command Window)

C:> <Any operating system command> Example: dir

C:> <Any DB2 system command> Example: db2start C:> db2 <CLP Command>

(To invoke the CLP in non-interactive mode) Examples:

db2 list applications db2 connect to sample db2 select * from department C:> db2

(To invoke the CLP in interactive mode)

DB2 Command Window

DB2 Command Line Processor (CLP) in interactive mode

db2 => <CLP Command> Examples:

db2 => list applications db2 => connect to sample db2 => select * from department db2 => xquery <a>hello</a>

Linux/UNIX Machine

Linux/UNIX Shell

/home/user1 $ <Any operating system command> Example: ls

/home/user1 $ <Any DB2 system command> Example: db2start

DB2 Command Line Processor (CLP) in interactive mode

/home/user1 $ db2 <CLP Command> (To invoke the CLP in non-interactive mode) Examples:

db2 list applications db2 connect to sample db2 select * from department

/home/user1 $ db2

(To invoke the CLP in interactive mode)

db2 => <CLP Command> Examples:

db2 => list applications db2 => connect to sample db2 => select * from department db2 => xquery <a>hello</a>

Figure 4.2 The Command Line Processor versus the Command Window

N O T E In this chapter we use DB2 commands to refer to both types of commands: DB2 system commands and DB2 CLP commands. When a section is only applicable to a given type of command, it will be explicitly indicated. Refer to Section 2.1, SQL Statements, XQuery Statements, and DB2 Commands, for an explanation about the differ- ences between these two types of commands.

The Command Window is only available on Windows; this is due to some architecture differ- ences in Windows versus Linux and UNIX. If you are familiar with the Linux and UNIX plat- forms, you can think of the Command Window on Windows as the Linux/UNIX shell. Figure 4.2 illustrates this: The commands and statements inside the DB2 Command Window box on the left-hand side of the figure are equivalent to the ones inside the Linux/UNIX shell box on the right-hand side of the figure.

To start the Command Window, click on Start > Programs > IBM DB2 > DB2COPY1

(Default) > Command Line Tools > Command Window (see Figure 4.1). Alternatively, to

invoke the Command Window from a MSDOS window, issue the command db2cmd. This

command spawns another window which displays DB2 CLP in the title bar. Note that the Com- mand Window looks like any MS-DOS window except for this title bar.

From an MS-DOS window, you can perform operating system commands and DB2 system com- mands but not DB2 CLP commands, SQL statements, or Xquery statements. However, you can perform all of these from a Command Window.

For example, as shown in Figure 4.2, from the MS-DOS window you can execute the following:

• Operating system commands: dir • DB2 system commands: db2start

You can also perform these from the Command Window, and in addition you can perform DB2 CLP commands and SQL statements:

• DB2 CLP command: db2 list applications

• SQL statements: db2 SELECT * FROM department • XQuery statements: db2 "xquery <a>hello</a>"

If you try to execute a CLP command, SQL statement, or XQuery statement from an MS-DOS window, you will receive the following error:

DB21061E Command line environment not initialized

as illustrated in Figure 4.3. The figure also shows how the same statement works from the

Command Window after it is invoked with the db2cmd command.

The Command Line Processor is an application written in the C language containing embedded SQL. It provides you with a text-based interface to the DB2 engine that lets you issue CLP com-

mands, SQL statements, and XQuery statements. The CLP executable is called db2 and it is

stored under the ...sqllib/bin directory.

N O T E We recommend that you learn how to use the Command Line Processor, as it is the common tool available with all DB2 versions and clients.

4.2.1.1 Methods to Work with the CLP

There are three ways to issue a DB2 command or SQL statement with the CLP: interactive mode, non-interactive mode, and non-interactive mode using a file as input. These methods are discussed in the following sections.