DB2 10.5 for Linux, UNIX, and Windows

Using command line SQL statements and XQuery statements

This section provides information about using Structured Query Language (SQL) statements from the command line.

These statements can be executed directly from an operating system command prompt, and can be used to define and manipulate information stored in a database table, index, or view in much the same way as if the commands were written into an application program. Information can be added, deleted, or updated, and reports can be generated from the contents of tables.

You can use SQL statements from the command line, and you can use a stored procedure (SYSPROC.ADMIN_CMD()) to run some CLP commands through SQL. For more information abouthow to use this stored procedure, refer to the SQL Administrative Routines.

To issue XQuery statements in CLP, prefix the statements with the XQUERY keyword.

Note: If you use the CLP to issue XQuery statements, it is best to choose a termination character other than the semicolon (-t option). This ensures that statements or queries that use namespace declarations are not misinterpreted, since namespace declarations are also terminated by a semicolon.

All SQL statements that can be executed through the command line processor are listed in the CLP column of Table 1. The syntax of all the SQL statements, whether executed from the command line or embedded in a source program, is described in the SQL Reference. The syntax of many embedded SQL statements and CLP SQL statements is identical. However, host variables, parameter markers, descriptor names, and statement names are applicable only to embedded SQL. The syntax of CALL, CLOSE, CONNECT, DECLARE CURSOR, FETCH, and OPEN does depend on whether these statements are embedded or executed through the CLP. The CLP syntax of these statements is provided in the following section:

CALL


>>-CALL--procedure-name--(--+--------------------+--)----------><
                            | .-,--------------. |      
                            | V                | |      
                            '---+-expression-+-+-'      
                                +-?----------+          
                                '-null-------'

CLOSE


>>-CLOSE--cursor-name------------------------------------------><

CONNECT


>>-CONNECT------------------------------------------------------>

>--+------------------------------------------------------------+-><
   +-TO--server-name--+----------------+--+-------------------+-+   
   |                  '-| lock-block |-'  '-| authorization |-' |   
   +-RESET------------------------------------------------------+   
   |                   (1)                                      |   
   '-| authorization |------------------------------------------'   

authorization

|--USER--authorization-name------------------------------------->

>--+-------------------------------------------------------+----|
   +-USING--password--+----------------------------------+-+   
   |                  '-NEW--password--CONFIRM--password-' |   
   '-CHANGE PASSWORD---------------------------------------'   

lock-block

   .-IN SHARE MODE-------------------------.   
|--+---------------------------------------+--------------------|
   '-IN EXCLUSIVE MODE--+----------------+-'   
                        '-ON SINGLE NODE-'

Notes:

This form is only valid if implicit connect is enabled.

DECLARE CURSOR


>>-DECLARE--cursor-name--CURSOR--+-----------+------------------>
                                 '-WITH HOLD-'   

>--+---------------------------------------------------+-------->
   '-DATABASE--dbname--+-----------------------------+-'   
                       '-USER--user--USING--password-'     

>--FOR--+-select-statement---------+---------------------------><
        '-XQUERY--xquery-statement-'

FETCH


>>-FETCH--+------+--cursor-name--------------------------------->
          '-FROM-'                

>--+------------------------------------------------------+----><
   +-FOR--+-ALL-+--+-ROW--+-------------------------------+   
   |      '-n---'  '-ROWS-'                               |   
   '-LOB--+-COLUMN--+--ALL--INTO--filename--+-APPEND----+-'   
          '-COLUMNS-'                       +-NEW-------+     
                                            '-OVERWRITE-'

OPEN


>>-OPEN--cursor-name-------------------------------------------><

Note:

When CALL is issued:
- An expression must be used for each IN or INOUT parameter of the procedure. For an INOUT parameter, the expression must be a single literal value. The INOUT XML parameters must be either NULL (if nullable) or in the following format: XMLPARSE(DOCUMENT string). Note that the string in the argument for XMLPARSE must be a string literal and is subject to the CURRENT IMPLICIT XMLPARSE OPTION special register. It cannot be an expression.
- A question mark (?) must be used for each OUT parameter of the procedure.
- The stored procedure must be cataloged. If an uncataloged procedure is called, a SQL0440N error message is returned.
The following CLP script creates a procedure called PROC4 after it creates a table with an XML column C1. It uses three XML parameters: IN (PARM1), INOUT (PARM2) and OUT (PARM3), and returns a result set with XML data.
```
CREATE TABLE TAB4(C1 XML) 
CREATE PROCEDURE PROC4(IN PARM1 XML, INOUT PARM2 XML, OUT PARM3 XML)
LANGUAGE SQL
BEGIN
   DECLARE STMT CLOB(1M) DEFAULT '';
   DECLARE C1 CURSOR WITH RETURN FOR S1;
   SET STMT = 'SELECT C1 FROM TAB4';

   /* INSERT PARM1 */
   INSERT INTO TAB4 VALUES(PARM1);

   /* MANIPULATE PARM2 */

   /* SET PARM3 AND INSERT */
   SET PARM3 = XMLPARSE(DOCUMENT '<a>333</a>');
   INSERT INTO TAB4 VALUES(PARM3);

   /* RETURN A RESULT SET WITH XML DATA */
   PREPARE S1 FROM STMT;
   OPEN C1;
END
```
To call the procedure PROC4 from the command line processor, issue a CALL statement:
```
CALL PROC4(XMLPARSE(DOCUMENT '<a>111</a>'), XMLPARSE(DOCUMENT '<a>222</a>'), ?)
```
The CLP version of CONNECT permits the user to change the password, using the following parameters:
NEW password

