Who should read the SQL Reference book

This book is intended for programmers who want to write applications that will use SQL to access an AS/400 database.

It is assumed that you possess an understanding of system administration, database administration, or application programming for the AS/400, as provided by the SQL Programming Concepts book and that you have some knowledge of the following:

• COBOL for AS/400
• ILE C for AS/400
• VisualAge C++ for OS/400
• ILE COBOL for AS/400
• AS/400 Toolbox for Java or AS/400 Developer Kit for Java
• ILE RPG for AS/400
• AS/400 PL/I
• REXX
• RPG III (part of RPG for AS/400)
• Structured Query Language (SQL)

References in this book to RPG and COBOL refer to the RPG or COBOL language in general. References to COBOL for AS/400, ILE COBOL for AS/400, ILE RPG for AS/400, or RPG III (part of RPG for AS/400) refer to specific elements of the product where they differ from each other.

This manual is a reference rather than a tutorial. It assumes you are already familiar with SQL programming. This manual also assumes that you will be writing applications solely for the AS/400.

If you need more information about using SQL statements, statement syntax, and parameters, see the SQL Programming Concepts book.

If you are planning applications that are portable to other IBM environments, it will be necessary for you to refer to books for those environments in addition to this one (such as IBM SQL Reference Version 2, SC26-8416).

Assumptions Relating to Examples of SQL Statements

The examples of SQL statements shown in this guide are based on the sample tables in Appendix A of the SQL Programming Concepts book and assume the following:

• They are shown in the interactive SQL environment or written in COBOL. EXEC SQL and END-EXEC are used to delimit an SQL statement in a COBOL program. A description of how to use SQL statements in a COBOL program is provided in the SQL Programming with Host Languages book.
• Each SQL example is shown on several lines, with each clause of the statement on a separate line.
• SQL keywords are highlighted.
• Table names provided in Appendix A of the SQL Programming Concepts book use the collection CORPDATA. Table names that are not provided in that appendix should use collections that you create.
• Calculated columns are enclosed in parentheses, ().
• The SQL naming convention is used.
• The APOST and APOSTSQL precompiler options are assumed (although they are not the default in COBOL). Character-string constants within SQL and host language statements are delimited by apostrophes (').
• A sort sequence of *HEX is used.

Whenever the examples vary from these assumptions, it is stated.

How to Read Syntax Diagrams

Throughout this book, syntax is described using the structure defined as follows:

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

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

  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 other than complete statements start with the >--- symbol and end with the ---> symbol.

• Required items appear on the horizontal line (the main path).

  >>-required_item-----------------------------------------------><
   
  

• Optional items appear below the main path.

  >>-required_item---+---------------+---------------------------><
                     '-optional_item-'
   
  

  If an optional item appears above the main path, that item has no effect on the execution of the statement and is used only for readability.

                     .-optional_item-.
  >>-required_item---+---------------+---------------------------><
   
  

• 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 of the stack appears on the main path.

  >>-required_item----+-required_choice1-+-----------------------><
                      '-required_choice2-'
   
  

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

  >>-required_item----+------------------+-----------------------><
                      +-optional_choice1-+
                      '-optional_choice2-'
   
  

  If one of the items is the default, it will appear above the main path and the remaining choices will be shown below.

                      .-default_choice--.
  >>-required_item----+-----------------+------------------------><
                      +-optional_choice-+
                      '-optional_choice-'
   
  

• An arrow returning to the left, above the main line, indicates an item that can be repeated.

                      .-------------------.
                      V                   |
  >>-required_item-------repeatable_item--+----------------------><
   
  

  If the repeat arrow contains a comma, you must separate repeated items with a comma.

                      .-,-----------------.
                      V                   |
  >>-required_item-------repeatable_item--+----------------------><
   
  

  A repeat arrow above a stack indicates that you can repeat the items in the stack.

• Keywords appear in uppercase (for example, FROM). They must be spelled exactly as shown. Variables appear in all lowercase letters (for example, column-name). They represent user-supplied names or values.
• If punctuation marks, parentheses, arithmetic operators, or other such symbols are shown, you must enter them as part of the syntax.