DB2 Server for VSE & VM: Quick Reference

About This Manual

This reference pictorially summarizes Structured Query Language statements used by:

• DB2 Server for VM and DB2 Server for VSE
• Interactive SQL Facility (ISQL) commands
• Database Services Utility (DBS Utility) commands.

It also contains information about the following:

• SQL language elements
• Functions
• Queries
• Preprocessing application programs
• ISQL program function keys
• Operator commands
• Catalog tables
• Application server support for remote applications
• Multiple application server support for DB2 Server for VSE
• SQL communication area (SQLCA) and SQL descriptor area (SQLDA)
• SQL reserved words
• Database Services Utility reserved words.

Who Should Use This Manual

This manual is intended as a quick reference for application developers, system programmers, and database administrators who write application programs using SQL, or use ISQL, or the Database Services Utility in a DB2 Server for VM or DB2 Server for VSE environment. It contains syntax diagrams for SQL statements, ISQL commands, operator commands, and DBS Utility commands.

It is assumed that the VM user has some knowledge of VM (CMS, CP), a programming language, and structured query language (SQL). It is assumed that the VSE user has some knowledge of a VSE system, a CICS/VSE^(R) system or batch as applicable, a programming language, and structured query language (SQL).

Both the VSE and VM user should be familiar with the information in the DB2 Server for VSE & VM Overview. For further information on the required environment, refer to either the DB2 Server for VM Program Directory or the DB2 Server for VSE Program Directory for your database manager.

Related Publications

For more information about the DB2 Server for VM and DB2 Server for VSE database managers, ISQL, and the DBS Utility, refer to the following IBM publications for DB2 Server for VM or DB2 Server for VSE as appropriate:

• DB2 Server for VSE & VM Overview
• DB2 Server for VSE & VM SQL Reference
• DB2 Server for VSE & VM Interactive SQL Guide and Reference
• DB2 Server for VSE & VM Database Services Utility
• DB2 Server for VSE & VM Operation.

Syntax Notation Conventions

Throughout this manual, syntax is described using the structure defined below.

• Read the syntax diagrams from left to right and from top to bottom, following the path of the line.

  The >>--- symbol indicates the beginning of a statement or command.

  The ---> symbol indicates that the statement syntax is continued on the next line.

  The >--- symbol indicates that a statement is continued from the previous line.

  The --->< symbol indicates the end of a statement.

  Diagrams of syntactical units that are not complete statements start with the >--- symbol and end with the ---> symbol.

• Some SQL statements, Interactive SQL (ISQL) commands, or database services utility (DBS Utility) commands can stand alone. For example:

  
  

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

  Others must be followed by one or more keywords or variables. For example:

  
  

  >>-SET AUTOCOMMIT OFF------------------------------------------><

• Keywords may have parameters associated with them which represent user-supplied names or values. These names or values can be specified as either constants or as user-defined variables called host_variables (host_variables can only be used in programs).

  
  

  >>-DROP SYNONYM--synonym---------------------------------------><

• Keywords appear in either uppercase (for example, SAVE) or mixed case (for example, CHARacter). All uppercase characters in keywords must be present; you can omit those in lowercase.
• Parameters appear in lowercase and in italics (for example, synonym).
• If such symbols as punctuation marks, parentheses, or arithmetic operators are shown, you must use them as indicated by the syntax diagram.
• All items (parameters and keywords) must be separated by one or more blanks.
• Required items appear on the same horizontal line (the main path). For example, the parameter integer is a required item in the following command:

  
  

  >>-SHOW DBSPACE--integer---------------------------------------><

  This command might appear as:

    SHOW DBSPACE 1
  

• Optional items appear below the main path. For example:

  
  

  >>-CREATE----+---------+--INDEX-------------------------------->< '-UNIQUE--'

  This statement could appear as either:

    CREATE INDEX
  

  or

    CREATE UNIQUE INDEX
  

