DB2 Server for VSE & VM: Quick Reference

Interactive SQL Commands

Starting and Stopping ISQL - VM Users

To use ISQL in a VM environment, follow these steps:

  1. Log on to VM.
  2. IPL CMS.
  3. Start ISQL.

To start ISQL, type the following 4-character transaction identifier and press ENTER: 

  ISQL

To stop communication with the DB2 Server for VM database manager through ISQL, type the following command in the input area and press ENTER: 

  EXIT

To end your VM terminal session, type the following command and press ENTER. 

  LOGOFF

Starting and Stopping ISQL - VSE Users

To start ISQL, type the following 4-character transaction identifier and press ENTER: 

  ISQL

If your installation has not defined a default authorization name, enter the authorization-name and password on the ISQL welcome panel, or use the explicit SQL CONNECT statement as follows: 

  CONNECT authorization-name IDENTIFIED BY password

To stop communication with the DB2 Server for VSE database manager through ISQL, type the following command in the input area and press ENTER: 

  EXIT

BACKOUT

Nullifies changes made since the last SAVE command or, if no previous SAVE command was issued, since the start of the INPUT command. Use this command only while applying the INPUT command with AUTOCOMMIT on. 

>>-BACKOUT-----------------------------------------------------><

BACKWARD

Displays rows that occur before those that are currently displayed. 

>>-BACKWARD----+--------------+--------------------------------><
               +-rows_integer-+
               '-MAX----------'

CANCEL

Cancels a command, an SQL statement, or a logical unit of work that is in progress. 

>>-CANCEL------------------------------------------------------><

CHANGE

Modifies the current SQL statement in the command buffer and displays the result. 

>>-CHAnge--/--replaced_string--/----+-----------------------+--><
                                    '-replacing_string--/---'

COLUMN

Displays the result of the interactive select-statement so that it begins with the specified column at the left edge of the display. 

             .-1-----------------------.
>>-COLumn----+-------------------------+-----------------------><
             '-position_in_select_list-'

DISPLAY

Shows the results of the associated interactive select-statement on the display. This command can only be issued from a routine. 

>>-DISPLAY-----------------------------------------------------><

END

Ends the display of either a SELECT, COUNTER, SHOW or INPUT command, or a DISPLAY command in a routine. 

>>-END---------------------------------------------------------><

ERASE

Erases stored SQL statements. 

            .--------------------------.
            V                          |
>>-ERASE-------stored_statement_name---+-----------------------><

EXIT

Ends the current ISQL terminal session. 

>>-EXIT--------------------------------------------------------><

FORMAT

Controls the format of the query result that is displayed. 

                  .-------------------------.
           (1)    V                         |
>>-FORMAT-------------| Format options |----+------------------><
 
Format options
 
|---+--+-BTITLE-+---+-------+-------------------------------------------+->
    |  '-TTITLE-'   +-str---+                                           |
    |               '-ERASE-'                                           |
    +-COLumn--column-id--+-DPLACes--integer--+--------------------------+
    |                    +-NAME--str---------+                          |
    |                    +-WIDth--integer----+                          |
    |                    |        .-OFF--.   |                          |
    |                    '-ZEROs--+-ON---+---'                          |
    |         (2)                                                       |
    +-EXCLude-------+---------+--+-column-id----------------+-----------+
    |               '-ALL BUT-'  |    .--------------.      |           |
    |                            |    V              |      |           |
    |                            '-(-----column-id---+---)--'           |
    |          (2)                                                      |
    +--+-GROUP---------+---+-+--------+--+-column-id----------------+-+-+
    |  |          (2)  |   | '-EXCEPT-'  |    .--------------.      | | |
    |  +-SUBTotal------+   |             |    V              |      | | |
    |  |       (2)     |   |             '-(-----column-id---+---)--' | |
    |  '-TOTal---------'   '-ERASE------------------------------------' |
    |                                                                   |
    |                                                                   |
    |         (2)                                                       |
    +-INCLude-------+-----------------------------------------+---------+
    |               '-+------+--+-column-id----------------+--'         |
    |                 '-ONLY-'  |    .--------------.      |            |
    |                           |    V              |      |            |
    |                           '-(-----column-id---+---)--'            |
    +-NULL str----------------------------------------------------------+
    |      (2)                                                          |
    | .-ON-------.                                                      |
    | |     (2)  |                                                      |
    +-+-OFF------+------------------------------------------------------+
    |         (2)   .-ON--.                                             |
    +-OUTLINE-------+-OFF-+---------------------------------------------+
    |               .-2-------.                                         |
    +-SEParator--+--+-integer-+---BLANKs--+-----------------------------+
    |            '-str--------------------'                             |
    |          .-20------.                                              |
    '-VARChar--+-integer-+----------------------------------------------'
 
