db2 v8 sql reference
TRANSCRIPT
DB2 Universal Database for z/OS
Version 8
SQL Reference
SC18-7426-00
DB2 Universal Database for z/OS
Version 8
SQL Reference
SC18-7426-00
Note Before using this information and the product it supports, be sure to read the general information under Notices on page 1369.
First Edition (March 2004) This edition applies to Version 8 of IBM DB2 Universal Database for z/OS (DB2 UDB for z/OS), product number 5625-DB2, and to any subsequent releases until otherwise indicated in new editions. Make sure you are using the correct edition for the level of the product. Specific changes are indicated by a vertical bar to the left of a change. A vertical bar to the left of a figure caption indicates that the figure has changed. Editorial changes that have no technical significance are not noted. Copyright International Business Machines Corporation 1982, 2004. All rights reserved. US Government Users Restricted Rights Use, duplication or disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
ContentsAbout this book . . . . . . . . . . . . . . . . . . . . . . . . xv Who should read this book . . . . . . . . . . . . . . . . . . . . xv Conventions and terminology used in this book . . . . . . . . . . . . . xv Terminology and citations . . . . . . . . . . . . . . . . . . . . xv Conventions for describing mixed data values . . . . . . . . . . . . xvi Industry standards . . . . . . . . . . . . . . . . . . . . . . . xvi How to read the syntax diagrams . . . . . . . . . . . . . . . . . . xvii Accessibility . . . . . . . . . . . . . . . . . . . . . . . . . xviii How to send your comments . . . . . . . . . . . . . . . . . . . . xix Summary of changes to this book . . . . . . . . . . . . . . . . . xxi Chapter 1. DB2 concepts . . . . . . . . . . . . . . . . . . . Structured query language . . . . . . . . . . . . . . . . . . . Static SQL . . . . . . . . . . . . . . . . . . . . . . . . Dynamic SQL . . . . . . . . . . . . . . . . . . . . . . . Deferred embedded SQL . . . . . . . . . . . . . . . . . . Interactive SQL . . . . . . . . . . . . . . . . . . . . . . SQL Call Level Interface (CLI) and Open Database Connectivity (ODBC) . Java database connectivity and embedded SQL for Java . . . . . . . Schemas . . . . . . . . . . . . . . . . . . . . . . . . . Tables . . . . . . . . . . . . . . . . . . . . . . . . . . Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . Keys . . . . . . . . . . . . . . . . . . . . . . . . . . . Unique keys . . . . . . . . . . . . . . . . . . . . . . . Primary keys . . . . . . . . . . . . . . . . . . . . . . . Parent keys . . . . . . . . . . . . . . . . . . . . . . . Foreign keys . . . . . . . . . . . . . . . . . . . . . . . Constraints . . . . . . . . . . . . . . . . . . . . . . . . . Unique constraints . . . . . . . . . . . . . . . . . . . . . Referential constraints. . . . . . . . . . . . . . . . . . . . Check constraints . . . . . . . . . . . . . . . . . . . . . Triggers . . . . . . . . . . . . . . . . . . . . . . . . . Storage structures . . . . . . . . . . . . . . . . . . . . . . Storage groups . . . . . . . . . . . . . . . . . . . . . . . Databases . . . . . . . . . . . . . . . . . . . . . . . . Catalog. . . . . . . . . . . . . . . . . . . . . . . . . . Views . . . . . . . . . . . . . . . . . . . . . . . . . . Sequences . . . . . . . . . . . . . . . . . . . . . . . . Routines . . . . . . . . . . . . . . . . . . . . . . . . . Functions . . . . . . . . . . . . . . . . . . . . . . . . Stored procedures . . . . . . . . . . . . . . . . . . . . Application processes, concurrency, and recovery . . . . . . . . . . Locking, commit, and rollback . . . . . . . . . . . . . . . . Unit of work . . . . . . . . . . . . . . . . . . . . . . . Unit of recovery . . . . . . . . . . . . . . . . . . . . . Rolling back work . . . . . . . . . . . . . . . . . . . . . Packages and application plans . . . . . . . . . . . . . . . . . Distributed data. . . . . . . . . . . . . . . . . . . . . . . Connections . . . . . . . . . . . . . . . . . . . . . . . Distributed unit of work . . . . . . . . . . . . . . . . . . . Remote unit of work . . . . . . . . . . . . . . . . . . . . Character conversion . . . . . . . . . . . . . . . . . . . . Copyright IBM Corp. 1982, 2004
|
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
|
|
. 1 . 3 . 3 . 3 . 3 . 4 . 4 . 4 . 4 . 5 . 6 . 6 . 6 . 7 . 7 . 7 . 7 . 8 . 8 . 10 . 11 . 11 . 12 . 12 . 12 . 12 . 13 . 14 . 14 . 14 . 15 . 15 . 15 . 16 . 16 . 17 . 18 . 18 . 19 . 22 . 24
iii
| |
Character sets and code pages . . . Coded character sets and CCSIDs . Determining the encoding scheme and Expanding conversions . . . . . . Contracting conversions . . . . .
. . . . . . CCSID . . . . . .
. . of . .
. . . . . . a string . . . . . .
. . . . .
. . . . .
. . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
27 29 29 32 32 33 37 37 38 38 40 40 40 46 47 47 47 48 49 50 50 51 53 55 56 56 58 60 61 62 64 68 69 69 70 71 74 76 78 81 82 82 83 83 84 85 85 85 87 87 87 88 88 89 89
|
|
Chapter 2. Language elements . . . . . . . . . . . . . Characters . . . . . . . . . . . . . . . . . . . . . Tokens . . . . . . . . . . . . . . . . . . . . . . . Identifiers . . . . . . . . . . . . . . . . . . . . . . SQL identifiers . . . . . . . . . . . . . . . . . . . Host identifiers . . . . . . . . . . . . . . . . . . . Restrictions for distributed access . . . . . . . . . . . . Naming conventions . . . . . . . . . . . . . . . . . . SQL path . . . . . . . . . . . . . . . . . . . . . . Qualification of unqualified object names . . . . . . . . . . Unqualified alias, index, sequence, table, trigger, and view names Unqualified data type, function, procedure, and specific names . Aliases and synonyms . . . . . . . . . . . . . . . . . Authorization IDs and authorization-names. . . . . . . . . . Authorization IDs and schema names . . . . . . . . . . Authorization IDs and statement preparation . . . . . . . . Authorization IDs and dynamic SQL . . . . . . . . . . . Authorization IDs and remote execution . . . . . . . . . . Data types . . . . . . . . . . . . . . . . . . . . . Nulls . . . . . . . . . . . . . . . . . . . . . . . Numbers . . . . . . . . . . . . . . . . . . . . . Character strings . . . . . . . . . . . . . . . . . . Graphic strings . . . . . . . . . . . . . . . . . . . Binary strings . . . . . . . . . . . . . . . . . . . Large objects (LOBs) . . . . . . . . . . . . . . . . Datetime values . . . . . . . . . . . . . . . . . . Row ID values . . . . . . . . . . . . . . . . . . . XML values . . . . . . . . . . . . . . . . . . . . Distinct types . . . . . . . . . . . . . . . . . . . Promotion of data types . . . . . . . . . . . . . . . . Casting between data types . . . . . . . . . . . . . . . Assignment and comparison . . . . . . . . . . . . . . . Numeric assignments . . . . . . . . . . . . . . . . String assignments . . . . . . . . . . . . . . . . . Datetime assignments . . . . . . . . . . . . . . . . Row ID assignments . . . . . . . . . . . . . . . . . Distinct type assignments . . . . . . . . . . . . . . . Assignments to LOB locators. . . . . . . . . . . . . . Numeric comparisons . . . . . . . . . . . . . . . . String comparisons . . . . . . . . . . . . . . . . . Datetime comparisons . . . . . . . . . . . . . . . . Row ID comparisons . . . . . . . . . . . . . . . . . Distinct type comparisons . . . . . . . . . . . . . . . Rules for result data types. . . . . . . . . . . . . . . . Numeric operands . . . . . . . . . . . . . . . . . . Character and graphic string operands . . . . . . . . . . Binary string operands . . . . . . . . . . . . . . . . Datetime operands . . . . . . . . . . . . . . . . . Row ID operands . . . . . . . . . . . . . . . . . . Distinct type operands . . . . . . . . . . . . . . . .
iv
SQL Reference
| | | |
| |
| |
| |
Conversion rules for operations that combine strings . . . . . . . . Constants . . . . . . . . . . . . . . . . . . . . . . . . . Integer constants . . . . . . . . . . . . . . . . . . . . . Floating-point constants . . . . . . . . . . . . . . . . . . Decimal constants . . . . . . . . . . . . . . . . . . . . . Character string constants . . . . . . . . . . . . . . . . . . Datetime constants . . . . . . . . . . . . . . . . . . . . Graphic string constants . . . . . . . . . . . . . . . . . . Special registers . . . . . . . . . . . . . . . . . . . . . . General rules for special registers . . . . . . . . . . . . . . . CURRENT CLIENT_ACCTNG . . . . . . . . . . . . . . . . CURRENT CLIENT_APPLNAME . . . . . . . . . . . . . . . CURRENT CLIENT_USERID . . . . . . . . . . . . . . . . CLIENT_WRKSTNNAME . . . . . . . . . . . . . . . . . CURRENT APPLICATION ENCODING SCHEME . . . . . . . . . CURRENT DATE . . . . . . . . . . . . . . . . . . . . CURRENT DEGREE . . . . . . . . . . . . . . . . . . . CURRENT LOCALE LC_CTYPE . . . . . . . . . . . . . . . CURRENT MEMBER . . . . . . . . . . . . . . . . . . . CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION . . . CURRENT OPTIMIZATION HINT . . . . . . . . . . . . . . CURRENT PACKAGE PATH . . . . . . . . . . . . . . . . CURRENT PACKAGESET . . . . . . . . . . . . . . . . . CURRENT PATH . . . . . . . . . . . . . . . . . . . . CURRENT PRECISION . . . . . . . . . . . . . . . . . . CURRENT REFRESH AGE . . . . . . . . . . . . . . . . . CURRENT RULES . . . . . . . . . . . . . . . . . . . . CURRENT SCHEMA . . . . . . . . . . . . . . . . . . . CURRENT SERVER . . . . . . . . . . . . . . . . . . . CURRENT SQLID . . . . . . . . . . . . . . . . . . . . CURRENT TIME . . . . . . . . . . . . . . . . . . . . . CURRENT TIMESTAMP . . . . . . . . . . . . . . . . . . CURRENT TIMEZONE . . . . . . . . . . . . . . . . . . USER . . . . . . . . . . . . . . . . . . . . . . . . . Using special registers in a user-defined function or a stored procedure Column names . . . . . . . . . . . . . . . . . . . . . . Qualified column names . . . . . . . . . . . . . . . . . . Correlation names . . . . . . . . . . . . . . . . . . . . Column name qualifiers to avoid ambiguity . . . . . . . . . . . Column name qualifiers in correlated references . . . . . . . . . Resolution of column name qualifiers and column names . . . . . . References to variables . . . . . . . . . . . . . . . . . . . References to host variables . . . . . . . . . . . . . . . . Host variables in dynamic SQL . . . . . . . . . . . . . . . References to LOB host variables . . . . . . . . . . . . . . References to LOB locator variables . . . . . . . . . . . . . References to stored procedure result sets . . . . . . . . . . . References to result set locator variables . . . . . . . . . . . . References to built-in session variables . . . . . . . . . . . . Host structures in PL/I, C, and COBOL . . . . . . . . . . . . . Host-variable-arrays in PL/I, C, C++, and COBOL . . . . . . . . . Functions . . . . . . . . . . . . . . . . . . . . . . . . Types of functions . . . . . . . . . . . . . . . . . . . . Function invocation . . . . . . . . . . . . . . . . . . . . Function resolution . . . . . . . . . . . . . . . . . . . . Best fit consideration . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. 89 . 93 . 93 . 94 . 94 . 94 . 95 . 95 . 96 . 97 . 99 . 99 . 100 . 100 . 101 . 101 . 102 . 102 . 103 . 103 . 103 . 104 . 104 . 105 . 105 . 106 . 107 . 108 . 109 . 109 . 110 . 110 . 110 . 110 111 . 113 . 114 . 114 . 115 . 116 . 117 . 119 . 119 . 121 . 121 . 122 . 122 . 123 . 123 . 125 . 126 . 127 . 127 . 128 . 129 . 133
Contents
v
|
|
|
| | | |
Expressions . . . . . . . . . . . . . . . . Without operators . . . . . . . . . . . . . With arithmetic operators . . . . . . . . . . . With the concatenation operator . . . . . . . . Scalar-fullselect . . . . . . . . . . . . . . Datetime operands and durations . . . . . . . Datetime arithmetic in SQL . . . . . . . . . . Precedence of operations . . . . . . . . . . CASE expressions . . . . . . . . . . . . . CAST specification . . . . . . . . . . . . . Sequence reference . . . . . . . . . . . . Predicates . . . . . . . . . . . . . . . . . Basic predicate . . . . . . . . . . . . . . Quantified predicate . . . . . . . . . . . . BETWEEN predicate . . . . . . . . . . . . DISTINCT predicate . . . . . . . . . . . . EXISTS predicate . . . . . . . . . . . . . IN predicate . . . . . . . . . . . . . . . LIKE predicate . . . . . . . . . . . . . . NULL predicate . . . . . . . . . . . . . . Search conditions . . . . . . . . . . . . . . Options affecting SQL . . . . . . . . . . . . . Precompiler options for dynamic statements . . . . Decimal point representation . . . . . . . . . Apostrophes and quotation marks in string delimiters Katakana characters for EBCDIC . . . . . . . . Mixed data in character strings . . . . . . . . Formatting of datetime strings . . . . . . . . . SQL standard language . . . . . . . . . . . Positioned updates of columns . . . . . . . . Mappings from SQL to XML . . . . . . . . . . Mapping SQL character sets to XML character sets . Mapping SQL identifiers to XML names . . . . . Mapping SQL data values to XML data values . . . Chapter 3. Functions . . . . . . Aggregate functions . . . . . . AVG . . . . . . . . . . . COUNT . . . . . . . . . . COUNT_BIG . . . . . . . . MAX . . . . . . . . . . . MIN . . . . . . . . . . . STDDEV . . . . . . . . . . SUM . . . . . . . . . . . VARIANCE or VARIANCE_SAMP XMLAGG . . . . . . . . . Scalar functions . . . . . . . . ABS . . . . . . . . . . . ACOS . . . . . . . . . . . ADD_MONTHS . . . . . . . ASIN . . . . . . . . . . . ATAN . . . . . . . . . . . ATANH . . . . . . . . . . ATAN2 . . . . . . . . . . BLOB . . . . . . . . . . . CCSID_ENCODING . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
133 135 135 139 141 142 143 147 148 151 156 160 161 162 165 166 167 168 171 177 178 179 181 181 182 183 183 184 184 185 186 186 186 187 189 195 196 197 198 200 201 202 203 204 205 207 208 209 210 212 213 214 215 216 217
|
vi
SQL Reference
|
|
| |
CEILING . . . . . . . . . . . . CHAR . . . . . . . . . . . . . CLOB . . . . . . . . . . . . . COALESCE . . . . . . . . . . CONCAT . . . . . . . . . . . COS . . . . . . . . . . . . . COSH. . . . . . . . . . . . . DATE . . . . . . . . . . . . . DAY . . . . . . . . . . . . . DAYOFMONTH . . . . . . . . . DAYOFWEEK . . . . . . . . . . DAYOFWEEK_ISO . . . . . . . . DAYOFYEAR . . . . . . . . . . DAYS . . . . . . . . . . . . . DBCLOB . . . . . . . . . . . DECIMAL or DEC . . . . . . . . DECRYPT_BIT, DECRYPT_CHAR, and DEGREES . . . . . . . . . . . DIGITS . . . . . . . . . . . . DOUBLE_PRECISION or DOUBLE . . ENCRYPT_TDES . . . . . . . . EXP . . . . . . . . . . . . . FLOAT . . . . . . . . . . . . FLOOR . . . . . . . . . . . . GENERATE_UNIQUE . . . . . . . GETHINT . . . . . . . . . . . GETVARIABLE . . . . . . . . . GRAPHIC . . . . . . . . . . . HEX . . . . . . . . . . . . . HOUR . . . . . . . . . . . . IDENTITY_VAL_LOCAL . . . . . . IFNULL . . . . . . . . . . . . INSERT . . . . . . . . . . . . INTEGER or INT . . . . . . . . . JULIAN_DAY . . . . . . . . . . LAST_DAY . . . . . . . . . . . LCASE . . . . . . . . . . . . LEFT . . . . . . . . . . . . . LENGTH . . . . . . . . . . . . LN . . . . . . . . . . . . . . LOCATE . . . . . . . . . . . . LOG10 . . . . . . . . . . . . LOWER . . . . . . . . . . . . LTRIM . . . . . . . . . . . . MAX . . . . . . . . . . . . . MICROSECOND . . . . . . . . . MIDNIGHT_SECONDS . . . . . . MIN . . . . . . . . . . . . . MINUTE . . . . . . . . . . . . MOD . . . . . . . . . . . . . MONTH . . . . . . . . . . . . MQPUBLISH . . . . . . . . . . MQREAD . . . . . . . . . . . MQREADCLOB . . . . . . . . . MQRECEIVE . . . . . . . . . . MQRECEIVECLOB . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . DECRYPT_DB . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
218 219 226 228 230 231 232 233 235 236 237 238 239 240 241 244 246 249 250 251 253 255 256 257 258 260 261 263 266 267 268 272 273 276 278 279 280 281 283 284 285 287 288 289 290 291 292 293 294 295 297 298 300 302 304 306
Contents
vii
| | | | |
MQSEND . . . . . MQSUBSCRIBE . . . MQUNSUBSCRIBE . . MULTIPLY_ALT . . . NEXT_DAY . . . . . NULLIF . . . . . . POSSTR . . . . . POWER . . . . . . QUARTER . . . . . RADIANS . . . . . RAISE_ERROR . . . RAND . . . . . . . REAL . . . . . . . REPEAT . . . . . . REPLACE . . . . . RIGHT . . . . . . ROUND . . . . . . ROUND_TIMESTAMP . ROWID . . . . . . RTRIM . . . . . . SECOND . . . . . SIGN . . . . . . . SIN . . . . . . . . SINH . . . . . . . SMALLINT . . . . . SPACE . . . . . . SQRT . . . . . . . STRIP . . . . . . SUBSTR . . . . . . TAN . . . . . . . TANH . . . . . . . TIME . . . . . . . TIMESTAMP . . . . TIMESTAMP_FORMAT TRANSLATE . . . . TRUNCATE . . . . TRUNC_TIMESTAMP . UCASE . . . . . . UPPER . . . . . . VARCHAR . . . . . VARCHAR_FORMAT . VARGRAPHIC . . . WEEK . . . . . . WEEK_ISO . . . . . XML2CLOB . . . . XMLCONCAT . . . . XMLELEMENT . . . XMLFOREST . . . . XMLNAMESPACES . YEAR . . . . . . . Table functions . . . . MQREADALL . . . . MQREADALLCLOB. . MQRECEIVEALL . . MQRECEIVEALLCLOB
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
308 310 312 314 315 317 318 320 321 322 323 324 325 326 328 330 332 334 336 337 338 339 340 341 342 343 344 345 347 349 350 351 352 354 355 358 360 361 362 363 368 369 372 373 374 375 376 378 380 382 383 384 386 388 390
viii
SQL Reference
|
Chapter 4. Queries . . . . . . . . . . . . . Authorization . . . . . . . . . . . . . . . . subselect . . . . . . . . . . . . . . . . . select-clause . . . . . . . . . . . . . . . from-clause . . . . . . . . . . . . . . . . where-clause . . . . . . . . . . . . . . . group-by-clause . . . . . . . . . . . . . . having-clause . . . . . . . . . . . . . . . Examples of subselects . . . . . . . . . . . fullselect . . . . . . . . . . . . . . . . . . Character conversion in unions and concatenations . Selecting the result CCSID . . . . . . . . . . Examples of fullselects . . . . . . . . . . . select-statement . . . . . . . . . . . . . . . common-table-expression . . . . . . . . . . order-by-clause . . . . . . . . . . . . . . fetch-first-clause . . . . . . . . . . . . . . update-clause . . . . . . . . . . . . . . . read-only-clause . . . . . . . . . . . . . . optimize-for-clause . . . . . . . . . . . . . isolation-clause . . . . . . . . . . . . . . queryno-clause . . . . . . . . . . . . . . Examples of select statements . . . . . . . . Chapter 5. Statements . . . . . . . . . . . How SQL statements are invoked . . . . . . . Embedding a statement in an application program Dynamic preparation and execution . . . . . . Static invocation of a SELECT statement . . . . Dynamic invocation of a SELECT statement . . Interactive invocation . . . . . . . . . . . ALLOCATE CURSOR . . . . . . . . . . . . ALTER DATABASE . . . . . . . . . . . . . ALTER FUNCTION (external) . . . . . . . . . ALTER FUNCTION (SQL scalar) . . . . . . . . ALTER INDEX . . . . . . . . . . . . . . ALTER PROCEDURE (external) . . . . . . . . ALTER PROCEDURE (SQL) . . . . . . . . . ALTER SEQUENCE . . . . . . . . . . . . ALTER STOGROUP . . . . . . . . . . . . ALTER TABLE . . . . . . . . . . . . . . ALTER TABLESPACE . . . . . . . . . . . . ALTER VIEW . . . . . . . . . . . . . . . ASSOCIATE LOCATORS . . . . . . . . . . BEGIN DECLARE SECTION . . . . . . . . . CALL . . . . . . . . . . . . . . . . . . CLOSE . . . . . . . . . . . . . . . . . COMMENT . . . . . . . . . . . . . . . . COMMIT . . . . . . . . . . . . . . . . . CONNECT . . . . . . . . . . . . . . . . CREATE ALIAS . . . . . . . . . . . . . . CREATE AUXILIARY TABLE . . . . . . . . . CREATE DATABASE . . . . . . . . . . . . CREATE DISTINCT TYPE . . . . . . . . . . CREATE FUNCTION . . . . . . . . . . . . CREATE FUNCTION (external scalar) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
393 394 395 395 399 406 407 408 408 412 413 413 415 416 417 419 421 421 422 423 423 425 425 427 431 431 432 433 434 434 436 438 441 458 464 479 490 496 501 504 545 557 558 562 563 572 574 581 583 589 591 594 597 604 605
|
|
|
Contents
ix
|
|
|
|
CREATE FUNCTION (external table) . . . CREATE FUNCTION (sourced) . . . . . CREATE FUNCTION (SQL scalar) . . . . CREATE GLOBAL TEMPORARY TABLE . . CREATE INDEX . . . . . . . . . . . CREATE PROCEDURE . . . . . . . . CREATE PROCEDURE (external) . . . . CREATE PROCEDURE (SQL) . . . . . CREATE SEQUENCE . . . . . . . . . CREATE STOGROUP . . . . . . . . . CREATE SYNONYM . . . . . . . . . CREATE TABLE . . . . . . . . . . . CREATE TABLESPACE . . . . . . . . CREATE TRIGGER . . . . . . . . . . CREATE VIEW . . . . . . . . . . . DECLARE CURSOR . . . . . . . . . DECLARE GLOBAL TEMPORARY TABLE . DECLARE STATEMENT . . . . . . . . DECLARE TABLE . . . . . . . . . . DECLARE VARIABLE . . . . . . . . . DELETE . . . . . . . . . . . . . . DESCRIBE (prepared statement or table) . DESCRIBE CURSOR . . . . . . . . . DESCRIBE INPUT . . . . . . . . . . DESCRIBE PROCEDURE . . . . . . . DROP. . . . . . . . . . . . . . . END DECLARE SECTION . . . . . . . EXECUTE . . . . . . . . . . . . . EXECUTE IMMEDIATE . . . . . . . . EXPLAIN . . . . . . . . . . . . . FETCH . . . . . . . . . . . . . . FREE LOCATOR . . . . . . . . . . GET DIAGNOSTICS . . . . . . . . . GRANT . . . . . . . . . . . . . . GRANT (collection privileges) . . . . . . GRANT (database privileges) . . . . . . GRANT (distinct type or JAR privileges) . . GRANT (function or procedure privileges) . GRANT (package privileges) . . . . . . GRANT (plan privileges) . . . . . . . . GRANT (schema privileges) . . . . . . GRANT (sequence privileges) . . . . . . GRANT (system privileges) . . . . . . . GRANT (table or view privileges) . . . . . GRANT (use privileges) . . . . . . . . HOLD LOCATOR . . . . . . . . . . INCLUDE . . . . . . . . . . . . . INSERT . . . . . . . . . . . . . . LABEL . . . . . . . . . . . . . . LOCK TABLE . . . . . . . . . . . . OPEN . . . . . . . . . . . . . . . PREPARE . . . . . . . . . . . . . REFRESH TABLE . . . . . . . . . . RELEASE (connection) . . . . . . . . RELEASE SAVEPOINT . . . . . . . . RENAME . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
628 645 657 667 673 691 692 710 721 729 732 734 772 793 805 812 824 836 837 840 843 851 859 861 863 866 880 881 886 889 903 927 928 941 944 945 947 949 954 957 958 960 961 964 967 969 970 972 986 988 990 995 1011 1013 1016 1017
x
SQL Reference
|
| |
|
|
|
REVOKE . . . . . . . . . . . . . . . . . . . . . . REVOKE (collection privileges) . . . . . . . . . . . . . . REVOKE (database privileges) . . . . . . . . . . . . . . REVOKE (distinct type or JAR privileges) . . . . . . . . . . REVOKE (function or procedure privileges) . . . . . . . . . REVOKE (package privileges) . . . . . . . . . . . . . . REVOKE (plan privileges) . . . . . . . . . . . . . . . . REVOKE (schema privileges) . . . . . . . . . . . . . . REVOKE (sequence privileges) . . . . . . . . . . . . . . REVOKE (system privileges) . . . . . . . . . . . . . . . REVOKE (table or view privileges) . . . . . . . . . . . . . REVOKE (use privileges) . . . . . . . . . . . . . . . . ROLLBACK . . . . . . . . . . . . . . . . . . . . . SAVEPOINT . . . . . . . . . . . . . . . . . . . . . SELECT . . . . . . . . . . . . . . . . . . . . . . SELECT INTO . . . . . . . . . . . . . . . . . . . . SET CONNECTION . . . . . . . . . . . . . . . . . . SET CURRENT APPLICATION ENCODING SCHEME . . . . . SET CURRENT DEGREE . . . . . . . . . . . . . . . . SET CURRENT LOCALE LC_CTYPE . . . . . . . . . . . SET CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION SET CURRENT OPTIMIZATION HINT . . . . . . . . . . . SET CURRENT PACKAGE PATH . . . . . . . . . . . . . SET CURRENT PACKAGESET . . . . . . . . . . . . . . SET CURRENT PRECISION . . . . . . . . . . . . . . . SET CURRENT REFRESH AGE . . . . . . . . . . . . . SET CURRENT RULES . . . . . . . . . . . . . . . . SET CURRENT SQLID . . . . . . . . . . . . . . . . . SET ENCRYPTION PASSWORD . . . . . . . . . . . . . SET host-variable assignment . . . . . . . . . . . . . . SET PATH . . . . . . . . . . . . . . . . . . . . . SET SCHEMA . . . . . . . . . . . . . . . . . . . . SET transition-variable assignment . . . . . . . . . . . . SIGNAL SQLSTATE . . . . . . . . . . . . . . . . . . UPDATE . . . . . . . . . . . . . . . . . . . . . . VALUES . . . . . . . . . . . . . . . . . . . . . . VALUES INTO . . . . . . . . . . . . . . . . . . . . WHENEVER . . . . . . . . . . . . . . . . . . . . . Chapter 6. SQL control statements . . . . . . References to SQL parameters and SQL variables . SQL-procedure-statement . . . . . . . . . . assignment-statement . . . . . . . . . . . CALL statement . . . . . . . . . . . . . . CASE statement . . . . . . . . . . . . . compound-statement . . . . . . . . . . . . GET DIAGNOSTICS statement . . . . . . . . GOTO statement . . . . . . . . . . . . . IF statement . . . . . . . . . . . . . . . ITERATE statement . . . . . . . . . . . . LEAVE statement . . . . . . . . . . . . . LOOP statement . . . . . . . . . . . . . REPEAT statement . . . . . . . . . . . . RESIGNAL statement . . . . . . . . . . . RETURN statement . . . . . . . . . . . . SIGNAL statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
1020 1025 1026 1029 1031 1036 1038 1039 1041 1043 1046 1049 1051 1054 1056 1057 1060 1062 1063 1065 1067 1069 1070 1074 1076 1077 1079 1080 1082 1084 1087 1090 1093 1096 1097 1108 1109 1111 1113 1114 1115 1116 1118 1120 1122 1127 1128 1130 1131 1132 1133 1134 1135 1138 1140
|
|
| | |
Contents
xi
WHILE statement . . . . . . . . . . . . . . . . . . . . . . . 1143 Appendix A. Limits in DB2 UDB for z/OS . . . . . . . . . . . . . 1145
Appendix B. Reserved schema names and reserved words . . . . . . 1151 Reserved schema names . . . . . . . . . . . . . . . . . . . . 1151 Reserved words . . . . . . . . . . . . . . . . . . . . . . . 1151 Appendix C. Characteristics of SQL statements in DB2 UDB for z/OS 1155 Actions allowed on SQL statements . . . . . . . . . . . . . . . . 1155 SQL statements allowed in external functions and stored procedures . . . . 1158 SQL statements allowed in SQL procedures . . . . . . . . . . . . . 1160 Appendix D. SQL communication Description of SQLCA fields . . . The included SQLCA . . . . . . The REXX SQLCA . . . . . . area . . . . . . (SQLCA) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1165 1165 1168 1170 1173 1174 1174 1175 1183 1183 1189 1189 1191 1192 1196 1197 1198 1200 1208 1209 1211 1212 1213 1214 1216 1217 1218 1219 1220 1221 1222 1223 1224 1225 1226 1232 1234 1235 1239 1241 1242
Appendix E. SQL descriptor area (SQLDA) . Field descriptions . . . . . . . . . . . The SQLDA Header . . . . . . . . . SQLVAR entries . . . . . . . . . . Unrecognized and unsupported SQLTYPES . The included SQLDA . . . . . . . . . . Identifying an SQLDA in C or C++ . . . . . The REXX SQLDA . . . . . . . . . . Appendix F. DB2 catalog tables . . . Table spaces and indexes . . . . . . Catalog table space buffer pools . . SQL statements allowed on the catalog Reorganizing the catalog . . . . . New and changed catalog tables . . . SYSIBM.IPLIST table . . . . . . . SYSIBM.IPNAMES table . . . . . . SYSIBM.LOCATIONS table . . . . . SYSIBM.LULIST table . . . . . . . SYSIBM.LUMODES table . . . . . . SYSIBM.LUNAMES table . . . . . . SYSIBM.MODESELECT table . . . . SYSIBM.SYSAUXRELS table . . . . SYSIBM.SYSCHECKDEP table . . . . SYSIBM.SYSCHECKS table . . . . . SYSIBM.SYSCHECKS2 table . . . . SYSIBM.SYSCOLAUTH table . . . . SYSIBM.SYSCOLDIST table . . . . . SYSIBM.SYSCOLDISTSTATS table . . SYSIBM.SYSCOLDIST_HIST table . . SYSIBM.SYSCOLSTATS table . . . . SYSIBM.SYSCOLUMNS table . . . . SYSIBM.SYSCOLUMNS_HIST table . . SYSIBM.SYSCONSTDEP table . . . . SYSIBM.SYSCOPY table . . . . . . SYSIBM.SYSDATABASE table . . . . SYSIBM.SYSDATATYPES table . . . . SYSIBM.SYSDBAUTH table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
|
|
xii
SQL Reference
SYSIBM.SYSDBRM table . . . . . . SYSIBM.SYSDUMMY1 table . . . . . SYSIBM.FIELDS table . . . . . . . SYSIBM.SYSFOREIGNKEYS table . . SYSIBM.SYSINDEXES table . . . . . SYSIBM.SYSINDEXES_HIST table . . SYSIBM.SYSINDEXPART table . . . . SYSIBM.SYSINDEXPART_HIST table . SYSIBM.SYSINDEXSTATS table . . . SYSIBM.SYSINDEXSTATS_HIST table . SYSIBM.SYSJARCLASS_SOURCE table SYSIBM.SYSJARCONTENTS table . . SYSIBM.SYSJARDATA table . . . . . SYSIBM.SYSJAROBJECTS table . . . SYSIBM.SYSJAVAOPTS table . . . . SYSIBM.SYSKEYCOLUSE table . . . SYSIBM.SYSKEYS table . . . . . . SYSIBM.SYSLOBSTATS table . . . . SYSIBM.SYSLOBSTATS_HIST table . . SYSIBM.SYSPACKAGE table . . . . SYSIBM.SYSPACKAUTH table . . . . SYSIBM.SYSPACKDEP table . . . . SYSIBM.SYSPACKLIST table . . . . SYSIBM.SYSPACKSTMT table . . . . SYSIBM.SYSPARMS table . . . . . SYSIBM.SYSPKSYSTEM table . . . . SYSIBM.SYSPLAN table . . . . . . SYSIBM.SYSPLANAUTH table . . . . SYSIBM.SYSPLANDEP table . . . . SYSIBM.SYSPLSYSTEM table . . . . SYSIBM.SYSRELS table . . . . . . SYSIBM.SYSRESAUTH table . . . . SYSIBM.SYSROUTINEAUTH table . . SYSIBM.SYSROUTINES table . . . . SYSIBM.SYSROUTINES_OPTS table . SYSIBM.SYSROUTINES_SRC table . . SYSIBM.SYSSCHEMAAUTH table . . SYSIBM.SYSSEQUENCEAUTH table . SYSIBM.SYSSEQUENCES table . . . SYSIBM.SYSSEQUENCESDEP table . SYSIBM.SYSSTMT table . . . . . . SYSIBM.SYSSTOGROUP table . . . . SYSIBM.SYSSTRINGS table . . . . . SYSIBM.SYSSYNONYMS table . . . . SYSIBM.SYSTABAUTH table . . . . SYSIBM.SYSTABCONST table . . . . SYSIBM.SYSTABLEPART table . . . . SYSIBM.SYSTABLEPART_HIST table . SYSIBM.SYSTABLES table . . . . . SYSIBM.SYSTABLESPACE table . . . SYSIBM.SYSTABLES_HIST table . . . SYSIBM.SYSTABSTATS table . . . . SYSIBM.SYSTABSTATS_HIST table . . SYSIBM.SYSTRIGGERS table . . . . SYSIBM.SYSUSERAUTH table . . . . SYSIBM.SYSVIEWDEP table . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
1244 1245 1246 1247 1248 1252 1253 1256 1258 1259 1260 1261 1262 1263 1264 1265 1266 1267 1268 1269 1275 1276 1277 1278 1281 1283 1284 1288 1289 1290 1291 1292 1293 1294 1301 1302 1303 1304 1305 1307 1308 1311 1312 1314 1315 1317 1318 1322 1324 1328 1331 1332 1333 1334 1335 1338
Contents
xiii
SYSIBM.SYSVIEWS table . . . . . . . . . . . . . . . . . . . . 1339 SYSIBM.SYSVOLUMES table . . . . . . . . . . . . . . . . . . 1341 SYSIBM.USERNAMES table . . . . . . . . . . . . . . . . . . . 1342 Appendix G. Using the catalog in database design . . . Retrieving catalog information about DB2 storage groups . . Retrieving catalog information about a table . . . . . . . Retrieving catalog information about partition order. . . . . Retrieving catalog information about aliases . . . . . . . Retrieving catalog information about columns . . . . . . . Retrieving catalog information about indexes . . . . . . . Retrieving catalog information about views . . . . . . . . Retrieving catalog information about authorizations. . . . . Retrieving catalog information about parent keys . . . . . Retrieving catalog information about foreign keys . . . . . Retrieving catalog information about check pending . . . . Retrieving catalog information about check constraints . . . Retrieving catalog information about LOBs . . . . . . . . Retrieving catalog information about user-defined functions and procedures . . . . . . . . . . . . . . . . . . Retrieving catalog information about triggers . . . . . . . Retrieving catalog information about sequences . . . . . . Adding and retrieving comments . . . . . . . . . . . Verifying the accuracy of the database definition . . . . . Appendix H. Sample user-defined ALTDATE . . . . . . . . . . ALTTIME . . . . . . . . . . CURRENCY . . . . . . . . . DAYNAME . . . . . . . . . MONTHNAME . . . . . . . . TABLE_LOCATION . . . . . . TABLE_NAME . . . . . . . . TABLE_SCHEMA . . . . . . . WEATHER . . . . . . . . . functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . stored . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1343 1343 1343 1343 1344 1344 1345 1345 1345 1346 1346 1347 1347 1347 1348 1348 1348 1349 1349 1351 1352 1355 1357 1359 1360 1361 1363 1365 1367
|
|
Notices . . . . . . . . . . . . . . . . . . . . . . . . . . 1369 Programming interface information . . . . . . . . . . . . . . . . . 1370 Trademarks . . . . . . . . . . . . . . . . . . . . . . . . . 1371 Glossary . . . . . . . . . . . . . . . . . . . . . . . . . . 1373 Bibliography . . . . . . . . . . . . . . . . . . . . . . . . 1407
Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . X-1 .
xiv
SQL Reference
About this bookThis book is a reference for Structured Query Language (SQL) for DB2 Universal Database for z/OS. Unless otherwise stated, references to SQL in this book imply SQL for DB2 UDB for z/OS, and all objects described in this book are objects of DB2 UDB for z/OS. The syntax and semantics of most SQL statements are essentially the same in all IBM relational database products, and the language elements common to the products provide a base for the definition of IBM SQL. Consult IBM DB2 Universal Database SQL Reference for Cross-Platform Development if you intend to develop applications that adhere to IBM SQL.
Important In this version of DB2 UDB for z/OS, the DB2 Utilities Suite is available as an optional product. You must separately order and purchase a license to such utilities, and discussion of those utility functions in this publication is not intended to otherwise imply that you have a license to them. See Part 1 of DB2 Utility Guide and Reference for packaging details.
Visit the following Web site for information about ordering DB2 books and obtaining other valuable information about DB2 UDB for z/OS: http://www.ibm.com/software/data/db2/zos/library.html
Who should read this bookThis book is intended for end users, application programmers, system and database administrators, and for persons involved in error detection and diagnosis. This book is a reference rather than a tutorial. It assumes that you are already familiar with SQL programming concepts. When you first use this book, consider reading Chapters 1 and 2 sequentially. These chapters describe the basic concepts of relational databases and SQL, the basic syntax of SQL, and the language elements that are common to many SQL statements. The rest of the chapters and appendixes are designed for the quick location of answers to specific SQL questions. They provide you with query forms, SQL statements, SQL procedure statements, DB2 limits, SQLCA, SQLDA, catalog tables, and SQL reserved words.
Conventions and terminology used in this bookThis section explains conventions and terminology used in this book.
Terminology and citationsIn this information, DB2 Universal Database for z/OS is referred to as "DB2 UDB for z/OS." In cases where the context makes the meaning clear, DB2 UDB for z/OS is referred to as "DB2." When this information refers to titles of books in this library, a short title is used. (For example, "See DB2 SQL Reference" is a citation to IBM DB2 Universal Database for z/OS SQL Reference.)
Copyright IBM Corp. 1982, 2004
xv
When referring to a DB2 product other than DB2 UDB for z/OS, this information uses the products full name to avoid ambiguity. The following terms are used as indicated: DB2 Represents either the DB2 licensed program or a particular DB2 subsystem.
DB2 PM Refers to the DB2 Performance Monitor tool, which can be used on its own or as part of the DB2 Performance Expert for z/OS product. C, C++, and C language Represent the C or C++ programming language. CICS Represents CICS Transaction Server for z/OS or CICS Transaction Server for OS/390. IMS MVS
Represents the IMS Database Manager or IMS Transaction Manager. Represents the MVS element of the z/OS operating system, which is equivalent to the Base Control Program (BCP) component of the z/OS operating system. Represents the functions that are provided by the RACF component of the z/OS Security Server.
RACF
Conventions for describing mixed data valuesAt sites using a double-byte character set (DBCS), character strings can include a mixture of single-byte and double-byte characters. When mixed data values are shown in the examples, the conventions shown in Figure 1 apply:
Figure 1. Conventions used when mixed data values are shown in examples
| | | | | | | |
Industry standardsDB2 UDB for z/OS is consistent with the following industry standards for SQL: v Information technology - Database languages - SQL- Part 1: Framework (SQL/Framework) ISO/IEC 9075-1:1999 v Information technology - Database languages - SQL- Part 2: Foundation (SQL/Foundation) ISO/IEC 9075-2:1999 v Information technology - Database languages - SQL- Part 4: Persistent Stored Modules (SQL/PSM) ISO/IEC 9075-4:1999
xvi
SQL Reference
| | | | | | | | | | |
v Information technology - Database languages - SQL- Part 5: Host Language Bindings (SQL/Bindings) ISO/IEC 9075-5:1999 v Information technology - Database languages - SQL- Part 10: Object Language (SQL/OLB) ISO/IEC 9075-10:2000 v Information technology - Database languages - SQL- Part 13: SQL Routines and Types Using the Java Programming Language (SQL/JRT) ISO/IEC 9075-13:2002 v Information technology - Database languages - SQL- Part 14: XML-Related Specifications (SQL/XML) ISO/IEC 9075-14:2003 v ANSI (American National Standards Institute) X3.135-1999, Database Language SQL
How to read the syntax diagramsThe following rules apply to the syntax diagrams that are used in this book: v 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. v Required items appear on the horizontal line (the main path).required_item
v 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
v 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
About this book
xvii
If one of the items is the default, it appears above the main path and the remaining choices are shown below.default_choice required_item optional_choice optional_choice
v An arrow returning to the left, above the main line, indicates an item that can be repeated.
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 repeat the items in the stack. v 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. v If punctuation marks, parentheses, arithmetic operators, or other such symbols are shown, you must enter them as part of the syntax.
AccessibilityAccessibility features help a user who has a physical disability, such as restricted mobility or limited vision, to use software products. The major accessibility features in z/OS products, including DB2 UDB for z/OS, enable users to: v Use assistive technologies such as screen reader and screen magnifier software v Operate specific or equivalent features by using only a keyboard v Customize display attributes such as color, contrast, and font size Assistive technology products, such as screen readers, function with the DB2 UDB for z/OS user interfaces. Consult the documentation for the assistive technology products for specific information when you use assistive technology to access these interfaces. Online documentation for Version 8 of DB2 UDB for z/OS is available in the DB2 Information Center, which is an accessible format when used with assistive technologies such as screen reader or screen magnifier software. The DB2 Information Center for z/OS solutions is available at the following Web site: http://publib.boulder.ibm.com/infocenter/db2zhelp.
xviii
SQL Reference
How to send your commentsYour feedback helps IBM to provide quality information. Please send any comments that you have about this book or other DB2 UDB for z/OS documentation. You can use the following methods to provide comments: v Send your comments by e-mail to [email protected] and include the name of the product, the version number of the product, and the number of the book. If you are commenting on specific text, please list the location of the text (for example, a chapter and section title, page number, or a help topic title). v You can also send comments from the Web. Visit the library Web site at: www.ibm.com/software/db2zos/library.html This Web site has a feedback page that you can use to send comments. v Print and fill out the reader comment form located at the back of this book. You can give the completed form to your local IBM branch office or IBM representative, or you can send it to the address printed on the reader comment form.
About this book
xix
xx
SQL Reference
Summary of changes to this bookThe major changes to this book are: Chapter 1, DB2 concepts includes new descriptions of materialized query tables, referential constraints, and mixed-byte character strings. Chapter 2, Language elements contains numerous changes to descriptions of naming conventions, assignment and comparison, constants, special registers (including several new special registers), host variable arrays, functions, expressions (including the CAST specification and new expressions scalar fullselect, NEXT VALUE, and PREVIOUS VALUE), and predicates (including the new predicate DISTINCT). Chapter 3, Functions includes changes to many column, scalar, and table functions and addition of several new functions. Also, the term aggregate functions is now used to refer to the functions that were previously called column functions. The new functions are: v DECRYPT_BIT, DECRYPT_CHAR, and DECRYPT_DB on page 246 v ENCRYPT_TDES on page 253 v GETHINT on page 260 v GETVARIABLE on page 261 v XML2CLOB on page 374 v XMLAGG on page 205 v XMLCONCAT on page 375 v XMLELEMENT on page 376 v XMLFOREST on page 378 v XMLNAMESPACES on page 380 For a list and brief description of all the functions, see Table 42 on page 189. Chapter 4, Queries includes changes for subselect and select-statement. For subselect, the description of the select list is enhanced, the GROUP BY clause is changed to allow expressions, and the FROM clause is expanded to support specifying a cardinality when using a table function and specifying an INSERT statement, which allows selecting values from rows that are being inserted. For select-statement, the description is changed to include support of nested table expressions. Chapter 5. Statements includes new statements, as well as changed statements. The new statements are: v ALTER SEQUENCE on page 496 v ALTER VIEW on page 557 v CREATE SEQUENCE on page 721 v GET DIAGNOSTICS on page 928 v GRANT (sequence privileges) on page 960 v REFRESH TABLE on page 1011 v REVOKE (sequence privileges) on page 1041 v SET CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION on page 1067 v SET CURRENT PACKAGE PATH on page 1070 v SET CURRENT REFRESH AGE on page 1077 v SET SCHEMA on page 1090
Copyright IBM Corp. 1982, 2004
xxi
Statements with new clauses, new values for existing clauses, or other changes include: v ALTER FUNCTION (external) on page 441 v ALTER FUNCTION (SQL scalar) on page 458 v ALTER INDEX on page 464 v ALTER PROCEDURE (external) on page 479 v ALTER PROCEDURE (SQL) on page 490 v ALTER TABLE on page 504 v ALTER TABLESPACE on page 545 v COMMENT on page 574 v CREATE AUXILIARY TABLE on page 591 v CREATE DISTINCT TYPE on page 597 v CREATE FUNCTION (external scalar) on page 605 v CREATE FUNCTION (external table) on page 628 v CREATE INDEX on page 673 v CREATE PROCEDURE (external) on page 692 v CREATE PROCEDURE (SQL) on page 710 v CREATE TABLE on page 734 v CREATE TABLESPACE on page 772 v DECLARE CURSOR on page 812 v DELETE on page 843 v DROP on page 866 v EXECUTE on page 881 v EXECUTE IMMEDIATE on page 886 v EXPLAIN on page 889 v FETCH on page 903 v INSERT on page 972 v LOCK TABLE on page 988 v PREPARE on page 995 v REVOKE (table or view privileges) on page 1046 v SELECT INTO on page 1057 v SET CURRENT PACKAGESET on page 1074 v SET PATH on page 1087 v UPDATE on page 1097 Chapter 6, SQL control statements includes changes to statements that can be used in SQL procedures and addition of new statements. The new statements that can be used in SQL procedures are: v ITERATE statement on page 1131 v RESIGNAL statement on page 1135 v RETURN statement on page 1138 v SIGNAL statement on page 1140 Appendix F, DB2 catalog tables includes descriptions of new and changed catalog tables. (See New and changed catalog tables on page 1200 for a summary of all catalog table changes.)
xxii
SQL Reference
Chapter 1. DB2 conceptsStructured query language . . . . . . . . . . . . . . . . . . . Static SQL . . . . . . . . . . . . . . . . . . . . . . . . Dynamic SQL . . . . . . . . . . . . . . . . . . . . . . . Deferred embedded SQL . . . . . . . . . . . . . . . . . . Interactive SQL . . . . . . . . . . . . . . . . . . . . . . SQL Call Level Interface (CLI) and Open Database Connectivity (ODBC) . Java database connectivity and embedded SQL for Java . . . . . . . Schemas . . . . . . . . . . . . . . . . . . . . . . . . . Tables . . . . . . . . . . . . . . . . . . . . . . . . . . Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . Keys . . . . . . . . . . . . . . . . . . . . . . . . . . . Unique keys . . . . . . . . . . . . . . . . . . . . . . . Primary keys . . . . . . . . . . . . . . . . . . . . . . . Parent keys . . . . . . . . . . . . . . . . . . . . . . . Foreign keys . . . . . . . . . . . . . . . . . . . . . . . Constraints . . . . . . . . . . . . . . . . . . . . . . . . . Unique constraints . . . . . . . . . . . . . . . . . . . . . Referential constraints. . . . . . . . . . . . . . . . . . . . Check constraints . . . . . . . . . . . . . . . . . . . . . Triggers . . . . . . . . . . . . . . . . . . . . . . . . . Storage structures . . . . . . . . . . . . . . . . . . . . . . Storage groups . . . . . . . . . . . . . . . . . . . . . . . Databases . . . . . . . . . . . . . . . . . . . . . . . . Catalog. . . . . . . . . . . . . . . . . . . . . . . . . . Views . . . . . . . . . . . . . . . . . . . . . . . . . . Sequences . . . . . . . . . . . . . . . . . . . . . . . . Routines . . . . . . . . . . . . . . . . . . . . . . . . . Functions . . . . . . . . . . . . . . . . . . . . . . . . Stored procedures . . . . . . . . . . . . . . . . . . . . Application processes, concurrency, and recovery . . . . . . . . . . Locking, commit, and rollback . . . . . . . . . . . . . . . . Unit of work . . . . . . . . . . . . . . . . . . . . . . . Unit of recovery . . . . . . . . . . . . . . . . . . . . . Rolling back work . . . . . . . . . . . . . . . . . . . . . Rolling back all changes . . . . . . . . . . . . . . . . . Rolling back selected changes using savepoints . . . . . . . . Packages and application plans . . . . . . . . . . . . . . . . . Distributed data. . . . . . . . . . . . . . . . . . . . . . . Connections . . . . . . . . . . . . . . . . . . . . . . . Distributed unit of work . . . . . . . . . . . . . . . . . . . Connection management . . . . . . . . . . . . . . . . . SQL connection states . . . . . . . . . . . . . . . . . . Application process connection states . . . . . . . . . . . . When a connection is ended . . . . . . . . . . . . . . . . Remote unit of work . . . . . . . . . . . . . . . . . . . . Connection management . . . . . . . . . . . . . . . . . Character conversion . . . . . . . . . . . . . . . . . . . . Character sets and code pages . . . . . . . . . . . . . . . . Coded character sets and CCSIDs . . . . . . . . . . . . . . Determining the encoding scheme and CCSID of a string . . . . . . Expanding conversions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 . 3 . 3 . 3 . 4 . 4 . 4 . 4 . 5 . 6 . 6 . 6 . 7 . 7 . 7 . 7 . 8 . 8 . 10 . 11 . 11 . 12 . 12 . 12 . 12 . 13 . 14 . 14 . 14 . 15 . 15 . 15 . 16 . 16 . 16 . 17 . 17 . 18 . 18 . 19 . 19 . 20 . 21 . 22 . 22 . 22 . 24 . 27 . 29 . 29 . 32
|
| | | | |
| |
Copyright IBM Corp. 1982, 2004
1
DB2 conceptsContracting conversions . . . . . . . . . . . . . . . . . . . . 32
2
SQL Reference
DB2 concepts
Structured query languageStructured query language (SQL) is a standardized language for defining and manipulating data in a relational database. In accordance with the relational model of data, the database is perceived as a set of tables, relationships are represented by values in tables, and data is retrieved by specifying a result table that can be derived from one or more tables. DB2 UDB for z/OS transforms the specification of a result table into a sequence of internal operations that optimize data retrieval. This transformation occurs when the SQL statement is prepared. This transformation is also known as binding. All executable SQL statements must be prepared before they can be executed. The result of preparation is the executable or operational form of the statement. The method of preparing an SQL statement and the persistence of its operational form distinguish static SQL from dynamic SQL.
Static SQLThe source form of a static SQL statement is embedded within an application program written in a host language such as COBOL. The statement is prepared before the program is executed and the operational form of the statement persists beyond the execution of the program. Static SQL statements in a source program must be processed before the program is compiled. This processing can be accomplished through the DB2 precompiler or the SQL statement coprocessor. The DB2 precompiler or the coprocessor checks the syntax of the SQL statements, turns them into host language comments, and generates host language statements to invoke DB2. The preparation of an SQL application program includes precompilation, the preparation of its static SQL statements, and compilation of the modified source program, as described in Part 5 of DB2 Application Programming and SQL Guide.
Dynamic SQLPrograms that contain embedded dynamic SQL statements must be precompiled like those that contain static SQL, but unlike static SQL, the dynamic statements are constructed and prepared at run time. The source form of a dynamic statement is a character string that is passed to DB2 by the program using the static SQL PREPARE or EXECUTE IMMEDIATE statement. A statement that is prepared using the PREPARE statement can be referenced in a DECLARE CURSOR, DESCRIBE, or EXECUTE statement. Whether the operational form of the statement is persistent depends on whether dynamic statement caching is enabled. For details on dynamic statement caching, see Part 6 of DB2 Application Programming and SQL Guide. SQL statements embedded in a REXX application are dynamic SQL statements. SQL statements submitted to an interactive SQL facility and to the CALL Level Interface (CLI) are also dynamic SQL.
Deferred embedded SQLA deferred embedded SQL statement is neither fully static nor fully dynamic. Like a static statement, it is embedded within an application, but like a dynamic statement, it is prepared during the execution of the application. Although prepared at run time, a deferred embedded SQL statement is processed with bind-time rules such that
Chapter 1. DB2 concepts
3
DB2 conceptsthe authorization ID and qualifier determined at bind time for the plan or package owner are used. Deferred embedded SQL statements are used for DB2 private protocol access to remote data.
Interactive SQLIn this book, interactive SQL refers to SQL statements submitted to SPUFI (SQL processor using file input). SPUFI prepares and executes these statements dynamically. For more details about using SPUFI, see Part 1 of DB2 Application Programming and SQL Guide.
SQL Call Level Interface (CLI) and Open Database Connectivity (ODBC)The DB2 Call Level Interface (CLI) is an application programming interface in which functions are provided to application programs to process dynamic SQL statements. DB2 CLI allows users to access SQL functions directly through a call interface. CLI programs can also be compiled using an Open Database Connectivity (ODBC) Software Developers Kit, available from Microsoft or other vendors, enabling access to ODBC data sources. Unlike using embedded SQL, no precompilation is required. Applications developed using this interface can be executed on a variety of databases without being compiled against each of databases. Through the interface, applications use procedure calls at execution time to connect to databases, to issue SQL statements, and to get returned data and status information. The DB2 ODBC Guide and Reference describes the APIs supported with this interface.
Java database connectivity and embedded SQL for JavaDB2 provides two standards-based Java programming APIs: Java Database Connectivity (JDBC) and embedded SQL for Java (SQLJ). Both can be used to create Java applications and applets that access DB2. Static SQL cannot be used by JDBC. SQLJ applications use JDBC as a foundation for such tasks as connecting to databases and handling SQL errors, but can contain embedded static SQL statements in the SQLJ source files. An SQLJ file has to be translated with the SQLJ translator before the resulting Java source code can be compiled. The DB2 Application Programming Guide and Reference for Java describes the APIs supported with these interfaces.
SchemasA schema is a collection of named objects. The objects that a schema can contain include distinct types, functions, stored procedures, sequences, and triggers. An object is assigned to a schema when it is created. The schema name of the object determines the schema to which the object belongs. When a distinct type, function, sequence, or trigger is created, it is given a qualified, two-part name. The first part is the schema name (or the qualifier), which is either implicitly or explicitly specified. The second part is the name of the object. When a stored procedure is created, it is given a three-part name. The first part is a location name, which is implicitly or explicitly specified, the second part is the schema name, which is implicitly or explicitly specified, and the third part is the name of the object.
4
SQL Reference
DB2 conceptsSchemas extend the concept of qualifiers for tables, views, indexes, and aliases to enable the qualifiers for distinct types, functions, stored procedures, sequences, and triggers to be called schema names.
TablesTables are logical structures maintained by DB2. Tables are made up of columns and rows. There is no inherent order of the rows within a table. At the intersection of every column and row is a specific data item called a value. A column is a set of values of the same type. A row is a sequence of values such that the nth value is a value of the nth column of the table. Every table must have one or more columns, but the number of rows can be zero. Some types of tables include: base table A table created with the SQL statement CREATE TABLE and used to hold persistent user data. auxiliary table A table created with the SQL statement CREATE AUXILIARY TABLE and used to hold the data for a column that is defined in a base table. temporary table A table defined by either the SQL statement CREATE GLOBAL TEMPORARY TABLE (a created temporary table) or DECLARE GLOBAL TEMPORARY TABLE (a declared temporary table) and used to hold data temporarily, such as the intermediate results of SQL transactions. Both created temporary tables and declared temporary tables persist only as long as the application process. The description of a created temporary table is stored in the DB2 catalog and the description is shareable across application processes while the description of a declared temporary table is neither stored nor shareable. Thus, each application process might refer to the same declared temporary table but have its own unique description of it. For a complete comparison of the two types of temporary tables, including how they differ from base tables, see Part 2 (Volume 1) of DB2 Administration Guide. | | | | | | | | | | | | | | | | | | | materialized query table A table created with the SQL statement CREATE TABLE and used to contain materialized data that is derived from one or more source tables specified by a fullselect. A source table is a base table, view, table expression, or user-defined table function. The fullselect specifies the queries that are used to refresh the materialized query table and to keep the data in the materialized query table synchronized with the data in the source tables from which the materialized query table was derived. The fullselect is used to determine the column definitions of a materialized query table and is executed against the source tables to populate the materialized query table. A materialized query table can be either user-maintained or system-maintained. A user-maintained materialized query table can be updated by the user with INSERT, UPDATE, DELETE, and REFRESH TABLE statements and with the LOAD utility. A system-maintained materialized query table can be updated only through the REFRESH TABLE statement. Materialized query tables can be used to improve the performance of dynamic SQL queries. If the database manager determines that a portion ofChapter 1. DB2 concepts
5
DB2 concepts| | | | | a query could be resolved using a materialized query table, the query may be rewritten by the database manager to use the materialized query table. This decision is based in part on the settings of the CURRENT REFRESH AGE and the CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION special registers. result table A set of rows that DB2 selects or generates from one or more tables or views. empty table A table with zero rows. sample table One of several tables sent with the DB2 licensed program that contains sample data. Many examples in this book are based on sample tables. See Appendix A of DB2 Application Programming and SQL Guide for a description of the sample tables.
IndexesAn index is an ordered set of pointers to rows of a base table or an auxiliary table. Each index is based on the values of data in one or more columns. An index is an object that is separate from the data in the table. When you define an index using the CREATE INDEX statement, DB2 builds this structure and maintains it automatically. Indexes can be used by DB2 to improve performance and ensure uniqueness. In most cases, access to data is faster with an index. A table with a unique index cannot have rows with identical keys. For more details on designing indexes and on their uses, see The Official Introduction to DB2 UDB for z/OS.
KeysA key is one or more columns that are identified as such in the description of a table, an index, or a referential constraint. Referential constraints are described in Referential constraints on page 8. The same column can be part of more than one key. A composite key is an ordered set of two or more columns of the same table. The ordering of the columns is not constrained by their ordering within the table. The term value, when used with respect to a composite key, denotes a composite value. Thus, a rule, such as the value of the foreign key must be equal to the value of the parent key, means that each component of the value of the foreign key must be equal to the corresponding component of the value of the parent key.
Unique keysA unique key is a key that is constrained so that no two of its values are equal. DB2 enforces the constraint during the execution of the LOAD utility and the SQL INSERT and UPDATE statements. The mechanism used to enforce the constraint is a unique index. Thus, every unique key is a key of a unique index. Such an index is also said to have the UNIQUE attribute. The columns of a unique key cannot contain null values. A unique key can be defined using the UNIQUE clause of the CREATE TABLE or ALTER TABLE statement. When a unique key is defined in a CREATE TABLE
6
SQL Reference
DB2 conceptsstatement, the table is marked unavailable until the unique index is created by the user. However, if the CREATE TABLE statement is processed by the schema processor, DB2 automatically creates the unique index. When a unique key is defined in an ALTER TABLE statement, a unique index must already exist on the columns of that unique key.
Primary keysA primary key is a unique key that is a part of the definition of a table. A table can have only one primary key, and the columns of a primary key cannot contain null values. Primary keys are optional and can be defined in CREATE TABLE or ALTER TABLE statements. The unique index on a primary key is called a primary index. When a primary key is defined in a CREATE TABLE statement, the table is marked unavailable until the primary index is created by the user unless the CREATE TABLE statement is processed by the schema processor. In that case, DB2 automatically creates the primary index. When a primary key is defined in an ALTER TABLE statement, a unique index must already exist on the columns of that primary key. This unique index is designated as the primary index.
Parent keysA parent key is either a primary key or a unique key in the parent table of a referential constraint. The values of a parent key determine the valid values of the foreign key in the constraint.
Foreign keysA foreign key is a key that is specified in the definition of a referential constraint using the CREATE or ALTER statement. A foreign key refers to or is related to a specific parent key. A table can have zero or more foreign keys. The value of a composite foreign key is null if any component of the value is null.
ConstraintsConstraints are rules that control values in columns to prevent duplicate values or set restrictions on data added to a table. Constraints fall into the following three types: v A unique constraint is a rule that prevents duplicate values in one or more columns in a table. Unique constraints are unique and primary keys. For example, a unique constraint could be defined on a supplier identifier in a supplier table to ensure that the same supplier identifier is not given to two suppliers. v A referential constraint is a rule about values in one or more columns in one or more tables. For example, a set of tables shares information about a corporations suppliers. Occasionally, a suppliers ID changes. You can define a referential constraint stating that the ID of the supplier in the table must match a supplier ID in the supplier information. This constraint prevents insert, update, or delete operations that would otherwise result in missing supplier information. v A check constraint sets restrictions on data added to a specific table. For example, the constraint could be added to define a salary level for an employee to never be less than a stated amount when salary data is added or updated in a table for personnel information.Chapter 1. DB2 concepts
7
DB2 concepts
Unique constraintsA unique constraint is a rule that the values of a key are valid only if they are unique in a table. Unique constraints are optional and can be defined in the CREATE TABLE or ALTER TABLE statements with the PRIMARY KEY clause or the UNIQUE clause. The columns specified in a unique constraint must be defined as NOT NULL. A unique index enforces the uniqueness of the key during changes to the columns of the unique constraint. A table can have an arbitrary number of unique constraints, with at most one unique constraint defined as a primary key. A table cannot have more than one unique constraint on the same set of columns. A unique constraint that is referenced by the foreign key of a referential constraint is called the parent key.
Referential constraintsReferential integrity is the state in which all values of all foreign keys at a given DB2 are valid. A referential constraint is the rule that the nonnull values of a foreign key are valid only if they also appear as values of its parent key. The table that contains the parent key is called the parent table of the referential constraint, and the table that contains the foreign key is a dependent of that table. Referential constraints are optional and can be defined using CREATE TABLE and ALTER TABLE statements. Refer to Part 2 (Volume 1) of DB2 Administration Guide for examples. | | | | | | | | | | | | | | | | Referential constraints between base tables are also an important factor in determining whether a materialized query table can be used for a query. For instance, in a data warehouse environment, data is usually extracted from other sources, transformed, and loaded into data warehouse tables. In such an environment, the referential integrity constraints can be maintained and enforced by other means than the database manager to avoid the overhead of enforcing them by DB2. However, referential constraints between base tables in materialized query table definitions are important in a query rewrite to determine whether or not a materialized query table can be used in answering a query. In such cases, you can use informational referential constraints to declare a referential constraint to be true to allow DB2 to take advantage of the referential constraints in the query rewrite. DB2 allows the user application to enforce informational referential constraints, while it ignores the informational referential constraints for inserting, updating, deleting, and using the LOAD and CHECK DATA utilities. Thus, the overhead of DB2 enforcement of referential integrity is avoided and more queries can qualify for automatic query rewrite using materialized query tables. DB2 enforces referential constraints when: v An INSERT statement is applied to a dependent table. v An UPDATE statement is applied to a foreign key of a dependent table. v An UPDATE statement is applied to the parent key of a parent table. v A DELETE statement is applied to a parent table. All affected referential constraints and all delete rules of all affected relationships must be satisfied in order for the delete operation to succeed. v The LOAD utility with the ENFORCE CONSTRAINTS option is run on a dependent table. v The CHECK DATA utility is run.
|
8
SQL Reference
DB2 conceptsThe order in which referential constraints are enforced is undefined. To ensure that the order does not affect the result of the operation, there are restrictions on the definition of delete rules and on the use of certain statements. The restrictions are specified in the descriptions of the SQL statements CREATE TABLE, ALTER TABLE, INSERT, UPDATE, and DELETE. The rules of referential integrity involve the following concepts and terminology: parent key A primary key or a unique key of a referential constraint. parent table A table that is a parent in at least one referential constraint. A table can be defined as a parent in an arbitrary number of referential constraints. dependent table A table that is a dependent in at least one referential constraint. A table can be defined as a dependent in an arbitrary number of referential constraints. A dependent table can also be a parent table. descendent table A table that is a dependent of another table or a table that is a dependent of a descendent table. referential cycle A set of referential constraints in which each associated table is a descendent of itself. parent row A row that has at least one dependent row. dependent row A row that has at least one parent row. descendent row A row that is dependent on another row or a row that is a dependent of a descendent row. self-referencing row A row that is a parent of itself. self-referencing table A table that is both parent and dependent in the same referential constraint. The constraint is called a self-referencing constraint. The following rules provide referential integrity: insert rule A nonnull insert value of the foreign key must match some value of the parent key of the parent table. The value of a composite foreign key is null if any component of the value is null. update rule A nonnull update value of the foreign key must match some value of the parent key of the parent table. The value of a composite foreign key is treated as null if any component of the value is null. delete rule Controls what happens when a row of the parent table is deleted. The choices of action, made when the referential constraint is defined, are RESTRICT, NO ACTION, CASCADE, or SET NULL. SET NULL can be specified only if some column of the foreign key allows null values.Chapter 1. DB2 concepts
9
DB2 conceptsMore precisely, the delete rule applies when a row of the parent table is the object of a delete or propagated delete operation and that row has dependents in the dependent table of the referential constraint. Let P denote the parent table, let D denote the dependent table, and let p denote a parent row that is the object of a delete or propagated delete operation. If the delete rule is: v RESTRICT or NO ACTION, an error occurs and no rows are deleted. v CASCADE, the delete operation is propagated to the dependent rows of p in D. v SET NULL, each nullable column of the foreign key of each dependent row of p in D is set to null. Each referential constraint in which a table is a parent has its own delete rule, and all applicable delete rules are used to determine the result of a delete operation. Thus, a row cannot be deleted if it has dependents in a referential constraint with a delete rule of RESTRICT or NO ACTION or the deletion cascades to any of its descendents that are dependents in a referential constraint with the delete rule of RESTRICT or NO ACTION. The deletion of a row from parent table P involves other tables and can affect rows of these tables: v If D is a dependent of P and the delete rule is RESTRICT or NO ACTION, D is involved in the operation but is not affected by the operation and the deletion from the parent table P does not take place. v If D is a dependent of P and the delete rule is SET NULL, D is involved in the operation and rows of D might be updated during the operation. v If D is a dependent of P and the delete rule is CASCADE, D is involved in the operation and rows of D might be deleted during the operation. If rows of D are deleted, the delete operation on P is said to be propagated to D. If D is also a parent table, the actions described in this list apply, in turn, to the dependents of D. Any table that can be involved in a delete operation on P is said to be delete-connected to P. Thus, a table is delete-connected to table P if it is a dependent of P or a dependent of a table to which delete operations from P cascade.
Check constraintsA check constraint is a rule that specifies the values allowed in one or more columns of every row of a table. Check constraints are optional and can be defined using the SQL statements CREATE TABLE and ALTER TABLE. The definition of a check constraint is a restricted form of a search condition that must not be false for any row of the table. One of the restrictions is that a column name in a check constraint on table T must identify a column of T. See Part 2 of DB2 Application Programming and SQL Guide for examples. A table can have an arbitrary number of check constraints. DB2 enforces the constraints when: v A row is inserted into the table. v A row of the table is updated. v The LOAD utility with the ENFORCE CONSTRAINTS option is used to populate the table. A check constraint is enforced by applying its search condition to each row that is inserted, updated, or loaded. An error occurs if the result of the search condition is false for any row.
10
SQL Reference
DB2 concepts
TriggersA trigger defines a set of actions that are executed when a delete, insert, or update operation occurs on a specified table. When such an SQL operation is executed, the trigger is said to be activated. Triggers can be used along with referential constraints and check constraints to enforce data integrity rules. Triggers are more powerful than constraints because they can also be used to cause updates to other tables, automatically generate or transform values for inserted or updated rows, or invoke functions that perform operations both inside and outside of DB2. For example, instead of preventing an update to a column if the new value exceeds a certain amount, a trigger can substitute a valid value and send a notice to an administrator about the invalid update. Triggers are useful for defining and enforcing business rules that involve different states of the data, for example, limiting a salary increase to 10%. Such a limit requires comparing the value of a salary before and after an increase. For rules that do not involve more than one state of the data, consider using referential and check constraints. Triggers also move the application logic that is required to enforce business rules into the database, which can result in faster application development and easier maintenance because the business rule is no longer repeated in several applications, but one version is centralized to the trigger. With the logic in the database, for example, the previously mentioned limit on increases to the salary column of a table, DB2 checks the validity of the changes that any application makes to the salary column. In addition, the application programs do not need to be changed when the logic changes. Triggers are optional and are defined using the CREATE TRIGGER statement. For information on using triggers, see Part 2 of DB2 Application Programming and SQL Guide.
Storage structuresIn DB2, a storage structure is a set of one or more VSAM data sets that hold DB2 tables or indexes. A storage structure is also called a page set. A storage structure can be one of the following: table space A table space can hold one or more base tables, or one auxiliary table. All tables are kept in table spaces. A table space can be defined using the CREATE TABLESPACE statement. index space An index space contains a single index. An index space is defined when the index is defined using the CREATE INDEX statement.
Chapter 1. DB2 concepts
11
DB2 concepts
Storage groupsDefining and deleting the data sets of a storage structure can be left to DB2. If it is left to DB2, the storage structure has an associated storage group. The storage group is a list of DASD volumes on which DB2 can allocate data sets for associated storage structures. The association between a storage structure and its storage group is explicitly or implicitly defined by the statement that created the storage structure. Alternatively, Storage Management Subsystem (SMS) can be used to manage DB2 data sets. Refer to Part 2 (Volume 1) of DB2 Administration Guide for more information.
DatabasesIn DB2, a database is a set of table spaces and index spaces. These index spaces contain indexes on the tables in the table spaces of the same database. Databases are defined using the CREATE DATABASE statement and are primarily used for administration. Whenever a table space is created, it is explicitly or implicitly assigned to an existing database.
CatalogEach DB2 maintains a set of tables that contain information about the data under its control. These tables are collectively known as the catalog. The catalog tables contain information about DB2 objects such as tables, views, and indexes. In this book, catalog refers to a DB2 catalog unless otherwise indicated. In contrast, the catalogs maintained by access method services are known as integrated catalog facility catalogs. Tables in the catalog are like any other database tables with respect to retrieval. If you have authorization, you can use SQL statements to look at data in the catalog tables in the same way that you retrieve data from any other table in the system. Each DB2 ensures that the catalog contains accurate descriptions of the objects that the DB2 controls.
ViewsA view provides an alternative way of looking at the data in one or more tables. A view is a named specification of a result table. The specification is an SQL SELECT statement that is effectively executed whenever the view is referenced in an SQL statement. At any time, the view consists of the rows that would result if the fullselect were executed. Thus, a view can be thought of as having columns and rows just like a base table. However, columns added to the base tables after the view is defined do not appear in the view. For retrieval, all views can be used like base tables. Whe