DB2 Server for VSE & VM: Quick Reference

DB2 Language Elements

Primitive Elements

character
A letter, digit, space, or special-character

letter
The letters a to z, A to Z, or national language extender (# @ $), or as specified in SYSCHARSETS

digit
The digits 0 to 9

space
The space character

special-character
Any element in a character set other than a letter, digit, or space

hexadecimal-character
A pair of characters in the range 00 to FF

double-byte-character
A character that occupies 2 bytes.

SQL Comments

An SQL comment is all text following two consecutive hyphens (--) on the same line of a static SQL statement in an application program or the command portion of a DBS Utility command.

Comments are allowed wherever a separator (space character) is valid.

Identifiers

identifier 

>>-+-ordinary_identifier--+------------------------------------><
   '-delimited_identifier-'

ordinary_identifier 

                       .-----------------------.
                       V                       |
>>-uppercase_letter------+------------------+--+---------------><
                         +-uppercase_letter-+
                         +-digit------------+
                         '- _ --------------'

Notes:

  1. A reserved word cannot be used as an ordinary identifier. For a list of SQL reserved words, see SQL Reserved Words.

delimited_identifier 

>>-"--non_space_character--------------------------------------->
 
>-----+----------------------------------------------+--"------><
      |  .------------------.                        |
      |  V           (1)    |                        |
      '----character--------+---non_space_character--'

Notes:

  1. With the exception of ".

long_identifier
An identifier with a maximum length of 18 characters (not including any quotation marks).

short_identifier
An identifier with a maximum length of 8 characters (not including any quotation marks).

host_identifier
As defined by the host language, has a maximum length imposed by the host language.

Names and Other Metavariables

A metavariable (or parameter) is a lowercase character or group of characters used in syntax diagrams to represent a group of variables.

authorization_name

With a VSE system, authorization names and passwords are limited to 8 characters and cannot have embedded blanks. 

>>-short_identifier--------------------------------------------><

collection_id 

>>-short_identifier--------------------------------------------><
 

column_name
 
>>-+-------------------+--long_identifier----------------------><
   +-table_name.-------+
   +-view_name.--------+
   +-synonym.----------+
   '-correlation_name.-'
 


constraint_name
 
>>-long_identifier---------------------------------------------><
 


correlation_name
 
>>-long_identifier---------------------------------------------><
 


cursor_name
 
>>-long_ordinary_identifier------------------------------------><
 


cursor_variable
 
>>-long_ordinary_identifier------------------------------------><
 


dbspace_name
 
>>-+--------+--short_ordinary_identifier-----------------------><
   '-owner.-'
 


descriptor_name
 
>>-:host_identifier--------------------------------------------><
 


host_variable
 
>>-:host_identifier----+----------------------------------+----><
                       | .-INDICATOR-.                    |
                       '-+-----------+--:host_identifier--'
 


host_variable_list
 
   .-,----------------.
   V                  |
>>----host_variable---+----------------------------------------><
 


index_id
 
>>-short_ordinary_identifier-----------------------------------><
 


index_name
 
>>-+--------+--long_identifier---------------------------------><
   '-owner.-'
 


owner_name
 
>>-short_ordinary_identifier-----------------------------------><
 


package_id
 
>>-short_ordinary_identifier-----------------------------------><
 


package_name
 
>>-+--------+--long_identifier---------------------------------><
   '-owner.-'
 


package_spec
 
>>-+----------------------------+------------------------------->
   +-short_ordinary_identifier.-+
   '-host_identifier.-----------'
 
>-----+-short_ordinary_identifier-+----------------------------><
      |                 (1)       |
      '-host_identifier-----------'

Notes:

  1. Cannot be a qualified subfield name. 

password
 
>>-short_ordinary_identifier-----------------------------------><
 


program_name
 
>>-short_ordinary_identifier-----------------------------------><
 


routine_name
 
>>-+--------+--long_identifier---------------------------------><
   '-owner.-'
 


section_variable
 
>>-host_identifier---------------------------------------------><
 


server_name
 
>>-long_ordinary_name------------------------------------------><
 


statement_name
 
>>-long_ordinary_identifier------------------------------------><
 


statement_variable
 
>>-long_ordinary_identifier------------------------------------><
 


subsystemid
 
>>-short_ordinary_identifier-----------------------------------><
 


synonym
 
>>-+--------+--long_identifier---------------------------------><
   '-owner.-'
 


table_id
 
>>-short_ordinary_identifier-----------------------------------><
 


table_name
 
>>-+--------+--long_identifier---------------------------------><
   '-owner.-'
 


view_id
 
>>-short_ordinary_identifier-----------------------------------><
 


view_name
 
>>-+--------+--long_identifier---------------------------------><
   '-owner.-'

Data Types

|Result Set LOCATOR

|For RESULT SET LOCATOR data. This data type is used to |identify host variables that are used by the DB2 Server for VSE & VM |requester to uniquely indicate a query result set returned by a stored |procedure. 

|RESULT SET LOCATOR
| 
| 
||--+-| Assembler |-+--------------------------------------------|
|   +-| C |---------+
|   +-| COBOL |-----+
|   '-| PL/I |------'
| 
|Assembler
| 
||---variable-name----+-DC-+--F----------------------------------|
|                     '-DS-'
| 
|C
| 
||---+---------+---+----------+---------------------------------->
|    +-auto----+   +-const----+
|    +-extern--+   '-volatile-'
|    +-static--+
|    '-_Packed-'
| 
|                                              .-,---------------------------------.
|                                              V                                   |
|>-----SQL TYPE IS RESULT_SET_LOCATOR VARYING-----variable-name--+--------------+--+---;->
|                                                                '-= init-value-'
| 
|>---------------------------------------------------------------|
| 
|COBOL
| 
||---01--variable-name--SQL TYPE IS RESULT-SET-LOCATOR VARYING--.-->
| 
|>---------------------------------------------------------------|
| 
|PL/I
| 
||---+-DECLARE-+---+-variable-name----------------+-------------->
|    '-DCL-----'   |    .-,----------------.      |
|                  |    V                  |      |
|                  '-(-----variable-name---+---)--'
| 
|>----SQL TYPE IS RESULT_SET_LOCATOR VARYING--+---------------------------------------+---;->
|                                             '-Alignment and/or Scope and/or Storage-'
| 
|>---------------------------------------------------------------|
|

CHARacter

For character data that has a fixed number of characters (integers). The maximum number of characters is 254. 

                .-(1)-------.
>>-CHARacter----+-----------+----------------------------------><
                '-(integer)-'

DATE

A three-part value that designates a point in time according to the Gregorian calendar. Internally represented as 4-byte packed decimal. The three parts are the year, month, and day. The date can be formatted in several ways. The range of year is 0001 to 9999. The range of month is 1 to 12. The range of day is 1 to n where n depends on the month. 

>>-DATE--------------------------------------------------------><

DECimal

For decimal data. The p identifies the total number of decimal digits a number can have. The s identifies the number of digits to the right of the decimal point. For example, DECIMAL(5,2) creates a decimal column consisting of five digits, two of which are to the right of the decimal point. The NUMERIC parameter is a synonym for DECIMAL. 

                 .-(5,0)----------------------.
>>-+-DECimal-+---+----------------------------+----------------><
   '-NUMERIC-'   |      (1)                   |
                 '-(--p-------+---------+--)--'
                              |    (2)  |
                              '-,s------'

Notes:

  1. The p is an integer value that defines the precision of the number.

  2. Thes is an integer value that defines the scale of the number.

FLOAT

For floating-point numbers. Floating-point numbers range from 5.4E-79 to 7.2E+75. When integer is between 1 and 21, it is a single-precision floating-point number; REAL is a synonym for FLOAT in this situation. When integer is between 22 and 53, it is a double-precision floating-point number; DOUBLE PRECISION is a synonym for FLOAT in this situation. 

            .-(53)------.
>>-+-FLOAT--+-----------+-+------------------------------------><
   |        '-(integer)-' |
   +-REAL-----------------+
   '-DOUBLE PRECISION-----'

GRAPHIC

For double-byte character set (DBCS) data that has a fixed number of DBCS characters (integer). The maximum number of DBCS characters is 127. 

              .-(1)-------.
>>-GRAPHIC----+-----------+------------------------------------><
              '-(integer)-'

INTeger

For large positive or negative whole numbers. The largest number that can be accommodated is 2147483647; the smallest number is -2147483648. 

>>-INTeger-----------------------------------------------------><

LONG VARCHAR

For character data that varies in length up to 32,767 characters. 

                 (1)
>>-LONG VARCHAR------------------------------------------------><

Notes:

  1. ISQL does not support INSERT, UPDATE, or SELECT of tables or views with LONG VARCHAR columns.

LONG VARGRAPHIC

For double-byte character set (DBCS) data that varies in length. A LONG VARGRAPHIC can be up to a maximum of 16,383 DBCS characters. 

                    (1)
>>-LONG VARGRAPHIC---------------------------------------------><

Notes:

  1. ISQL does not support INSERT, UPDATE, or SELECT of tables or views with LONG VARGRAPHIC columns.

SMALLINT

For small positive or negative whole numbers. The largest number that can be accommodated is 32767; the smallest is -32768. 

>>-SMALLINT----------------------------------------------------><

TIME

A three-part value in a number of formats that designates a time of day according to a 24-hour clock. Internally represented as 3-byte packed decimal. The three parts are the hour, minute, and second. The range of hour is 0 to 24, and the range of minute and second is 0 to 59. 

>>-TIME--------------------------------------------------------><

TIMESTAMP

A seven-part value that designates a date and time, including a fractional part. Internally represented as 10-byte packed decimal. The seven parts are year, month, day, hour, minute, second, and microsecond. 

>>-TIMESTAMP---------------------------------------------------><

VARCHAR

For character data that varies in length. The integer refers to the maximum number of characters for any entry and can be a value up to 32767. When the value is greater than 254, the data type is considered a long string. 

            (1)
>>-VARCHAR---------(integer)-----------------------------------><

Notes:

  1. ISQL does not support INSERT, UPDATE, or SELECT of tables or views with VARCHAR>254.

VARGRAPHIC

For double-byte character set (DBCS) data that varies in length. The integer is the number of DBCS characters for any entry; the maximum is 16383. When integer is greater than 127, the data type is considered a long string. 

               (1)
>>-VARGRAPHIC---------(integer)--------------------------------><

Notes:

  1. ISQL does not support INSERT, UPDATE, or SELECT of tables or views with VARGRAPHIC>127.

String Representations of Dates and Times

Date Strings

A string representation of a date is a character string that starts with a digit and has a length of at least 8 characters.
Format Name Abbrev. Date Format Example
International Standards Organization ISO yyyy-mm-dd 1993-12-12
IBM USA standard USA mm/dd/yyyy 12/12/1993
IBM European standard EUR dd.mm.yyyy 12.12.1993
Japanese Industrial Standard Christian Era JIS yyyy-mm-dd 1993-12-12
Site-defined LOCAL Any site-defined form --

Time Strings

A string representation of a time is a character string that starts with a digit and has a length of at least 4 characters.
Format Name Abbrev. Time Format Example
International Standards Organization ISO hh.mm.ss 13.30.05
IBM USA standard USA hh:mm AM or PM 1:30 PM
IBM European standard EUR hh.mm.ss 13.30.05
Japanese Industrial Standard Christian Era JIS hh:mm:ss 13:30:05
Site-defined LOCAL Any site-defined form --

Constants

Integer Constant 

             .----------.
             V          |
>>-+-----+------digit---+--------------------------------------><
   +- + -+
   '- - -'

Decimal Constant 

>>-+---------+--.---+-----------------------+------------------><
   '-integer-'      |                  (1)  |
                    '-unsigned_integer------'

Notes:

  1. At least one number is needed with the decimal point.

Floating-Point Constant 

>>--+-decimal-+--E--integer------------------------------------><
    '-integer-'

Character Constant - SBCS 

        .----------------.
        V                |
>>-'------+-----------+--+--'----------------------------------><
          '-character-'

Character Constant - MIXED 

        .-----------------------------------------------.
        V                                               |
>>-'-------+-----------------------------------------+--+--'---><
           +-character-------------------------------+
           |      .-------------------------.        |
           |      V                         |        |
           '- < -----double_byte_character--+--- > --'

Character Constant - Hexadecimal 

           .------------------------------.
           V                              |
>>-X--'-------+-----------------------+---+--'-----------------><
              '-hexadecimal_character-'

Graphic Constant - in PL/I Programs 

                                                 (1)
>>-<'----+-------------------------------+--'G>----------------><
         |  .-------------------------.  |
         |  V                         |  |
         '----double_byte_character---+--'

Notes:

  1. N is a synonym for G.

or 

                                                 (1)
>>-'<----+-------------------------------+-->'G----------------><
         |  .-------------------------.  |
         |  V                         |  |
         '----double_byte_character---+--'

Notes:

  1. N is a synonym for G.

Graphic Constant - In All Other Contexts 

        (1)
>>-G'<---------+-------------------------------+-->'-----------><
               |  .-------------------------.  |
               |  V                         |  |
               '----double_byte_character---+--'

Notes:

  1. N is a synonym for G.

Special Registers

The following special registers are supported by the database manager.

Special Registers
Description

USER
The runtime authorization ID

CURRENT DATE
The current date in the local time zone

CURRENT SERVER
The current application server

CURRENT TIME
The current time in the local time zone

CURRENT TIMESTAMP
The current timestamp in the local time zone

CURRENT TIMEZONE
A signed time duration as a DECIMAL(6,0) number containing the local time-zone value.

Expressions

An expression specifies a value. The form of an expression is as follows: 

   .-| operator |--------------------------------.
   V                                       (1)   |
>>----+-----+---+-column_name----------+---------+-------------><
      +- + -+   +-constant-------------+
      '- - -'   +-(expression)---------+
                +-function-------------+
                +-host_variable--------+
                +-| labeled_duration |-+
                '-special_register-----'
 
operator
 
               (2)
|---+- CONCAT ------+-------------------------------------------|
    +- / -----------+
    +- * -----------+
    +- + -----------+
    '- - -----------'

Notes:

  1. Not all combinations of operands and operations are supported.

  2. Either || or !! can be used as a synonym for CONCAT. 

 
labeled_duration
 
|--+-column_name---+---+-DAY----------+-------------------------|
   +-constant------+   +-DAYS---------+
   +-(expression)--+   +-HOUR---------+
   +-function------+   +-HOURS--------+
   '-host_variable-'   +-MICROSECOND--+
                       +-MICROSECONDS-+
                       +-MINUTE-------+
                       +-MINUTES------+
                       +-MONTH--------+
                       +-MONTHS-------+
                       +-SECOND-------+
                       +-SECONDS------+
                       +-YEAR---------+
                       '-YEARS--------'

Date Arithmetic

         (1)                                    (1)
>>-date---------+- + -+---+-date_dur---------+------------------>
                '- - -'   '-labeled_date_dur-'
 
>------ = --date-----------------------------------------------><

Notes:

  1. These operands can be specified in either order. 

>>-+-date----------+-- - ----+-date----------+-- = --date_dur--><
   |          (1)  |         |          (1)  |
   '-date_str------'         '-date_str------'

Notes:

  1. Only one of these two operands can be a string.

Time Arithmetic

         (1)                                    (1)
>>-time---------+- + -+---+-time_dur---------+------------------>
                '- - -'   '-labeled_time_dur-'
 
>------ = --time-----------------------------------------------><

Notes:

  1. These operands can be specified in either order. 

>>-+-time----------+-- - ----+-time----------+-- = --time_dur--><
   |          (1)  |         |          (1)  |
   '-time_str------'         '-time_str------'

Notes:

  1. Only one of these two operands can be a string.

Timestamp Arithmetic

              (1)                                 (1)
>>-timestamp---------+- + -+---+-date_dur------+---------------->
                     '- - -'   +-labeled_dur---+
                               +-time_dur------+
                               '-timestamp_dur-'
 
>------ = --timestamp------------------------------------------><

Notes:

  1. These operands can be specified in either order. 

>>-+-timestamp----------+-- - ----+-timestamp----------+-------->
   |               (1)  |         |               (1)  |
   '-timestamp_str------'         '-timestamp_str------'
 
>---- = --timestamp_dur----------------------------------------><

Notes:

  1. Only one of these two operands can be a string.

Predicate

Specifies a condition that is true, false, or unknown about a row or group.

Basic Predicate

>>-exp----+- = -------+---+-exp---------+----------------------><
          |      (1)  |   '-(subselect)-'
          +- <> ------+
          +- < -------+
          +- > -------+
          +- <= ------+
          '- >= ------'

Notes:

  1. Either ^= or ^= can be used as an alternative to the <> operator.

BETWEEN Predicate

>>-exp--+-----+--BETWEEN--exp--AND--exp------------------------><
        '-NOT-'

EXISTS Predicate

>>-+-----+--EXISTS----(subselect)------------------------------><
   '-NOT-'

IN Predicate

>>-exp--+-----+--IN--+-(subselect)-------------------------+---><
        '-NOT-'      |    .-,-----------------------.      |
                     |    V                         |      |
                     '-(----+-constant-----------+--+---)--'
                            +-host_variable_list-+
                            '-special_register---'

LIKE Predicate

>>-column_name---+-----+--LIKE----+-USER----------+------------->
                 '-NOT-'          +-host_variable-+
                                  '-str_constant--'
 
>-----+--------------------------------+-----------------------><
      '-ESCAPE-+-host_variable------+--'
               '-character_constant-'

NULL Predicate

>>-column_name--IS--+-----+--NULL------------------------------><
                    '-NOT-'

Quantified Predicate

>>-exp----+- = -------+---+-SOME-+---(subselect)---------------><
          |      (1)  |   +-ANY--+
          +- <> ------+   '-ALL--'
          +- < -------+
          +- > -------+
          +- <= ------+
          '- >= ------'

Notes:

  1. Either ^= or ^= can be used as an alternative to the <> operator.

Search Conditions

Specifies a condition that is true, false, or unknown about a row or group. The common form of a search condition is column_name operator value. Refer to Predicate for additional functions available for search conditions. 

>>-+-----+----+-predicate----------+---------------------------->
   '-NOT-'    '-(search_condition)-'
 
      .----------------------------------------------------.
      V                                                    |
>--------+----------------------------------------------+--+---><
         '--+-AND-+--+-----+----+-predicate----------+--'
            '-OR--'  '-NOT-'    '-(search_condition)-'

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