IBM Books

Command Reference

Command Line Processor Options

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:

  1. Sets up default options.

  2. Reads DB2OPTIONS to override the defaults.

  3. Reads the command line to override DB2OPTIONS.

  4. Accepts input from UPDATE COMMAND OPTIONS as a final interactive override.

Table 1 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.

Table 1. CLP Command Options
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:

Show SQLCA Data Option (-a):
Displays SQLCA data to standard output after executing a DB2 command or an SQL statement. The SQLCA data is displayed instead of an error or success message.

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.

Auto-commit Option (-c):
This option specifies whether each command or statement is to be treated independently. If set ON (-c), each command or statement is automatically committed or rolled back. If the command or statement is successful, it and all successful commands and statements that were issued before it with autocommit OFF (+c or -c-) are committed. If, however, the command or statement fails, it and all successful commands and statements that were issued before it with autocommit OFF are rolled back. If set OFF (+c or -c-), COMMIT or ROLLBACK must be issued explicitly, or one of these actions will occur when the next command with autocommit ON (-c) is issued.

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:

  1. db2 create database test

  2. db2 connect to test

  3. db2 +c "create table a (c1 int)"

  4. db2 select c2 from a

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 tables

then returns an empty list.

Display SQLCODE/SQLSTATE Option (-e):
The -e{c|s} option tells the command line processor to display the SQLCODE (-ec) or the SQLSTATE (-es) to standard output. Options -ec and -es are not valid in CLP interactive mode.

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.

Read from Input File Option (-f):
The -ffilename option tells the command line processor to read input from a specified file, instead of from standard input. Filename is an absolute or relative file name which may include the directory path to the file. If the directory path is not specified, the current directory is used.

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.

Log Commands in History File Option (-l):
The -lfilename option tells the command line processor to log commands to a specified file. This history file contains records of the commands executed and their completion status. Filename is an absolute or relative file name which may include the directory path to the file. If the directory path is not specified, the current directory is used. If the specified file or default file already exists, the new log entry is appended to that file.

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.

Display Output Option (-o):
The -o option tells the command line processor to send output data and messages to standard output.

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.

Display DB2 Interactive Prompt Option (-p):
The -p option tells the command line processor to display the command line processor prompt when the user is in interactive mode.

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.

Save to Report File Option (-r):
The -rfilename option causes any output data generated by a command to be written to a specified file, and is useful for capturing a report that would otherwise scroll off the screen. Messages or error codes are not written to the file. Filename is an absolute or relative file name which may include the directory path to the file. If the directory path is not specified, the current directory is used. New report entries are appended to the file.

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.

Stop Execution on Command Error Option (-s):
When commands are issued in interactive mode, or from an input file, and syntax or command errors occur, the -s option causes the command line processor to stop execution and to write error messages to standard output.

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 2. 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

Statement Termination Character Option (-t):
The -t option tells the command line processor to use a semicolon (;) as the statement termination character, and disables the backslash (\) line continuation character.
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.

Verbose Output Option (-v):
The -v option causes the command line processor to echo (to standard output) the command text entered by the user prior to displaying the output, and any messages from that command. ECHO is exempt from this 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.

Show Warning Messages Option (-w):
The -w option tells the command line processor to show SQL statement warning messages.

The default setting for this command option is ON.

Save all Output to File Option (-z):
The -zfilename option causes all output generated by a command to be written to a specified file, and is useful for capturing a report that would otherwise scroll off the screen. It is similar to the -r option; in this case, however, messages, error codes, and other informational output are also written to the file. Filename is an absolute or relative file name which may include the directory path to the file. If the directory path is not specified, the current directory is used. New report entries are appended to the file.

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.

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]