DB2 10.5 for Linux, UNIX, and Windows

db2cli - DB2 interactive CLI command

Starts the interactive call level interface (CLI) environment for design and prototyping in CLI.

Authorization

The db2cli command is located in the bin subdirectory of the directory where you installed IBM® data server product. On UNIX and Linux operating systems, the db2cli command is located in the home_dir/sqllib/bin directory, where home_dir is the home directory of the instance owner. On Windows operating systems, the command is located in the DB2PATH\bin directory, where DB2PATH is the installation location of the DB2® copy.

None

Required connection

None

Command syntax


>>-db2cli--+------------------------------------------------+--><
           +-validate--+----------------------+-------------+   
           |           +-| Validate options |-+             |   
           |           '- -help---------------'             |   
           +-bind--+-filename--| Bind options |-+-----------+   
           |       '- -help---------------------'           |   
           +-registerdsn--| Register DSN options |----------+   
           +-csc-- -info------------------------------------+   
           +-| Execsql options |----------------------------+   
           +-install--| Install options |-------------------+   
           +-writecfg--| Write configuration file options |-+   
           +-refreshldap--+----------+----------------------+   
           |              '- -global-'                      |   
           '- -help-----------------------------------------'   

Validate options

|--+-+- -dsn--dsn_name------------------+--+------------------------------------+-+-->
   | '- -database--database:server:port-'  '- -connect--+---------------------+-' |   
   |                                                    '-| Connect options |-'   |   
   '- -all------------------------------------------------------------------------'   

>--+------------------+-----------------------------------------|
   '- -f--output_file-'   

Connect options

|--+-------------------------+---------------------------------->
   +- -displaylic-- -odbcdsn-+   
   '- -useserverlic----------'   

>--+-+------------------------------------------+------------------------------------+--|
   | '- -user--username--+--------------------+-'                                    |   
   |                     '- -passwd--password-'                                      |   
   '-+------------------+-- --keystoredbpassword--keystoredbpasswd-- -user--username-'   
     '- -user--username-'                                                                

Bind options

|--+- -dsn--dsn_name------------------+------------------------->
   '- -database--database:server:port-'   

>--+------------------------------------------+----------------->
   '- -user--username--+--------------------+-'   
                       '- -passwd--password-'     

>--+-----------------------------------+------------------------|
   '- -options--"various_bind_options"-'   

Register DSN options

                                                                          .- -user---.     
|--+-+- -add--+-+-------+--dsn_name---------+--------------------------+--+----------+-+--|
   | |        | '- -dsn-'                   |                          |  '- -system-' |   
   | |        '- -alldsn--+---------------+-'                          |               |   
   | |                    '- -includeldap-'                            |               |   
   | +- -remove--+--------------------------+--+-+-------+--dsn_name-+-+               |   
   | |           +- -copyname--copy_name----+  | '- -dsn-'           | |               |   
   | |           '- -allcopies--+---------+-'  '- -alldsn------------' |               |   
   | |                          '- -force-'                            |               |   
   | '- -list--+-----------------------+-------------------------------'               |   
   |           +- -copyname--copy_name-+                                               |   
   |           '- -allcopies-----------'                                               |   
   '- -help----------------------------------------------------------------------------'   

Execsql options

                .- -execute-----.   
|----execsql----+---------------+------------------------------->
                '- -prepareonly-'   

>--+-| Server options |---+-- -inputsql--sqlfile---------------->
   '- -connstring--string-'                        

>--+------------------------+----------------------------------->
   '- -outfile--output_file-'   

>--+---------------------------------+-------------------------->
   '- -statementdelimiter--delimiter-'   

>--+-----------------------------------+--+--------+------------|
   '- -commentstart--comment_indicator-'  '- -help-'   

Server options

|-- -dsn--dsn_name--+----------------------------+--------------|
                    '-| Authentication options |-'   

Authentication options

|-- -user--username--+------------------+-----------------------|
                     '- -passwd--passwd-'   

Install options (available only on Windows)

|--+-+- -setup---------------------------------+-+--------------|
   | +- -cleanup--+--------------------------+-+ |   
   | |            '- -appdatapath--path_name-' | |   
   | '- -cleanup--+---------+------------------' |   
   |              '- -force-'                    |   
   '- -help--------------------------------------'   

Write configuration file options