>---------------------------------------------------------------|

Notes:

  1. Formatting can be performed only on the first 45 columns of a query result.

  2. Any formatting command containing the keywords EXCLude, GROUP, INCLude, ON/OFF, OUTLINE, SUBTotal, or TOTal, causes the query to be reexecuted.

FORWARD

Moves your display forward through a query result. 

>>-FORWARD----+--------------+---------------------------------><
              +-rows_integer-+
              '-MAX----------'

HELP

Retrieves online documentation (available as an option during installation of the database manager). 

>>-HELP----+------------+--------------------------------------><
           +-CONTENTs---+
           '-topic_name-'

HOLD

Prevents an SQL statement from being processed when it is entered. 

>>-HOLD--sql_statement-----------------------------------------><

IGNORE

Nullifies a partially entered, multiple line command. 

>>-IGNORE------------------------------------------------------><

INPUT

Inserts one or more rows of data into a table or view. 

>>-INPut----+-table_name-+---+---------------------------+-----><
            '-view_name--'   |    .-,-----------------.  |
                             |    V                   |  |
                             '-(-----column_name--)---+--'

Interactive Select

The Interactive Select statement retrieves data from a table.

interactive-select-statement 

>>-| fullselect |--+----------------------------------------------+->
                   |           .-,-----------------------------.  |
                   |           V                     .-ASC--.  |  |
                   '-ORDER BY------+-column_name-+---+------+--+--'
                                   '-integer-----'   '-DESC-'
 
>----+---------------+-----------------------------------------><
     '-WITH--+-RR-+--'
             +-CS-+
             '-UR-'
 

 
fullselect
 
|--+-subselect----+--------------------------------------------->
   '-(fullselect)-'
 
      .------------------------------------------.
      V                                          |
>--------+------------------------------------+--+--------------|
         '--+-UNION-----+---+-subselect----+--'
            '-UNION ALL-'   '-(fullselect)-'
 

 
subselect
 
             .-ALL------.
|--SELECT----+----------+---+-*----------------------------+---->
             '-DISTINCT-'   |  .-,-----------------------. |
                            |  V                         | |
                            '----+-expression---------+--+-'
                                 +-table_name.*-------+
                                 +-view_name.*--------+
                                 '-correlation_name.*-'
 
           .-,-----------------------------------------.
           V                                           |
>----FROM------+-table_name-+---+------------------+---+-------->
               '-view_name--'   '-correlation_name-'
 
>-----+--------------------------+------------------------------>
      '-WHERE--search_condition--'
 
>-----+-------------------------------+------------------------->
      |           .-,--------------.  |
      |           V                |  |
      '-GROUP BY-----column_name---+--'
 
>-----+---------------------------+-----------------------------|
      '-HAVING--search_condition--'

ISQLTRACE

Traces activities within ISQL. 

>>-ISQLTrace----+-DUMP----+------------------------------------><
                +-DISPLAY-+
                '-integer-'

LEFT

Starts the display integer columns to the left, counting from the leftmost column on the display. 

           .-1-------.
>>-LEFT----+---------+-----------------------------------------><
           '-integer-'

LIST

Lists information about stored SQL statements, or lists the settings of certain operational characteristics set by the SET command. 

                  .-.----------------------------.
                  V                              |
