This section explains how to invoke and use the command line processor, and describes CLP options.
The db2 command starts the command line processor. The CLP is used to execute database utilities, SQL statements and online help. It offers a variety of command options, and can be be started in:
| Note: | On Windows NT, db2cmd - Open DB2 Command Window opens the CLP-enabled DB2 window, and initializes the DB2 command line environment. Issuing this command is equivalent to clicking on the DB2 Command Window icon. |
QUIT stops the command line processor. TERMINATE also stops the command line processor, but removes the associated back-end process and frees any memory that is being used. TERMINATE is recommended if the database has been stopped, or if database configuration parameters have been changed.
| Note: | Existing connections should be reset before terminating the CLP. |
The shell command (!), allows operating system commands to be executed from the interactive or the batch mode on UNIX based systems, and on OS/2 or the Windows operating system (!ls on UNIX, and !dir on OS/2 or the Windows operating system, for example).
| Note: | Shell command support is not available on Windows 3.1. |
Authorization
None
>>-db2---+-------------------------------------------+---------><
| +-----------------+ |
| V | |
+---+--------------+-+--+------------------++
| +-option-flag--+ +-db2-command------+|
| +-sql-statement----+|
| +-?--+------------++|
| +-phrase-----+ |
| +-message----+ |
| +-sqlstate---+ |
| +-class-code-+ |
+-----comment-------------------------------+
|
Command Parameters
? options requests a description and the current settings of the CLP options. ? help requests information about reading the online help syntax diagrams.
| Note: | In each case, a blank space must separate the question mark (?) from the variable name. |
The CLP command options can be specified by setting the command line processor DB2OPTIONS environment variable (which must be in uppercase), or with command line flags.
Users can set options for an entire session using DB2OPTIONS.
View the current settings for the option flags and the value of DB2OPTIONS using LIST COMMAND OPTIONS. Change an option setting from the interactive input mode or a command file using UPDATE COMMAND OPTIONS.
The command line processor sets options in the following order:
Table 2 summarizes the CLP option flags. These options can be specified in almost
any sequence and combination. To turn an option on, prefix the corresponding
option letter with a minus sign (-). To turn an option off, either prefix the
option letter with a minus sign and follow the option letter with another
minus sign, or prefix the option letter with a plus sign (+). For
example, -c turns the auto-commit option on, and either
-c- or +c turns it off. These option letters are
not case sensitive, that is, -a and -A are equivalent.
| Option Flag | Description | Default Setting |
|---|---|---|
| -a | This option tells the command line processor to display SQLCA data. | OFF |
| -c | This option tells the command line processor to automatically commit SQL statements. | ON |
| -e{c&splitvbar.s} | This option tells the command line processor to display SQLCODE or SQLSTATE. These options are mutually exclusive. | OFF |
| -ffilename | This option tells the command line processor to read command input from a file instead of from standard input. | OFF |
| -lfilename | This option tells the command line processor to log commands in a history file. | OFF |
| -o | This option tells the command line processor to display output data and messages to standard output. | ON |
| -p | This option tells the command line processor to display a command line processor prompt when in interactive input mode. | ON |
| -rfilename | This option tells the command line processor to write the report generated by a command to a file. | OFF |
| -s | This option tells the command line processor to stop execution if errors occur while executing commands in a batch file or in interactive mode. | OFF |
| -t | This option tells the command line processor to use a semicolon (;) as the statement termination character. | OFF |
| -tdx | This option tells the command line processor to define and to use x as the statement termination character. | OFF |
| -v | This option tells the command line processor to echo command text to standard output. | OFF |
| -w | This option tells the command line processor to display SQL statement warning messages. | ON |
| -zfilename | This option tells the command line processor to redirect all output to a file. It is similar to the -r option, but includes any messages or error codes with the output. | OFF |
Example
The AIX command:
export DB2OPTIONS='+a -c +ec -o -p'sets the following default settings for the session:
Display SQLCA - off Auto Commit - on Display SQLCODE - off Display Output - on Display Prompt - on |
The following is a detailed description of these options:
The default setting for this command option is OFF (+a or -a-).
The -o and the -r options affect the -a option; see the option descriptions for details.
The default setting for this command option is ON.
The auto-commit option does not affect any other command line processor option.
Example: Consider the following scenario:
The SQL statement in step 4 fails because there is no column named C2 in table A. Since that statement was issued with auto-commit ON (default), it rolls back not only the statement in step 4, but also the one in step 3, because the latter was issued with auto-commit OFF. The command:
db2 list tablesthen returns an empty list.
The default setting for this command option is OFF (+e or -e-).
The -o and the -r options affect the -e option; see the option descriptions for details.
The display SQLCODE/SQLSTATE option does not affect any other command line processor option.
Example: To retrieve SQLCODE from the command line processor running on AIX, enter:
sqlcode=&rprime.db2 -ec +o db2-command&rprime.
When other options are combined with option -f, option -f must be specified last. For example:
db2 -tvf filename
| Note: | This option cannot be changed from within the interactive mode. |
The default setting for this command option is OFF (+f or -f-).
Commands are processed until QUIT or TERMINATE is issued, or an end-of-file is encountered.
If both this option and a database command are specified, the command line processor does not process any commands, and an error message is returned.
Input file lines which begin with the comment characters -- are treated as comments by the command line processor. Comment characters must be the first non-blank characters on a line.
If the -ffilename option is specified, the -p option is ignored.
The read from input file option does not affect any other command line processor option.
When other options are combined with option -l, option -l must be specified last. For example:
db2 -tvl filename
The default setting for this command option is OFF (+l or -l-).
The log commands in history file option does not affect any other command line processor option.
The default setting for this command option is ON.
The interactive mode start-up information is not affected by this option. Output data consists of report output from the execution of the user-specified command, and SQLCA data (if requested).
The following options may be affected by the +o option:
If both -o and -e options are specified, the data and either the SQLCODE or the SQLSTATE are displayed on the screen.
If both -o and -v options are specified, the data is displayed, and the text of each command issued is echoed to the screen.
The display output option does not affect any other command line processor option.
The default setting for this command option is ON.
Turning the prompt off is useful when commands are being piped to the command line processor. For example, a file containing CLP commands could be executed by issuing:
db2 +p < myfile.clp
The -p option is ignored if the -ffilename option is specified.
The display DB2 interactive prompt option does not affect any other command line processor option.
The default setting for this command option is OFF (+r or -r-).
If the -a option is specified, SQLCA data is written to the file.
The -r option does not affect the -e option. If the -e option is specified, SQLCODE or SQLSTATE is written to standard output, not to a file.
If -rfilename is set in DB2OPTIONS, the user can set the +r (or -r-) option from the command line to prevent output data for a particular command invocation from being written to the file.
The save to report file option does not affect any other command line processor option.
The default setting for this command option is OFF (+s or -s-). This setting causes the command line processor to display error messages, continue execution of the remaining commands, and to stop execution only if a system error occurs (return code 8).
The following table summarizes this behavior:
Table 3. CLP Return Codes and Command Execution
| Return Code | -s Option Set | +s Option Set |
|---|---|---|
| 0 (success) | execution continues | execution continues |
| 1 (0 rows selected) | execution continues | execution continues |
| 2 (warning) | execution continues | execution continues |
| 4 (DB2 or SQL error) | execution stops | execution continues |
| 8 (System error) | execution stops | execution stops |
| Note: | This option cannot be changed from within the interactive mode. |
The default setting for this command option is OFF (+t or -t-).
To define a termination character, use -td followed by the chosen termination character. For example, -tdx sets x as the statement termination character.
The termination character cannot be used to concatenate multiple statements from the command line, since only the last non-blank character on each input line is checked for a termination symbol.
The statement termination character option does not affect any other command line processor option.
The default setting for this command option is OFF (+v or -v-).
The -v option has no effect if +o (or -o-) is specified.
The verbose output option does not affect any other command line processor option.
The default setting for this command option is ON.
The default setting for this command option is OFF (+z or -z-).
If the -a option is specified, SQLCA data is written to the file.
The -z option does not affect the -e option. If the -e option is specified, SQLCODE or SQLSTATE is written to standard output, not to a file.
If -zfilename is set in DB2OPTIONS, the user can set the +z (or -z-) option from the command line to prevent output data for a particular command invocation from being written to the file.
The save all output to file option does not affect any other command line processor option.
Return Codes
When the command line processor finishes processing a command or an SQL statement, it returns an exit (or return) code. These codes are transparent to users executing CLP functions from the command line, but they can be retrieved when those functions are executed from a shell script.
For example, the following Bourne shell script executes the GET DATABASE MANAGER CONFIGURATION command, then inspects the CLP return code:
db2 get database manager configuration if ["$?" = "0"] then echo "OK!" fi
The return code can be one of the following:
The command line processor does not provide a return code while a user is executing statements from interactive mode, or while input is being read from a file (using the -f option).
A return code is available only after the user quits interactive mode, or when processing of an input file ends. In these cases, the return code is the logical OR of the distinct codes returned from the individual commands or statements executed to that point.
For example, if a user in interactive mode issues commands resulting in return codes of 0, 1, and 2, a return code of 3 will be returned after the user quits interactive mode. The individual codes 0, 1, and 2 are not returned. Return code 3 tells the user that during interactive mode processing, one or more commands returned a 1, and one or more commands returned a 2.
A return code of 4 results from a negative SQLCODE returned by a DB2 command or an SQL statement. A return code of 8 results only if the command line processor encounters a system error.
If commands are issued from an input file or in interactive mode, and the command line processor experiences a system error (return code 8), command execution is halted immediately. If one or more DB2 commands or SQL statements end in error (return code 4), command execution stops if the -s (Stop Execution on Command Error) option is set; otherwise, execution continues.
The command line processor operates as follows:
Before accessing a database, the user must perform preliminary tasks, such as starting DB2 with START DATABASE MANAGER. The user must also connect to a database before it can be queried. Connect to a database by doing one of the following:
For information about working with tables within a database, see the SQL Reference.
If a command exceeds the character limit allowed at the command prompt, a backslash (\) can be used as the line continuation character. When the command line processor encounters the line continuation character, it reads the next line and concatenates the characters contained on both lines. Alternatively, the -t option can be used to set a line termination character (see ***). In this case, the line continuation character is invalid, and all statements and commands must end with the line termination character.
The command line processor recognizes a string called NULL as a null string. Fields that have been set previously to some value can later be set to null. For example,
db2 update database manager configuration using tm_database NULLsets the tm_database field to null. This operation is case sensitive. A lowercase null is not interpreted as a null string, but rather as a string containing the letters null.
Using the Command Line Processor in Command Files
CLP requests to the database manager can be imbedded in a shell script command file. The following example shows how to enter the CREATE TABLE statement in a shell script command file:
db2 "create table mytable (name VARCHAR(20), color CHAR(10))"
For more information about commands and command files, see the appropriate operating system manual.
The command line processor consists of two processes: the front-end process (the DB2 command), which acts as the user interface, and the back-end process (db2bp), which maintains a database connection.
Maintaining Database Connections
Each time that db2 is invoked, a new front-end process is started. The back-end process is started by the first db2 invocation, and can be explicitly terminated with TERMINATE. All front-end processes with the same parent are serviced by a single back-end process, and therefore share a single database connection.
For example, the following db2 calls from the same operating system command prompt result in separate front-end processes sharing a single back-end process, which holds a database connection throughout:
The following invocations from the same operating system prompt result in separate database connections because each has a distinct parent process, and therefore a distinct back-end process:
Communication between Front-end and Back-end Processes
The front-end process and back-end processes communicate through three message queues: a request queue, an input queue, and an output queue.
Environment Variables
The following environment variables offer a means of configuring
communication between the two processes:
Table 4. Environment Variables
| Variable | Minimum | Maximum | Default |
|---|---|---|---|
| DB2BQTIME | 1 second | 5294967295 | 1 second |
| DB2BQTRY | 0 tries | 5294967295 | 60 tries |
| DB2RQTIME | 1 second | 5294967295 | 5 seconds |
| DB2IQTIME | 1 second | 5294967295 | 5 seconds |
The values of DB2BQTIME and DB2BQTRY can be increased during peak periods to optimize query time.
The DB2RQTIME variable specifies the length of time the back-end process waits for a request from the front-end process. At the end of this time, if no request is present on the request queue, the back-end process checks whether the parent of the front-end process still exists, and terminates itself if it does not exist. Otherwise, it continues to wait on the request queue.
The DB2IQTIME variable specifies the length of time the back-end process waits on the input queue for the front-end process to pass the commands. After this time has elapsed, the back-end process checks whether the front-end process is active, and returns to wait on the request queue if the front-end process no longer exists. Otherwise, the back-end process continues to wait for input from the front-end process.
To view the values of these environment variables, use LIST COMMAND OPTIONS.
The back-end environment variables inherit the values set by the front-end process at the time the back-end process is initiated. However, if the front-end environment variables are changed, the back-end process will not inherit these changes. The back-end process must first be terminated, and then restarted (by issuing the db2 command) to inherit the changed values.
An example of when the back-end process must be terminated is provided by the following scenario:
The back-end process started by user A is still active when user B starts using the CLP, because the parent of user B's front-end process (the operating system window from which the commands are issued) is still active. The back-end process attempts to service the new commands issued by user B; however, user B's front-end process does not have enough authority to use the message queues of the back-end process, because it needs the authority of user A, who created that back-end process. A CLP session must end with a TERMINATE command before a user starts a new CLP session using the same operating system window. This creates a fresh back-end process for each new user, preventing authority problems, and setting the correct values of environment variables (such as DB2INSTANCE) in the new user's back-end process.
Usage Notes
Commands can be entered either in uppercase or in lowercase from the command prompt. However, parameters that are case sensitive to DB2 must be entered in the exact case desired. For example, the comment-string in the WITH clause of the CHANGE DATABASE COMMENT command is a case sensitive parameter.
Delimited identifiers are allowed in SQL statements. For more detailed information on the use of delimited identifiers in SQL statements, see the SQL Reference.
Special characters, or metacharacters (such as $ & * ( ) ; < > ? \ ' ") are allowed within CLP commands. If they are used outside the CLP interactive mode, or the CLP batch input mode, these characters are interpreted by the operating system shell. Quotation marks or an escape character are required if the shell is not to take any special action.
For example, when executed inside an AIX Korn shell environment,
db2 select * from org where division > 'Eastern'is interpreted as "select <the names of all files> from org where division". The result, an SQL syntax error, is redirected to the file Eastern. The following syntax produces the correct output:
db2 "select * from org where division > 'Eastern'"
Special characters vary from platform to platform. In the AIX Korn shell, the above example could be rewritten using an escape character (\), such as \*, \>, or \'. In the OS/2 shell, \* or \' results in a syntax error.
Most operating system environments allow input and output to be redirected. For example, if a connection to the SAMPLE database has been made, the following request queries the STAFF table, and sends the output to a file named staflist.txt in the mydata directory:
db2 "select * from staff" > mydata/staflist.txt
For environments such as Windows 3.1, where output redirection is not supported, CLP options can be used. For example, the request can be rewritten as
db2 -r mydata\staflist.txt "select * from staff" db2 -z mydata\staflist.txt "select * from staff"For more information on CLP options for Windows, see the Installing and Using DB2 Clients for Windows book.
The command line processor is not a programming language. For example, it does not support host variables, and the statement,
db2 connect to :HostVar in share modeis syntactically incorrect, because :HostVar is not a valid database name.
The command line processor represents SQL NULL values as hyphens (-). If the column is numeric, the hyphen is placed at the right of the column. If the column is not numeric, the hyphen is at the left. For information about using the command line processor with DB2 Connect and host databases, see the DB2 Connect User's Guide.