لطفا منتظر باشید ...
Using IBM DB2 UDB 8.1 Command-Line Processor (CLP)
CLP is IBM DB2 command-line utility thatallows 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 log/image/library/english/10069_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=> ?db2=> ? echo |
DESCRIBE | Describes table columns or indexes for a table. | db2 describe table addressdb2 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=> quitC:\ |
TERMINATE | Similar to QUIT, but terminates all background processes and frees memory. | db2=> terminate |
