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 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.
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 3. 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.
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 about CLP options for Windows, see the Installation and Configuration Supplement 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 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. For information about using the command line processor with DB2 Connect and host databases, see the DB2 Connect User's Guide.