Command Reference

Chapter 4. Using Command Line SQL 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.

All SQL statements that can be executed through the command line processor are listed in the CLP column of Table 8. 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 CLOSE, CONNECT, DECLARE CURSOR, FETCH, OPEN, and SELECT does depend on whether these statements are embedded or executed through the CLP. The CLP syntax of these statements is provided below:

CALL

>>-CALL----+-SQLJ.INSTALL_JAR--(--jar-url, jar-id--)--+--------><
           +-SQLJ.REPLACE_JAR--(--jar-url, jar-id--)--+
           +-SQLJ.REMOVE_JAR--(--jar-id--)------------+
           '-SQLJ.REFRESH_CLASSES--(--void--)---------'
 

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:

1. This form is only valid if implicit connect is enabled.

DECLARE CURSOR

>>-DECLARE--cursor-name--CURSOR---+-----------+----------------->
                                  '-WITH HOLD-'
 
>----FOR--select-statement-------------------------------------><
 

FETCH

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

OPEN

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

SELECT

 
fullselect
 
|--+-subselect---------+---------------------------------------->
   +-(fullselect)------+
   '-| values-clause |-'
 
      .---------------------------------------------------.
      V                                                   |
>--------+---------------------------------------------+--+-----|
         '--+-UNION---------+---+-subselect---------+--'
            +-UNION ALL-----+   +-(fullselect)------+
            +-EXCEPT--------+   '-| values-clause |-'
            +-EXCEPT ALL----+
            +-INTERSECT-----+
            '-INTERSECT ALL-'
 
subselect
 
|---select-clause--from-clause---------------------------------->
 
>----+--------------+--+-----------------+--+---------------+---|
     '-where-clause-'  '-group-by-clause-'  '-having-clause-'
 
select-clause
 
              .-ALL------.
|---SELECT----+----------+-------------------------------------->
              '-DISTINCT-'
 
>-----+-*----------------------------------------------+--------|
      |  .-,-----------------------------------------. |
      |  V                                           | |
      '----+-expression--+----------------------+-+--+-'
           |             | .-AS-.               | |
           |             '-+----+--column-name--' |
           +-table-name.*-------------------------+
           +-view-name.*--------------------------+
           '-correlation-name.*-------------------'
 
values-clause
 
              .-,---------------------.
              V                       |
|---VALUES-------| row-expression |---+-------------------------|
 
row-expression
 
|---+--+-expression-+---------------+---------------------------|
    |  '-NULL-------'               |
    |    .-,-----------------.      |
    |    V                   |      |
    '-(------+-expression-+--+---)--'
             '-NULL-------'
 

Notes:

1. When CALL is issued through the command line processor, only the identified procedures and their respective parameters are supported:

   jar-url

   Specifies the URL that contains the jar file to be installed or replaced. The only supported URL scheme is file:.

   jar-id

   Specifies the jar identifier in the database to be associated with the file specified by the jar-url.

   For example:

      db2 call sqlj.install_jar ( "file:///C:/java/jarfiles/myprocs.zip", "myprocs" )
   

   SQLJ.REFRESH_CLASSES

   Causes DB2 to use updated stored procedure Java classes without restarting the instance. If this statement is not called after updating stored procedure Java classes, DB2 continues to use the previous version of the stored procedure classes.

2. 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.

3. When FETCH or SELECT is issued through the command line processor, decimal and floating-point numbers are displayed with the country's decimal delimiter, that is, a period (.) in the U.S., Canada, and the U.K.; a comma (,) in most other countries. However, when INSERT, UPDATE, 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 that use a comma for that purpose.

4. When FETCH or SELECT 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

5. A new LOB option has been added to FETCH. If the LOB clause is specified, only the next row is fetched:
   • 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.

6. When SELECT is issued through the command line processor to query tables containing LOB columns, each LOB column is truncated to 4KB in the output.

7. The command line processor displays BLOB columns in hexadecimal representation.

8. SQL statements that contain references to structured type columns cannot be issued if an appropriate transform function is not available. For more information see the Application Development Guide.

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

1. 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
   

2. Connect to the database.
3. Issue the following command:

      db2 bind @clp.lst collection nullid datetime iso
   

For detailed information about the command line processor, see Chapter 2, Command Line Processor (CLP). For more information about the syntax of SQL statements and the function provided by SQL statements, see the SQL Reference. For information about reading syntax diagrams, see Appendix A, How to Read the Syntax Diagrams.

Table 8. SQL Statements (DB2 Universal Database)

SQL Statement	Dynamic1	Command Line Processor (CLP)	Call Level Interface3 (CLI)	SQL Procedure
ALLOCATE CURSOR				X
assignment statement				X
ASSOCIATE LOCATORS				X
ALTER { BUFFERPOOL, NICKNAME,10 NODEGROUP, SERVER,10 TABLE, TABLESPACE, USER MAPPING,10 TYPE, VIEW  }	X	X	X
BEGIN DECLARE SECTION2
CALL		X9	X4	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, DISTINCT TYPE, EVENT MONITOR, FUNCTION, FUNCTION MAPPING,10 INDEX, INDEX EXTENSION, METHOD, NICKNAME,10 NODEGROUP, PROCEDURE, SCHEMA, TABLE, TABLESPACE, TRANSFORM TYPE MAPPING,1 TRIGGER, USER MAPPING,10 TYPE, VIEW, WRAPPER10 }	X	X	X	X11
DECLARE CURSOR2		X	SQLAllocStmt()	X
DECLARE GLOBAL TEMPORARY TABLE	X	X	X	X
DELETE	X	X	X	X
DESCRIBE8		X	SQLColAttributes(), SQLDescribeCol(), SQLDescribParam()6
DISCONNECT		X	SQLDisconnect()
DROP	X	X	X	X11
END DECLARE SECTION2
EXECUTE			SQLExecute()	X
EXECUTE IMMEDIATE			SQLExecDirect()	X
EXPLAIN	X	X	X	X
FETCH		X	SQLExtendedFetch()7, SQLFetch(), SQLFetchScroll()7	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 PASSTHRU10	X	X	X	X
SET PATH	X	X	X	X
SET SCHEMA	X	X	X	X
SET SERVER OPTION10	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: 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 DB2 CLI function, the function name is listed. Although this statement is not dynamic, with DB2 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 DB2 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 DB2 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. For information on the DESCRIBE SQL statement, refer to the SQL Reference. For information on the DESCRIBE CLP command, refer to the Command Reference. When CALL is issued through the command line processor, only certain procedures and their respective parameters are supported (see ***). Statement is supported only for federated database servers. SQL procedures can only issue CREATE and DROP statements for indexes, tables, and views.