How this book is structured Appendix F, The SAMPLE database"on page 803 describes the sample tables used in examples Appendix G,"Reserved schema names and reserved words"on page 823 contains the reserved schema names and the reserved words for the ibm SQL and ISO/ ANS SQL99 standards. Appendix H, " Comparison of isolation levels"on page 827 contains a summary of the isolation levels Appendix I, "Interaction of triggers and constraints"on page 829 discusses the interaction of triggers and referential constraints Appendix J, "Explain tables"on page 833 describes the Explain tables. Appendix K, " Explain register values"on page 857 describes the interaction of the Current EXPLAIN MODE and CURRENT EXPLAIN SNAPSHOT special register values with each other and with the PREP and BIND Appendix L,"Recursion example: bill of materials"on page 861 contains an example of a recursive query Appendix M, " Exception tables"on page 867 contains information about user-created tables that are used with the set INTEGRiTY statement Appendix N, "SQL statements allowed in routines"on page 873 lists the SQL statements that are allowed to execute in routines with different SQL data access contexts Appendix O, "CALL invoked from a compiled statement"on page 877 describes the Call statement that can be invoked from a compiled Appendix P, Japanese and traditional-Chinese extended UNIX code(EUC) considerations"on page 883 lists considerations when using extended UNIX code(euc) character sets Appendix Q, " Backus-Naur form(BNF) specifications for DATALINKs"on age 891 contains the Backus-Naur form(BNF) specifications for DATALINKS a brief overview of volume 2 The second volume of the SQL Reference contains information about the syntax and semantics of SQL statements. The specific chapters in that volume are briefly described here SQL statements"contains syntax diagrams, semantic descriptions, rules, and examples of all SQL statements SQL control statements"contains syntax diagrams, semantic descriptions, rules, and examples of SQL procedure statements XIl SQL Reference, Volume 1
v Appendix F, “The SAMPLE database” on page 803 describes the sample tables used in examples. v Appendix G, “Reserved schema names and reserved words” on page 823 contains the reserved schema names and the reserved words for the IBM SQL and ISO/ANS SQL99 standards. v Appendix H, “Comparison of isolation levels” on page 827 contains a summary of the isolation levels. v Appendix I, “Interaction of triggers and constraints” on page 829 discusses the interaction of triggers and referential constraints. v Appendix J, “Explain tables” on page 833 describes the Explain tables. v Appendix K, “Explain register values” on page 857 describes the interaction of the CURRENT EXPLAIN MODE and CURRENT EXPLAIN SNAPSHOT special register values with each other and with the PREP and BIND commands. v Appendix L, “Recursion example: bill of materials” on page 861 contains an example of a recursive query. v Appendix M, “Exception tables” on page 867 contains information about user-created tables that are used with the SET INTEGRITY statement. v Appendix N, “SQL statements allowed in routines” on page 873 lists the SQL statements that are allowed to execute in routines with different SQL data access contexts. v Appendix O, “CALL invoked from a compiled statement” on page 877 describes the CALL statement that can be invoked from a compiled statement. v Appendix P, “Japanese and traditional-Chinese extended UNIX code (EUC) considerations” on page 883 lists considerations when using extended UNIX code (EUC) character sets. v Appendix Q, “Backus-Naur form (BNF) specifications for DATALINKs” on page 891 contains the Backus-Naur form (BNF) specifications for DATALINKs. A brief overview of Volume 2 The second volume of the SQL Reference contains information about the syntax and semantics of SQL statements. The specific chapters in that volume are briefly described here: v “SQL statements” contains syntax diagrams, semantic descriptions, rules, and examples of all SQL statements. v “SQL control statements” contains syntax diagrams, semantic descriptions, rules, and examples of SQL procedure statements. How this book is structured xii SQL Reference, Volume 1
How to read the syntax diagrams How to read the syntax diagrams Throughout this book, syntax is described using the structure defined as follows Read the syntax diagrams from left to right and top to bottom, following the path of the line The symbol indicates the beginning of a syntax diagram. The symbol indicates that the syntax is continued on the next line The -- symbol indicates that the syntax is continued from the previous line The -+4 symbol indicates the end of a syntax diagram. Syntax fragments start with the h- symbol and end with the symbol Required items appear on the horizontal line(the main path Optional items appear below the main path. — required item If an optional item appears above the main path, that item has no effect on execution, and is used only for readability. p— required ite If you can choose from two or more items, they appear in a stack. If you must choose one of the items, one item of the stack appears on the main path bP-required it equired choice required choice If choosing one of the items is optional, the entire stack appears below the about this book xIll
How to read the syntax diagrams Throughout this book, syntax is described using the structure defined as follows: Read the syntax diagrams from left to right and top to bottom, following the path of the line. The ─── symbol indicates the beginning of a syntax diagram. The ─── symbol indicates that the syntax is continued on the next line. The ─── symbol indicates that the syntax is continued from the previous line. The ── symbol indicates the end of a syntax diagram. Syntax fragments 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 execution, and is used only for readability. required_item optional_item If you can choose from two or more items, they appear 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. How to read the syntax diagrams About this book xiii
How to read the syntax diagrams Optional_choice tional choice If one of the items is the default, it will appear above the main path, and the remaining choices will be shown below fault choice p一 required it Optional- tional choice An arrow returning to the left, above the main line, indicates an item that can be repeated. In this case, repeated items must be separated by one or more -required If the repeat arrow contains a comma, you must separate repeated items with a comma A repeat arrow above a stack indicates that you can make more than one choice from the stacked items or repeat a single choice. Keywords appear in uppercase(for example, FROM). They must be spelled exactly as shown. Variables appear in lowercase(for example, col umn-name They represent user-supplied names or values in the syntax If punctuation marks, parentheses, arithmetic operators, or other such symbols are shown, you must enter them as part of the syntax. Sometimes a single variable represents a larger fragment of the syntax. For example, in the following diagram, the variable parameter-block represents he whole syntax fragment that is labeled parameter-block -required item- parameter-block XIV SQL Reference, Volume 1
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. required_item default_choice optional_choice optional_choice An arrow returning to the left, above the main line, indicates an item that can be repeated. In this case, repeated items must be separated by one or more blanks. required_item repeatable_item If the repeat arrow contains a comma, you must separate repeated items with a comma. required_item , repeatable_item A repeat arrow above a stack indicates that you can make more than one choice from the stacked items or repeat a single choice. Keywords appear in uppercase (for example, FROM). They must be spelled exactly as shown. Variables appear in lowercase (for example, column-name). They represent user-supplied names or values in the syntax. If punctuation marks, parentheses, arithmetic operators, or other such symbols are shown, you must enter them as part of the syntax. Sometimes a single variable represents a larger fragment of the syntax. For example, in the following diagram, the variable parameter-block represents the whole syntax fragment that is labeled parameter-block: required_item parameter-block How to read the syntax diagrams xiv SQL Reference, Volume 1
How to read the syntax diagrams Adjacent segments occurring between"large bullets"()may be specified in p一 required item-item1-●-tem2-●-ttem3-b-ttem4 order. Both of the following are valid. hd item3 may be specified in either The above diagram shows that item required item iteml item2 item3 item4 required item iteml item3 item2 item4 Common syntax elements syntax diagrams. The fragments are referenced as follow g The following sections describe a number of syntax fragments that are used in Function designator A function designator uniquely identifies a single function. Function designators typically appear in DDL statements for functions(such as DROP or ALTER) Syntax function-designator FUNCTION-funct ion-name- (data-type-) SPECIFIC FUNCTION-specific-name- Description: FUNCTION function-name Identifies a particular function, and is valid only if there is exactly one function instance with the name function-name in the schema. The identified function can have any number of parameters defined for it. In dynamic SQL statements, the CURRENT SCHEMA special register is used as a qualifier for an unqualified object name. In static SQL statements, the About this book x
parameter-block: parameter1 parameter2 parameter3 parameter4 Adjacent segments occurring between “large bullets” (*) may be specified in any sequence. required_item item1 * item2 * item3 * item4 The above diagram shows that item2 and item3 may be specified in either order. Both of the following are valid: required_item item1 item2 item3item4 required_item item1 item3item2 item4 Common syntax elements The following sections describe a number of syntax fragments that are used in syntax diagrams. The fragments are referenced as follows: fragment Function designator A function designator uniquely identifies a single function. Function designators typically appear in DDL statements for functions (such as DROP or ALTER). Syntax: function-designator: FUNCTION function-name ( ) , ( data-type ) SPECIFIC FUNCTION specific-name Description: FUNCTION function-name Identifies a particular function, and is valid only if there is exactly one function instance with the name function-name in the schema. The identified function can have any number of parameters defined for it. In dynamic SQL statements, the CURRENT SCHEMA special register is used as a qualifier for an unqualified object name. In static SQL statements, the How to read the syntax diagrams About this book xv
Function designator QUALIFIER Precompile/bind option im specifies the qualifier for unqualified object names. If no function name exists in the named or implied schema, an error(SQLSTATE 42704)is raised. If there is more than one instance of the function in the named or implied schema, an error(SQLSTATE 42725)is raised FUNCTION function-name(data-type,) Provides the function signature, which uniquely identifies the function. The function resolution algorithm is not used function-name Specifies the name of the function. In dynamic SQL statements, the CURRENT SCHEMA sPecial register is used as a qualifier for an unqualified object name. In static SQL statements, the QUALIFIER precompile/bind option implicitly specifies the qualifier for unqualified object names ( data-type,…) Values must match the data types that were specified(in the corresponding position) on the CREATE FUNCTION statement. number of data types, and the logical concatenation of the data is used to identify the specific function instance If a data type is unqualified, the type name is resolved by searching the schemas on the SQL path. This also applies to data type names specified for a REFERENCE type It is not necessary to specify the length, precision, or scale for the parameterized data types. Instead, an empty set of parentheses can be coded to indicate that these attributes are to be ignored when looking for a data FLOATO cannot be used(SQLSTATE 42601 ), because the parameter value indicates different data types(REAl or DOUBLE If length, precision, or scale is coded, the value must exactly match that specified in the CREATE FUNCTION statement. A type of FLOAT(n) does not need to match the defined value for n, because 0< n< 25 means REAL, and 24 n 54 means Double. Matching occurs on the basis of whether the type is rEal or DOUBLE If no function with the specified signature exists in the named or implied schema, an error(SQLSTATE 42883)is raised SPECIFIC FUNCTION specific-name Identifies a particular user-defined function, using the name that is specified or defaulted to at function creation time. In dynamic SQL tatements, the CURRENT SCHEMA special register is used as a qualifier for an unqualified object name. In static SQL statements, the QUALIFIER XVI
QUALIFIER precompile/bind option implicitly specifies the qualifier for unqualified object names. If no function by this name exists in the named or implied schema, an error (SQLSTATE 42704) is raised. If there is more than one instance of the function in the named or implied schema, an error (SQLSTATE 42725) is raised. FUNCTION function-name (data-type,...) Provides the function signature, which uniquely identifies the function. The function resolution algorithm is not used. function-name Specifies the name of the function. In dynamic SQL statements, the CURRENT SCHEMA special register is used as a qualifier for an unqualified object name. In static SQL statements, the QUALIFIER precompile/bind option implicitly specifies the qualifier for unqualified object names. (data-type,...) Values must match the data types that were specified (in the corresponding position) on the CREATE FUNCTION statement. The number of data types, and the logical concatenation of the data types, is used to identify the specific function instance. If a data type is unqualified, the type name is resolved by searching the schemas on the SQL path. This also applies to data type names specified for a REFERENCE type. It is not necessary to specify the length, precision, or scale for the parameterized data types. Instead, an empty set of parentheses can be coded to indicate that these attributes are to be ignored when looking for a data type match. FLOAT() cannot be used (SQLSTATE 42601), because the parameter value indicates different data types (REAL or DOUBLE). If length, precision, or scale is coded, the value must exactly match that specified in the CREATE FUNCTION statement. A type of FLOAT(n) does not need to match the defined value for n, because 0 < n < 25 means REAL, and 24 < n < 54 means DOUBLE. Matching occurs on the basis of whether the type is REAL or DOUBLE. If no function with the specified signature exists in the named or implied schema, an error (SQLSTATE 42883) is raised. SPECIFIC FUNCTION specific-name Identifies a particular user-defined function, using the name that is specified or defaulted to at function creation time. In dynamic SQL statements, the CURRENT SCHEMA special register is used as a qualifier for an unqualified object name. In static SQL statements, the QUALIFIER Function designator xvi SQL Reference, Volume 1