• If you can choose from two or more items, they appear vertically in a stack.

  If you must choose one of the items, one item appears on the main path. For example:

  
  

  >>-SHOW LOCK DBSPACE----+-ALL-----+---------------------------->< '-integer-'

  Here, the command could be either:

    SHOW LOCK DBSPACE ALL
  

  or

    SHOW LOCK DBSPACE 1
  

  If choosing one of the items is optional, the entire stack appears below the main path. For example:

  
  

  >>-BACKWARD----+---------+------------------------------------->< +-integer-+ '-MAX-----'

  Here, the command could be:

    BACKWARD
  

  or

    BACKWARD 2
  

  or

    BACKWARD MAX
  

• The repeat symbol indicates that an item can be repeated. For example:

  
  

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

  This statement could appear as:

    ERASE NAME1
  

  or

    ERASE NAME1 NAME2
  

  A repeat symbol above a stack indicates that you can make more than one choice from the stacked items, or repeat a choice. For example:

  
  

  .-,-----------------------. V | >>-VALUES--(------+-constant-----------+--+--)----------------->< +-host_variable_list-+ +-NULL---------------+ '-special_register---'

• If an item is above the main line, it represents a default, which means that it will be used if no other item is specified. In the following example, the ASC keyword appears above the line in a stack with DESC. If neither of these values is specified, the command would be processed with option ASC.

  
  

  .-ASC--. >>-+------+---------------------------------------------------->< '-DESC-'

• When an optional keyword is followed on the same path by an optional default parameter, the default parameter is assumed if the keyword is not entered. However, if this keyword is entered, one of its associated optional parameters must also be specified.

  In the following example, if you enter the optional keyword PCTFREE =, you also have to specify one of its associated optional parameters. If you do not enter PCTFREE =, the database manager will set it to the default value of 10.

  
  

  .-PCTFREE = 10------. >>-+-------------------+--------------------------------------->< '-PCTFREE = integer-'

• Words that are only used for readability and have no effect on the execution of the statement are shown as a single uppercase default. For example:

  
  

  .-PRIVILEGES--. >>-REVOKE ALL----+-------------+-------------------------------><

  Here, specifying either REVOKE ALL or REVOKE ALL PRIVILEGES means the same thing.

• Sometimes a single parameter represents a fragment of syntax that is expanded below. In the following example, fieldproc_block is such a fragment and it is expanded following the syntax diagram containing it.

  
  

  >>-+----------------------------+---| fieldproc_block |-------->< '-NOT NULL--+-------------+--' +-UNIQUE------+ '-PRIMARY KEY-'

   

  
  

  fieldproc_block |--FIELDPROC--program_name----+-------------------------+-------| | .-,-----------. | | V | | '-(-----constant---+---)--'

|How to Send Your Comments

|Your feedback is important in helping to provide the most accurate and |high-quality information. If you have any comments about this book or |any other DB2 Server for VSE & VM documentation: |

• |Visit our home page at:

  |http://www-4.ibm.com/software/data/db2/vse-vm/

• |A form for readers' comments is provided at the back of this |publication. If the form has been removed, address your comments |to:
  |IBM Canada Ltd. Laboratory
  |Information Development
  |2G/345/1150/TOR
  |1150 Eglinton Ave East
  |North York, Ontario, Canada M3C 1H7
  
• |Send your comments by electronic mail to one of the following |addresses:
  |

  	Format	Address
  	Internet	torrcf@ca.ibm.com
  	Facsimile	(416) 448-6161 (Attention RCF Coordinator)

  |Be sure to include the name of the book, the form number (including the |suffix), and the page, section title, or topic you are commenting on.

  |If you choose to respond through the Internet, please include either your |entire Internet network address, or a postal address.

• |Fill out the form at the back of this book and return it by mail, by fax, |or by giving it to an IBM representative. |

Conventions for Representing Mixed Data Values

When mixed data values are shown in examples, the following conventions are used:

Convention

Meaning

<

Represents the mixed shift-out character (X'0E').

>

Represents the mixed shift-in character (X'0F').

x

Represents an SBCS character (x can be any lowercase letter).

(XX)

Represents a DBCS character ((XX) can be any double-byte uppercase letter).

Short Forms Used in Syntax Diagrams

Some words have been shortened in the syntax diagrams in this book. The words are:

Full Word

Short Form

duration

dur

expressions

exp

string

str