Learning Visually with Examples [Electronic resources] نسخه متنی

Figure 4.3. Invoking the Command Window from a MS-DOS command prompt

[View full size image]

Figure 4.4. Figure 4.4 The Command Line Processor in interactive mode

Chapter 5 for information about DB2 registry variables).

history

r uncmd <

n >

Reexecutes command number

n from the list given by the

history command. If

n is not specified (or

n = -1), the previous command is invoked.

To reexecute the third command in the history list:

r 3 

e dit <

n >

Edits the command number

n using an editor defined by the DB2 registry variable DB2_CLP_EDITOR. If not set, this uses the vi editor on Linux/UNIX and Notepad on Windows.

To edit the fifth command in the history list:

e 5 

Exclamation mark (!)

This is the escape character that lets you issue operating system commands from within the CLP interactive mode

!dir

Figure 4.5 shows a few examples of the commands in Table 4.1 in action.

Method 2: Non-interactive Mode

To work with the CLP in non-interactive mode is equivalent to working with the DB2 Command Window (on Windows) or the Linux/UNIX shell. If you start the CLP in interactive mode, entering the

quit command takes you to the CLP in non-interactive mode. In this mode you need to prefix the CLP command or SQL statement with the

db2 executable. For example:

db2 connect to sample 
db2 list applications all 
db2 select * from employee 

Using this method you can execute operating system commands in addition to DB2 commands and SQL statements from the same window or session.

NOTE

References to the Command Window and the CLP on Windows platforms are sometimes used interchangeably in DB2 books to indicate the use of a command line interface as opposed to a GUI tool.

Many DB2 users prefer to work in this environment because they can use some shortcut key strokes, such as pressing the up arrow key to repeat the last commands on Windows, or to take advantage of operating system mechanisms like piping the output of the CLP to the

more command on Linux and UNIX to display the output in portions.

Every time you issue the

db2 executable, a "CLP session" is created where a front-end process is invoked. This takes the rest of the statement as input and then closes the process. For example, when you issue:

db2 list db directory 

db2 invokes a CLP front-end process that takes

list db directory as input. Once the CLP digests this command, it implicitly issues the

quit command to end the CLP front-end process. The front-end and back-end processes are discussed in more detail later in this chapter.

Figure 4.6 shows the CLP in non-interactive mode.