|--+-add--+-+- -dsn--dsn_name------------------------------------------+--+--------------------------------------------------------------------+-+-+--|
   |      | '- -database--db_name-- -host--host_name-- -port--p_number-'  |                      .-;----------------------------------.        | | |   
   |      |                                                               |                      V                                    |        | | |   
   |      |                                                               +-+- -parameter--+--"----parameter_name--=--parameter_value-+--"-----+ | |   
   |      |                                                               | '- -parameters-'                                                   | | |   
   |      |                                                               |                              .-;------------------------------.    | | |   
   |      |                                                               |                              V                                |    | | |   
   |      |                                                               +- -sessionglobalvariables--"----globvar_name--=--globvar_value-+--"-+ | |   
   |      |                                                               |                        .-;--------------------------------.        | | |   
   |      |                                                               |                        V                                  |        | | |   
   |      |                                                               '- -specialregisters--"----register_name--=--register_value-+--"-----' | |   
   |      |                      .-;----------------------------------.                                                                          | |   
   |      |                      V                                    |                                                                          | |   
   |      +-+- -parameter--+--"----parameter_name--=--parameter_value-+--"-----------------------------------------------------------------------+ |   
   |      | '- -parameters-'                                                                                                                     | |   
   |      |                              .-;------------------------------.                                                                      | |   
   |      |                              V                                |                                                                      | |   
   |      +- -sessionglobalvariables--"----globvar_name--=--globvar_value-+--"-------------------------------------------------------------------+ |   
   |      |                        .-;--------------------------------.                                                                          | |   
   |      |                        V                                  |                                                                          | |   
   |      '- -specialregisters--"----register_name--=--register_value-+--"-----------------------------------------------------------------------' |   
   +-remove--+-+- -dsn--dsn_name------------------------------------------+--+------------------------------------------------------+-+------------+   
   |         | '- -database--db_name-- -host--host_name-- -port--p_number-'  |                      .-;--------------.              | |            |   
   |         |                                                               |                      V                |              | |            |   
   |         |                                                               +-+- -parameter--+--"----parameter_name-+--"-----------+ |            |   
   |         |                                                               | '- -parameters-'                                     | |            |   
   |         |                                                               |                                .-;------------.      | |            |   
   |         |                                                               |                                V              |      | |            |   
   |         |                                                               +- -sessionglobalvariables--+-"----globvar_name-+--"-+-+ |            |   
   |         |                                                               |                           '- -force----------------' | |            |   
   |         |                                                               |                          .-;-------------.           | |            |   
   |         |                                                               |                          V               |           | |            |   
   |         |                                                               '- -specialregisters--+-"----register_name-+--"-+------' |            |   
   |         |                                                                                     '- -force-----------------'        |            |   
   |         |                      .-;--------------.                                                                                |            |   
   |         |                      V                |                                                                                |            |   
   |         +-+- -parameter--+--"----parameter_name-+--"-----------------------------------------------------------------------------+            |   
   |         | '- -parameters-'                                                                                                       |            |   
   |         |                                .-;------------.                                                                        |            |   
   |         |                                V              |                                                                        |            |   
   |         +- -sessionglobalvariables--+-"----globvar_name-+--"-+-------------------------------------------------------------------+            |   
   |         |                           '- -force----------------'                                                                   |            |   
   |         |                          .-;-------------.                                                                             |            |   
   |         |                          V               |                                                                             |            |   
   |         '- -specialregisters--+-"----register_name-+--"-+------------------------------------------------------------------------'            |   
   |                               '- -force-----------------'                                                                                     |   
   '- -help----------------------------------------------------------------------------------------------------------------------------------------'

Command parameters

validate

Validates and tests the CLI environment configuration. This option shows a list of keywords that are found in the db2cli.ini and db2dsdriver.cfg files. If any keywords are invalid for the specified data source or database name, they are listed as UNKNOWN. IBM data server clients (the IBM Data Server Client or the IBM Data Server Runtime Client), the db2cli validate command lists the installed DB2 client packages on a Windows operating system. When you issue the db2cli validate command from the IBM data server clients, the list is limited to the IBM data server clients that are installed on the Windows operating system. To list IBM Data Server Driver for ODBC and CLI packages and the IBM data server client packages that are installed on a Windows operating system, you must issue the db2cli validate command from the IBM Data Server Driver for ODBC and CLI installation. The list indicates the current copy name as [C] and the default copy name as [D].

-dsn dsn_name: Specifies the data source name (DSN) to validate. If dsn_name has white space in the middle, surround it with double quotation marks.
-database database:server:port: Specifies the database name, server name, and port number to validate. You must use a colon to separate the database name, server name, and port number. The server name must consist of a fully qualified domain name (FQDN), for example, TESTDB:dbserver.example.com:19677.
-all: Validates every database and DSN entries present in the db2dsdriver.cfg file.
-connect: The db2cli validate command connects and writes information about the connection attempt to the command output.
Restriction: If a connection is made to a gateway that is at a lower version than the client version, new features that are supported with a corresponding client-server combination might not be available.
-f OUTPUT_FILE: Specifies an optional output file for the results of the db2cli command.
-help: Shows the help information that is related to the usage of the validate parameter.

Connect

Specifies the DSN or database to which the db2cli validate command can test a connection. The information about the connection attempt and license information is listed in the command output.

-displaylic: Displays the presence of a valid license and where the license is located. The license can be present on the client, server, or both. For the license that is located on DB2 for i or DB2 for z/OS® server, the command output contains information about whether the user-defined function (UDF) or the stored procedure (SP) is used for the licensing and the license version.

-odbcdsn: Enables the user to validate database connectivity with the ODBC driver manager.

-useserverlic: Tests the license that is located on DB2 for i or DB2 for z/OS server by calling the user-defined function (UDF) or the stored procedure (SP) that is used for the licensing. The -useserverlic option bypasses the client-side license check. Displays the license version and whether the user-defined function (UDF) or the stored procedure (SP) is used for the licensing.
Remember: You can use the -useserverlic option only with a connection to DB2 for i or DB2 for z/OS server.

-user username: Specifies the user name to use for the connection.
-passwd password: Specifies the password to use for the connection.
-keystoredbpassword keystoredbpasswd: Specifies the password for the SSL connection when the Authentication parameter is set to CERTIFICATE.

bind

Binds the specified filename against the target database. The db2cli bind command first connects to the target database with the information provided in the db2cli bind command parameters then performs the bind action. Bind files have the extension .bnd.

filename

The filename can be a name of bind file or list file that contains the name of several bind files. If a list file is specified, the @ character must be prefixed to the list file name. The fully qualified bind file name can be specified within a double quotation mark.

-dsn dsn_name

Specifies the data source name (DSN) you bind to. When using this bind option, all information except for a valid user ID and password required to connect to the target database must be present in:

The data source name section in the db2cli.ini file.
The database section or the DSN section in the db2dsdriver.cfg file.
The local catalog entry.

-database database:server:port

Specifies the database name, server name, and port number you bind to. You must use a colon to separate the database name, server name, and port number. The server name must consist of a fully qualified domain name (FQDN), for example, TESTDB:dbserver.example.com:19677.

-user username

Specifies the user name to use for the connection.

-passwd password

Specifies the password to use for the connection.

-options "various_bind_options"

Specifies the bind options and their values. The following bind options are available:

BLOCKING
COLLECTION
ENCODING
GENERIC
GRANT
GRANT_ROLE
ISOLATION
KEEPDYNAMIC
REOPT

-help

Shows the help information that is related to the usage of the bind parameter.

registerdsn

Specifies the db2cli register DSN mode. Use this command parameter to register a DSN in the Windows operating system.

-add dsn_name|-alldsn

Adds system or user ODBC data sources to the Microsoft ODBC Data Source Administrator.

dsn_name

