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.
Table 1 summarizes the CLP option flags. These options can be specified in 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 |
-d | This option tells the command line processor to retrieve and display XML declarations of XML data. | OFF |
-e{c|s} | This option tells the command line processor to display SQLCODE or SQLSTATE. These options are mutually exclusive. | OFF |
-f filename | This option tells the command line processor to read command input from a file instead of from standard input. | OFF |
-i | This option tells the command line processor to 'pretty print' the XML data with proper indentation. This option will only affect the result set of XQuery statements. | OFF |
-l filename | This option tells the command line processor to log commands in a history file. | OFF |
-m | This option tells the command line processor to print the number of rows affected for INSERT/DELETE/UPDATE/MERGE. | OFF |
-n | Removes the new line character within a single delimited token. If this option is not specified, the new line character is replaced with a space. This option must be used with the -t option. | 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 |
-q | This option tells the command line processor to preserve whitespaces and linefeeds in strings delimited with single or double quotation marks. When option q is ON, option n is ignored. | OFF |
-r filename | 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 or -tdxx | This option tells the command line processor to define and to use x or xx as the statement termination character or characters (1 or 2 characters in length). | 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 FETCH/SELECT warning messages. | ON |
-x | This option tells the command line processor to return data without any headers, including column names. This flag will not affect all commands. It applies to SQL statements and some commands that are based on SQL statements such as LIST TABLES. | OFF |
-z filename | 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
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 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.
db2 list tables
then returns an empty list. The -d option tells the command line processor whether to retrieve and display XML declarations of XML data.
If set ON (-d), the XML declarations will be retrieved and displayed. If set OFF (+d or -d-), the XML declarations will not be retrieved and displayed. The default setting for this command option is OFF.
The XML declaration option does not affect any other command line processor options.
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.
sqlcode=`db2 -ec +o db2-command`
When the CLP is called with a file input option, it will automatically set the CLIENT APPLNAME special register to CLP filename.
db2 -tvf filename
When you run a CLP script file using the db2 -tvf filename command, it sets the CLIENT APPLNAME special register to CLP filename. The next command you run, resets the CLIENT APPLNAME and CLIENT ACCTNG special registers to the old value before the db2 -tvf filename command was issued. If the next command you run is db2 terminate or the last statement in filename is terminate, then the special registers are not reset. This is useful for monitoring which batch job is currently running and differentiating the CLP workload.
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 the QUIT command or TERMINATE command 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.
Input file lines which begin with (= are treated as the beginning of a comment block. Lines which end with =) mark the end of a comment block. The block of input lines that begins at (= and ends at =) is treated as a continuous comment by the command line processor. Spaces before (= and after =) are allowed. Comments may be nested, and may be used nested in statements. The command termination character (;) cannot be used after =).
If the -f filename option is specified, the -p option is ignored.
The read from input file option does not affect any other command line processor option.
Note that the default termination character is one of the new line characters unless otherwise specified with the -t option or the end-of-file.
The -i option tells the command line processor to 'pretty print' the XML data with proper indentation. This option will only affect the result set of XQuery statements.
The default setting for this command option is OFF (+i or -i-).
The pretty print option does not affect any other command line processor options.
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 -m option tells the command line processor whether or not to print the number of rows affected for INSERT, DELETE, UPDATE, or MERGE.
If set ON (-m), the number of rows affected will be displayed for the statement of INSERT/DELETE/UPDATE/MERGE. If set OFF (+m or -m-), the number of rows affected will not be displayed. For other statements, this option will be ignored. The default setting for this command option is OFF.
The -o and the -r options affect the -m option; see the option descriptions for details.
The default setting for this command option is OFF (+n or -n-).
This option must be used with the -t option; see the option description for details.
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).
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.
db2 +p < myfile.clp
The -p option is ignored if the -f filename option is specified.
The display DB2 interactive prompt option does not affect any other command line processor option.
The -q option tells the command line processor to preserve whitespaces and linefeeds in strings delimited with single or double quotation marks.
The default setting for this command option is OFF (+q or -q-).
If option -q is ON, option -n is ignored.
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 -r filename 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).
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 |
The default setting for this command option is OFF (+t or -t-).
db2 -td%% -f file1.txt
or db2 -f file2.txt
where file2.txt contains
the following as the first statement in the file:--#SET TERMINATOR %%
The default setting for this command option is OFF.
The termination character or characters cannot be used to concatenate multiple statements from the command line, since checks for a termination symbol are performed on only the last one or two non-blank characters of each input line.
The statement termination character options do not affect any othercommand 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.
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 -z filename 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.