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
REC2XML SNAPSHOT DATABASE REPEAT SNAPSHOT DBM REPLACE 442 SNAPSHOT_DYN_ SQL RIGHT 443 SNAPSHOT FCM ROUND SNAPSHOT FCMPARTITION RTRIM SNAPSHOT LOCK RTRIM(SYSFUN schema) SNAPSHOT LOCKWAIT SECOND SNAPSHOT QUIESCERS SIGN SNAPSHOT RANGES SNAPSHOT STATEMENT SINH 451 SNAPSHOT SUBSECT SMALLINT SNAPSHOT SWITCHES SOUNDEX SNAPSHOT TABLE E 454 SNAPSHOT TBS SNAPSHOT TBS CFG SUBSTR SQLCACHE_SNAPSHOT TABLE NAME Procedures TABLE SCHEMA 461 GET ROUTINE SAR 46 TAN PUT ROUTINE SAR TANH User-defined functions TIMESTAMP Chapter 4. Queries 553 TIMESTAMP FORMAT 468 SOL IMESTAMP ISO 470 Subselect TIMESTAMPDIFF select-clause 555 TO CHAR from-clause TO DATE table-reference 561 TRANSLATE joined-table TRUnCAtE or TRUNC where-clause TYPE ID group-by-cla ause TYPE NAME ause TYPE SCHEMA order-by-clause UCASE or UPPER fetch-first-clause ALUE Examples of subselects VARCHAR 583 VARCHAR FORMAT Examples of grouping sets, cube, and VARGRAPHIC WEEK Fullselect 597 WEEK ISO Examples of a fullselect YEAR Select-statement 601 Table functions common-tab express MQREADALL update-clause MQREADALLCLOB read-only-clause MQRECEIVEALL ptimize-for-clause MQRECEIVEALLCLOB Examples of a select-statemeni SNAPSHOT AGENT SNAPSHOT APPL AppendⅸxA. SQL limits 0 SNAPSHOT APPL INFO 510 SNAPSHOT BP 512 AppendⅸxB. SQLCA(sQL SNAPSHOT CONTAINER 514 communications area) 615 VI SQL Reference, Volume 1
REC2XML . . . . . . . . . . . . 436 REPEAT . . . . . . . . . . . . . 441 REPLACE . . . . . . . . . . . . 442 RIGHT . . . . . . . . . . . . . 443 ROUND . . . . . . . . . . . . . 444 RTRIM . . . . . . . . . . . . . 446 RTRIM (SYSFUN schema) . . . . . . . 447 SECOND . . . . . . . . . . . . 448 SIGN . . . . . . . . . . . . . . 449 SIN . . . . . . . . . . . . . . 450 SINH . . . . . . . . . . . . . . 451 SMALLINT . . . . . . . . . . . . 452 SOUNDEX . . . . . . . . . . . . 453 SPACE . . . . . . . . . . . . . 454 SQRT . . . . . . . . . . . . . . 455 SUBSTR . . . . . . . . . . . . . 456 TABLE_NAME. . . . . . . . . . . 460 TABLE_SCHEMA. . . . . . . . . . 461 TAN . . . . . . . . . . . . . . 463 TANH . . . . . . . . . . . . . 464 TIME . . . . . . . . . . . . . . 465 TIMESTAMP . . . . . . . . . . . 466 TIMESTAMP_FORMAT . . . . . . . . 468 TIMESTAMP_ISO . . . . . . . . . . 470 TIMESTAMPDIFF. . . . . . . . . . 471 TO_CHAR . . . . . . . . . . . . 473 TO_DATE . . . . . . . . . . . . 474 TRANSLATE . . . . . . . . . . . 475 TRUNCATE or TRUNC. . . . . . . . 478 TYPE_ID. . . . . . . . . . . . . 480 TYPE_NAME . . . . . . . . . . . 481 TYPE_SCHEMA . . . . . . . . . . 482 UCASE or UPPER . . . . . . . . . 483 VALUE . . . . . . . . . . . . . 484 VARCHAR . . . . . . . . . . . . 485 VARCHAR_FORMAT . . . . . . . . 487 VARGRAPHIC. . . . . . . . . . . 489 WEEK . . . . . . . . . . . . . 491 WEEK_ISO . . . . . . . . . . . . 492 YEAR. . . . . . . . . . . . . . 493 Table functions. . . . . . . . . . . 494 MQREADALL . . . . . . . . . . . 495 MQREADALLCLOB . . . . . . . . . 497 MQRECEIVEALL . . . . . . . . . . 499 MQRECEIVEALLCLOB. . . . . . . . 502 SNAPSHOT_AGENT . . . . . . . . 505 SNAPSHOT_APPL . . . . . . . . . 506 SNAPSHOT_APPL_INFO . . . . . . . 510 SNAPSHOT_BP . . . . . . . . . . 512 SNAPSHOT_CONTAINER. . . . . . . 514 SNAPSHOT_DATABASE . . . . . . . 516 SNAPSHOT_DBM . . . . . . . . . 521 SNAPSHOT_DYN_SQL . . . . . . . . 523 SNAPSHOT_FCM . . . . . . . . . 525 SNAPSHOT_FCMPARTITION . . . . . 526 SNAPSHOT_LOCK . . . . . . . . . 527 SNAPSHOT_LOCKWAIT . . . . . . . 529 SNAPSHOT_QUIESCERS . . . . . . . 531 SNAPSHOT_RANGES . . . . . . . . 532 SNAPSHOT_STATEMENT . . . . . . . 533 SNAPSHOT_SUBSECT . . . . . . . . 535 SNAPSHOT_SWITCHES . . . . . . . 537 SNAPSHOT_TABLE . . . . . . . . . 538 SNAPSHOT_TBS . . . . . . . . . . 540 SNAPSHOT_TBS_CFG . . . . . . . . 542 SQLCACHE_SNAPSHOT . . . . . . . 544 Procedures . . . . . . . . . . . . 545 GET_ROUTINE_SAR . . . . . . . . 546 PUT_ROUTINE_SAR . . . . . . . . 548 User-defined functions . . . . . . . . 550 Chapter 4. Queries . . . . . . . . . 553 SQL queries. . . . . . . . . . . . 553 Subselect. . . . . . . . . . . . . 554 select-clause. . . . . . . . . . . 555 from-clause . . . . . . . . . . . 560 table-reference . . . . . . . . . . 561 joined-table . . . . . . . . . . . 565 where-clause . . . . . . . . . . 568 group-by-clause . . . . . . . . . 569 having-clause . . . . . . . . . . 576 order-by-clause . . . . . . . . . 576 fetch-first-clause . . . . . . . . . 579 Examples of subselects . . . . . . . 580 Examples of joins . . . . . . . . . 583 Examples of grouping sets, cube, and rollup . . . . . . . . . . . . . 586 Fullselect. . . . . . . . . . . . . 597 Examples of a fullselect . . . . . . . 598 Select-statement . . . . . . . . . . 601 common-table-expression . . . . . . 601 update-clause . . . . . . . . . . 603 read-only-clause . . . . . . . . . 604 optimize-for-clause . . . . . . . . 605 Examples of a select-statement . . . . 605 Appendix A. SQL limits . . . . . . . 607 Appendix B. SQLCA (SQL communications area) . . . . . . . 615 vi SQL Reference, Volume 1
QLCA field descriptions 615 SYSCAT. HIERARCHIES Error reporting 619 SYSCATINDEXAUTH SQLCA usage in partitioned database SYSCATINDEXCOLUSE 75 620 SYSCATINDEXDEP SYSCATINDEXES Appendix C. SQLDA (SQL descriptor SYSCATINDEXEXPLOITRULES 621 SYSCATINDEXEXTENSIONDEP SQLDA field descriptions SYSCAT. INDEXEXTENSIONMETHODS Fields in the SQLDA header SYSCATINDEXEXTENSIONPARMS Fields in an occurrence of a base SQLVAR 623 SYSCAT. INDEXEXTENSIONS Fields in an occurrence of a secondary SYSCATINDEXOPTIONS QLVAR SYSCAT.KEYCOLUSE Effect of dESCribe on the SQLDA SYSCAT. NAMEMAPPINGS SQLTYPE and SQLLEN 629 SYSCAT. PACKAGEAUTH ognized and unsupported SYSCAT. PACKAGEDEP SQLTYPEs SYSCAT. PACKAC Packed decimal numbers 631 SYSCAT. PARTTTIONMAPS SQLLEN field for decimal SYSCATPASSTHRUAUTH SYSCAT. PREDICATESPECS Appendix D Catalog views 633 SYSCAT. PROCOPTIONS Road map to catalog views 633 SYSCAT PROCPARMOPTIONS Road map to updatable catalog views 636 SYSCAT.REFERENCES System catalog views SYSCAT. REVTYPEMAPPINGS 705 SYSIBM.SYSDUMMY1 SYSCAT. ROUTINEAUTH 707 SYSCAT.ATTRIBUTES 639 SYSCAT. ROUTINEDEP SYSCAT BUFFERPOOLDBPARTITIONS 641 SYSCAT. ROUTINEPARMS SYSCAT. BUFFERPOOLS 642 SYSCAT ROUTINES SYSCAT. CASTFUNCTIONS SYSCAT. SCHEMAAUTH SYSCAT. CHECKS SYSCAT. SCHEMATA SYSCAT. COLAUTH SYSCAT. SEQUENCEAUTH SYSCAT. COLCHECKS SYSCAT.SEQU! SYSCAT. COLDIST SYSCAT. SERVEROPTIONS SYSCAT COLGROUPDIST SYSCAT SERVERS SYSCAT COLGROUPDISTCOUNTS 649 SYSCAT.STATEMENTS SYSCAT COLGROUPS SYSCAT.TABAUTH SYSCAT. COLOPTIONS 651 SYSCAT. TABCONST SYSCAT. COLUMNS SYSCAT.TABDEP SYSCAT. COLUSE SYSCAT. TABLES SYSCAT CONSTDEP 658 SYSCAT.TABLESPACES 735 SYSCATDATATYPES SYSCAT. TABOPTIONS SYSCATDBAUTH SYSCAT.TBSPACEAUTH SYSCAT. DBPARTITIONGROUPDEF SYSCAT. TRANSFORMS SYSCAT. DBPARTITIONGROUPS SYSCAT.TRIGDEP SYSCAT.EVENTMONITORS 665 SYSCAT. TRIGGERS SYSCAT.EVENTS SYSCAT.TYPEMAPPINGS SYSCATEVENTTABLES 668 SYSCAT. USEROPTIONS SYSCAT. FULLHIERARCHIES 669 SYSCAT. VIEWS SYSCAT. FUNCMAPOPTIONS SYSCAT. WRAPOPTIONS SYSCAT. FUNCMAPPARMOPTIONS SYSCAT. WRAPPERS 746 SYSCAT. FUNCMAPPINGS SYSSTAT. COLDIST Contents vIl
SQLCA field descriptions . . . . . . . 615 Error reporting. . . . . . . . . . . 619 SQLCA usage in partitioned database systems . . . . . . . . . . . . . 620 Appendix C. SQLDA (SQL descriptor area) . . . . . . . . . . . . . . 621 SQLDA field descriptions . . . . . . . 621 Fields in the SQLDA header . . . . . 622 Fields in an occurrence of a base SQLVAR 623 Fields in an occurrence of a secondary SQLVAR . . . . . . . . . . . . 625 Effect of DESCRIBE on the SQLDA . . . . 627 SQLTYPE and SQLLEN . . . . . . . . 629 Unrecognized and unsupported SQLTYPEs . . . . . . . . . . . 631 Packed decimal numbers . . . . . . 631 SQLLEN field for decimal . . . . . . 632 Appendix D. Catalog views . . . . . . 633 ‘Road map’ to catalog views . . . . . . 633 ‘Road map’ to updatable catalog views . . 636 System catalog views . . . . . . . . 636 SYSIBM.SYSDUMMY1 . . . . . . . . 638 SYSCAT.ATTRIBUTES . . . . . . . . 639 SYSCAT.BUFFERPOOLDBPARTITIONS . . 641 SYSCAT.BUFFERPOOLS . . . . . . . 642 SYSCAT.CASTFUNCTIONS . . . . . . 643 SYSCAT.CHECKS . . . . . . . . . . 644 SYSCAT.COLAUTH . . . . . . . . . 645 SYSCAT.COLCHECKS . . . . . . . . 646 SYSCAT.COLDIST . . . . . . . . . 647 SYSCAT.COLGROUPDIST . . . . . . . 648 SYSCAT.COLGROUPDISTCOUNTS. . . . 649 SYSCAT.COLGROUPS . . . . . . . . 650 SYSCAT.COLOPTIONS . . . . . . . . 651 SYSCAT.COLUMNS . . . . . . . . . 652 SYSCAT.COLUSE . . . . . . . . . . 657 SYSCAT.CONSTDEP . . . . . . . . . 658 SYSCAT.DATATYPES . . . . . . . . 659 SYSCAT.DBAUTH . . . . . . . . . 661 SYSCAT.DBPARTITIONGROUPDEF . . . 663 SYSCAT.DBPARTITIONGROUPS . . . . 664 SYSCAT.EVENTMONITORS . . . . . . 665 SYSCAT.EVENTS . . . . . . . . . . 667 SYSCAT.EVENTTABLES . . . . . . . 668 SYSCAT.FULLHIERARCHIES . . . . . . 669 SYSCAT.FUNCMAPOPTIONS . . . . . 670 SYSCAT.FUNCMAPPARMOPTIONS . . . 671 SYSCAT.FUNCMAPPINGS. . . . . . . 672 SYSCAT.HIERARCHIES. . . . . . . . 673 SYSCAT.INDEXAUTH . . . . . . . . 674 SYSCAT.INDEXCOLUSE . . . . . . . 675 SYSCAT.INDEXDEP . . . . . . . . . 676 SYSCAT.INDEXES . . . . . . . . . 677 SYSCAT.INDEXEXPLOITRULES . . . . . 682 SYSCAT.INDEXEXTENSIONDEP . . . . 683 SYSCAT.INDEXEXTENSIONMETHODS . . 684 SYSCAT.INDEXEXTENSIONPARMS . . . 685 SYSCAT.INDEXEXTENSIONS. . . . . . 686 SYSCAT.INDEXOPTIONS . . . . . . . 687 SYSCAT.KEYCOLUSE . . . . . . . . 688 SYSCAT.NAMEMAPPINGS . . . . . . 689 SYSCAT.PACKAGEAUTH . . . . . . . 690 SYSCAT.PACKAGEDEP. . . . . . . . 691 SYSCAT.PACKAGES . . . . . . . . . 693 SYSCAT.PARTITIONMAPS . . . . . . 699 SYSCAT.PASSTHRUAUTH. . . . . . . 700 SYSCAT.PREDICATESPECS . . . . . . 701 SYSCAT.PROCOPTIONS . . . . . . . 702 SYSCAT.PROCPARMOPTIONS . . . . . 703 SYSCAT.REFERENCES . . . . . . . . 704 SYSCAT.REVTYPEMAPPINGS . . . . . 705 SYSCAT.ROUTINEAUTH . . . . . . . 707 SYSCAT.ROUTINEDEP . . . . . . . . 708 SYSCAT.ROUTINEPARMS . . . . . . . 709 SYSCAT.ROUTINES . . . . . . . . . 711 SYSCAT.SCHEMAAUTH . . . . . . . 718 SYSCAT.SCHEMATA . . . . . . . . 719 SYSCAT.SEQUENCEAUTH . . . . . . 720 SYSCAT.SEQUENCES . . . . . . . . 721 SYSCAT.SERVEROPTIONS. . . . . . . 723 SYSCAT.SERVERS. . . . . . . . . . 724 SYSCAT.STATEMENTS . . . . . . . . 725 SYSCAT.TABAUTH . . . . . . . . . 726 SYSCAT.TABCONST . . . . . . . . . 728 SYSCAT.TABDEP . . . . . . . . . . 729 SYSCAT.TABLES . . . . . . . . . . 730 SYSCAT.TABLESPACES . . . . . . . . 735 SYSCAT.TABOPTIONS . . . . . . . . 736 SYSCAT.TBSPACEAUTH . . . . . . . 737 SYSCAT.TRANSFORMS. . . . . . . . 738 SYSCAT.TRIGDEP . . . . . . . . . 739 SYSCAT.TRIGGERS . . . . . . . . . 740 SYSCAT.TYPEMAPPINGS . . . . . . . 741 SYSCAT.USEROPTIONS . . . . . . . 743 SYSCAT.VIEWS . . . . . . . . . . 744 SYSCAT.WRAPOPTIONS . . . . . . . 745 SYSCAT.WRAPPERS . . . . . . . . . 746 SYSSTAT.COLDIST . . . . . . . . . 747 Contents vii
SYSSTAT COLUMNS EMPLOYEE table SYSSTATINDEXES EMP ACT table SYSSTAT ROUTINES EMP PHOTO table 810 SYSSTAT TABLES 757 EMP RESUME table IN TRAY table Appendix E Federated systems ORG table Valid server types in SQL statements PROJECT table CTLIB wrapper SALES table DBLIB 759 STAFF table DJXMSSQL3 wrapper 759 STAFFG table(double-byte code pages only) 815 DRDA wrapper ample files with BLOB and CLOB data type 816 Informix wrapper 761 Quintana photo MSSQLODBC3 wrapper Quintana resume 816 NET8 wrapper Nicholls photo ODBC wrapper Nicholls resume OLE DB wrapper Adamson photo Function mapping option6s:,p的 SQLNET wrapper Column options for federated syst Walker resume erver options for federated systems Appendix G. Reserved schema names User options for federated systems and reserved words 823 Wrapper options for federated systems 774 Default forward data type mappings Appendix H. Comparison of isolation DB2 for z/OS and OS/390 data sources 776 evels DB2 for iSeries data sources DB2 Server for VM and VsE data sources 779 Appendix I Interaction of triggers and DB2 for UNIX and Windows data sources 780 constraints Informix data sources Oracle SqlneT data sources Appendix J. Explain tables 833 Oracle net8 data sources Microsoft SQL Server data sources EXPLAIN ARGUMENT table oDBC data sources EXPLAIN INSTANCE table Sybase data sources EXPLAIN_OBJECT table 841 Default reverse data type mappings EXPLAIN OPERATOR table DB2 for z/OS and OS/390 data sources EXPLAIN PREDICATE table DB2 for iseries data sources EXPLAIN STATEMENT table DB2 Server for VM and Vse data sources 795 EXPLAIN STREAM table 851 DB2 for unix and windows data sources 796 ADVISE INDEX table Informix data sources ADVISE WORKLOAD table Oracle SqLneT data sources Oracle net8 data sources Appendix K. Explain register values 85 Microsoft SQL Server data sources Sybase data sources 801 Appendix L. Recursion example: bill of materials Appendix F The SAMPLE database Example 1: Single level explosion Creating the SAMPLE database Example 2: Summarized explo OSIO Erasing the SAMPLE database Example 3: Controlling depth CL SCHED table DEPARTMENT table VIll SQL Reference, Volume 1
SYSSTAT.COLUMNS. . . . . . . . . 749 SYSSTAT.INDEXES . . . . . . . . . 751 SYSSTAT.ROUTINES. . . . . . . . . 755 SYSSTAT.TABLES . . . . . . . . . . 757 Appendix E. Federated systems . . . . 759 Valid server types in SQL statements . . . 759 CTLIB wrapper . . . . . . . . . 759 DBLIB wrapper . . . . . . . . . 759 DJXMSSQL3 wrapper . . . . . . . 759 DRDA wrapper . . . . . . . . . 759 Informix wrapper. . . . . . . . . 761 MSSQLODBC3 wrapper . . . . . . 761 NET8 wrapper. . . . . . . . . . 761 ODBC wrapper . . . . . . . . . 761 OLE DB wrapper . . . . . . . . . 761 SQLNET wrapper. . . . . . . . . 761 Column options for federated systems . . . 762 Function mapping options for federated systems . . . . . . . . . . . . . 763 Server options for federated systems . . . 764 User options for federated systems . . . . 773 Wrapper options for federated systems . . 774 Default forward data type mappings . . . 775 DB2 for z/OS and OS/390 data sources 776 DB2 for iSeries data sources . . . . . 777 DB2 Server for VM and VSE data sources 779 DB2 for UNIX and Windows data sources 780 Informix data sources . . . . . . . 781 Oracle SQLNET data sources . . . . . 782 Oracle NET8 data sources . . . . . . 783 Microsoft SQL Server data sources . . . 785 ODBC data sources . . . . . . . . 788 Sybase data sources . . . . . . . . 789 Default reverse data type mappings. . . . 791 DB2 for z/OS and OS/390 data sources 792 DB2 for iSeries data sources . . . . . 793 DB2 Server for VM and VSE data sources 795 DB2 for UNIX and Windows data sources 796 Informix data sources . . . . . . . 797 Oracle SQLNET data sources . . . . . 798 Oracle NET8 data sources . . . . . . 799 Microsoft SQL Server data sources . . . 801 Sybase data sources . . . . . . . . 801 Appendix F. The SAMPLE database . . . 803 Creating the SAMPLE database . . . . . 803 Erasing the SAMPLE database . . . . . 803 CL_SCHED table . . . . . . . . . . 803 DEPARTMENT table . . . . . . . . . 804 EMPLOYEE table . . . . . . . . . . 806 EMP_ACT table . . . . . . . . . . 808 EMP_PHOTO table . . . . . . . . . 810 EMP_RESUME table . . . . . . . . . 810 IN_TRAY table . . . . . . . . . . . 811 ORG table . . . . . . . . . . . . 811 PROJECT table. . . . . . . . . . . 811 SALES table . . . . . . . . . . . 812 STAFF table. . . . . . . . . . . . 814 STAFFG table (double-byte code pages only) 815 Sample files with BLOB and CLOB data type 816 Quintana photo . . . . . . . . . 816 Quintana resume . . . . . . . . . 816 Nicholls photo . . . . . . . . . . 818 Nicholls resume . . . . . . . . . 818 Adamson photo . . . . . . . . . 819 Adamson resume . . . . . . . . . 819 Walker photo . . . . . . . . . . 821 Walker resume . . . . . . . . . . 821 Appendix G. Reserved schema names and reserved words . . . . . . . . 823 Appendix H. Comparison of isolation levels . . . . . . . . . . . . . 827 Appendix I. Interaction of triggers and constraints . . . . . . . . . . . 829 Appendix J. Explain tables . . . . . . 833 Explain tables . . . . . . . . . . . 833 EXPLAIN_ARGUMENT table . . . . . . 834 EXPLAIN_INSTANCE table . . . . . . 838 EXPLAIN_OBJECT table . . . . . . . 841 EXPLAIN_OPERATOR table . . . . . . 844 EXPLAIN_PREDICATE table . . . . . . 846 EXPLAIN_STATEMENT table. . . . . . 848 EXPLAIN_STREAM table . . . . . . . 851 ADVISE_INDEX table . . . . . . . . 853 ADVISE_WORKLOAD table . . . . . . 856 Appendix K. Explain register values. . . 857 Appendix L. Recursion example: bill of materials . . . . . . . . . . . . 861 Example 1: Single level explosion . . . . 861 Example 2: Summarized explosion . . . . 863 Example 3: Controlling depth . . . . . . 864 viii SQL Reference, Volume 1
Appendix M. Exception tables Categories of DB2 technical information 896 Rules for creating an exception table Printing DB2 books from PDF files Handling rows in eption table 869 Ordering printed DB2 books 904 Querying exception tables 870 Accessing online help 904 F Appendix N SQL statements allowed in Information Center from a browser 873 Finding product information by accessing the DB2 Information Center from the Appendix O CALL invoked from a administration tools compiled statement 877 Viewing technical documentation online directly from the DB2 HTML Documentation Appendix P. Japanese and traditional-Chinese extended unix code Updating the HTML documentation installed (EUC)considerations on your machine Language elements files from Characters 88 Documentation CD to a Web server 912 Tokens 883 Troubleshooting DB2 documentation search with Netscape 4.x Data types Searching the DB2 documentation onstants Online DB2 troubleshooting informatio Functions Accessibility Expressions Keyboard Input and Navigation Predicates Accessible Display 916 Functions Alternative Alert Cues LENGTH Compatibility with Assistive Technologies 916 SUBSTR Accessible documentation TRANSLATE 888 DB2 tutorials VARGRAPHIC DB2 Information Center for topics 917 CONNECT Appendⅸxs. Notices 919 PREPARE Appendix Q. Backus-Naur form(BNF Index 925 specifications for DATALINKs Contacting IBM 945 ppendix R DB2 Universal Database Product information technical information 895 Overview of DB2 Universal Database technical information ontents IX
Appendix M. Exception tables . . . . . 867 Rules for creating an exception table . . . 867 Handling rows in an exception table . . . 869 Querying exception tables . . . . . . . 870 Appendix N. SQL statements allowed in routines . . . . . . . . . . . . 873 Appendix O. CALL invoked from a compiled statement . . . . . . . . 877 Appendix P. Japanese and traditional-Chinese extended UNIX code (EUC) considerations . . . . . . . . 883 Language elements . . . . . . . . . 883 Characters . . . . . . . . . . . 883 Tokens . . . . . . . . . . . . 883 Identifiers . . . . . . . . . . . 883 Data types . . . . . . . . . . . 884 Constants . . . . . . . . . . . 886 Functions . . . . . . . . . . . 886 Expressions . . . . . . . . . . . 887 Predicates . . . . . . . . . . . 887 Functions . . . . . . . . . . . . 888 LENGTH . . . . . . . . . . . 888 SUBSTR . . . . . . . . . . . . 888 TRANSLATE . . . . . . . . . . 888 VARGRAPHIC. . . . . . . . . . 889 Statements . . . . . . . . . . . . 889 CONNECT . . . . . . . . . . . 889 PREPARE . . . . . . . . . . . 889 Appendix Q. Backus-Naur form (BNF) specifications for DATALINKs . . . . . 891 Appendix R. DB2 Universal Database technical information . . . . . . . . 895 Overview of DB2 Universal Database technical information . . . . . . . . 895 Categories of DB2 technical information 896 Printing DB2 books from PDF files . . . . 903 Ordering printed DB2 books . . . . . . 904 Accessing online help . . . . . . . . 904 Finding topics by accessing the DB2 Information Center from a browser . . . . 906 Finding product information by accessing the DB2 Information Center from the administration tools . . . . . . . . . 908 Viewing technical documentation online directly from the DB2 HTML Documentation CD. . . . . . . . . . . . . . . 909 Updating the HTML documentation installed on your machine . . . . . . . . . . 910 Copying files from the DB2 HTML Documentation CD to a Web Server. . . . 912 Troubleshooting DB2 documentation search with Netscape 4.x . . . . . . . . . . 912 Searching the DB2 documentation . . . . 913 Online DB2 troubleshooting information . . 914 Accessibility . . . . . . . . . . . 915 Keyboard Input and Navigation . . . . 915 Accessible Display . . . . . . . . 916 Alternative Alert Cues . . . . . . . 916 Compatibility with Assistive Technologies 916 Accessible Documentation . . . . . . 916 DB2 tutorials . . . . . . . . . . . 916 DB2 Information Center for topics . . . . 917 Appendix S. Notices . . . . . . . . 919 Trademarks . . . . . . . . . . . . 922 Index . . . . . . . . . . . . . 925 Contacting IBM . . . . . . . . . . 945 Product information . . . . . . . . . 945 Contents ix
About this book The SQL Reference in its two volumes defines the SQL language used by DB2 Universal Database Version 8, and includes: Information about relational database concepts, language elements, functions, and the forms of queries(Volume 1) Information about the syntax and semantics of SQL statements(Volume 2) Who should use this book This book is intended for anyone who wants to use the Structured Query Language(SQL) to access a database. It is primarily for programmers and database administrators but it can also be used by those who access databases through the command line processor(CLP This book is a reference rather than a tutorial. It assumes that you will be writing application programs and therefore presents the full functions of the database manager How this book is structured This book contains information about the following major topics Chapter 1,Concepts"on page 1 discusses the basic concepts of relational databases and SQL Chapter 2, "Language elements"on page 61 describes the basic syntax of SQL and the language elements that are common to many SQL statements Chapter 3, Functions"on page 247 contains syntax diagrams, semantic descriptions, rules, and usage examples of SQL column and scalar Chapter 4,"Queries"on page 553 describes the various forms of a query Appendix A, "SQL limits"on page 607 lists SQL limitations Ppendix B, "SQLCA (SQL communications area)"on page 615 describes the SQlCa structure. Appendix C, "SQLDA (SQL descriptor area)"on page 621 describes the SQLDA structure Appendix D, "Catalog views"on page 633 describes the database catalog Appendix E, "Federated systems"on page 759 describes options and type mappings for Federated Systems. ht IBM Corp. 1993-2002
About this book The SQL Reference in its two volumes defines the SQL language used by DB2 Universal Database Version 8, and includes: v Information about relational database concepts, language elements, functions, and the forms of queries (Volume 1). v Information about the syntax and semantics of SQL statements (Volume 2). Who should use this book This book is intended for anyone who wants to use the Structured Query Language (SQL) to access a database. It is primarily for programmers and database administrators, but it can also be used by those who access databases through the command line processor (CLP). This book is a reference rather than a tutorial. It assumes that you will be writing application programs and therefore presents the full functions of the database manager. How this book is structured This book contains information about the following major topics: v Chapter 1, “Concepts” on page 1 discusses the basic concepts of relational databases and SQL. v Chapter 2, “Language elements” on page 61 describes the basic syntax of SQL and the language elements that are common to many SQL statements. v Chapter 3, “Functions” on page 247 contains syntax diagrams, semantic descriptions, rules, and usage examples of SQL column and scalar functions. v Chapter 4, “Queries” on page 553 describes the various forms of a query. v Appendix A, “SQL limits” on page 607 lists SQL limitations. v Appendix B, “SQLCA (SQL communications area)” on page 615 describes the SQLCA structure. v Appendix C, “SQLDA (SQL descriptor area)” on page 621 describes the SQLDA structure. v Appendix D, “Catalog views” on page 633 describes the database catalog views. v Appendix E, “Federated systems” on page 759 describes options and type mappings for Federated Systems. © Copyright IBM Corp. 1993 - 2002 xi