Indicates the DSN to register. The value of dsn_name must be the DSN that is defined in the db2cli.ini file or the dsn alias defined indb2dsdriver.cfg file and cannot be a DBALIAS name. The -add dsn-name parameter adds data sources for cataloged databases that are available in the local database directory. The dsn-name can indicate a data source that is defined in the db2cli.ini or the dsn aliases defined in thedb2dsdriver.cfg file, and a database alias in the local database directory in IBM Data Server clients. If the dsn_name has white space in the middle, surround it with double quotation marks.

-dsn

Enables the user to specify a DSN name that needs to be added to the Microsoft ODBC Data Source Administrator. The db2cli registerdsn -add -dsn command is available on Windows operating systems.

-alldsn

Registers all the data sources that are defined in the db2cli.ini file, the dsn aliases defined in the db2dsdriver.cfg file, and the local database catalog. You must use this parameter with the -add parameter.

-includeldap: Registers all the data sources that are specified in the LDAP server, the db2cli.ini file, the db2dsdriver.cfg file, and the local database catalog. You must ensure that you can connect successfully to the LDAP server by configuring the LDAP section in the IBM data server driver configuration file or configuring the LDAP connection in your Windows environment.

-user

Registers data sources as user ODBC data sources. If no parameter is specified, data sources are registered as user ODBC data sources.

-system

Registers data sources as system ODBC data sources.

-remove dsn_name

Removes a system or user ODBC data source from the Microsoft ODBC Data Source Administrator. If the dsn_name has white space in the middle, surround it with double quotation marks.

-alldsn: Removes all the user or system ODBC data source entries of a specified DB2 copy from the Microsoft ODBC Data Source Administrator. The db2cli register -remove -alldsn command is available on Windows operating systems.

-copyname copy_name: Removes the user or system ODBC data source entries of a specified DB2 copy from the Microsoft ODBC Data Source Administrator. If you do not specify a copy name, the current copy name is used. The current copy name is the copy name of the client where the db2cli utility is from. If you specify the -copyname option with the -alldsn option, all data source entries in the Microsoft ODBC Data Source Administrator are removed from the specified DB2 copy. The db2cli register -remove -copyname command is available on Windows operating systems.

-allcopies: Removes all the user or system ODBC data source entries of all the DB2 copies from the Microsoft ODBC Data Source Administrator. Unless you also specify the -force option, you are prompted to confirm that you want to remove all ODBC data source entries. The db2cli register -remove -allcopies command is available on Windows operating systems.

-force: Removes all the user or system ODBC data source entries without prompting you for confirmation. You can use the -force option only with the -allcopies option. The db2cli register -remove -allcopies -force command is available on Windows operating systems.

-dsn: Specifies a DSN name to remove from the Microsoft ODBC Data Source Administrator. The db2cli register -remove -dsn command is available on Windows operating systems. The default option is -user, which removes a user DSN. You must specify the -system option to remove a system DSN.

-list

Lists all the system or user IBM Data Server ODBC data sources that are registered in the Microsoft ODBC Data Source Administrator.

-copyname copy_name: Lists the user or system ODBC data source entries of the specified DB2 copy in the Microsoft ODBC Data Source Administrator. If you do not specify the copy name, the current copy name is used. The current copy name is the copy name of the client where the db2cli utility is from. The db2cli register -list -copyname command is available on Windows operating systems.

-allcopies: Lists all the user or system ODBC data source entries of all the DB2 copies in the Microsoft ODBC Data Source Administrator. The db2cli register -list -allcopies command is available on Windows operating systems.

-help

Shows the help information that is related to the usage of the registerdsn parameter.

Note: To add, remove, or list 32-bit ODBC data source entries on 64-bit Windows machines, use db2cli32 command.

csc -info

Displays connection supervisor client (CSC) information. A CSC is a dynamically loadable library that CLI uses for the purpose of end-to-end monitoring.

-execsql

Executes or prepares the SQL statements specified in an input file. Can also save output to a file.

-execute: Specifies that the SQL statements in the SQL script file are prepared and run. This is the default if no parameter is specified. Results are displayed in the console. To save the output to a file, specify the option -output with the absolute or relative path of the file. SQL statements in the SQL script file cannot have parameter markers.
-prepareonly: Specifies that the SQL statements in the file specified by the -inputSql option are prepared but not run. Use this option to check the syntax of the SQL statements without running the statements.
-commentstart comment_indicator: Specifies the character combination that appear at the beginning of a line to indicate a comment line. The default value of comment_indicator is two dashes (--). If a comment spans multiple lines, start each line with the comment_indicator character combination. The maximum length of a comment line is 128 characters. In the input SQL file, the text after the comment characters can contain statement cursor attributes. The cursor attributes apply to the SQL statement immediately following the comment.
-connstring string: Specifies the database name, server, and port number of the target database. The information must be specified in the format as defined by the InConnectionString argument in the SQLDriverConnect API function. For example, DATABASE=SAMPLE;HOSTNAME=test.ibm.com;PORT=50000;UID=db2user;PWD=db2pwd.
-dsn dsn_name: Specifies the data source name in the db2cli.ini file or the dsn alias defined in db2dsdriver.cfg file.. If the dsn_name has white space in the middle, surround it with double quotation marks.
-help: Displays summary usage information.
-inputsql sqlfile: Specifies the input file that contains SQL statements. The value of sqlfile is the absolute or relative path of the file. The SQL statements in the file are separated by a delimiter. Only preparable statements are used. DB2 commands such as DESCRIBE TABLE and BIND are not allowed.
Only SQL statements and comments can be in the input SQL file. The file cannot contain CLI specific attributes or keywords. Batch SQL statements are not supported.
-outfile outputfile: Specifies the absolute or relative path of the file to store the output results. When this option is not specified, the results are displayed in the console.
-passwd password: Specifies the password to use for authenticating with the database.
-statementdelimiter delimiter: Specifies the character combination that is used in the input SQL file to separate statements. The default value of delimiter is a carriage return.
-user username: Specifies the user for authenticating with the database.

install