When invoking the CLP in non-interactive mode, double quotes (

" ) enclosing the CLP command or SQL statement may be required if these contain special characters that the operating system may interpret as wildcard characters. This is especially important on Linux and UNIX platforms. If double quotes are not used, the error message DB2 reports will vary depending on where the wildcard character is used in the statement. For example, if you issue this statement:

db2 select * from employee 

you

may receive the following error message, since the asterisk (*) is a wildcard character:

SQL0104N An unexpected token "*" was found following "select " 

Use double quotes to avoid parsing errors:

db2 "select * from employee" 

A more deceiving example occurs when you use the greater than (

> ) character. For example, the statement:

db2 select lastname from employee where salary > 10000 

will be first parsed by the operating system, which will interpret

> 10000 as the redirection of the output to the file

10000 . After executing the above statement, your current directory will have a new file with the name

10000 containing a DB2 syntax error message because only

select lastname from employee where salary was passed to DB2. Again, to resolve this problem, make sure to enclose the statement in double quotes:

db2 "select lastname from employee where salary > 10000" 

Method 3: Non-interactive Mode Using a File as Input

The CLP can use a file containing one or more CLP commands or SQL statements and process them one after the other. This is ideal to develop DB2 database scripts. For example, Figure 4.7 shows the contents of the file

myInput.txt , which we will use as input to the CLP.

To execute this DB2 script file, the

-f command option (for

file ) followed by the file name is required to indicate to the CLP that this file contains the input. (CLP command options are described in detail in the next section.) If the input file contains a statement terminator character, the

-t command option (for

terminator ) is required to indicate a terminator character is present. By default, the statement terminator is a semicolon (;). If you want to use a different terminator, the

d

character option (for delimiter) indicates which delimiter character is being used as the terminator. Use the

-v option (for

verbose ) to echo the command you are executing. Figure 4.8 provides an example of invoking the CLP using these command options.

NOTE

The input file must be a text file. Be aware that invisible characters may cause the DB2 CLP to fail processing the file. If using the Notepad application on Windows, for example, saving the text file with Unicode encoding rather than ANSI encoding will cause this error:

DB21007E End of file reached while reading the command. 

If you prefix each of the CLP commands with

db2 (the CLP executable) in a file and remove the terminator characters, you are effectively converting this file into an operating system script rather than a DB2 script. Depending on the operating system, you may have to make additional modifications. For example, on Windows, you need to use

rem for comments. You may also need to change the file name so that the

.bat extension is used. Figure 4.9 shows this for the file

myOS_Input.bat .

On Linux and UNIX platforms, use the pound sign (

# ) for comments. You may also need to change the permissions of the file so that it is executable. Typically you can use this command to change the file permissions:

chmod +x myOS_Input.txt 

Figure 4.10 shows the same script for a Linux or UNIX platform.

NOTE

DB2 scripts do not accept parameters, but operating system scripts do. In other words, if you need to invoke your scripts with parameters, you need to use operating system scripts.

4.2.1.2 CLP Command Options

The CLP is just another program designed to interact with DB2. Like many other programs, the CLP has been designed to accept several parameter options. The CLP command

list command options displays the available CLP command option parameters (see Figure 4.11).

To turn on an option, use a dash (-) in the command line. To turn off an option, use a plus symbol (

+ ). Some options are on (or off) by default. For example, to enable autocommit, invoke the CLP as follows:

db2 -c insert into employee (firstnme) values ('Raul') 

After you execute this command, a

COMMIT statement is automatically issued because autocommit is enabled. (As you can see in Figure 4.11, the Auto-Commit option was already on by default, so including

-c in the above example is not necessary.)

To disable autocommit, invoke the CLP as follows:

db2 +c insert into employee (firstnme) values ('Raul') 

Note that specifying a command option in the

db2 command applies only to that session of the CLP. Issuing the

db2 command without an option will use the default command option values, or the ones contained in the DB2OPTIONS registry variable, which we discuss later in this section.

You can also change a command option when working with the CLP in interactive mode using the following command:

update command options using  
option value option value 
... 

Figure 4.12 shows an example where the

v option (verbose) is used. This option causes the command or statement to be repeated or echoed when executed as discussed earlier. In Figure 4.12, note that the

SELECT * FROM department statement is echoed.

If you would like the changes to your CLP options effective across all your CLP sessions, you can set the DB2OPTIONS registry variable with the desired options. In the command:

db2set db2options="-v -z myfile.log" 

the DB2OPTIONS registry variable is set so that any command executed will be echoed (

-v option), and the output will be spooled in the file

myfile.log

(-z myfile.log option). The changes take effect immediately for the current session and any other new CLP sessions that you start.

To reset the values to the default, issue this command:

db2set db2options= 

DB2 registry variables are explained in detail in Chapter 5, Understanding the DB2 Environment, DB2 Instances, and Databases.

4.2.1.3 Obtaining Help Information from the CLP

One of the most useful CLP commands is the help command represented by a question mark (

? ). This command provides help on SQL error codes (SQLCODE), DB2 messages, and CLP command syntax. For example:

db2 ? SQL0104N 
db2 ? DB21004E 
db2 ? list applications 

In addition, using the help command by itself displays the entire list of CLP commands, as shown in Figure 4.13.

NOTE

The help (

? ) command can display CLP command syntax, but not SQL statement syntax. Refer to the

DB2

UDB

SQL Reference manual for SQL statement syntax.

Figure 4.14 shows other examples of the help (

? ) command.

4.2.1.4 Line Continuation

There are two ways to use line continuation from the CLP: with the backslash character and with the delimiter terminator character.

Method 1: Using the Backslash (\) Character

You can use the backslash (

\ ) character in either interactive or non-interactive mode. Figure 4.15 shows an example using interactive mode first, followed by a non-interactive mode example.

Notice that after entering

\ and pressing Enter, the prompt changes to:

db2 (cont.) => 

Method 2: Using a Delimiter Terminator Character with the CLP in Interactive Mode

Using this method, the CLP is invoked in interactive mode using the terminator delimiter option. For example:

db2 -td! 

After entering this command and pressing Enter, the CLP is invoked in interactive mode. You can wrap commands onto multiple lines until you type the terminator character, which is the exclamation mark (

! ) in the example shown in Figure 4.16.

Use this method when you have statements that include carriage returns. If you copy and paste one of these statements into the CLP, the carriage returns will cause the statement to continue in another line, which is acceptable, because the CLP processes the command after the terminator character is entered.

The following statement has one carriage return character after

staff and one after

Edwards ; therefore, use method 2 to start the DB2 CLP in interactive mode:

select * from staff 
where name = 'Edwards' 
and job = 'Sales' 

After you copy and paste the statement into the CLP, enter the terminator character and press Enter to execute it.

4.2.1.5 The CLP Front-end and Back-end Processes

The CLP has both front-end and a back-end processes. The front-end allows you to perform actions without connecting to a database. For example, issuing the command:

db2 list db directory 

does not require a connection to a database. Depending on the operation, the instance need not be started either.

The back-end process is needed when you perform actions against a database. The back-end process is created when you connect to the database in a CLP session and can be identified by the application name

db2bp . Figure 4.17 shows the output of the

list applications command, which shows this process (or thread in Windows) indicating a connection to the

sample database.

To remove the connection to a database, issue the

connect reset statement, the

terminate command, or the

disconnect statement.

Connect reset and

terminate will work even if the process is in the middle of a unit of work.

Disconnect only works when there is no active unit of work. Closing a window or session without previously issuing a

terminate command will close the CLP application and front-end process and remove the connection to the database, but does not guarantee that the back-end process will be terminated.

NOTE

The

terminate command is the only one that guarantees the back-end process is indeed terminated. Even if the

list applications command does not display the

db2bp back-end process running, use the

terminate command to be certain.

It is important to make sure that the back-end process is terminated because in some circumstances a change to a parameter, environment variable, or DB2 registry variable will not take effect until this is performed. For example, in a multi-partition environment, the DB2NODE environment variable is used to indicate which partition is the coordinator. After changing the value of this variable, you must issue a

terminate command for it to take effect.

NOTE

We recommend issuing a

terminate command before a

db2stop command. This prevents the back-end process from maintaining an attachment to an instance that is no longer active.

4.2.2. The Command Editor

The

Command Editor is the graphical user interface (GUI) version of the Command Line Processor. The Command Editor offers several other functions in addition to those provided by the CLP and the Command Window.

• The ability to execute multiple DB2 commands and SQL statements interactively and simultaneously. With the CLP, only one command at a time can be executed interactively. If you want to execute multiple commands, you have to save them in a text file and execute the file with the

  -f option as explained in section 4.2.1.1, Methods to Work with the CLP.

• The ability to save all the commands you typed in the Command Editor window to a file or as a task to be executed from the Task center.

• The ability to display a Visual Explain output of the access path chosen by the DB2 optimizer for an SQL statement. The Visual Explain tool is discussed in the next section.

• The ability to display results in well-formatted tables.

db2ce starts it from a command line prompt.

Figure 4.18 shows the start screen of the Command Editor. The Target field is empty until you click on the

Add button, which displays the Specify Target window. Select a Target type from the pull-down menu, and the database you want to work with in the Available targets pull-down menu.

The Command Editor makes an implicit connection to the database you have chosen, and you can then start entering your commands from the command input window (see Figure 4.19).