db2cli - DB2 interactive CLI command

Authorization

The db2cli command is located in the bin subdirectory of the directory where you installed this driver. On UNIX operating systems, for the IBM® Data Server Client, this 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 |-+   
           '- -help-----------------------------------------'   

Validate options

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

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

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

Register DSN options

                                                                          .- -user---.     
|--+-+- -add--+-+-------+--dsn_name-+----------------------------------+--+----------+-+--|
   | |        | '- -dsn-'           |                                  |  '- -system-' |   
   | |        '- -alldsn------------'                                  |               |   
   | +- -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--+--------------------------+-+-+----------------|
   | |          '- -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                                    |                                                                      | |   
   |      '-+- -parameter--+--"----parameter_name--=--parameter_value-+--"-------------------------------------------------------------------' |   
   |        '- -parameters-'                                                                                                                   |   
   +-remove--+-+- -dsn--dsn_name------------------------------------------+--+--------------------------------------------+-+------------------+   
   |         | '- -database--db_name-- -host--host_name-- -port--p_number-'  |                      .-;--------------.    | |                  |   
   |         |                                                               |                      V                |    | |                  |   
   |         |                                                               '-+- -parameter--+--"----parameter_name-+--"-' |                  |   
   |         |                                                                 '- -parameters-'                             |                  |   
   |         |                      .-;--------------.                                                                      |                  |   
   |         |                      V                |                                                                      |                  |   
   |         '-+- -parameter--+--"----parameter_name-+--"-------------------------------------------------------------------'                  |   
   |           '- -parameters-'                                                                                                                |   
   '- -help------------------------------------------------------------------------------------------------------------------------------------'   

Bind options

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

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

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

Command parameters

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.

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:

1. Catalog the server node. For more information, see "Cataloging a TCP/IP node from a client using the CLP".
2. Catalog the database that you want to connect to. For more information, see "Cataloging a database from a client by using the CLP".
3. (Optional) Catalog the Database Connection Services (DCS) directory. For more information, see "Catalog DCS database command".
4. 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, both the db2cli and db2cli32 versions can be used 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 to be registered 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.