Registers or unregisters the IBM Data Server Driver for ODBC and CLI in the Windows registry.

-setup: Registers the IBM Data Server Driver for ODBC and CLI under ODBC in the Windows registry. This parameter also creates configuration folders (cfg, cfgcache, db2dump) and sample configuration file in the default application data path.

-appdatapath <path name>: Creates configuration folders (cfg, cfgcache, db2dump) and sample configuration files in the path name.

-cleanup

Unregisters the current IBM Data Server Driver for ODBC and CLI from the Windows registry under ODBC. The cleanup then removes the folders, configuration sample files, and license management files that are created at the application data path for the current installation. Any user-created files or folders are not removed unless you specify the –force option.

-force: When specified with the –cleanup option, the entire install-specific folder is removed from the application data path. This folder belongs to this installation only. Any user-created files or folders in this folder are removed without any prompt or warning.

-help: Shows the help information that is related to the usage of the install parameter.

writecfg add|remove

Updates the db2dsdriver.cfg configuration file.

add -dsn| -database | -parameter[s]

Adds information about the DSN, database, or parameters to the db2dsdriver.cfg configuration file.

-dsn dsn_name

Specifies the DSN for which you want to add or update the parameter elements, the session global variable parameter element, or the entire <dsn> subsection in the configuration file. If the dsn_name has white space in the middle, surround it with double quotation marks.

If there is no existing <dsn> subsection with the dsn_nameas dsn alias in the configuration file, a new <dsn> subsection in the <dsncollection> section is added.

If there is a dsn subsection with the dsn_nameas dsn alias in the configuration file, new parameters or session global variables information provided is appended to the existing <dsn> subsection.

-parameter[s] "par1=par1_val[;...;parN=parN_val]"

Specifies the parameter information to add or update parameter elements in the specified <dsn> subsection of the db2dsdriver.cfg configuration file.

If you specify a new parameter element that is not found in the <dsn> subsection of the db2dsdriver.cfg configuration file, a new parameter element is added to the <dsn> subsection.

If the specified parameter element is already present in the configuration file, the existing parameter value in the <dsn> subsection is updated with the value that you specify with the -parameter[s] option.

Special characters, such as path separators in the value, must be preceded by the escape character "\".

The db2cli writecfg command does not verify the syntax or validate the value of the specified parameter.

-sessionglobalvariables "globvar_name=globvar_value[;...;globvar_nameN=globvarN_value]"

Specifies the session global variable information to add or update a parameter element for the specified DSN in the db2dsdriver.cfg configuration file.

If you specify a new session global variable parameter that is not found in the <dsn> subsection of the db2dsdriver.cfg configuration file, a new session global variable parameter element is added to the <dsn> subsection.

If the specified session global variable parameter element is already present in the configuration file, the session global variable value in the <dsn> subsection is updated with the value that you specify with the -sessionglobalvariables option.

Special characters, such as path separators in the value, must be preceded by the escape character "\".

The db2cli writecfg command does not verify the syntax or validate the value of the specified session global variable parameter.

-specialregisters "register_name=register_value[;...;register_nameN=registerN_value]"

Specifies special register information that is used to add or update a parameter element for the specified DSN in the db2dsdriver.cfg configuration file.

If you specify a special register parameter that is not in the <dsn> subsection of the db2dsdriver.cfg configuration file, a new special register parameter element is added to that subsection. If the specified special register parameter element is already in the configuration file, the special register value in the <dsn> subsection is updated with the value that you specify for the -specialregisters parameter.

You must precede any special characters in the special register value (registerN_value) with the escape character "\".

The db2cli writecfg command does not verify the syntax or validate the value of the specified special register parameter.

-database db_name -host host_name -port port_number

Specifies the connection information for a <database> subsection. This information consists of the database name, the host name where the database is located, and the port number of the database server.

If a <database> subsection with the same db_name information exists in the db2dsdriver.cfg configuration file, the specified parameter element is appended to the <database> subsection. Otherwise, the specified parameter elements or the session global variable parameter elements are added to a new <database> subsection.

-parameter[s] "parameter_name1=par1_val[;...;parN=parN_val]"

Specifies the parameter information to add or update a parameter element for the specified database name in the db2dsdriver.cfg configuration file.

If you specify a new parameter element that is not found in the <database> subsection of the db2dsdriver.cfg configuration file, a new parameter element is added to the <database> subsection.

If the specified parameter element is already present in the configuration file, the existing parameter value in the <database> subsection is updated with the value that you specify with the -parameter[s] option.

Special characters, such as path separators in the value, must be preceded by the escape character "\".

The db2cli writecfg command does not verify the syntax or validate the value of the specified parameter.

-sessionglobalvariables "globvar_name=globvar_value[;...;globvar_nameN=globvarN_value]"

Specifies the session global variable information to add or update a parameter element for the specified database name in the db2dsdriver.cfg configuration file.

If you specify a new session global variable parameter that is not found in the <database> subsection of the db2dsdriver.cfg configuration file, a new session global variable parameter element is added to the <database> subsection.

If the specified session global variable parameter element is already present in the configuration file, the session global variable value in the <database> subsection is updated with the value that you specify with the -sessionglobalvariables option.

Special characters, such as path separators in the value, must be preceded by the escape character "\".

The db2cli writecfg command does not verify the syntax or validate the value of the specified session global variable parameter.

-specialregisters "register_name=register_value[;...;register_nameN=registerN_value]"

Specifies special register information that is used to add or update a parameter element for the specified database name in the db2dsdriver.cfg configuration file.

If you specify a special register parameter that is not in the <database> subsection of the db2dsdriver.cfg configuration file, a special register parameter element is added to that subsection. If the specified special register parameter element is already in the configuration file, the special register value in the <database> subsection is updated with the value that you specify for the -specialregisters parameter.

You must precede any special characters in the special register value (registerN_value) with the escape character "\".

The db2cli writecfg command does not verify the syntax or validate the value of the specified special register parameter.

-parameter[s] "parameter_name1=par1_val[;...;parN=parN_val]"

