IBM DB2 Universal database 重重 SQL Reference volume I e 8 Sc09484400
IBM® DB2 Universal Database™ SQL Reference Volume 1 Version 8 SC09-4844-00
efore using this information and the product it supports, be sure to read the general information under Notices This document contains proprietary information of IBM. It is provided under a license agreement and is protected by copyright law. The information contained in this publication does not include any product warranties, and any statements provided in this manual should not be interpreted as such. You can order IBM publications online or through your local IBM representative. ToorderpublicationsonlinegototheIbMPublicationsCenteratwww.ibm.com/shop/publications/order To find your local IBM representative, go to the IBM Directory of Worldwide Contacts at To order DB2 publications from DB2 Marketing and Sales in the United States or Canada, call 1-800-IBM-YOU (426-4968) When you send information to IBM, you grant IBM a nonexclusive right to use or distribute the information in any way it believes appropriate without incur ny obligation to you o Copyright International Business Machines Corporation 1993-2002. All rights reserved. US Government Users Restricted Rights- Use, duplication or disclosure restricted by GSA ADP Schedule Contract with IBM Corp
Before using this information and the product it supports, be sure to read the general information under Notices. This document contains proprietary information of IBM. It is provided under a license agreement and is protected by copyright law. The information contained in this publication does not include any product warranties, and any statements provided in this manual should not be interpreted as such. You can order IBM publications online or through your local IBM representative. v To order publications online, go to the IBM Publications Center at www.ibm.com/shop/publications/order v To find your local IBM representative, go to the IBM Directory of Worldwide Contacts at www.ibm.com/planetwide To order DB2 publications from DB2 Marketing and Sales in the United States or Canada, call 1-800-IBM-4YOU (426-4968). When you send information to IBM, you grant IBM a nonexclusive right to use or distribute the information in any way it believes appropriate without incurring any obligation to you. © Copyright International Business Machines Corporation 1993 - 2002. All rights reserved. US Government Users Restricted Rights – Use, duplication or disclosure restricted by GSA ADP Schedule Contract with IBM Corp
Contents About this book Remote unit of work Who should use this book Application-directed distributed unit of How this book is structured work a brief overview of volume 2 Data representation considerations How to read the syntax diagrams xiii DB2 federated systems Common syntax elements Federated systems Function designator Data sources Method designator The federated database Procedure designator The SQL Compiler and the query 038991345 Conventions used in this manual optimizer Error conditions Highlighting convention Pass-through sessions Related documentation Wrappers and wrapper modules Server definitions and server option Chapter 1. Concepts User mappings and user options 51 Relational databases Nicknames and data source objects Structured Query Language(SQl Column options Authorization and privileges Data type mappings 4 Tables Function mappings options 255675 Function mappings and function templates 56 Views Index spe Indexes Chapter 2. Language elements Keys Characters 61 Tokens 63 Unique constraints identifiers ferential constraints Naming conventions and implicit object Table check constraints name qualifications Isolation levels 13 Aliases Queries Authorization ids and authorization Table expressions names Application processes, concurrency, and Column names recovery References to host variables DB2 Call level interface(CLI)and open Data types database connectivity (ODBC) Data Java database connectivity (DBC) and mbedded SQL for Java(SQLD) programs . 19 Character strings Packages 822999% Catalog views Character conversion Large objects(LOBs 99 Event monitors 101 Triggers DATALINK values paces and other storage struc XML values Data partitioning across multiple partitions 28 User-defined types Distributed relational databases Promotion of data types c Copyright IBM Corp. 1993-2002
Contents About this book . . . . . . . . . . xi Who should use this book . . . . . . . xi How this book is structured . . . . . . . xi A brief overview of Volume 2 . . . . . xii How to read the syntax diagrams . . . . xiii Common syntax elements . . . . . . . xv Function designator . . . . . . . . xv Method designator . . . . . . . . xvii Procedure designator . . . . . . . xviii Conventions used in this manual . . . . . xx Error conditions. . . . . . . . . . xx Highlighting conventions . . . . . . xx Related documentation . . . . . . . . xxi Chapter 1. Concepts . . . . . . . . .1 Relational databases. . . . . . . . . .1 Structured Query Language (SQL) . . . . .1 Authorization and privileges . . . . . . .2 Schemas. . . . . . . . . . . . . . 4 Tables . . . . . . . . . . . . . . 5 Views . . . . . . . . . . . . . . 6 Aliases . . . . . . . . . . . . . . 7 Indexes . . . . . . . . . . . . . . 7 Keys . . . . . . . . . . . . . . . 7 Constraints. . . . . . . . . . . . .8 Unique constraints . . . . . . . . .9 Referential constraints . . . . . . . .9 Table check constraints . . . . . . . 12 Isolation levels . . . . . . . . . . . 13 Queries . . . . . . . . . . . . . 16 Table expressions . . . . . . . . . . 16 Application processes, concurrency, and recovery . . . . . . . . . . . . . 16 DB2 Call level interface (CLI) and open database connectivity (ODBC) . . . . . . 19 Java database connectivity (JDBC) and embedded SQL for Java (SQLJ) programs . . 19 Packages . . . . . . . . . . . . . 20 Catalog views . . . . . . . . . . . 20 Character conversion . . . . . . . . . 20 Event monitors . . . . . . . . . . . 23 Triggers . . . . . . . . . . . . . 24 Table spaces and other storage structures . . 26 Data partitioning across multiple partitions 28 Distributed relational databases . . . . . 29 Remote unit of work . . . . . . . . 30 Application-directed distributed unit of work . . . . . . . . . . . . . 33 Data representation considerations . . . 38 DB2 federated systems . . . . . . . . 39 Federated systems . . . . . . . . . 39 Data sources . . . . . . . . . . . 41 The federated database . . . . . . . 43 The SQL Compiler and the query optimizer . . . . . . . . . . . . 44 Compensation . . . . . . . . . . 45 Pass-through sessions . . . . . . . . 46 Wrappers and wrapper modules . . . . 48 Server definitions and server options. . . 50 User mappings and user options . . . . 51 Nicknames and data source objects . . . 52 Column options. . . . . . . . . . 53 Data type mappings . . . . . . . . 54 Function mappings and function templates 56 Function mappings options . . . . . . 57 Index specifications . . . . . . . . 58 Chapter 2. Language elements . . . . . 61 Characters . . . . . . . . . . . . 61 Tokens . . . . . . . . . . . . . . 63 Identifiers . . . . . . . . . . . . . 65 Naming conventions and implicit object name qualifications . . . . . . . . 65 Aliases . . . . . . . . . . . . . 70 Authorization IDs and authorization names . . . . . . . . . . . . . 71 Column names . . . . . . . . . . 76 References to host variables . . . . . . 83 Data types . . . . . . . . . . . . 92 Data types . . . . . . . . . . . 92 Numbers . . . . . . . . . . . . 94 Character strings . . . . . . . . . 95 Graphic strings . . . . . . . . . . 97 Binary strings . . . . . . . . . . 98 Large objects (LOBs) . . . . . . . . 99 Datetime values . . . . . . . . . 101 DATALINK values . . . . . . . . 105 XML values . . . . . . . . . . . 107 User-defined types . . . . . . . . 108 Promotion of data types. . . . . . . 111 © Copyright IBM Corp. 1993 - 2002 iii
Casting between data types Dynamic dispatch of methods Assignments and comparisons Rules for result data types Expressions without operators Rules for string conversions Expressions with the concatenation Partition-compatible data types Constant 143 Expressions with arithmetic operators Integer constants Two-integer operands Floating-point constants 44 Integer and decimal operands Decimal constants Two-decimal operands r string constants Decimal arithmetic in SQL Hexadecimal constants Floating-point operands Graphic string constants User-defined types as operand 194 Special registers 146 Scalar fullselect 194 Datetime operations and durations CLIENT ACCTNG Datetime arithmetic in SQL CLIENT APPLNAME recedence CLIENT USERID CASE expression 201 CLIENT WRKSTNNAME 151 CAST specifications CURRENT DATE Dereference operations CURRENT DBPARTITIONNUM OLAP functions CURRENT DEFAULT TRANSFORM XML functions GROUP Method invocation CURRENT DEGREE 15 Subtype treatment CURRENT EXPLAIN MODE Sequence reference 220 CURRENT EXPLAIN SNAPSHOT Predicates CURRENT MAINTAINED TABLE TYPES Predicates FOR OPTIMIZATION Search conditions CURRENT PATH 159 Basic predicate CURRENT QUERY OPTIMIZATION Quantified predicate 30 CURRENT REFRESH AGE BETWeEN predicate CURRENT SCHEMA EXISTS predicate CURRENT SERVER CURRENT TIME LIKE predicate CURRENT TIMESTAMP NULL predicate 243 CURRENT TIMEZONE TYPE predicate Functions Chapter 3 Functions External, SQL, and sourced user-defined Functions overview functions Aggregate functions Scalar, column, row, and table AVG user-defined functions 168 CORRELATION Function signatures 169 COUNT Function resolution 170 COUNT BIG Function invocation COVARIANCE Conservative binding semantics 175 GROUPING Method MAX External and SQL user-defined methods 178 MIN Method signatures 179 Regression functions Method resolution STDDE SUM IV SQL Reference
Casting between data types . . . . . 113 Assignments and comparisons . . . . 117 Rules for result data types . . . . . . 134 Rules for string conversions . . . . . 140 Partition-compatible data types . . . . 141 Constants . . . . . . . . . . . . 143 Integer constants . . . . . . . . . 143 Floating-point constants. . . . . . . 144 Decimal constants. . . . . . . . . 144 Character string constants . . . . . . 144 Hexadecimal constants . . . . . . . 145 Graphic string constants . . . . . . 145 Special registers . . . . . . . . . . 146 Special registers . . . . . . . . . 146 CLIENT ACCTNG . . . . . . . . 148 CLIENT APPLNAME . . . . . . . 149 CLIENT USERID . . . . . . . . . 150 CLIENT WRKSTNNAME . . . . . . 151 CURRENT DATE . . . . . . . . . 152 CURRENT DBPARTITIONNUM . . . . 153 CURRENT DEFAULT TRANSFORM GROUP . . . . . . . . . . . . 154 CURRENT DEGREE . . . . . . . . 155 CURRENT EXPLAIN MODE . . . . . 156 CURRENT EXPLAIN SNAPSHOT . . . 157 CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION . . . . . . . 158 CURRENT PATH . . . . . . . . . 159 CURRENT QUERY OPTIMIZATION . . 160 CURRENT REFRESH AGE. . . . . . 161 CURRENT SCHEMA . . . . . . . 162 CURRENT SERVER . . . . . . . . 163 CURRENT TIME . . . . . . . . . 164 CURRENT TIMESTAMP . . . . . . 165 CURRENT TIMEZONE . . . . . . . 166 USER . . . . . . . . . . . . . 167 Functions . . . . . . . . . . . . 168 External, SQL, and sourced user-defined functions. . . . . . . . . . . . 168 Scalar, column, row, and table user-defined functions . . . . . . . 168 Function signatures . . . . . . . . 169 Function resolution . . . . . . . . 170 Function invocation . . . . . . . . 174 Conservative binding semantics . . . . 175 Methods . . . . . . . . . . . . . 178 External and SQL user-defined methods 178 Method signatures . . . . . . . . 179 Method resolution . . . . . . . . 180 Method invocation . . . . . . . . 183 Dynamic dispatch of methods . . . . 184 Expressions . . . . . . . . . . . . 187 Expressions without operators . . . . 188 Expressions with the concatenation operator . . . . . . . . . . . . 188 Expressions with arithmetic operators . . 191 Two-integer operands . . . . . . . 192 Integer and decimal operands. . . . . 193 Two-decimal operands . . . . . . . 193 Decimal arithmetic in SQL . . . . . . 193 Floating-point operands. . . . . . . 194 User-defined types as operands . . . . 194 Scalar fullselect . . . . . . . . . 194 Datetime operations and durations . . . 194 Datetime arithmetic in SQL . . . . . 196 Precedence of operations . . . . . . 200 CASE expressions. . . . . . . . . 201 CAST specifications . . . . . . . . 203 Dereference operations . . . . . . . 206 OLAP functions . . . . . . . . . 207 XML functions . . . . . . . . . . 214 Method invocation . . . . . . . . 218 Subtype treatment . . . . . . . . 219 Sequence reference . . . . . . . . 220 Predicates . . . . . . . . . . . . 225 Predicates . . . . . . . . . . . 225 Search conditions . . . . . . . . . 226 Basic predicate . . . . . . . . . . 229 Quantified predicate . . . . . . . . 230 BETWEEN predicate . . . . . . . . 233 EXISTS predicate . . . . . . . . . 234 IN predicate . . . . . . . . . . 235 LIKE predicate . . . . . . . . . . 238 NULL predicate . . . . . . . . . 243 TYPE predicate . . . . . . . . . 244 Chapter 3. Functions . . . . . . . . 247 Functions overview . . . . . . . . . 247 Aggregate functions . . . . . . . . . 269 AVG . . . . . . . . . . . . . . 270 CORRELATION . . . . . . . . . . 272 COUNT . . . . . . . . . . . . . 273 COUNT_BIG . . . . . . . . . . . 275 COVARIANCE. . . . . . . . . . . 277 GROUPING . . . . . . . . . . . 278 MAX . . . . . . . . . . . . . . 280 MIN . . . . . . . . . . . . . . 282 Regression functions . . . . . . . . . 284 STDDEV . . . . . . . . . . . . . 288 SUM . . . . . . . . . . . . . . 289 iv SQL Reference, Volume 1
VARIANCE ENCRYPT Scalar functions 291 EVENT MON STATE ABS or ABSVAL EXP ACOS 293 FLOAT ASCI FLOOR GETHINT ATAN GENERATE_UNIQUE ATAN2 297 GRAPHIC ATANH BIGINT HEX BLOB HOUR CEILING or CEIL IDENTITY VAL LOCAL 30 INSERT CHR INTEGER CLOB JULIAN_DAY COALESCE 311 LCASE or LOWER CONCAT LCASE (SYSFUN schema COS 313 LEFT COSH 314 LENGTH COT 31 DATE 316 LOCATE DAY LOG DAYNAME 319 LOG10 395 DAYOFWEEK LONG VARCHAR DAYOFWEEK ISO 321 LONG VARGRAPHIC DAYOFYEAR LTRIM DAYS LTRIM (SYSFUN schema) DBCLOB MICROSECOND 401 DBPARTITIONNUM 325 MIDNIGHT SECONDS DECIMAL 330 MINUTE DECRYPT BIN and DECRYPT CHAR MOD DEGREES MONTH DEREF MONTHNAME DIFFERENCE AQPUBLISH 407 DIGITS EAD DLCOMMENT MQREADCLOB DLLINKTYPE 339 MORECElVE DLNEWCOPY MQRECETVECLOB DLPREVIOUSCOPY DLREPLACECONTENT MQSUBSCRIBE 420 DLURLCOMPLETE MQUNSUBSCRIBE DLURLCOMPLETEONLY MULTIPLY ALT DLURLCOMPLETEWRITE NULLIF DLURLPATH POSSTR DLURLPATHONLY POWER DLURLPATHWRITE QUARTER DLURLSCHEME RADIANS DLURLSERVER 354 RAISE ERROR. DLVALUE 355 RAND DOUBLE REAL
VARIANCE . . . . . . . . . . . . 290 Scalar functions . . . . . . . . . . 291 ABS or ABSVAL . . . . . . . . . . 292 ACOS. . . . . . . . . . . . . . 293 ASCII . . . . . . . . . . . . . . 294 ASIN . . . . . . . . . . . . . . 295 ATAN. . . . . . . . . . . . . . 296 ATAN2 . . . . . . . . . . . . . 297 ATANH . . . . . . . . . . . . . 298 BIGINT . . . . . . . . . . . . . 299 BLOB . . . . . . . . . . . . . . 301 CEILING or CEIL. . . . . . . . . . 302 CHAR . . . . . . . . . . . . . 303 CHR . . . . . . . . . . . . . . 309 CLOB. . . . . . . . . . . . . . 310 COALESCE . . . . . . . . . . . . 311 CONCAT . . . . . . . . . . . . 312 COS . . . . . . . . . . . . . . 313 COSH. . . . . . . . . . . . . . 314 COT . . . . . . . . . . . . . . 315 DATE . . . . . . . . . . . . . . 316 DAY . . . . . . . . . . . . . . 318 DAYNAME . . . . . . . . . . . . 319 DAYOFWEEK . . . . . . . . . . . 320 DAYOFWEEK_ISO . . . . . . . . . 321 DAYOFYEAR . . . . . . . . . . . 322 DAYS . . . . . . . . . . . . . . 323 DBCLOB. . . . . . . . . . . . . 324 DBPARTITIONNUM . . . . . . . . . 325 DECIMAL . . . . . . . . . . . . 330 DECRYPT_BIN and DECRYPT_CHAR . . . 332 DEGREES . . . . . . . . . . . . 334 DEREF . . . . . . . . . . . . . 335 DIFFERENCE . . . . . . . . . . . 336 DIGITS . . . . . . . . . . . . . 337 DLCOMMENT. . . . . . . . . . . 338 DLLINKTYPE . . . . . . . . . . . 339 DLNEWCOPY . . . . . . . . . . . 340 DLPREVIOUSCOPY . . . . . . . . . 343 DLREPLACECONTENT . . . . . . . 345 DLURLCOMPLETE . . . . . . . . . 347 DLURLCOMPLETEONLY . . . . . . . 348 DLURLCOMPLETEWRITE. . . . . . . 349 DLURLPATH . . . . . . . . . . . 350 DLURLPATHONLY . . . . . . . . . 351 DLURLPATHWRITE . . . . . . . . . 352 DLURLSCHEME . . . . . . . . . . 353 DLURLSERVER . . . . . . . . . . 354 DLVALUE . . . . . . . . . . . . 355 DOUBLE. . . . . . . . . . . . . 357 ENCRYPT . . . . . . . . . . . . 359 EVENT_MON_STATE . . . . . . . . 362 EXP . . . . . . . . . . . . . . 363 FLOAT . . . . . . . . . . . . . 364 FLOOR . . . . . . . . . . . . . 365 GETHINT . . . . . . . . . . . . 366 GENERATE_UNIQUE . . . . . . . . 367 GRAPHIC . . . . . . . . . . . . 369 HASHEDVALUE . . . . . . . . . . 371 HEX . . . . . . . . . . . . . . 373 HOUR . . . . . . . . . . . . . 375 IDENTITY_VAL_LOCAL . . . . . . . 376 INSERT . . . . . . . . . . . . . 382 INTEGER . . . . . . . . . . . . 384 JULIAN_DAY . . . . . . . . . . . 386 LCASE or LOWER . . . . . . . . . 387 LCASE (SYSFUN schema) . . . . . . . 388 LEFT . . . . . . . . . . . . . . 389 LENGTH . . . . . . . . . . . . 390 LN. . . . . . . . . . . . . . . 392 LOCATE . . . . . . . . . . . . . 393 LOG . . . . . . . . . . . . . . 394 LOG10 . . . . . . . . . . . . . 395 LONG_VARCHAR . . . . . . . . . 396 LONG_VARGRAPHIC . . . . . . . . 397 LTRIM . . . . . . . . . . . . . 398 LTRIM (SYSFUN schema) . . . . . . . 400 MICROSECOND . . . . . . . . . . 401 MIDNIGHT_SECONDS . . . . . . . . 402 MINUTE. . . . . . . . . . . . . 403 MOD . . . . . . . . . . . . . . 404 MONTH . . . . . . . . . . . . . 405 MONTHNAME . . . . . . . . . . 406 MQPUBLISH . . . . . . . . . . . 407 MQREAD . . . . . . . . . . . . 410 MQREADCLOB . . . . . . . . . . 412 MQRECEIVE . . . . . . . . . . . 414 MQRECEIVECLOB . . . . . . . . . 416 MQSEND . . . . . . . . . . . . 418 MQSUBSCRIBE . . . . . . . . . . 420 MQUNSUBSCRIBE . . . . . . . . . 422 MULTIPLY_ALT . . . . . . . . . . 424 NULLIF . . . . . . . . . . . . . 426 POSSTR . . . . . . . . . . . . . 427 POWER . . . . . . . . . . . . . 429 QUARTER . . . . . . . . . . . . 430 RADIANS . . . . . . . . . . . . 431 RAISE_ERROR. . . . . . . . . . . 432 RAND . . . . . . . . . . . . . 434 REAL . . . . . . . . . . . . . . 435 Contents v