Specifies the new password that is to be assigned to the user name. Passwords can be up to 18 characters in length. The system on which the password will be changed depends on how user authentication has been set up.

CONFIRM password

A string that must be identical to the new password. This parameter is used to catch entry errors.

CHANGE PASSWORD

If this option is specified, the user is prompted for the current password, a new password, and for confirmation of the new password. Passwords are not displayed at entry.
The DATABASE clause in the DECLARE CURSOR statement is only applicable when the cursor is being used for a subsequent load from cursor operation.
To use the DECLARE CURSOR statement with an XQuery statement, users must prefix the XQuery statement with the keyword XQUERY explicitly.
When FETCH is issued through the command line processor, decimal and floating-point numbers are displayed with the territory's decimal delimiter, that is, a period (.) in the U.S., Canada, and the U.K.; a comma (,) in most other countries/regions. However, when INSERT, UPDATE, CALL, and other SQL statements are issued through the command line processor to update tables, a period must be used as the decimal delimiter, even in countries/regions that use a comma for that purpose.
When FETCH is issued through the command line processor, null values are typically displayed as a hyphen (-). For databases configured with DFT_SQLMATHWARN YES, expressions that result in an arithmetic error are processed as null values. Such arithmetic error nulls are displayed as a plus (+).
For example, create and populate table t1 as follows:
```
   create table t1 (i1 int , i2 int);
   insert into t1 values (1,1),(2,0),(3,null);
```
The statement: select i1/i2 from t1 generates the following result:
```
   1
   ---
     1
     +
     -
   3 records selected
```
A new LOB option has been added to FETCH. If the LOB clause is specified, only the next row is fetched:
- When SELECT is issued through the command line processor to query tables containing LOB columns, all columns are truncated to 8KB in the output.
- Each LOB column value is fetched into a file with the name filename.xxx, where filename is specified in the LOB clause, and xxx is a file extension from 001 to 999 (001 is the first LOB column in the select list of the corresponding DECLARE CURSOR statement, 002 is the second LOB column, and 999 is the 999th column). The maximum number of LOB columns that can be fetched into files is 999.
- Names of the files containing the data are displayed in the LOB columns.
The command line processor displays BLOB columns in hexadecimal representation.
SQL statements that contain references to structured type columns cannot be issued if an appropriate transform function is not available.
A CLP imposed limit of 64K for SQL statements and for CLP commands that contain SQL statement components has now been removed. However there is a limitation of 64K on the CLP output size for each row.
XML data, retrieved via SELECT, CALL or XQuery, is truncated to 4000 bytes in the output.

To change the way that the CLP displays data (when querying databases using SQL statements through the CLP), rebind the CLP bind files against the database being queried. For example, to display date and time in ISO format, do the following:

Create a text file containing the names of the CLP bind files. This file is used as the list file for binding multiple files with one BIND command. In this example the file is named clp.lst, and its contents are:
```
   db2clpcs.bnd +
   db2clprr.bnd +
   db2clpur.bnd +
   db2clprs.bnd +
   db2clpns.bnd
```
Connect to the database.

Issue the following command:

   db2 bind @clp.lst collection nullid datetime iso