Specifies the parameter information to add or update a parameter element for all databases and DSNs in the db2dsdriver.cfg configuration file.

To add a parameter to the global <parameters> section, specify the parameter information without indicating a database or a data source.

If you specify a new parameter element that is not found in the <parameters> subsection of the db2dsdriver.cfg configuration file, a new parameter element is added to the <parameters> subsection.

If the specified parameter element is already present in the configuration file, the existing parameter value in the <parameters> subsection is updated with the value that you specify with the -parameter[s] option.

Special characters, such as path separators in the value, must be preceded by the escape character "\".

The db2cli writecfg command does not verify the syntax or validate the value of the specified parameter.

-sessionglobalvariables "globvar_name=globvar_value[;...;globvar_nameN=globvarN_value]"

Specifies the parameter information to add or update a parameter element for all databases or DSNs in the db2dsdriver.cfg configuration file.

If you specify a new session global variable parameter that is not found in the <parameters> subsection of the db2dsdriver.cfg configuration file, a new session global variable parameter element is added to the <parameters> subsection.

If the specified session global variable parameter element is already present in the configuration file, the session global variable value in the <parameters> subsection is updated with the value that you specify with the -sessionglobalvariables option.

Special characters, such as path separators in the value, must be preceded by the escape character "\".

The db2cli writecfg command does not verify the syntax or validate the value of the specified parameter.

-specialregisters "register_name=register_value[;...;register_nameN=registerN_value]"

Specifies special register information that is used to add or update a parameter element for all databases in the db2dsdriver.cfg configuration file.

If you specify a special register parameter that is not in the <parameters> subsection of the db2dsdriver.cfg configuration file, a special register parameter element is added to the <parameters> subsection. If the specified special register parameter element is already in the configuration file, the special register value in the <parameters> subsection is updated with the value that you specify for the -specialregisters parameter.

You must precede any special characters in the special register value (registerN_value) with the escape character "\".

The db2cli writecfg command does not verify the syntax or validate the value of the specified special register parameter.

remove -dsn| -database | -parameter[s]

Removes information about the DSN, database, or parameters from the db2dsdriver.cfg configuration file.

-dsn dsn_name

Specifies the DSN for which you want to remove the parameter elements, the session global variable parameter element, or the entire <dsn> subsection in the configuration file. If the dsn_name has white space in the middle, surround it with double quotation marks.

To remove parameter elements or session global variable parameter elements information, specify the corresponding DSN and the parameter or session global variable information.

To remove the entire data source subsection, specify only the DSN without any parameters or session global variables information.

-parameter[s] "parameter_name1[;...;parameter_nameN]": Specifies the parameter information to remove from the specified DSN in the db2dsdriver.cfg configuration file.
If the indicated parameter is not in the specified <dsn> subsection of the configuration file, no action is taken.

-sessionglobalvariables "globvar_name[;...;globvar_nameN]": Specifies the session global variable information to remove from the specified DSN in the db2dsdriver.cfg configuration file.
If the indicated session global variable is not in the specified <dsn> subsection of the configuration file, no action is taken.

-sessionglobalvariables -force: Removes all the session global variable information from the specified <dsn> subsection in the db2dsdriver.cfg configuration file. No action is taken if the session global variables are not in the <dsn> subsection.

-specialregisters "register_name=register_value[;...;register_nameN=registerN_value]": Specifies the special register information to remove from the specified <dsn> subsection in the db2dsdriver.cfg configuration file. If the indicated special register is not in the specified <dsn> subsection of the configuration file, no action is taken.

-specialregisters -force: Removes all the special register information from the specified <dsn> subsectionin the db2dsdriver.cfg configuration file. If the special registers are not in the <dsn> subsection, no action is taken.

-database db_name -host host_name -port port_number

Specifies the database name for which you want to remove parameter elements, session global variable parameter elements, or the entire <database> subsection in the configuration file.

To remove parameter elements or session global variable parameter elements information, specify the corresponding database name and the parameter or session global variable information.

To remove the entire database subsection, specify only the database name without any parameter or session global variable information.

-parameter[s] "parameter_name1[;...;parameter_nameN]": Specifies the parameter information to remove from the specified database name in the db2dsdriver.cfg configuration file.
If the indicated parameter is not in the <database> subsection of the configuration file, no action is taken.

-sessionglobalvariables "globvar_name[;...;globvar_nameN]": Specifies the session global variable information to remove from the specified database name in the db2dsdriver.cfg configuration file.
If the indicated session global variable is not specified in the <database> subsection of the configuration file, no action is taken.

-sessionglobalvariables -force: Removes all the session global variable information from the specified database name in the db2dsdriver.cfg configuration file. No action is taken if the session global variables are not in the <database> subsection.

-specialregisters "register_name=register_value[;...;register_nameN=registerN_value]": Specifies the special register information to remove from the specified database name in the db2dsdriver.cfg configuration file. If the indicated special register is not in the specified <database> subsection of the configuration file, no action is taken.

-specialregisters -force: Removes all the special register information from the specified database name in the db2dsdriver.cfg configuration file. No action is taken if the special registers are not in the <database> subsection.

-parameter[s] "parameter_name1[;...;parameter_nameN]"

Specifies the parameter information that is to be removed from the <parameters> section in the db2dsdriver.cfg configuration file.

If the indicated parameter is not in the <parameters> section of the configuration file, no action is taken.

-sessionglobalvariables "globvar_name[;...;globvar_nameN]"

Specifies the session global variable information that is to be removed from the <parameters> section in the db2dsdriver.cfg configuration file.

If the indicated session global variable is not in the <parameters> section of the configuration file, no action is taken.

-sessionglobalvariables -force: Removes all the session global variable information from the <parameters> section in the db2dsdriver.cfg configuration file. No action is taken if the session global variables are not in the <parameters> subsection.

-specialregisters "register_name=register_value[;...;register_nameN=registerN_value]": Specifies the special register information to remove from the <parameters> section in the db2dsdriver.cfg configuration file. If the indicated special register is not in the <parameters> subsection of the configuration file, no action is taken.

