The db2cli executable is located in the sqllib/samples/cli/ DB2® installation subdirectory of the home directory of the database instance owner. In case of IBM® Data Server Driver for ODBC and CLI, the db2cli executable is in the clidriver/bin installation directory.
This topic describes only the pureQuery function of db2cli command. The pureQuery function is supplied by the execsql option of the command. For CLI information about the command, see the db2cli - DB2 interactive CLI command in the DB2 for Linux, UNIX and Windows information center.
None
None
>>-db2cli--+-| execsql-mode |-+-------------------------------->< '- -help-----------' execsql-mode (1) .- -execute-----. |--execsql------+---------------+--+-| server-option |-+--------> '- -prepareonly-' '- -connstring--str-' >-- -inputsql--sqlfile--+-----------------------+---------------> '- -outfile--outputfile-' >--+---------------------------------+--------------------------> '- -statementdelimiter--delimiter-' >--+----------------------------------+-------------------------> '- -commentstart--commentindicator-' >--+---------------------------+--------------------------------> '- -cursorhold--holdability-' >--+----------------------------------+-------------------------> '- -cursorconcurrency--concurrency-' >--+--------------------+--+--------+---------------------------| '- -cursortype--type-' '- -help-' server-option |-- -dsn--dsn_name--+--------------------+----------------------| '-| authentication |-' authentication |-- -user-- -username--+--------------------+-------------------| '- -passwd -password-'
To understand the conventions that are used in the diagram, see How to read syntax diagrams.
The following options are the pureQuery-specific options for the db2cli command.
The default value is yes.
Only SQL statements and comments can be in the input SQL file. The file cannot contain CLI specific attributes or keywords.
Only preparable SQL statements are allowed in input SQL file. DB2 commands such DESCRIBE TABLE and BIND are not allowed.
If any SQL statements in the file depend on DDL in the input file, you must run all the DDL statements before you run the command db2cli execsql -prepareonly.
When you use the -execute option with the db2cli execsql command, SQL statements cannot have parameter markers.
Batch SQL statements are not supported.
The SQL statements are executed with DB2 CLI API SQLExecDirect(). When the db2cli command runs SQL statements on a database, the command recognizes the settings that are specified in the db2cli.ini or db2dsdriver.cfg set for the database.
The error message string that is returned by the db2cli command is the string that is returned by the CLI error handling API SQLError() or SQLGetDiagRec().
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
The 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( )
You 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
You can 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, ?)
You 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.
You 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.
You 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.