db2 connect to database
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.
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.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.
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.
If DB2_CLPPROMPT is defined as (%ia@%i, %da@%d), the input prompt will have the following values:
(@DB2, @)
(@DB2, @)
(@DB2, @SAMPLE)
(KEON14@DB2, @SAMPLE)
(@DB2, HORTON7@SAMPLE)
(KEON14@DB2, HORTON7@SAMPLE)
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.
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:
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.
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.
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 \'.
db2 "select * from staff" > mydata/staflist.txt
db2 -r mydata\staflist.txt "select * from staff"
db2 -z mydata\staflist.txt "select * from staff"
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.
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.