DB2 Version 9.7 for Linux, UNIX, and Windows

Command line processor features

The command line processor operates as follows:
  • The CLP command (in either case) is typed at the command prompt.
  • The command is sent to the command shell by pressing the ENTER key.
  • Output is automatically directed to the standard output device.
  • Piping and redirection are supported.
  • The user is notified of successful and unsuccessful completion.
  • Following execution of the command, control returns to the operating system command prompt, and the user can enter more commands.
  • When the CLP is called with a file input option, it will automatically set the CLIENT APPLNAME special register to CLP filename.
You can start the command line processor by either:
  • typing the db2 command, or,
  • on Linux operating systems, click Main Menu and, select IBM DB2 > Command Line Processor.
Certain CLP commands and SQL statements require that the server instance is running and a database connection exists. Connect to a database by doing one of the following:
  • Issue the SQL statement:
    db2 connect to database
  • Establish an implicit connection to the default database defined by the DB2® registry variable DB2DBDFT.

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 different 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 NULL
sets 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.

Customizing the Command Line Processor

It is possible to customize the interactive input prompt by using the DB2_CLPPROMPT registry variable. This registry variable can be set to any text string of maximum length 100 and can contain the tokens %i, %ia, %d, %da and %n. Specific values will be substituted for these tokens at run-time.

Table 1. DB2_CLPPROMPT tokens and run-time values
DB2_CLPPROMPT token Value at run-time
%ia Authorization ID of the current instance attachment
%i Local alias of the currently attached instance. If no instance attachment exists, the value of the DB2INSTANCE registry variable. On Windows platforms only, if the DB2INSTANCE registry variable is not set, the value of the DB2INSTDEF registry variable.
%da Authorization ID of the current database connection
%d Local alias of the currently connected database. If no database connection exists, the value of the DB2DBDFT registry variable.
%n New line

You can specify the number of commands to be stored in the command history by using the DB2_CLPHISTSIZE registry variable. The HISTORY command lets you access the contents of the command history that you run within a CLP interactive mode session.

You can also specify the editor that is opened when you issue the EDIT command by using the DB2_CLP_EDITOR registry variable. From a CLP interactive session, the EDIT command opens an editor preloaded with a user-specified command which can then be edited and run.

Examples

If DB2_CLPPROMPT is defined as (%ia@%i, %da@%d), the input prompt will have the following values:

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.

Command Line Processor design

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:
  • db2 'connect to sample',
  • db2 'select * from org',
  • . foo (where foo is a shell script containing DB2 commands), and
  • db2 -tf myfile.clp
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:
  • foo
  • . foo &
  • foo &
  • sh foo

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 2. 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
DB2BQTIME
When the command line processor is invoked, the front-end process checks if the back-end process is already active. If it is active, the front-end process reestablishes a connection to it. If it is not active, the front-end process activates it. The front-end process then idles for the duration specified by the DB2BQTIME variable, and checks again. The front-end process continues to check for the number of times specified by the DB2BQTRY variable, after which, if the back-end process is still not active, it times out and returns an error message.
DB2BQTRY
Works in conjunction with the DB2BQTIME variable, and specifies the number of times the front-end process tries to determine whether the back-end process is active.

The values of DB2BQTIME and DB2BQTRY can be increased during peak periods to optimize query time.

DB2RQTIME
Once the back-end process has been started, it waits on its request queue for a request from the front-end. It also waits on the request queue between requests initiated from the command prompt.

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.

DB2IQTIME
When the back-end process receives a request from the front-end process, it sends an acknowledgment to the front-end process indicating that it is ready to receive input via the input queue. The back-end process then waits on its input queue. It also waits on the input queue while a batch file (specified with the -f option) is executing, and while the user is in interactive mode.

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:
  1. User A logs on, issues some CLP commands, and then logs off without issuing TERMINATE.
  2. User B logs on using the same window.
  3. When user B issues certain CLP commands, they fail with message DB21016 (system error).
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.

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

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 \'.

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 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"
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 mode
is 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.

To correctly display the national characters for single byte (SBCS) languages from the DB2 command line processor window, a True Type font must be selected. For example, in a Windows environment, open the command window properties notebook and select a font such as Lucinda Console. Non-printable characters such as bell (0x7) are printed without substitution, so special consideration must be given for the ASCII null character (0x0). Even though it is acceptable to have an ASCII null character (0x0) within a VARCHAR string, the display of the string ends at the first ASCII null character that is found within the string.

For example,
db2 "insert into sample values (x'410041')"
db2 "insert into sample values (x'410741')"

db2 "select CHARACTER_LENGTH(DATA, OCTETS) LENGTH, DATA, hex(DATA) HEX_VALUE from sample"

     LENGTH           DATA                       HEX_VALUE
----------- -------------------- ----------------------------------------
      3                A                         410041
      3                AA                        410741

The command line processor does not support national language support (NLS) characters in file path names. This particularly affects commands such as IMPORT, EXPORT, and REGISTER XMLSCHEMA, where problematic file path names would most frequently be encountered.

Piping the output of a command line processor command into another command line processor command is supported. For example: db2 -x <SQL_statement> | db2 +p -tv. This support is limited only by the pipe buffer size. Pipe buffer sizes are not configurable. If the pipe buffer size is exceeded by the first command, the command line processor might hang or fail when attempting to write the output to the pipe buffer. If the second command is not a command line processor command, for example a UNIX shell command, a hang or failure will not occur due to the pipe buffer size limitation.

The command line processor supports the db2dsdriver.cfg configuration file for database connections. You can use db2dsdriver.cfg keywords that are supported by embedded SQL with the command line processor.