Using IBM DB2 UDB 8.1 Command-Line Processor (CLP)

CLP is IBM DB2 command-line utility that allows you to execute SQL statements and invoke online help. This appendix provides the basic commands and options you can use with CLP to run either interactive commands or scripts against your DB2 database. Akin to Oracle's SQL*Plus, CLP is platform-independent. You can use it in interactive input mode, command mode, or batch mode.

Interactive mode

To enter the interactive mode, open the Command Line Processor window (StartàProgramsàIBM DB2àCommand Line ToolsàCommand Line Processor):

(c) Copyright IBM Corporation
		  1993,2002 Command Line Processor for DB2 SDK 8.1.0 ... For general help, type:
		  ?. ... db2 =>

For the most commonly used options, see Table E-2.

Command mode

Open the Command Line Processor window (StartàProgramsàIBM DB2àCommand Line ToolsàCommand Window). That initializes environment variables for you, so you can type the CLP commands prefixed with db2, for example:

C:\Program
		  Files\IBM\SQLLIB\BIN> db2 connect to acme Database Connection Information
		  Database server = DB2/NT 8.1.0 SQL authorization ID = BORIS Local database
		  alias = ACME C:\Program Files\IBM\SQLLIB\BIN> db2 select * from status
		  STATUS_ID_N STATUS_CODE_S STATUS_DESC_S ----------- -------------
		  ------------------------------ 2 20 COMPLETE 6 60 SHIPPED 8 70 INVOICED 9 80
		  CANCELLED 4 record(s) selected.

Batch mode

Batch mode allows you to execute SQL statements stored in the operating system's files. It is invoked with -f option (see Table E-2). For example,

C:\Program
		  Files\IBM\SQLLIB\BIN> db2 –f C:\myfiles\query.sql

executes the contents of file query.sql in the myfiles directory of the C:\ drive (presuming it does exist).

Table E-2: Common CLP Options
Option	Explanation
-c	Automatically commits SQL statements. This option is turned on by default; i.e., all your statements will be automatically committed unless you start your session with +c option (db2 +c).
-f <filename>	Reads command input from file <filename>. You have to specify the full path to your file unless it is in the current directory: db2 -f /home/btrukhnov/db2/queries/my_query.sql
-l <filename>	Creates a log of commands. For example: db2 -f my_query.sql -l logfiles/my_query.log
-r <filename>	Logs the command output to file <filename>.
-s	Stops execution on error; usually used with -t option, when script execution termination is desirable if a statement fails.
-t	Uses semicolon as the statement termination character.
-v	Echoes command text to standard output.
-w	Displays SQL statement warning messages.
-z <filename>	Redirects all output to file <filename>.

Command Line Processor has many commands; most of them are for database administration and not relevant to this book. Table E-3 lists some CLP commands that you may find useful.

Table E-3: Common CLP commands
CLP command	Description	Example
!	Invokes an operating system command.	db2=> !dir
?	Invokes online help.	db2=> ?db2=> ? echo
DESCRIBE	Describes table columns or indexes for a table.	db2 describe table address db2 describe indexes for table customer
ECHO	Writes to standard output.	db2 echo "Enter your query"
GET CONNECTION STATE	Displays the state of the current connection.	db2 get connection state
HELP	Invokes the Information Center.	db2 help
LIST ACTIVE DATABASES	Displays the list of databases ready for connection.	db2 list active databases
QUIT	Exits CLP interactive input mode.	db2=> quit C:\
TERMINATE	Similar to QUIT, but terminates all background processes and frees memory.	db2=> terminate