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 by using DB2OPTIONS.
View the current settings for the option flags and the value of DB2OPTIONS by using LIST COMMAND OPTIONS. Change an option setting from the interactive input mode or a command file by using UPDATE COMMAND OPTIONS.
- Sets up default options.
- Reads DB2OPTIONS to override the defaults.
- Reads the command line to override DB2OPTIONS.
- Accepts input from UPDATE COMMAND OPTIONS as a final interactive override.
Table 1 summarizes the CLP option flags. These options can be specified
in any sequence and combination. To turn on anon, prefix the corresponding option letter with a
minus sign (-). To turn off an option, prefix the option letter with a minus sign and follow the
option letter with another minus sign. Alternatively, prefix the option letter with a plus sign (+).
For example, -c
turns on the autocommit options, 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 |
-b | This option tells the command line processor to automatically create any missing or invalid packages necessary to run SQL statements. | ON |
-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 affects only 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, and MERGE statements. | 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 white spaces and
linefeeds in strings that are 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 that is generated by a command to a file. | OFF |
-s | This option tells the command line processor to stop execution if errors occur while commands are executed 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 and SELECT warning messages. | ON |
-x | This option tells the command line processor to return data without any headers, including column names. This flag does not affect all commands. It applies to SQL statements and some commands that are based on SQL statements such as LIST TABLES. | OFF |
-y | This option tells the command line processor to retrieve SQL message text from the connected database server if the CLP does not find any message text corresponding to an SQLCODE in a local message file. | ON |
-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 |
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
- Show SQLCA Data Option (
-a
): - Displays SQLCA data to standard output after the execution of 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. - Autobind
Option (
-b
): - Creates missing or invalid packages that are necessary to execute SQL
statements. If set
OFF
(+b
), command line processor does not attempt to rebind the package when server throws package not found error or timestamp conflict error to command line processor.The default setting for this command option is
ON
.This new command line option can also be set using DB2OPTIONS environment variable.
Use case scenario:
# explicitly dropping db2clpcs.bnd from a database db2 drop package NULLID.SQLC2J24 ... # turning off autobind (“+b”) results in SQL0805N # as db2clpcs.bnd is dropped now db2 +b "create table a (c1 int)" # bind needed file explicitly now db2 bind db2clpcs.bnd # now same SQL will be successful as binding is done explicitly db2 +b "create table a (c1 int)" # again explicitly dropping db2clpcs.bnd from a database db2 drop package NULLID.SQLC2J24 ... # keeping default autobind (“-b”) results autobinding of # missing packages and SQL execution is success now db2 -b "create table a (c1 int)" -OR- db2 "create table a (c1 int)"
- Autocommit 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 autocommitOFF
(+c
or-c-
) are committed. However, if the command or statement fails, it and all successful commands and statements that were issued before it with autocommitOFF
are rolled back. If setOFF
(+c
or-c-
), COMMIT or ROLLBACK statements must be issued explicitly, or one of these actions will occur when the next command with autocommitON
(-c
) is issued.The default setting for this command option is
ON
.The autocommit option does not affect any other command line processor option.
Example: Consider the following scenario:db2 create database test
db2 connect to test
db2 +c "create table a (c1 int)"
db2 select c2 from a
The SQL statement in step 4 fails because the column that is named C2 does not exist in table A. Since that statement was issued with autocommitON
(default), it rolls back not only the statement in step 4, but also the one in step 3, because the latter was issued with autocommitOFF
. The command:
then returns an empty list.db2 list tables
- XML Declaration Option (
-d
): -
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 are retrieved and displayed. If setOFF
(+d
or-d-
), the XML declarations will not be retrieved and displayed. The default setting for this command option isOFF
.The XML declaration option does not affect any other command line processor options.
- 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=`db2 -ec +o db2-command`
- Read from Input File Option (
-f
): - The
-f
filename 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 can include the directory path to the file. If the directory path is not specified, the current directory is used.When the CLP is called with a file input option, it automatically sets the CLIENT APPLNAME special register to
CLP filename
.When other options are combined with option-f
, option-f
must be specified last. For example,:db2 -tvf filename that
When you run a CLP script file by using the db2 -tvf filename command, it sets the CLIENT APPLNAME special register to CLP filename. The next command that 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 command in filename is TERMINATE, then the special registers are not reset. This procedure 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 that begin with the comment characters
--
are treated as comments by the command line processor. Comment characters must be the first nonblank characters on a line, unless the command input ends explicitly with the statement termination character after the comment characters are added. If you explicitly end the command input with the termination character, your comments can be placed mid-line or at the end of the line.Input file lines that begin with
(=
are treated as the beginning of a comment block. Lines that 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 can 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. - Pretty Print Option (
-i
): -
The
-i
option tells the command line processor to 'pretty print' the XML data with proper indentation. This option only affects 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.
- Log Commands in History File Option (-l):
- The
-l
filename option tells the command line processor to log commands to a specified file. This history file contains records of the commands that are executed and their completion status. Filename is an absolute or relative file name can 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 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 do not affect any other command line processor option.
- Display Number of Rows Affected Option (-m):
-
The
-m
option tells the command line process whether to print the number of rows affected for INSERT, DELETE, UPDATE, or MERGE statements.If set
ON (-m)
, the number of rows that are affected is displayed for the INSERT, DELETE, UPDATE, or MERGE statements. If setOFF (+m or -m-)
, the number of rows that are affected is not be displayed. For other statements, this option is ignored. The default setting for this command option isOFF
.The
-o
and the-r
options affect the-m
option; see the option descriptions for details. - Remove New Line Character Option (-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 cannot be changed from within the
interactive mode.
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. - 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 might be affected by the+o
option:-r
filename that: Interactive mode start-up information is not saved.-e
: SQLCODE or SQLSTATE is displayed on standard output even if+o
is specified.-a
: No effect if+o
is specified. If-a
,+o
and-r
filename are specified, SQLCA information is written to a file.
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 off the prompt is useful when commands are being piped to thecommand line processor . For example, a file that contains CLP commands could be executed by issuing: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.
- Preserve white spaces and Linefeeds Option (
-q
): -
The
-q
option tells the command line processor to preserve white spaces and linefeeds in strings that are delimited with single or double quotation marks.The default setting for this command option is
OFF
(+q
or-q-
).If option
-q
isON
, option-n
is ignored. - Save to Report File Option (-r):
- The
-r
filename option causes any output data that is 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 that can 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
-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.
- 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 Set0 (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 Options (-t and -tdx or -tdxx):
- The
-t
option tells the command line processor to use a semicolon (;) as the statement termination character, and disables the backslash (\) line continuation character. This option cannot be changed from within the interactive mode.The default setting for this command option is
OFF
(+t
or-t-
).Note: If you use the CLP to issue XQuery statements, it is best to choose a termination character other than the semicolon. This method ensures that statements or queries that use namespace declarations are not misinterpreted, since namespace declarations are also terminated by a semicolon.To define termination characters 1 or 2 characters in length, use-td
followed by the chosen character or characters. For example,-td%%
sets%%
as the statement termination characters. Alternatively, use the--#SET TERMINATOR
directive in an input file to set the statement termination characters. For example, :
ordb2 -td%% -f file1.txt
wheredb2 -f file2.txt
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.
- Verbose Output Option (-v):
- The
-v
option causes the command line processor to echo (to standard output) the command text that is entered by the user before 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 instructs the command line processor on whether to display warning messages that might occur during a query (FETCH or SELECT statement). Warnings can occur during various stages of the query execution that might result in the messages that are displayed before, during or after the data is returned, to ensure the data that is returned does not contain warning message text this flag can be used.The default setting for this command option is
ON
. - Suppress Printing of Column Headings Option (-x):
- The
-x
option tells the command line processor to return data without any headers, including column names. This flag does not affect all commands. It applies to SQL statements and some commands that are based on SQL statements such as LIST TABLES.The default setting for this command option is
OFF
. - Get SQL message text from connected database Server (-y):
-
The
-y
option tells the command line processor to retrieve SQL message text from the connected database server if the CLP does not find any message text corresponding to an SQLCODE in the local message file. This flag does not affect all commands. It applies only to SQL statements that are executed on a remote database server.This option does not affect Db2 help messages that return message text from local message files. For example,
db2 -y ? SQL4742N
does not go to the connected database server to bring the message detail.To indicate that the received message text is from the connected database server, a keyword [SERVER] will appear just after SQLCODE for server messages as follows:
$ db2 -y "<some sqlstatement>" SQL4742N [SERVER] THE STATEMENT CANNOT BE EXECUTED BY DB2 OR IN THE ACCELERATOR (REASON 1). SQLSTATE=42704
The default setting for the
-y
option isON
. - Save all Output to File Option (-z):
- The
-z
filename option causes all output that is 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; however, in this case, messages, error codes, and other informational output are also written to the file. Filename is an absolute or relative file name that can 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
-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.