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.
CALL
CLOSE
CONNECT
DECLARE CURSOR
FETCH
OPEN
- 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 theDECLARE 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
to999
(001
is the first LOB column in the select list of the corresponding DECLARE CURSOR statement,002
is the second LOB column, and999
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.
- 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
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:
|