-specialregisters -force: Removes all the special register information from the <parameters> section in the db2dsdriver.cfg configuration file. No action is taken if the special registers are not in the <parameters> subsection.

-help

Shows the help information that is related to the usage of the writecfg parameter.

refreshldap

Updates and appends all configuration information in the IBM data server driver configuration file (db2dsdriver.cfg) with the configuration information that are specified on the Lightweight Directory Access Protocol (LDAP) server. The db2cli refreshldap command retrieves the configuration information that is specified for the current user ID, which is used to connect to the LDAP server. All DSN entries in the IBM data server driver configuration file that were created using the db2cli refreshldap command contains the LDAP=1 attribute.

The authentication type that is defined for a database in the LDAP server is appended or updated to the <dsn> section for that DSN alias in the IBM data server driver configuration file.

The Protocol parameter that is defined for a database in the LDAP server is appended or updated to the <dsn> section for that DSN alias only if the value is not TCPIP.

The following table lists the DCS parameters and equivalent keywords that are supported in the IBM data server driver configuration file. Only the listed DCS parameters can be appended or updated with the refreshldap option.

Table 1. DCS parameters and equivalent IBM data server driver configuration keywords
DCS parameter	Equivalent keyword
`map-file`	SQLCODEMAP
INTERRUPT_ENABLED	InterruptProcessingMode
SYSPLEX	ConnectionLevelLoadBalancing
BIDI	BiDiCCSID

All supported DCS parameters except the SYSPLEX parameter are appended or updated to the <dsn> section. The SYSPLEX parameter that is present in the LDAP server is appended or updated to the corresponding <database> section. When you are using the refreshldap option to update the IBM data server driver configuration file, you can avoid unexpected behaviors by not configuring the db2cli.ini file. The db2cli.ini file configuration takes precedences over the IBM data server driver configuration file.

The db2cli refreshldap command creates the IBM data server driver configuration file if it is not present.

The db2cli refreshldap command updates the IBM data server driver configuration file with keywords that are equivalent to the CLI keywords, which are specified in the LDAP server. Any CLI keywords that are specified on the LDAP server that does not have equivalent IBM data server driver configuration keyword results in a warning message. Any gateway settings on the LDAP server, which are specified through GWNODE option are not appended or updated, and results in a warning message. The alternate server settings on the LDAP server are not appended or updated, and results in a warning message.

If there is a DSN entry on the LDAP server that matches an existing DSN entry in the IBM data server driver configuration file that is not created from the LDAP server entry (without the LDAP=1 attribute), a warning is returned and the DSN entry is not updated.

If multiple DSN entries exist on the IBM data server driver configuration file for a same database but with different alias, only the DSN entries that were created from the LDAP server entry (with the LDAP=1 attribute) are removed when the DSN entry on the LDAP server is deleted.

The db2cli refreshldap command can be issued only with the IBM Data Server Driver Package and IBM Data Server Driver for ODBC and CLI products.

The db2cli refreshldap command is similar to the REFRESH LDAP IMMEDIATE ALL command that can be issued from the IBM Data Server Client, IBM Data Server Runtime Client or IBM database server products.

-global: Updates and appends all configuration information in the IBM data server driver configuration file with the global configuration information, which is specified for all the user IDs, on the LDAP server.

-help

Shows the db2cli command help information.

Usage notes

The interactive CLI interface consists of a set of commands that you can use to design, prototype, and test CLI function calls. It is a testing tool that is provided for the convenience of those programmers who want to use it, and IBM makes no guarantees about its performance. This interface is not intended for users, and so does not have extensive error-checking capabilities.

Three types of commands are supported:

CLI commands: Commands that correspond to (and have the same name as) each of the function calls that is supported by CLI.
Support commands: Commands that do not have an equivalent CLI function.
Additional modes: You can use the additional modes for the db2cli command to validate and test the CLI environment configuration.

You can issue commands interactively or from within a file. Similarly, you can display the command output on the terminal or write it to a file. A useful feature of the IBM Data Server Driver for ODBC and CLI is the ability to capture all of the commands that are entered during a session, and to write them to a file, thus creating a command script that you can rerun at a later time.

For IBM Data Server client packages on Windows 64-bit operating systems, the 32-bit version of db2cli (db2cli32.exe) is supported in addition to the 64-bit version of the db2cli command.

SQL statements are executed by using the SQLExecDirect() function. When executing SQL statements, the db2cli execsql command uses the database settings specified in the db2cli.ini and db2dsdriver.cfg files.

Error messages returned by the db2cli command are formatted using the same format as the SQLGetDiagRec() function.

Command line ODBC Registration: To configure and register DSN for Windows:

Catalog the server node. For more information, see "Cataloging a TCP/IP node from a client using the CLP".
Catalog the database that you want to connect to. For more information, see "Cataloging a database from a client by using the CLP".
(Optional) Catalog the Database Connection Services (DCS) directory. For more information, see "Catalog DCS database command".
Register the DSN by using the db2cli interactive tool.
- For 32-bit ODBC DSN, use the db2cli32 command.
- For 64-bit ODBC DSN, use the db2cli command.

For 64-bit installations, you can use both the db2cli and db2cli32 versions to register the DSN. The db2cli command registers a 64-bit DSN and the db2cli32 command registers a 32-bit DSN.

For 32-bit installations, the db2cli command by default registers only 32-bit DSN.

The DSN that you want to register must be in the db2cli.ini or the db2dsdriver.cfg file.

Examples

db2cli validate

In the following example, the utility reads the [COMMON] section of db2cli.ini file, the [DSN] section for the sample DSN name in the db2cli.ini file, and the <dsn> element for the sample DSN name in the db2dsdriver.cfg file. Both valid and invalid keywords are displayed; invalid keywords are listed as UNKNOWN.

db2cli validate –dsn sample

In the next example, the utility reads the [COMMON] section of db2cli.ini file, the <database> section for the database dbname1, the server server1.net1.com, and port 50001, in the db2dsdriver.cfg file. Both valid and invalid keywords are displayed; invalid keywords are listed as UNKNOWN.