Table 1. SQL Statements (DB2®)
SQL Statement	Dynamic¹	Command Line Processor (CLP)	Call Level Interface³ (CLI)	SQL Procedure
ALLOCATE CURSOR				X
assignment statement				X
ASSOCIATE LOCATORS				X
ALTER { BUFFERPOOL, DATABASE PARTITION GROUP, NICKNAME,⁹ SERVER,⁹ TABLE, TABLESPACE, USER MAPPING,⁹ TYPE, VIEW }	X	X	X
BEGIN DECLARE SECTION²
CALL	X	X	X	X
CASE statement				X
CLOSE		X	`SQLCloseCursor()`, `SQLFreeStmt()`	X
COMMENT ON	X	X	X	X
COMMIT	X	X	`SQLEndTran()`, `SQLTransact()`	X
Compound SQL (Embedded)			X⁴
compound statement				X
CONNECT (Type 1)		X	`SQLBrowseConnect()`, `SQLConnect()`, `SQLDriverConnect()`
CONNECT (Type 2)		X	`SQLBrowseConnect()`, `SQLConnect()`, `SQLDriverConnect()`
CREATE { ALIAS, BUFFERPOOL, DATABASE PARTITION GROUP, DISTINCT TYPE, EVENT MONITOR, FUNCTION, FUNCTION MAPPING⁹, GLOBAL TEMPORARY TABLE, INDEX, INDEX EXTENSION, METHOD, NICKNAME,⁹ PROCEDURE, SCHEMA, SERVER, TABLE, TABLESPACE, TRANSFORM, TYPE MAPPING,⁹ TRIGGER, USER MAPPING,⁹ TYPE, VIEW, WRAPPER⁹ }	X	X	X	X¹⁰
DECLARE CURSOR²		X	`SQLAllocStmt()`	X
DECLARE GLOBAL TEMPORARY TABLE	X	X	X	X
DELETE	X	X	X	X
DESCRIBE⁸		X	`SQLColAttributes()`, `SQLDescribeCol()`, `SQLDescribeParam()`⁶
DISCONNECT		X	`SQLDisconnect()`
DROP	X	X	X	X¹⁰
END DECLARE SECTION²
EXECUTE			`SQLExecute()`	X
EXECUTE IMMEDIATE			`SQLExecDirect()`	X
EXPLAIN	X	X	X	X
FETCH		X	`SQLExtendedFetch()` , `SQLFetch()`, `SQLFetchScroll()`	X
FLUSH EVENT MONITOR	X	X	X
FOR statement				X
FREE LOCATOR			X⁴	X
GET DIAGNOSTICS				X
GOTO statement				X
GRANT	X	X	X	X
IF statement				X
INCLUDE²
INSERT	X	X	X	X
ITERATE				X
LEAVE statement				X
LOCK TABLE	X	X	X	X
LOOP statement				X
OPEN		X	`SQLExecute()`, `SQLExecDirect()`	X
PREPARE			`SQLPrepare()`	X
REFRESH TABLE	X	X	X
RELEASE		X		X
RELEASE SAVEPOINT	X	X	X	X
RENAME TABLE	X	X	X
RENAME TABLESPACE	X	X	X
REPEAT statement				X
RESIGNAL statement				X
RETURN statement				X
REVOKE	X	X	X
ROLLBACK	X	X	`SQLEndTran()`, `SQLTransact()`	X
SAVEPOINT	X	X	X	X
select-statement	X	X	X	X
SELECT INTO				X
SET CONNECTION		X	`SQLSetConnection()`
SET CURRENT DEFAULT TRANSFORM GROUP	X	X	X	X
SET CURRENT DEGREE	X	X	X	X
SET CURRENT EXPLAIN MODE	X	X	X, `SQLSetConnectAttr()`	X
SET CURRENT EXPLAIN SNAPSHOT	X	X	X, `SQLSetConnectAttr()`	X
SET CURRENT PACKAGESET
SET CURRENT QUERY OPTIMIZATION	X	X	X	X
SET CURRENT REFRESH AGE	X	X	X	X
SET EVENT MONITOR STATE	X	X	X	X
SET INTEGRITY	X	X	X
SET PASSTHRU⁹	X	X	X	X
SET PATH	X	X	X	X
SET SCHEMA	X	X	X	X
SET SERVER OPTION⁹	X	X	X	X
SET transition-variable⁵	X	X	X	X
SIGNAL statement				X
SIGNAL SQLSTATE⁵	X	X	X
UPDATE	X	X	X	X
VALUES INTO				X
WHENEVER²
WHILE statement				X
Notes: You can code all statements in this list as static SQL, but only those marked with X as dynamic SQL. You cannot execute this statement. An X indicates that you can execute this statement using either `SQLExecDirect()` or `SQLPrepare()` and `SQLExecute()`. If there is an equivalent CLI function, the function name is listed. Although this statement is not dynamic, with CLI you can specify this statement when calling either `SQLExecDirect()`, or `SQLPrepare()` and `SQLExecute()`. You can only use this within CREATE TRIGGER statements. You can only use the SQL DESCRIBE statement to describe output, whereas with CLI you can also describe input (using the `SQLDescribeParam()` function). You can only use the SQL FETCH statement to fetch one row at a time in one direction, whereas with the CLI `SQLExtendedFetch()` and `SQLFetchScroll()` functions, you can fetch into arrays. Furthermore, you can fetch in any direction, and at any position in the result set. The DESCRIBE SQL statement has a different syntax than that of the CLP DESCRIBE command. Statement is supported only for federated database servers. SQL procedures can only issue CREATE and DROP statements for indexes, tables, and views.