Starts the interactive call level interface (CLI) environment for design and prototyping in CLI.
In IBM® Data Server Driver for ODBC and CLI Version 9.7 Fix Pack 4 and later, 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
None
>>-db2cli--+------------------------------------------------+-->< +-validate--+----------------------+-------------+ | +-| Validate 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--+------------------+-- --keystorepassword--keystorepasswd-' '- -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-' | +-remove--+- -dsn--dsn_name------------------------------------------+----+ | +- -database--db_name-- -host--host_name-- -port--p_number-+ | | | .-;--------------. | | | | V | | | | '-+- -parameter--+--"----parameter_name-+--"---------------' | | '- -parameters-' | '- -help------------------------------------------------------------------'
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. In V9.7 Fix Pack 6 and later 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].
Specifies the db2cli register DSN mode. Use this command parameter to register a DSN in the Windows operating system.
The registerdsn -add-alldsn parameter continues to search for data sources that are available only in the db2cli.ini and db2dsdriver.cfg files and does not add data sources for cataloged databases.
Executes or prepares the SQL statements specified in an input file. Can also save output to a file.
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.
Registers or unregisters the IBM Data Server Driver for ODBC and CLI in the Windows registry.
If there is no dsn element with the dsn_name as dsn alias in the configuration file, you must indicate the database connection information. A new dsn element in the dsncollection section is added for the database. If you do not specify the database connection information, the SQL1531N error message is returned.
If there is a dsn element with the dsn_name as dsn alias in the configuration file, you must indicate the database connection information with the -database parameter. This connection information must match the existing connection information in the configuration file. Otherwise, the SQL1532N error message is returned.
If a database element with the same connection information exists in the db2dsdriver.cfg configuration file, the specified parameter element is added to this database element. Otherwise, the specified parameter element is added to a new database element.
To add a parameter to the global section, specify the parameter information without indicating a database or a data source.
If there is no parameter element for the specified data source or database in the configuration file, the parameter element is added to the specified data source or database.
If there is a parameter element for the specified data source or database in the configuration file, the existing value is updated with the value that you specify with -parameter[s].
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.
To remove parameter information, specify the corresponding DSN and the parameter information.
To remove the entire data source section, specify only the DSN without any parameter information.
To remove parameter information, specify the database connection information for the corresponding database section and the parameter information.
To remove the entire database section, specify only the database connection information without any parameter information.
If the indicated parameter is not in the specified database or data source section of the configuration file, no action is taken.
Special characters, such as path separators in the value string, must be preceded by the character "\".
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.
db2cli validate -dsn sample
db2cli validate -database dbname1:server1.net1.com:50001
db2cli validate -dsn "IBM - User Acceptance" -connect -user <userid> -passwd <password>
<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>
$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>
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>
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>
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>
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>
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>
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>
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( )
db2cli execsql -dsn sample -inputsql test.sql
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. 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.
--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.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.