db2cli - DB2 interactive CLI command to run SQL statements

The db2cli command is an interactive CLI command that starts the interactive Call Level Interface (CLI) environment for design and prototyping in CLI. The db2cli command can run or validate SQL statements that are in a file.

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.

This topic contains the following sections:

Authorization

None

Required connection

None

Syntax of command

Read syntax diagramSkip visual syntax diagram
>>-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-'   

Notes:
  1. All the execsql-mode options are case insensitive and the options can be specified in any order.

To understand the conventions that are used in the diagram, see How to read syntax diagrams.

Descriptions of options

The following options are the pureQuery-specific options for the db2cli command.

-cursorconcurrency concurrency
Specifies the cursor concurrency to apply to all statements. Use one of the following values for concurrency:
readonly
Sets the cursor concurrency to be read only. readonly is the default value.
lock
The cursor uses the lowest level of locking that is sufficient to ensure that the row can be updated. This value is supported by forward-only and keyset cursors.
values
The cursor uses optimistic concurrency control, comparing values.
-cursorhold holdability
Specifies the cursor holdability to apply to all statements. Use one of the following values for holdability:
yes
The cursors are not destroyed when the transaction is committed.
no
The cursors are destroyed when the transaction is committed.

The default value is yes.

-cursortype type
Specifies the permitted cursor type. Use one of the following values for type:
dynamic
A dynamic scrollable cursor that detects inserts, deletes, and updates to the result set and make the corresponding changes to the result set. Dynamic cursors are supported only when accessing DB2 for z/OS® Version 8.1 and later data servers.
forwardonly
The cursor scrolls only forward. forwardonly is the default type.
keyset
A keyset-driven scrollable cursor that can detect and make changes to the underlying data.
static
The data in the result set is static.

Usage notes

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().

Examples

The following example assumes 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

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.


Feedback