db2cli validate -database dbname1:server1.net1.com:50001

In the next example, the utility is used with a data source name. The data source name is applied with double quotation marks ("").

db2cli validate -dsn "IBM - User Acceptance" -connect -user <userid> -passwd <password>

db2cli writecfg

For the examples that are described in this section, assume that the db2dsdriver.cfg file has the following content:

<configuration>
  <dsncollection>
   <dsn alias="alias1" name="name1" host="server1.net1.com" port="50001"> 
    <parameter name="DisableAutoCommit" value="TRUE"/>
   </dsn>
  </dsncollection>
  <databases>
   <database name="name1" host="server1.net1.com" port="50001">
    <parameter name="CurrentSchema" value="OWNER1"/>
   </database> 
  </databases>
  <parameters>
     <parameter name="IsolationLevel" value="SQL_TXN_READ_COMMITTED"/>
  </parameters>
</configuration>

The following example adds a new data source element to the db2dsdriver.cfg configuration file:

db2cli writecfg add –dsn alias2 –database name2 -host server1.net1.com -port 50001

As a result of this command, the dsncollection section is modified as follows:

<dsncollection>
 <dsn alias="alias1" name="name1" host="server1.net1.com" port="50001"> 
  <parameter name="DisableAutoCommit" value="TRUE"/>
 </dsn>
 <dsn alias="alias2" name="name2" host="server1.net1.com" port="50001"/>
</dsncollection>

The following example adds parameter information to an existing data source in the configuration file:

db2cli writecfg add –dsn alias2 –parameters “DisableAutoCommit=FALSE;CurrentSchema=OWNER2;pureQueryXml=C:\\clico”

As a result of this command, the dsncollection section is modified as follows:

<dsncollection>
 <dsn alias="alias1" name="name1" host="server1.net1.com" port="50001"> 
  <parameter name="DisableAutoCommit" value="TRUE"/>
 </dsn>
 <dsn alias="alias2" name="name2" host="server1.net1.com" port="50001">
  <parameter name="DisableAutoCommit" value="FALSE"/>
  <parameter name="CurrentSchema" value="OWNER2"/>
..<parameter name="pureQueryXml" value="C:\clico"/>
 </dsn>
</dsncollection>

The following example adds a new database element with parameters in the configuration file:

db2cli writecfg add –database name2 –host server1.net1.com -port 50001  -parameters “LockTimeout=20;KeepAliveTimeout=20000”

As a result of this command, the databases section is modified as follows:

<databases>
 <database name="name1" host="server1.net1.com" port="50001">
  <parameter name="CurrentSchema" value="OWNER1"/>
 </database> 
 <database name="name2" host="server1.net1.com" port="50001">
  parameter name=" LockTimeout" value="20"/>
  parameter name="KeepAliveTimeout" value="20000"/>
 </database> 
</databases>

The following example modifies an existing parameter for an existing dsn element in the configuration file:

db2cli writecfg add –dsn alias1 –parameter “DisableAutoCommit=FALSE”

As a result of this command, the dsncollection section is modified as follows:

<dsncollection>
 <dsn alias="alias1" name="name1" host="server1.net1.com" port="50001"> 
  <parameter name="DisableAutoCommit" value="FALSE"/>
 </dsn>
 <dsn alias="alias2" name="name2" host="server1.net1.com" port="50001">
  <parameter name="DisableAutoCommit" value="FALSE"/>
  <parameter name="CurrentSchema" value="OWNER2"/>
..<parameter name="pureQueryXml" value="C:\clico"/>
 </dsn>
</dsncollection>

The following example adds a parameter element to the global section in the configuration file:

db2cli writecfg add -parameter "ReceiveTimeout=20000"

As a result of this command, the global section is modified as follows:

<parameters>
 <parameter name="IsolationLevel" value=" SQL_TXN_READ_COMMITTED"/>
 <parameter name="ReceiveTimeout" value="20000"/> 
</parameters>

The following example removes a parameter element from a database in the configuration file:

db2cli writecfg remove –database name1 -host server1.net1.com -port 50001 –parameter “CurrentSchema”

As a result of this command, the databases section is modified as follows:

<databases>
 <database name="name1" host="server1.net1.com" port="50001">
 </database>
 <database name="name2" host="server1.net1.com" port="50001">
  parameter name="LockTimeout" value="20"/>
  parameter name="KeepAliveTimeout" value="20000"/>
 </database>
 </databases>

The following example removes a dsn element in the configuration file:

db2cli writecfg remove –dsn alias1

As a result of this command, the dsncollection section is modified as follows:

<dsncollection>
 <dsn alias="alias2" name="name2" host="server1.net1.com" port="50001">
  <parameter name="DisableAutoCommit" value="FALSE"/>
  <parameter name="CurrentSchema" value="OWNER2"/>
  <parameter name="pureQueryXml" value="C:\clico"/>
 </dsn>
</dsncollection>

db2cli execsql

In following example assume that the following table and procedures are created in the SAMPLE database:

create table employee(empid integer, empname varchar(100)

  CREATE PROCEDURE proc1 (  ) 
  DYNAMIC RESULT SETS 1 P1: 
  BEGIN
    DECLARE cursor1 CURSOR WITH RETURN FOR  SELECT * FROM fprem;  
    OPEN cursor1;
  END P1

  CREATE PROCEDURE PROC2(IN ID1 INTEGER,OUT NAME VARCHAR(20)) 
  BEGIN 
  DECLARE CUR1 CURSOR WITH RETURN TO CALLER FOR SELECT * FROM EMPLOYEE1 WHERE ID=ID1; 
    OPEN CUR1; 
  END

This example also assumes the SQL file test.sql contains the following text:

--Populate table( employee )
insert into employee(empid, empname) values(1, 'Adam')
insert into employee(empid, empname) values(2, 'Atul')
select empid, empname  from employee

--Execute the stored procedure
Call proc1( )

Enter the following db2cli command in a console window to run the SQL statements in the file:

db2cli execsql –dsn sample –inputsql test.sql

The following text is displayed in the console window:

IBM DATABASE 2 Interactive CLI Sample Program
(C) COPYRIGHT International Business Machines Corp. 1993,1996
All Rights Reserved
Licensed Materials - Property of IBM
US Government Users Restricted Rights - Use, duplication or
disclosure restricted by GSA ADP Schedule Contract with IBM Corp.

insert into employee(empid, empname) values(1, 'Adam')
The SQL command completed successfully.

insert into employee(empid, empname) values(2, 'Atul')
The SQL command completed successfully.

select empid, empname  from employee

EMPID EMPNAME
1, Adam
2, Atul

Call proc1()

EMPID EMPNAME
1, Adam
2, Atul

Run a CALL statement for a stored procedure that has OUT arguments. The question mark (?) can be used as an OUT parameter.

The following example assumes that an SQL script file test2.sql contains the following text:

CALL PROC2( 1, ?)

Enter the following db2cli command in a console window to run the SQL statements in the file:

db2cli execsql –dsn sample –inputsql test2.sql

The following text is displayed in the console window:

Value of output parameters
--------------------------
Parameter Name  : NAME
Parameter Value : -

ID
-----------
    1

Specify the -prepareonly option to prepare the SQL statements without running them. The DDL statements that are needed for the SQL statements must be run before you run the db2cli execsql command with the -prepareonly option.

The following example assumes that the SQL file test3.sql contains the following text:

--populate table( employee )
insert into employee(empid, empname) values(1, 'Adam');
insert into employee(empid, empname) values(2, 'Atul');
select empid, empname  from employee;

Also, assume that the table EMPLOYEE was created in the database. Enter the following db2cli command in a console window to prepare the SQL statements in the file:

db2cli execsql –prepareonly –dsn sample –inputsql test3.sql

The following text is displayed in the console window:

IBM DATABASE 2 Interactive CLI Sample Program
(C) COPYRIGHT International Business Machines Corp. 1993,1996
All Rights Reserved
Licensed Materials - Property of IBM
US Government Users Restricted Rights - Use, duplication or
disclosure restricted by GSA ADP Schedule Contract with IBM Corp.

insert into employee(empid, empname) values(1, 'Adam')
The SQL command prepared successfully.

insert into employee(empid, empname) values(2, 'Atul')
The SQL command prepared successfully.

select empid, empname  from employee
The SQL command prepared successfully.
If you place DDL statements that are required for DML statements in the same file, 
the DML statements that require the DDL statements fail. 
For example, assume that the following text is in the file test4.sql, 
and assume that and the EMPLOYEE table has not been created in the database:
--create and populate table( employee )
create table employee(empid integer, empname varchar(100));
insert into employee(empid, empname) values(1, 'Adam');
insert into employee(empid, empname) values(2, 'Atul');
select empid, empname  from employee;

-- try to create another table with the same name
create table employee(empid integer, empname varchar(100));

The CREATE TABLE statement must be run before the INSERT and SELECT statements can be run successfully.

Enter the following db2cli command in a console window to prepare the SQL statements in the file:

db2cli execsql –prepareonly –dsn sample –inputsql test4.sql

The following text is displayed in the console window:

IBM DATABASE 2 Interactive CLI Sample Program
(C) COPYRIGHT International Business Machines Corp. 1993,1996
All Rights Reserved
Licensed Materials - Property of IBM
US Government Users Restricted Rights - Use, duplication or
disclosure restricted by GSA ADP Schedule Contract with IBM Corp.

create table employee(empid integer, empname varchar(100))
The SQL command prepared successfully.

insert into employee(empid, empname) values(1, 'Adam')
The SQL command failed. During SQL processing it returned:
[IBM][CLI Driver][DB2/6000] SQL0204N  "EMPLOYEE" is an undefined name.  SQLSTATE=42704

insert into employee(empid, empname) values(2, 'Atul')
The SQL command failed. During SQL processing it returned:
[IBM][CLI Driver][DB2/6000] SQL0204N  "EMPLOYEE" is an undefined name.  SQLSTATE=42704

select empid, empname  from employee
The SQL command failed. During SQL processing it returned:
[IBM][CLI Driver][DB2/6000] SQL0204N  "EMPLOYEE" is an undefined name.  SQLSTATE=42704

create table employee(empid integer, empname varchar(100))
The SQL command prepared successfully.

In this example, the two CREATE SQL statements prepared successfully, however the EMPLOYEE table was not created in the database. The INSERT and SELECT statements did not prepare successfully because the EMPLOYEE table was not in the database.

db2cli bind

The following example binds the db2cli.lst list file:

$ db2cli bind @db2cli.lst –database “mydb:test.torolab.ibm.com:446” 
–options “BLOCKING unambig REOPT always ISOLATION RR”
LINE    MESSAGES FOR db2cli.lst
------  --------------------------------------------------------------
        SQL0061W  The binder is in progress.
        SQL0091N  Binding was ended with "0" errors and "0" warnings.

db2cli refreshldap

The following db2dsdriver.cfg file is modified by the db2cli refreshldap command:

<configuration>
<dsncollection>
 <dsn alias="sample" name="sample" host="hotel53.ibm.com" port="40576” ldap=1>
  <parameter name="DisbaleAutoCommit" value="0"/>
  <parameter name="AllowDeferredPrepare" value="1"/> 
 </dsn>
 <dsn alias="EC205" name="STLEC1"  host="INEC005.ibm.com" port="446" ldap=1>
  <parameter name="InterruptProcessingMode" value="1"/>
 </dsn>
 <dsn alias="test1" name="test" host="xyz.ibm.com" port="446" ldap=1>
  <parameter name="QueryTimeoutInterval" value="15"/>
  <parameter name="Authentication" value="SERVER"/>
 </dsn>
</dsncollection>
 <databases>
  <database name="sample" host="hotel53.ibm.com" port="40576"/>
  <database name="STLEC1" host="INEC006.ibm.com" port="446">
   <parameter name="ConnectionLevelLoadBalancing" value="1"/>
  </database>
  <database name="test" host="xyz.ibm.com" port="446"/>
 </databases>
</configuration>