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.
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--procedure-name--(--+--------------------+--)---------->< | .-,--------------. | | V | | '---+-expression-+-+-' +-?----------+ '-null-------'
>>-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-'
>>-DECLARE--cursor-name--CURSOR--+-----------+------------------> '-WITH HOLD-' >--+---------------------------------------------------+--------> '-DATABASE--dbname--+-----------------------------+-' '-USER--user--USING--password-' >--FOR--+-select-statement---------+--------------------------->< '-XQUERY--xquery-statement-'
>>-FETCH--+------+--cursor-name---------------------------------> '-FROM-' >--+------------------------------------------------------+---->< +-FOR--+-ALL-+--+-ROW--+-------------------------------+ | '-n---' '-ROWS-' | '-LOB--+-COLUMN--+--ALL--INTO--filename--+-APPEND----+-' '-COLUMNS-' +-NEW-------+ '-OVERWRITE-'
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>'), ?)
create table t1 (i1 int , i2 int);
insert into t1 values (1,1),(2,0),(3,null);
1
---
1
+
-
3 records selected
db2clpcs.bnd +
db2clprr.bnd +
db2clpur.bnd +
db2clprs.bnd +
db2clpns.bnd
db2 bind @clp.lst collection nullid datetime iso
SQL Statement | Dynamic1 | Command Line Processor (CLP) | Call Level Interface3 (CLI) | SQL Procedure |
---|---|---|---|---|
ALLOCATE CURSOR | X | |||
assignment statement | X | |||
ASSOCIATE LOCATORS | X | |||
ALTER { BUFFERPOOL, DATABASE PARTITION GROUP, NICKNAME,9 SERVER,9 TABLE, TABLESPACE, USER MAPPING,9 TYPE, VIEW } | X | X | X | |
BEGIN DECLARE SECTION2 | ||||
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) | X4 | |||
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 MAPPING9, GLOBAL TEMPORARY TABLE, INDEX, INDEX EXTENSION, METHOD, NICKNAME,9 PROCEDURE, SCHEMA, SERVER, TABLE, TABLESPACE, TRANSFORM, TYPE MAPPING,9 TRIGGER, USER MAPPING,9 TYPE, VIEW, WRAPPER9 } | X | X | X | X10 |
DECLARE CURSOR2 | X | SQLAllocStmt() | X | |
DECLARE GLOBAL TEMPORARY TABLE | X | X | X | X |
DELETE | X | X | X | X |
DESCRIBE8 | X | SQLColAttributes(), SQLDescribeCol(), SQLDescribeParam()6 | ||
DISCONNECT | X | SQLDisconnect() | ||
DROP | X | X | X | X10 |
END DECLARE SECTION2 | ||||
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 | X4 | X | ||
GET DIAGNOSTICS | X | |||
GOTO statement | X | |||
GRANT | X | X | X | X |
IF statement | X | |||
INCLUDE2 | ||||
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 PASSTHRU9 | X | X | X | X |
SET PATH | X | X | X | X |
SET SCHEMA | X | X | X | X |
SET SERVER OPTION9 | X | X | X | X |
SET transition-variable5 | X | X | X | X |
SIGNAL statement | X | |||
SIGNAL SQLSTATE5 | X | X | X | |
UPDATE | X | X | X | X |
VALUES INTO | X | |||
WHENEVER2 | ||||
WHILE statement | X | |||
Notes:
|