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).
| 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.
| CLP command | Description | Example |
|---|---|---|
| ! | Invokes an operating system command. | db2=> !dir |
| ? | Invokes online help. | db2=> ? |
| 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 |
|
|
EAN: 2147483647
Pages: 208