DB2 Server for VSE & VM: SQL Reference

Chapter 1. Introduction

This introductory chapter:

• Identifies the book's purpose and audience
• Explains how the book is organized
• Explains how to use the book
• Explains how to read the syntax diagrams.

Note:

For ease of reading, this book follows the following convention:

• the term DB2 Server for VSE & VM is used where the discussion refers to both operating system environments (VM and VSE)
• the terms DB2 Server for VM and DB2 Server for VSE are used where the discussion must refer explicitly to either the VM or VSE operating system environments.

Who This Book Is For

This book is for programmers, system administrators, and database administrators who want to use SQL to access a DB2 Server for VSE & VM database. This book is a reference rather than a tutorial or guide. It assumes you are already familiar with SQL. This book also assumes that you will be writing applications for the VM or VSE environment and therefore presents the full functions of the DB2 Server for VSE & VM program.

Prerequisite Knowledge

It is assumed that you possess an understanding of system administration, database administration, or application programming in the DB2 Server for VM or DB2 Server for VSE environment, as provided by the appropriate guide, and you have some knowledge of the following:

• VM (CMS, CP) or VSE (CICS or batch, as applicable)
• A programming language
• Structured Query Language (SQL).

It also assumes that you are familiar with the information found in the DB2 Server for VSE & VM Overview manual.

How This Book Is Organized

This book has the following sections:

• Chapter 1, "Introduction" identifies the purpose, the audience, and the use of the book.
• Chapter 2, "Concepts" discusses the basic concepts of relational databases and SQL.
• Chapter 3, "Language Elements" describes the basic syntax of SQL and the language elements that are common to many SQL statements.
• Chapter 4, "Functions" contains syntax diagrams, semantic descriptions, rules, and usage examples of SQL column and scalar functions.
• Chapter 5, "Queries" describes the various forms of a query, which is a component of various SQL statements.
• Chapter 6, "Statements" contains syntax diagrams, semantic descriptions, rules, and examples of all SQL statements.
• The appendixes contain information about SQL limits, SQLCA, SQLDA, system catalog tables, SQL reserved words, supplied sample tables, and terminology differences.

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

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---+---)--'

SQL Reserved Words

The following words are reserved in the SQL language. They cannot be used in SQL statements except for their defined meaning in the SQL syntax or as host variables, preceded by a colon.

In particular, they cannot be used as names for tables, indexes, columns, views, or dbspaces unless they are enclosed in double quotation marks (").

ACQUIRE ADD ALL ALTER AND ANY AS ASC AVG   BETWEEN BY   CALL CHAR CHARACTER COLUMN COMMENT COMMIT CONCAT CONNECT COUNT CREATE CURRENT   DBA DBSPACE DELETE DESC DISTINCT DOUBLE DROP   EXCLUSIVE EXECUTE EXISTS EXPLAIN   FIELDPROC FOR FROM

GRANT GRAPHIC GROUP   HAVING   IDENTIFIED IN INDEX INSERT INTO IS   LIKE LOCK LONG   MAX MIN MODE   NAMED NHEADER NOT NULL   OF ON OPTION OR ORDER   PACKAGE PAGE PAGES PCTFREE PCTINDEX PRIVATE PRIVILEGES PROGRAM PUBLIC

RESOURCE REVOKE ROLLBACK ROW RUN   SCHEDULE SELECT SET SHARE SOME STATISTICS STORPOOL SUM SYNONYM   TABLE TO   UNION UNIQUE UPDATE USER   VALUES VIEW   WHERE WITH WORK

Conventions for Representing Mixed Data Values

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

Convention

Meaning

<

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

>

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

x

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

(XX)

Represents a DBCS character (where (XX) can be any double uppercase character).

Short Forms Used in Syntax Diagrams

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

Full Word

Short Form

character

char

expressions

exp

string

str