Home > Articles > Data > DB2

This chapter is from the book

This chapter is from the book

Using the Command Line Processor (CLP)

The CLP was introduced in Chapter 1. This section discusses how the CLP is used in the DB2 UDB environment, especially when making changes at the session level. There are two methods of interacting with DB2. First, you can enter the command line environment (type db2, and the prompt will change to “db2 =>”). In that “interactive” environment, your commands will be seen by DB2 first. Second, from a normal system command prompt, you can send commands to DB2 by prefacing a CLP command with “DB2.”

When using the CLP, be careful that the OS does not parse your SQL statements. When issuing commands from a normal command prompt ($), enclose any SQL statements or DB2 commands within quotation marks. This will ensure that they are not parsed by the OS. Within the interactive CLP, you can issue OS commands by prefacing them with an exclamation mark (!). For example:

db2 => ! ls -l

If the command exceeds the line limit allowed by the OS, use a slash (/) as the line continuation character.

The complete syntax and explanation of all commands are documented in the DB2 UDB documentation. You can also obtain syntax and information for all DB2 commands (but not SQL operations) from the CLP as follows:

DB2 ?-              Displays a list of all DB2 commands
   DB2 ?command-       Displays information about a specific command
   DB2 ?SQLnnnn-       Displays information about a specific SQLCODE
   DB2 ?DB2nnnn-       Displays information about DB2 error

To examine the current CLP settings, issue the following command:


Figure 2.41 shows the default settings, and provides help on changing them.

02fig41.jpgFigure 2.41. Displaying the CLP Command Options Settings

These settings can be updated globally or for each CLP session using the DB2OPTIONS profile variable. You may also view the settings (shown in Figure 2.42) with the command:


02fig42.jpgFigure 2.42. DB2 Help for Updating Command Options

This command displays all the available options, the possible values for each option, and the usage of the UPDATE COMMAND OPTIONS command.

This command turns off the auto-commit option.

The CLP has two parts: a front-end process and a back-end process. The front-end process is called db2 and the back-end process is called db2bp. The back-end process will maintain a connection to the database. To release this connection, use the TERMINATE command. To end an


interactive DB2 session, issue the QUIT command (this does not release the database connection, however). When looking at applications in DB2, you will see CLP connections to the system with an application of db2bp (shown in Figure 2.43).

02fig43.jpgFigure 2.43. List of Applications Showing db2bp Connected to SAMPLE

You can place multiple SQL statements and DB2 commands in a file to be executed using the CLP. Suppose you create a file called file.clp, as shown in Figure 2.44.

02fig44.jpgFigure 2.44. The Contents of file.clp

Every DB2 command or SQL statement in this file is terminated with a semicolon (;) which is the default termination character. You can change the default termination character, if you wish, using the -t option. To execute the commands in file.clp, type:

db2 -tvf file.clp

The output is displayed in Figure 2.45.

02fig45.jpgFigure 2.45. Results of Executing file .clp

Notice that each command is displayed as well as the output. This is because the -v (verbose option) was used to execute the file.

  • + Share This
  • 🔖 Save To Your Account