>>-LIST----+-SQL------+-stored-statement-name-+--+-+-----------><
           |          '-*---------------------'    |
           |      .-.--------------------.         |
           |      V                      |         |
           '-SET----+-*---------------+--+---------'
                    +-AUTOCommit------+
                    +-CASE------------+
                    |       (1)       |
                    +-CLAss-----------+
                    +-CONTInue--------+
                    |        (1)      |
                    +-COPies----------+
                    +-COSTest---------+
                    +-DECimal---------+
                    +-ISOLation-------+
                    +-LANGuage--------+
                    +-NULL------------+
                    +-PAGEsize--------+
                    |            (1)  |
                    +-PRINTRoute------+
                    +-RUNMode---------+
                    +-SEParator-------+
                    '-VARChar---------'

Notes:

  1. The parameters CLAss, COPies, and PRINTRoute are not applicable to VM.

PRINT - VM Users

Requests printed copies of a query result by sending it to the system printer. 

            .---------------------------------.
            V                                 |
>>-PRInt-------+---------------------------+--+----------------><
               |       (1)   .-A---------. |
               +-CLAss-------+-character-+-+
               |        (1)   .-1-------.  |
               '-COPies-------+-integer-+--'

Notes:

  1. The value for CLASS and COPIES specified in the PRINT command remain in effect until changed by a subsequent PRINT or CP SPOOL command.

PRINT - VSE Users

Requests printed copies of a query result by sending it to the system printer, POWER remote printer, or CICS/VSE terminal. 

            .----------------------------.
            V                            |
>>-PRInt-------+----------------------+--+---------------------><
               |        .-?---------. |
               +-CLAss--+-character-+-+
               |         .-1-------.  |
               +-COPies--+-integer-+--+
               +-TERMid--termid-------+
               +-DESTid--wkstat-------+
               +-SYStem---------------+
               '-TOUSER--+--------+---'
                         '-userid-'

RECALL

Retrieves a stored SQL statement. 

>>-RECALL----+-----------------------+-------------------------><
             +-stored_statement_name-+
             '-PREVIOUS--------------'

RENAME

Renames a stored SQL statement. 

>>-RENAME--old_stored_statement_name---------------------------><
 


>>-new_stored_statement_name-----------------------------------><

RIGHT

Starts the display integer columns to the right, counting from the leftmost column of the display. 

            .-1-------.
>>-RIGHT----+---------+----------------------------------------><
            '-integer-'

RUN

Initiates the processing of a routine. Multiple parameters are separated by blanks. Enclose a parameter in single quotation marks if it contains a blank. 

>>-RUN--routine_name----+--------------------------+-----------><
                        |    .--------------.      |
                        |    V              |      |
                        '-(-----parameter---+---)--'

SAVE

Saves all changes since the last SAVE command or, if no SAVE command was issued, since the start of the INPUT command. SAVE is used while you are using the INPUT command with AUTOCOMMIT on only. 

>>-SAVE--------------------------------------------------------><

SET

Sets specified operational characteristics. 



>>-SET----| Group_1 |--| Group_2 |-----------------------------><
 
Group_1
 
   .---------------------------------------.
   V               .-ON--.                 |
|----+-AUTOCommit--+-----+--------------+--+--------------------|
     |             '-OFF-'              |
     |       .-UPper--.                 |
     +-CASE--+--------+-----------------+
     |       '-STRing-'                 |
     |             .-?----------.       |
     |       (1)   |       (2)  |       |
     +-CLAss-------+-class------+-------+
     |           .-hyphen----.          |
     +-CONTinue--+-----------+----------+
     |           '-character-'          |
     |        (1)   .-1------------.    |
     +-COPies-------+--------------+----+
     |              |         (3)  |    |
     |              '-integer------'    |
     |          .-0-------.             |
     +-COSTest--+---------+-------------+
     |          +-integer-+             |
     |          '-OFF-----'             |
     |          .-//./----------------. |
     '-DECimal--+---------------------+-'
                '-/thousands/decimal/-'


Notes:



  1. The parameters CLAss, COPies, and PRINTRoute are not applicable to
    VM.

  2. Output class wanted (letters from A to Z).

  3. Number of copies to be printed.

  4. A maximum of 20 characters can be used for null values.


 
Group_2
 
   .---------------------------------------------------------.
   V              .-RR--.                                    |
|----+-ISOLation--+-----+---------------------------------+--+--|
     |            +-CS--+                                 |
     |            '-UR--'                                 |
     +-LANGuage--+-language_name-+------------------------+
     |           '-langid--------'                        |
     |       .-?-----------.                              |
     +-NULL--+-------------+------------------------------+
     |       |        (4)  |                              |
     |       '-string------'                              |
     |                  .-132-----.           .-66------. |
     +-PAGEsize--WIDth--+---------+---LENgth--+---------+-+
     |                  '-integer-'           '-integer-' |
     |            (1)                                     |
     +-PRINTRoute-------+-DESTid--wkstat--+---------------+
     |                  +-TERMid--termid--+               |
     |                  +-TOUser--userid--+               |
     |                  '-SYSTem----------'               |
     |          .-CONTInue--.                             |
     +-RUNMode--+-----------+-----------------------------+
     |          +-STOP------+                             |
     |          '-CANCEL----'                             |
     |              .-2-------.                           |
     +-SEParator--+-+---------+---BLANKs--+---------------+
     |            | '-integer-'           |               |
     |            '-string----------------'               |
     |          .-20------.                               |
     '-VARChar--+---------+-------------------------------'
                '-integer-'


Notes:



  1. The parameters CLAss, COPies, and PRINTRoute are not applicable to
    VM.

  2. Output class wanted, (letters from A to Z).

  3. Number of copies to be printed.

  4. A maximum of 20 characters can be used for null values.

START

Processes the current SQL statement or a stored SQL statement. 

>>-START---+-----------------------+---------------------------->
           '-stored_statement_name-'
 
>-----+-------------------------------+------------------------><
      |    .-------------------.      |
      |    V            (1)    |      |
      '-(-----parameter--------+---)--'

Notes:

  1. Enclose a parameter in single quotation marks when it contains a blank.

STORE

Saves the current SQL statement for later use. The statement remains stored until erased. 

>>-STORE--stored_statement_name---+---------+------------------><
                                  '-REPLACE-'

Notes:

  1. Related display formatting information can also be stored with the statement, however, some limitations exist. Refer to the DB2 Server for VSE & VM Interactive SQL Guide and Reference manual for more information about storing display information.

TAB

Displays all characters of a column that are too wide to fit on the display. 

          .-1-------.
>>-TAB----+---------+------------------------------------------><
          '-integer-'

Notes:

  1. The TAB command is valid only for CHAR and VARCHAR columns.

ISQL Program Function Keys

The following default Program Function (PF) keys are provided through ISQL.

PF1, PF13
Issues a HELP command, which retrieves an explanation of the use of online help information and provides a list of topics available.

PF2, PF14
Issues a START command, which starts the command in the SQL command buffer (the current SQL command).

PF3, PF15
Issues an END command, which ends the display of a query.

PF4, PF16
Issues a PRINT command, which requests the currently displayed query result to be printed on the system or workstation printer.

PF5, PF17
Issues a RECALL command, which displays the contents of the SQL command buffer.

PF6, PF18
Not assigned.

PF7, PF19
Issues a BACKWARD command.

PF8, PF20
Issues a FORWARD command, which can be used to scroll through the query result half a screen at a time.

PF9, PF21
Issues a HOLD command, which prevents an SQL command from being processed when it is entered.

PF10, PF22
Issues a LEFT 1 command, which moves your view of the query result one column to the left.

PF11, PF23
Issues a RIGHT 1 command, which moves your view of the query result one column to the right.

PF12, PF24
Performs the RETRIEVE function, which moves the previously entered line into the input area.

Note:The CP SET command can be used to set the PF key functions.

CMS Subset VM Users

This CMS subset section applies to VM users. There is no equivalent section for VSE.

CMS or CP commands can be entered during an ISQL session. Supported commands from the CMS subset environment can be entered without terminating the ISQL session. To enter the CMS subset environment, type CMS on the command line, and press ENTER. Refer to Figure 1.

While in the CMS subset environment, you should not use any commands, programs, or execs that access the database manager. If they are used, the results are unpredictable and error conditions could occur.

To exit from the CMS environment, type RETURN in the command line, and press ENTER.

Figure 1. Using the CMS Subset Environment

View figure.

[ Top of Page | Previous Page | Next Page | Table of Contents ]