db2 sql ref i

IBM DB2 Universal Database

SQL Reference Volume 1V ersion 8.2

SC09-4844-01

IBM DB2 Universal Database

SQL Reference Volume 1V ersion 8.2

SC09-4844-01

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 - 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 . . . . . . . . . . . ixWho should use this book . . . . . . . . . ix How this book is structured . . . . . . . . . ix A brief overview of Volume 2. . . . . . . . x How to read the syntax diagrams . . . . . . . x Common syntax elements . . . . . . . . . xii Function designator . . . . . . . . . . xii Method designator . . . . . . . . . . xiii Procedure designator . . . . . . . . . . xv Conventions used in this manual . . . . . . . xvi Error conditions . . . . . . . . . . . xvi Highlighting conventions . . . . . . . . xvi Related documentation . . . . . . . . . . xvii The SQL Compiler . . . . . . . The query optimizer . . . . . . Compensation . . . . . . . . Pass-through sessions . . . . . . Wrappers and wrapper modules . . Default wrapper names . . . . . Server definitions and server options . User mappings . . . . . . . . Nicknames and data source objects . Valid data source objects . . . . . Nickname column options . . . . Data type mappings . . . . . . Function mappings . . . . . . . Index specifications . . . . . . . Collating sequences. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41 41 42 43 44 45 46 46 47 47 48 49 50 50 51

|

|

Chapter 1. Concepts . . . . . . . . . 1Relational databases . . . . . . . . . . . . 1 Structured Query Language (SQL) . . . . . . . 1 Privileges, authority levels, and database authorities 1 Schemas . . . . . . . . . . . . . . . . 5 Tables . . . . . . . . . . . . . . . . 6 Views . . . . . . . . . . . . . . . . . 7 Aliases . . . . . . . . . . . . . . . . 7 Indexes . . . . . . . . . . . . . . . . 8 Keys . . . . . . . . . . . . . . . . . 8 Constraints . . . . . . . . . . . . . . . 8 Unique constraints . . . . . . . . . . . 9 Referential constraints . . . . . . . . . . 9 Table check constraints . . . . . . . . . 12 Informational constraints . . . . . . . . . 13 Isolation levels . . . . . . . . . . . . . 13 Comparison of isolation levels . . . . . . . 15 Queries and table expressions . . . . . . . . 16 Application processes, concurrency, and recovery . . 16 DB2 Call level interface (CLI) and open database connectivity (ODBC) . . . . . . . . . . . 18 Java database connectivity (JDBC) and embedded SQL for Java (SQLJ) programs . . . . . . . . 19 Packages . . . . . . . . . . . . . . . 19 Catalog views . . . . . . . . . . . . . 19 Character conversion . . . . . . . . . . . 20 Event monitors . . . . . . . . . . . . . 22 Triggers . . . . . . . . . . . . . . . 23 Table spaces and other storage structures . . . . 24 Data partitioning across multiple partitions . . . . 26 Distributed relational databases. . . . . . . . 27 Remote unit of work . . . . . . . . . . 28 Application-directed distributed unit of work . . 31 Data representation considerations. . . . . . 35 DB2 federated systems . . . . . . . . . . 35 Federated systems . . . . . . . . . . . 35 The federated server . . . . . . . . . . 36 What is a data source? . . . . . . . . . . 37 Supported data sources . . . . . . . . . 37 The federated database . . . . . . . . . 39 The federated database system catalog . . . . 40 Copyright IBM Corp. 1993 - 2004

Chapter 2. Language elements . . . . 55Characters . . . . . . . . . . . . . . Tokens . . . . . . . . . . . . . . . Identifiers . . . . . . . . . . . . . . Naming conventions and implicit object name qualifications . . . . . . . . . . . . Aliases . . . . . . . . . . . . . . Authorization IDs and authorization names . Column names . . . . . . . . . . . References to host variables . . . . . . . Data types . . . . . . . . . . . . . . Data types . . . . . . . . . . . . . Numbers . . . . . . . . . . . . . Character strings . . . . . . . . . . Graphic strings . . . . . . . . . . . Binary strings . . . . . . . . . . . Large objects (LOBs) . . . . . . . . . Datetime values . . . . . . . . . . . DATALINK values . . . . . . . . . . XML values . . . . . . . . . . . . User-defined types . . . . . . . . . . Promotion of data types . . . . . . . . Casting between data types . . . . . . . Assignments and comparisons . . . . . . Rules for result data types . . . . . . . Rules for string conversions . . . . . . Partition-compatible data types . . . . . Constants . . . . . . . . . . . . . Integer constants . . . . . . . . . . Floating-point constants . . . . . . . . Decimal constants . . . . . . . . . . Character string constants . . . . . . . Hexadecimal constants . . . . . . . . Graphic string constants . . . . . . . . Special registers . . . . . . . . . . . Special registers . . . . . . . . . . CURRENT CLIENT_ACCTNG . . . . . CURRENT CLIENT_APPLNAME . . . . CURRENT CLIENT_USERID . . . . . . . 55 . 57 . 58 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58 62 63 66 72 79 79 80 81 82 83 84 85 88 90 91 94 96 99 113 117 119 121 121 121 122 122 122 123 124 124 126 127 128

|

iii

| | |

| | |

CURRENT CLIENT_WRKSTNNAME . . . CURRENT DATE . . . . . . . . . . CURRENT DBPARTITIONNUM . . . . . CURRENT DEFAULT TRANSFORM GROUP CURRENT DEGREE . . . . . . . . . CURRENT EXPLAIN MODE . . . . . . CURRENT EXPLAIN SNAPSHOT . . . . CURRENT ISOLATION . . . . . . . . CURRENT LOCK TIMEOUT . . . . . . CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION . . . . . . . . . . CURRENT PACKAGE PATH . . . . . . CURRENT PATH . . . . . . . . . . CURRENT QUERY OPTIMIZATION . . . CURRENT REFRESH AGE . . . . . . . CURRENT SCHEMA . . . . . . . . . CURRENT SERVER . . . . . . . . . CURRENT TIME . . . . . . . . . . CURRENT TIMESTAMP . . . . . . . CURRENT TIMEZONE . . . . . . . . CURRENT USER . . . . . . . . . . SESSION_USER . . . . . . . . . . SYSTEM_USER . . . . . . . . . . . USER . . . . . . . . . . . . . . Functions . . . . . . . . . . . . . . External, SQL, and sourced user-defined functions . . . . . . . . . . . . . Scalar, column, row, and table user-defined functions . . . . . . . . . . . . . Function signatures . . . . . . . . . Function resolution . . . . . . . . . Function invocation . . . . . . . . . Conservative binding semantics . . . . . Methods . . . . . . . . . . . . . . External and SQL user-defined methods . . Method signatures . . . . . . . . . . Method resolution . . . . . . . . . . Method invocation . . . . . . . . . Dynamic dispatch of methods . . . . . . Expressions . . . . . . . . . . . . . Expressions without operators . . . . . . Expressions with the concatenation operator . Expressions with arithmetic operators . . . Two-integer operands . . . . . . . . Integer and decimal operands . . . . . . Two-decimal operands . . . . . . . . Decimal arithmetic in SQL . . . . . . . Floating-point operands . . . . . . . . User-defined types as operands . . . . . Scalar fullselect . . . . . . . . . . . Datetime operations and durations . . . . Datetime arithmetic in SQL . . . . . . . Precedence of operations . . . . . . . CASE expressions . . . . . . . . . . CAST specifications . . . . . . . . . Dereference operations . . . . . . . . OLAP functions . . . . . . . . . . XML functions . . . . . . . . . . . Method invocation . . . . . . . . . Subtype treatment . . . . . . . . . . Sequence reference . . . . . . . . .

. 129 . 130 . 131 132 . 133 . 134 . 135 . 136 . 137 . . . . . . . . . . . . . . . 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152

Predicates . . . . Predicates . . . Search conditions . Basic predicate . . Quantified predicate BETWEEN predicate EXISTS predicate . IN predicate . . . LIKE predicate . . NULL predicate . TYPE predicate . .

. . . . . . . . . . .

. . . . . . . . . . .

. . . . . . . . . . .

. . . . . . . . . . .

. . . . . . . . . . .

. . . . . . . . . . .

. . . . . . . . . . .

. . . . . . . . . . .

. . . . . . . . . . .

. . . . . . . . . . .

204 204 205 208 209 212 213 214 216 221 222

Chapter 3. Functions . . . . . . . . 225Functions overview . . . . . . . . . Supported functions and SQL administrative routines . . . . . . . . . . . . . Aggregate functions . . . . . . . . . AVG . . . . . . . . . . . . . . CORRELATION . . . . . . . . . . COUNT . . . . . . . . . . . . . COUNT_BIG . . . . . . . . . . . COVARIANCE . . . . . . . . . . . GROUPING . . . . . . . . . . . . MAX . . . . . . . . . . . . . . MIN . . . . . . . . . . . . . . Regression functions . . . . . . . . . STDDEV . . . . . . . . . . . . . SUM . . . . . . . . . . . . . . VARIANCE . . . . . . . . . . . . Scalar functions . . . . . . . . . . ABS or ABSVAL . . . . . . . . . . ACOS . . . . . . . . . . . . . . ASCII . . . . . . . . . . . . . . ASIN . . . . . . . . . . . . . . ATAN . . . . . . . . . . . . . . ATAN2 . . . . . . . . . . . . . ATANH . . . . . . . . . . . . . BIGINT . . . . . . . . . . . . . BLOB . . . . . . . . . . . . . . CEILING or CEIL . . . . . . . . . . CHAR . . . . . . . . . . . . . . CHR . . . . . . . . . . . . . . CLOB . . . . . . . . . . . . . . COALESCE . . . . . . . . . . . . CONCAT . . . . . . . . . . . . . COS . . . . . . . . . . . . . . COSH . . . . . . . . . . . . . . COT . . . . . . . . . . . . . . DATE . . . . . . . . . . . . . . DAY . . . . . . . . . . . . . . DAYNAME . . . . . . . . . . . . DAYOFWEEK . . . . . . . . . . . DAYOFWEEK_ISO . . . . . . . . . DAYOFYEAR . . . . . . . . . . . DAYS . . . . . . . . . . . . . . DBCLOB . . . . . . . . . . . . . DBPARTITIONNUM . . . . . . . . . DECIMAL . . . . . . . . . . . . DECRYPT_BIN and DECRYPT_CHAR . . . DEGREES . . . . . . . . . . . . DEREF . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 225 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 227 257 258 260 261 262 264 265 267 269 270 273 274 275 276 277 278 279 280 281 282 283 284 286 287 288 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 309 313 315 316

. 152 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 152 153 153 157 157 160 160 160 161 164 165 168 169 169 171 172 173 173 173 173 174 174 174 175 179 179 181 183 184 189 198 199 199

iv

SQL Reference, Volume 1

DIFFERENCE . . . . . DIGITS . . . . . . . DLCOMMENT . . . . . DLLINKTYPE . . . . . DLNEWCOPY . . . . . DLPREVIOUSCOPY . . . DLREPLACECONTENT . . DLURLCOMPLETE . . . DLURLCOMPLETEONLY . DLURLCOMPLETEWRITE . DLURLPATH . . . . . DLURLPATHONLY . . . DLURLPATHWRITE . . . DLURLSCHEME . . . . DLURLSERVER . . . . DLVALUE . . . . . . DOUBLE . . . . . . . ENCRYPT . . . . . . EVENT_MON_STATE . . EXP . . . . . . . . FLOAT . . . . . . . FLOOR . . . . . . . GETHINT . . . . . . GENERATE_UNIQUE . . GRAPHIC . . . . . . HASHEDVALUE . . . . HEX . . . . . . . . HOUR. . . . . . . . IDENTITY_VAL_LOCAL . INSERT . . . . . . . INTEGER . . . . . . JULIAN_DAY . . . . . LCASE or LOWER . . . LCASE (SYSFUN schema) . LEFT . . . . . . . . LENGTH . . . . . . . LN . . . . . . . . . LOCATE . . . . . . . LOG . . . . . . . . LOG10 . . . . . . . LONG_VARCHAR . . . LONG_VARGRAPHIC . . LTRIM . . . . . . . LTRIM (SYSFUN schema) . MICROSECOND . . . . MIDNIGHT_SECONDS . . MINUTE . . . . . . . MOD . . . . . . . . MONTH . . . . . . . MONTHNAME . . . . MULTIPLY_ALT . . . . NULLIF . . . . . . . POSSTR . . . . . . . POWER . . . . . . . QUARTER . . . . . . RADIANS . . . . . . RAISE_ERROR . . . . . RAND . . . . . . . . REAL . . . . . . . . REC2XML . . . . . . REPEAT . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

317 318 319 320 321 323 325 327 328 329 330 331 332 333 334 335 336 338 340 341 342 343 344 345 347 349 351 353 354 359 360 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 383 384 386 387 388 389 390 391 392 396

REPLACE . . . . . RIGHT . . . . . . ROUND . . . . . . RTRIM . . . . . . RTRIM (SYSFUN schema) SECOND . . . . . . SIGN . . . . . . . SIN . . . . . . . . SINH . . . . . . . SMALLINT . . . . . SOUNDEX . . . . . SPACE . . . . . . SQRT . . . . . . . SUBSTR . . . . . . TABLE_NAME . . . . TABLE_SCHEMA . . . TAN . . . . . . . TANH . . . . . . . TIME . . . . . . . TIMESTAMP . . . . TIMESTAMP_FORMAT . TIMESTAMP_ISO . . . TIMESTAMPDIFF . . . TO_CHAR . . . . . TO_DATE . . . . . TRANSLATE . . . . TRUNCATE or TRUNC . TYPE_ID . . . . . . TYPE_NAME . . . . TYPE_SCHEMA . . . UCASE or UPPER . . . VALUE . . . . . . VARCHAR . . . . . VARCHAR_FORMAT . VARGRAPHIC . . . . WEEK . . . . . . . WEEK_ISO . . . . . YEAR . . . . . . . Table functions . . . . Procedures . . . . . User-defined functions .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

397 398 399 401 402 403 404 405 406 407 408 409 410 411 414 415 417 418 419 420 421 423 424 426 427 428 430 431 432 433 434 435 436 438 440 442 443 444 445 446 447

Chapter 4. Queries . . . . . . . . . 449SQL queries . . . . . . . . . . Subselect . . . . . . . . . . . select-clause . . . . . . . . . from-clause . . . . . . . . . table-reference . . . . . . . . joined-table . . . . . . . . . where-clause . . . . . . . . group-by-clause . . . . . . . having-clause . . . . . . . . order-by-clause . . . . . . . . fetch-first-clause . . . . . . . Examples of subselects . . . . . Examples of joins . . . . . . . Examples of grouping sets, cube, and Fullselect . . . . . . . . . . . Examples of a fullselect . . . . . Select-statement . . . . . . . . common-table-expression . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . rollup . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 449 450 450 454 454 461 463 463 469 470 472 473 475 477 485 487 490 490

Contents

v

| |

update-clause . . . . . . read-only-clause . . . . . optimize-for-clause . . . . isolation-clause . . . . . . lock-request-clause . . . . Examples of a select-statement

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

495 496 496 497 497 498

Appendix A. SQL limits . . . . . . . 501 Appendix B. SQLCA (SQL communications area) . . . . . . . 507SQLCA field descriptions . . . . . . . . Error reporting . . . . . . . . . . . . SQLCA usage in partitioned database systems . . 507 . 510 . 511

Appendix C. SQLDA (SQL descriptor area) . . . . . . . . . . . . . . . 513SQLDA field descriptions . . . . . . . . . Fields in the SQLDA header . . . . . . . Fields in an occurrence of a base SQLVAR . . . Fields in an occurrence of a secondary SQLVAR Effect of DESCRIBE on the SQLDA . . . . . . SQLTYPE and SQLLEN . . . . . . . . . . Unrecognized and unsupported SQLTYPEs . . Packed decimal numbers . . . . . . . . SQLLEN field for decimal . . . . . . . . 513 514 515 516 517 519 520 520 521

Appendix D. Catalog views . . . . . 523System catalog views . . . . . . . Road map to the catalog views . . . SYSIBM.SYSDUMMY1 . . . . . . SYSCAT.ATTRIBUTES . . . . . . SYSCAT.BUFFERPOOLDBPARTITIONS SYSCAT.BUFFERPOOLS . . . . . . SYSCAT.CASTFUNCTIONS . . . . SYSCAT.CHECKS . . . . . . . . SYSCAT.COLAUTH . . . . . . . SYSCAT.COLCHECKS . . . . . . SYSCAT.COLDIST . . . . . . . . SYSCAT.COLGROUPDIST . . . . . SYSCAT.COLGROUPDISTCOUNTS . . SYSCAT.COLGROUPS . . . . . . SYSCAT.COLIDENTATTRIBUTES . . SYSCAT.COLOPTIONS . . . . . . SYSCAT.COLUMNS . . . . . . . SYSCAT.COLUSE . . . . . . . . SYSCAT.CONSTDEP . . . . . . . SYSCAT.DATATYPES . . . . . . . SYSCAT.DBAUTH . . . . . . . . SYSCAT.DBPARTITIONGROUPDEF . . SYSCAT.DBPARTITIONGROUPS . . . SYSCAT.EVENTMONITORS . . . . SYSCAT.EVENTS . . . . . . . . SYSCAT.EVENTTABLES . . . . . . SYSCAT.FULLHIERARCHIES . . . . SYSCAT.FUNCMAPOPTIONS . . . . SYSCAT.FUNCMAPPARMOPTIONS . SYSCAT.FUNCMAPPINGS . . . . . SYSCAT.HIERARCHIES . . . . . . SYSCAT.INDEXAUTH . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 523 525 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 546 547 548 550 551 552 553 554 555 556 557 558 559 560 561

|

SYSCAT.INDEXCOLUSE . . . . . SYSCAT.INDEXDEP . . . . . . . SYSCAT.INDEXES . . . . . . . . SYSCAT.INDEXEXPLOITRULES . . . SYSCAT.INDEXEXTENSIONDEP . . . SYSCAT.INDEXEXTENSIONMETHODS SYSCAT.INDEXEXTENSIONPARMS . . SYSCAT.INDEXEXTENSIONS . . . . SYSCAT.INDEXOPTIONS . . . . . SYSCAT.KEYCOLUSE . . . . . . SYSCAT.NAMEMAPPINGS . . . . SYSCAT.PACKAGEAUTH . . . . . SYSCAT.PACKAGEDEP . . . . . . SYSCAT.PACKAGES . . . . . . . SYSCAT.PARTITIONMAPS . . . . . SYSCAT.PASSTHRUAUTH . . . . . SYSCAT.PREDICATESPECS . . . . SYSCAT.PROCOPTIONS . . . . . SYSCAT.PROCPARMOPTIONS . . . SYSCAT.REFERENCES . . . . . . SYSCAT.REVTYPEMAPPINGS . . . SYSCAT.ROUTINEAUTH . . . . . SYSCAT.ROUTINEDEP . . . . . . SYSCAT.ROUTINEPARMS . . . . . SYSCAT.ROUTINES . . . . . . . SYSCAT.SCHEMAAUTH . . . . . SYSCAT.SCHEMATA . . . . . . . SYSCAT.SEQUENCEAUTH . . . . . SYSCAT.SEQUENCES . . . . . . SYSCAT.SERVEROPTIONS . . . . . SYSCAT.SERVERS . . . . . . . . SYSCAT.STATEMENTS . . . . . . SYSCAT.TABAUTH . . . . . . . SYSCAT.TABCONST . . . . . . . SYSCAT.TABDEP . . . . . . . . SYSCAT.TABLES . . . . . . . . SYSCAT.TABLESPACES . . . . . . SYSCAT.TABOPTIONS . . . . . . SYSCAT.TBSPACEAUTH . . . . . SYSCAT.TRANSFORMS . . . . . . SYSCAT.TRIGDEP . . . . . . . . SYSCAT.TRIGGERS . . . . . . . SYSCAT.TYPEMAPPINGS . . . . . SYSCAT.USEROPTIONS . . . . . . SYSCAT.VIEWS . . . . . . . . SYSCAT.WRAPOPTIONS . . . . . SYSCAT.WRAPPERS . . . . . . . SYSSTAT.COLDIST . . . . . . . SYSSTAT.COLUMNS . . . . . . . SYSSTAT.INDEXES . . . . . . . SYSSTAT.ROUTINES . . . . . . . SYSSTAT.TABLES . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

562 563 564 568 569 570 571 572 573 574 575 576 577 578 582 583 584 585 586 587 588 590 591 592 594 600 601 602 603 604 605 606 607 609 610 611 615 616 617 618 619 620 621 623 624 625 626 627 628 630 634 636

Appendix E. Federated systems . . . 637| | | |Valid server types in SQL statements BioRS wrapper . . . . . . . BLAST wrapper . . . . . . CTLIB wrapper . . . . . . . Documentum wrapper . . . . DRDA wrapper. . . . . . . Entrez wrapper . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 637 637 637 638 638 638 639

vi


| | |

| | | | | | |

|

| | |

Excel wrapper . . . . . . . . . . . Extended Search wrapper . . . . . . . HMMER wrapper . . . . . . . . . . Informix wrapper . . . . . . . . . . MSSQLODBC3 wrapper . . . . . . . . NET8 wrapper . . . . . . . . . . . ODBC wrapper . . . . . . . . . . . OLE DB wrapper . . . . . . . . . . Table-structured files wrapper . . . . . . Teradata wrapper . . . . . . . . . . Web services wrapper . . . . . . . . WebSphere Business Integration wrapper . . XML wrapper . . . . . . . . . . . Nickname column options for federated systems Function mapping options for federated systems Server options for federated systems . . . . User mapping options for federated systems . . Wrapper options for federated systems . . . . Default forward data type mappings . . . . DB2 for z/OS and OS/390 data sources . . DB2 for iSeries data sources . . . . . . DB2 Server for VM and VSE data sources . . DB2 for Linux, UNIX, and Windows data sources . . . . . . . . . . . . . Informix data sources . . . . . . . . Microsoft SQL Server data sources . . . . ODBC data sources . . . . . . . . . Oracle NET8 data sources . . . . . . . Sybase data sources . . . . . . . . . Teradata data sources. . . . . . . . . Default reverse data type mappings . . . . . DB2 for z/OS and OS/390 data sources . . DB2 for iSeries data sources . . . . . . DB2 for VM and VSE data sources . . . . DB2 for Linux, UNIX, and Windows data sources . . . . . . . . . . . . . Informix data sources . . . . . . . . Microsoft SQL Server data sources . . . . Oracle NET8 data sources . . . . . . . Sybase data sources . . . . . . . . . Teradata data sources. . . . . . . . .

. . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . .

639 639 639 639 639 640 640 640 640 640 640 641 641 641 648 648 662 663 663 664 665 666 667 668 669 672 673 674 675 677 678 679 680 681 682 683 684 685 686

Nicholls resume Adamson photo Adamson resume Walker photo . Walker resume .

. . . . .

. . . . .

. . . . .

. . . . .

. . . . .

. . . . .

. . . . .

. . . . .

. . . . .

. . . . .

. . . . .

698 699 699 700 700

Appendix G. Reserved schema names and reserved words . . . . . . . . 703 Appendix H. Interaction of triggers and constraints . . . . . . . . . . 707 Appendix I. Explain tables . . . . . . 709Explain tables . . . . . EXPLAIN_ARGUMENT table EXPLAIN_INSTANCE table EXPLAIN_OBJECT table. . EXPLAIN_OPERATOR table EXPLAIN_PREDICATE table EXPLAIN_STATEMENT table EXPLAIN_STREAM table . ADVISE_INDEX table . . ADVISE_INSTANCE table . ADVISE_MQT table . . . ADVISE_PARTITION table . ADVISE_TABLE table . . ADVISE_WORKLOAD table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 709 710 714 716 719 721 723 725 727 730 731 732 733 734

| | | |

Appendix J. Explain register values

735

Appendix K. Exception tables . . . . 741Rules for creating an exception table Handling rows in an exception table Querying exception tables . . . . . . . . . . . . . . . . . 741 . 743 . 743

Appendix L. SQL statements allowed in routines . . . . . . . . . . . . 745 Appendix M. CALL invoked from a compiled statement . . . . . . . . . 749 Appendix N. Japanese and traditional-Chinese extended UNIX code (EUC) considerations . . . . . 755Language elements Characters . . Tokens . . . Identifiers . . Data types . . Constants . . Functions . . . Expressions . . Predicates . . Functions . . . . LENGTH . . . SUBSTR . . . TRANSLATE . VARGRAPHIC . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 755 755 755 755 755 757 758 758 758 759 759 759 759 759

Appendix F. The SAMPLE databaseCreating the SAMPLE database . . . . . Erasing the SAMPLE database . . . . . . CL_SCHED table . . . . . . . . . . DEPARTMENT table . . . . . . . . . EMPLOYEE table . . . . . . . . . . EMP_ACT table . . . . . . . . . . EMP_PHOTO table . . . . . . . . . EMP_RESUME table . . . . . . . . . IN_TRAY table . . . . . . . . . . . ORG table . . . . . . . . . . . . PROJECT table . . . . . . . . . . . SALES table . . . . . . . . . . . . STAFF table . . . . . . . . . . . . STAFFG table (double-byte code pages only) . Sample files with BLOB and CLOB data type . Quintana photo . . . . . . . . . Quintana resume . . . . . . . . . Nicholls photo . . . . . . . . . . . . . . . . . . . . . . . . . . . .

687. . . . . . . . . . . . . . . . . . 687 687 687 687 688 690 691 692 692 692 693 693 694 695 696 696 697 698

Contents

vii

Statements . . CONNECT . PREPARE .

. . .

. . .

. . .

. . .

. . .

. . .

. . .

. . .

. . .

. . .

. . .

. 760 . 760 . 760

Appendix O. Backus-Naur form (BNF) specifications for DATALINKs . . . . 761 Appendix P. DB2 Universal Database technical information . . . . . . . . 763| | | | | |DB2 documentation and help . . . . . . . . DB2 documentation updates . . . . . . . DB2 Information Center . . . . . . . . . . DB2 Information Center installation scenarios . . Installing the DB2 Information Center using the DB2 Setup wizard (UNIX) . . . . . . . . . Installing the DB2 Information Center using the DB2 Setup wizard (Windows) . . . . . . . . Invoking the DB2 Information Center . . . . . Updating the DB2 Information Center installed on your computer or intranet server . . . . . . . Displaying topics in your preferred language in the DB2 Information Center . . . . . . . . . . DB2 PDF and printed documentation . . . . . Core DB2 information . . . . . . . . . Administration information . . . . . . . Application development information . . . . Business intelligence information . . . . . . DB2 Connect information . . . . . . . . Getting started information . . . . . . . . Tutorial information . . . . . . . . . . 763 763 764 765 768 770 772 773 774 775 775 775 776 777 777 778 778

| | | | | |

| | |

Optional component information . . . . . Release notes . . . . . . . . . . . Printing DB2 books from PDF files . . . . . Ordering printed DB2 books . . . . . . . Invoking contextual help from a DB2 tool . . . Invoking message help from the command line processor . . . . . . . . . . . . . . Invoking command help from the command line processor . . . . . . . . . . . . . . Invoking SQL state help from the command line processor . . . . . . . . . . . . . . DB2 tutorials . . . . . . . . . . . . DB2 troubleshooting information . . . . . . Accessibility . . . . . . . . . . . . . Keyboard input and navigation . . . . . Accessible display . . . . . . . . . . Compatibility with assistive technologies . . Accessible documentation . . . . . . . Dotted decimal syntax diagrams . . . . . . Common Criteria certification of DB2 Universal Database products . . . . . . . . . . .

. . . . .

778 779 780 780 781

. 782 . 783 . . . . . . . . . 783 783 784 785 785 785 786 786 786

. 788

| | |

Appendix Q. Notices . . . . . . . . 789Trademarks . . . . . . . . . . . . . . 791

Index . . . . . . . . . . . . . . . 793 Contacting IBMProduct information .

. . . . . . . . . . 809. . . . . . . . . . 809

viii


About this bookThe 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 bookThis 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 structuredThis 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 55 describes the basic syntax of SQL and the language elements that are common to many SQL statements. v Chapter 3, Functions, on page 225 contains syntax diagrams, semantic descriptions, rules, and usage examples of SQL column and scalar functions. v Chapter 4, Queries, on page 449 describes the various forms of a query. v Appendix A, SQL limits, on page 501 lists SQL limitations. v Appendix B, SQLCA (SQL communications area), on page 507 describes the SQLCA structure. v Appendix C, SQLDA (SQL descriptor area), on page 513 describes the SQLDA structure. v Appendix D, Catalog views, on page 523 describes the database catalog views. v Appendix E, Federated systems, on page 637 describes options and type mappings for Federated Systems. v Appendix F, The SAMPLE database, on page 687 describes the sample tables used in examples. v Appendix G, Reserved schema names and reserved words, on page 703 contains the reserved schema names and the reserved words for the IBM SQL and ISO/ANSI SQL99 standards. v Appendix H, Interaction of triggers and constraints, on page 707 discusses the interaction of triggers and referential constraints. v Appendix I, Explain tables, on page 709 describes the Explain tables. v Appendix J, Explain register values, on page 735 describes the interaction of the CURRENT EXPLAIN MODE and CURRENT EXPLAIN SNAPSHOT special register values with each other and with the PREP and BIND commands. Copyright IBM Corp. 1993 - 2004

ix

How this book is structuredv Appendix K, Exception tables, on page 741 contains information about user-created tables that are used with the SET INTEGRITY statement. v Appendix L, SQL statements allowed in routines, on page 745 lists the SQL statements that are allowed to execute in routines with different SQL data access contexts. v Appendix M, CALL invoked from a compiled statement, on page 749 describes the CALL statement that can be invoked from a compiled statement. v Appendix N, Japanese and traditional-Chinese extended UNIX code (EUC) considerations, on page 755 lists considerations when using extended UNIX code (EUC) character sets. v Appendix O, Backus-Naur form (BNF) specifications for DATALINKs, on page 761 contains the Backus-Naur form (BNF) specifications for DATALINKs.

A brief overview of Volume 2The second volume of the SQL Reference contains information about the syntax and semantics of SQL statements. The specific chapters in that volume are briefly described here: v SQL statements contains syntax diagrams, semantic descriptions, rules, and examples of all SQL statements. v SQL control statements contains syntax diagrams, semantic descriptions, rules, and examples of SQL procedure statements.

How to read the syntax diagramsThroughout this book, syntax is described using the structure defined as follows: Read the syntax diagrams from left to right and top to bottom, following the path of the line. The symbol indicates the beginning of a syntax diagram.

The symbol indicates that the syntax is continued on the next line. The symbol indicates that the syntax is continued from the previous line. The symbol indicates the end of a syntax diagram.

Syntax fragments start with the symbol and end with the symbol. Required items appear on the horizontal line (the main path).required_item

Optional items appear below the main path.required_item optional_item

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

x


How to read the syntax diagramsoptional_item required_item

If you can choose from two or more items, they appear in a stack. If you must choose one of the items, one item of the stack appears on the main path.required_item required_choice1 required_choice2

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

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

An arrow returning to the left, above the main line, indicates an item that can be repeated. In this case, repeated items must be separated by one or more blanks.

required_item

repeatable_item

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

A repeat arrow above a stack indicates that you can make more than one choice from the stacked items or repeat a single choice. Keywords appear in uppercase (for example, FROM). They must be spelled exactly as shown. Variables appear in lowercase (for example, column-name). They represent user-supplied names or values in the syntax. If punctuation marks, parentheses, arithmetic operators, or other such symbols are shown, you must enter them as part of the syntax.

About this book

xi

How to read the syntax diagramsSometimes a single variable represents a larger fragment of the syntax. For example, in the following diagram, the variable parameter-block represents the whole syntax fragment that is labeled parameter-block:required_item parameter-block

parameter-block:parameter1 parameter2

parameter3 parameter4

Adjacent segments occurring between large bullets (*) may be specified in any sequence.required_item item1 * item2 * item3 * item4

The above diagram shows that item2 and item3 may be specified in either order. Both of the following are valid:required_item item1 item2 item3 item4 required_item item1 item3 item2 item4

Common syntax elementsThe following sections describe a number of syntax fragments that are used in syntax diagrams. The fragments are referenced as follows:fragment

Function designatorA function designator uniquely identifies a single function. Function designators typically appear in DDL statements for functions (such as DROP or ALTER). Syntax: function-designator:FUNCTION function-name ( , ( SPECIFIC FUNCTION specific-name data-type ) )

Description: FUNCTION function-name Identifies a particular function, and is valid only if there is exactly one function instance with the name function-name in the schema. The identified function can have any number of parameters defined for it. In dynamic SQL statements, the CURRENT SCHEMA special register is used as a qualifier for an unqualified object name. In static SQL statements, the QUALIFIER

xii


Function designatorprecompile/bind option implicitly specifies the qualifier for unqualified object names. If no function by this name exists in the named or implied schema, an error (SQLSTATE 42704) is raised. If there is more than one instance of the function in the named or implied schema, an error (SQLSTATE 42725) is raised. FUNCTION function-name (data-type,...) Provides the function signature, which uniquely identifies the function. The function resolution algorithm is not used. function-name Specifies the name of the function. In dynamic SQL statements, the CURRENT SCHEMA special register is used as a qualifier for an unqualified object name. In static SQL statements, the QUALIFIER precompile/bind option implicitly specifies the qualifier for unqualified object names. (data-type,...) Values must match the data types that were specified (in the corresponding position) on the CREATE FUNCTION statement. The number of data types, and the logical concatenation of the data types, is used to identify the specific function instance. If a data type is unqualified, the type name is resolved by searching the schemas on the SQL path. This also applies to data type names specified for a REFERENCE type. It is not necessary to specify the length, precision, or scale for the parameterized data types. Instead, an empty set of parentheses can be coded to indicate that these attributes are to be ignored when looking for a data type match. FLOAT() cannot be used (SQLSTATE 42601), because the parameter value indicates different data types (REAL or DOUBLE). If length, precision, or scale is coded, the value must exactly match that specified in the CREATE FUNCTION statement. A type of FLOAT(n) does not need to match the defined value for n, because 0 < n < 25 means REAL, and 24 < n < 54 means DOUBLE. Matching occurs on the basis of whether the type is REAL or DOUBLE. If no function with the specified signature exists in the named or implied schema, an error (SQLSTATE 42883) is raised. SPECIFIC FUNCTION specific-name Identifies a particular user-defined function, using the name that is specified or defaulted to at function creation time. In dynamic SQL statements, the CURRENT SCHEMA special register is used as a qualifier for an unqualified object name. In static SQL statements, the QUALIFIER precompile/bind option implicitly specifies the qualifier for unqualified object names. The specific-name must identify a specific function instance in the named or implied schema; otherwise, an error (SQLSTATE 42704) is raised.

Method designatorA method designator uniquely identifies a single method. Method designators typically appear in DDL statements for methods (such as DROP or ALTER). Syntax:

About this book

xiii

Method designatormethod-designator:METHOD method-name ( , ( SPECIFIC METHOD specific-name data-type ) ) FOR type-name

Description: METHOD method-name Identifies a particular method, and is valid only if there is exactly one method instance with the name method-name for the type type-name. The identified method can have any number of parameters defined for it. If no method by this name exists for the type, an error (SQLSTATE 42704) is raised. If there is more than one instance of the method for the type, an error (SQLSTATE 42725) is raised. METHOD method-name (data-type,...) Provides the method signature, which uniquely identifies the method. The method resolution algorithm is not used. method-name Specifies the name of the method for the type type-name. (data-type,...) Values must match the data types that were specified (in the corresponding position) on the CREATE TYPE statement. The number of data types, and the logical concatenation of the data types, is used to identify the specific method instance. If a data type is unqualified, the type name is resolved by searching the schemas on the SQL path. This also applies to data type names specified for a REFERENCE type. It is not necessary to specify the length, precision, or scale for the parameterized data types. Instead, an empty set of parentheses can be coded to indicate that these attributes are to be ignored when looking for a data type match. FLOAT() cannot be used (SQLSTATE 42601), because the parameter value indicates different data types (REAL or DOUBLE). If length, precision, or scale is coded, the value must exactly match that specified in the CREATE TYPE statement. A type of FLOAT(n) does not need to match the defined value for n, because 0 < n < 25 means REAL, and 24 < n < 54 means DOUBLE. Matching occurs on the basis of whether the type is REAL or DOUBLE. If no method with the specified signature exists for the type in the named or implied schema, an error (SQLSTATE 42883) is raised. FOR type-name Names the type with which the specified method is to be associated. The name must identify a type already described in the catalog (SQLSTATE 42704). In dynamic SQL statements, the CURRENT SCHEMA special register is used as a qualifier for an unqualified object name. In static SQL statements, the QUALIFIER precompile/bind option implicitly specifies the qualifier for unqualified object names.

xiv


Method designatorSPECIFIC METHOD specific-name Identifies a particular method, using the name that is specified or defaulted to at method creation time. In dynamic SQL statements, the CURRENT SCHEMA special register is used as a qualifier for an unqualified object name. In static SQL statements, the QUALIFIER precompile/bind option implicitly specifies the qualifier for unqualified object names. The specific-name must identify a specific method instance in the named or implied schema; otherwise, an error (SQLSTATE 42704) is raised.

Procedure designatorA procedure designator uniquely identifies a single stored procedure. Procedure designators typically appear in DDL statements for procedures (such as DROP or ALTER). Syntax: procedure-designator:PROCEDURE procedure-name ( , ( SPECIFIC PROCEDURE specific-name data-type ) )

Description: PROCEDURE procedure-name Identifies a particular procedure, and is valid only if there is exactly one procedure instance with the name procedure-name in the schema. The identified procedure can have any number of parameters defined for it. In dynamic SQL statements, the CURRENT SCHEMA special register is used as a qualifier for an unqualified object name. In static SQL statements, the QUALIFIER precompile/bind option implicitly specifies the qualifier for unqualified object names. If no procedure by this name exists in the named or implied schema, an error (SQLSTATE 42704) is raised. If there is more than one instance of the procedure in the named or implied schema, an error (SQLSTATE 42725) is raised. PROCEDURE procedure-name (data-type,...) Provides the procedure signature, which uniquely identifies the procedure. The procedure resolution algorithm is not used. procedure-name Specifies the name of the procedure. In dynamic SQL statements, the CURRENT SCHEMA special register is used as a qualifier for an unqualified object name. In static SQL statements, the QUALIFIER precompile/bind option implicitly specifies the qualifier for unqualified object names. (data-type,...) Values must match the data types that were specified (in the corresponding position) on the CREATE PROCEDURE statement. The number of data types, and the logical concatenation of the data types, is used to identify the specific procedure instance. If a data type is unqualified, the type name is resolved by searching the schemas on the SQL path. This also applies to data type names specified for a REFERENCE type.About this book

xv

Procedure designatorIt is not necessary to specify the length, precision, or scale for the parameterized data types. Instead, an empty set of parentheses can be coded to indicate that these attributes are to be ignored when looking for a data type match. FLOAT() cannot be used (SQLSTATE 42601), because the parameter value indicates different data types (REAL or DOUBLE). If length, precision, or scale is coded, the value must exactly match that specified in the CREATE PROCEDURE statement. A type of FLOAT(n) does not need to match the defined value for n, because 0 < n < 25 means REAL, and 24 < n < 54 means DOUBLE. Matching occurs on the basis of whether the type is REAL or DOUBLE. If no procedure with the specified signature exists in the named or implied schema, an error (SQLSTATE 42883) is raised. SPECIFIC PROCEDURE specific-name Identifies a particular procedure, using the name that is specified or defaulted to at procedure creation time. In dynamic SQL statements, the CURRENT SCHEMA special register is used as a qualifier for an unqualified object name. In static SQL statements, the QUALIFIER precompile/bind option implicitly specifies the qualifier for unqualified object names. The specific-name must identify a specific procedure instance in the named or implied schema; otherwise, an error (SQLSTATE 42704) is raised.

Conventions used in this manualThis section specifies some conventions which are used consistently throughout this manual.

Error conditionsAn error condition is indicated within the text of the manual by listing the SQLSTATE associated with the error in parentheses. For example:A duplicate signature raises an SQL error (SQLSTATE 42723).

Highlighting conventionsThe following conventions are used in this book.Bold Italics Indicates commands, keywords, and other items whose names are predefined by the system. Indicates one of the following: v Names or values (variables) that must be supplied by the user. v General emphasis. v The introduction of a new term. v A reference to another source of information. Monospace Indicates one of the following: v Files and directories. v Information that you are instructed to type at a command prompt or in a window. v Examples of specific data values. v Examples of text similar to what may be displayed by the system. v Examples of system messages.

xvi


Related documentation

Related documentationThe following publications may prove useful in preparing applications: v Administration Guide Contains information required to design, implement, and maintain a database to be accessed either locally or in a client/server environment. v Application Development Guide Discusses the application development process and how to code, compile, and execute application programs that use embedded SQL and APIs to access the database. v DB2 Universal Database for iSeries SQL Reference This book defines Structured Query Language (SQL) as supported by DB2 Query Manager and SQL Development Kit on iSeries (AS/400). It contains reference information for the tasks of system administration, database administration, application programming, and operation. This manual includes syntax, usage notes, keywords, and examples for each of the SQL statements used on iSeries (AS/400) systems running DB2. v DB2 Universal Database for z/OS and OS/390 SQL Reference This book defines Structured Query Language (SQL) used in DB2 for z/OS (OS/390). It provides query forms, SQL statements, SQL procedure statements, DB2 limits, SQLCA, SQLDA, catalog tables, and SQL reserved words for z/OS (OS/390) systems running DB2. v DB2 Spatial Extender Users Guide and Reference This book discusses how to write applications to create and use a geographic information system (GIS). Creating and using a GIS involves supplying a database with resources and then querying the data to obtain information such as locations, distances, and distributions within areas. IBM SQL Reference This book contains all the common elements of SQL that span IBMs database products. It provides limits and rules that assist in preparing portable programs using IBM databases. This manual provides a list of SQL extensions and incompatibilities among the following standards and products: SQL92E, XPG4-SQL, IBM-SQL and the IBM relational database products. American National Standard X3.135-1992, Database Language SQL Contains the ANSI standard definition of SQL. ISO/IEC 9075:1992, Database Language SQL Contains the 1992 ISO standard definition of SQL. ISO/IEC 9075-2:1999, Database Language SQL -- Part 2: Foundation (SQL/Foundation) Contains a large portion of the 1999 ISO standard definition of SQL. ISO/IEC 9075-4:1999, Database Language SQL -- Part 4: Persistent Stored Modules (SQL/PSM) Contains the 1999 ISO standard definition for SQL procedure control statements. ISO/IEC 9075-5:1999, Database Language SQL -- Part 4: Host Language Bindings (SQL/Bindings) Contains the 1999 ISO standard definition for host language bindings and dynamic SQL.

v

v v v

v

v

About this book

xvii

Related documentation

xviii


Chapter 1. ConceptsThis chapter provides a high-level view of concepts that are important to understand when using Structured Query Language (SQL). The reference material contained in the rest of this manual provides a more detailed view.

Relational databasesA relational database is a database that is treated as a set of tables and manipulated in accordance with the relational model of data. It contains a set of objects used to store, manage, and access data. Examples of such objects are tables, views, indexes, functions, triggers, and packages. A partitioned relational database is a relational database whose data is managed across multiple partitions (also called nodes). This separation of data across partitions is transparent to users of most SQL statements. However, some data definition language (DDL) statements take partition information into consideration (for example, CREATE DATABASE PARTITION GROUP). (Data definition language is the subset of SQL statements used to describe data relationships in a database.) A federated database is a relational database whose data is stored in multiple data sources (such as separate relational databases). The data appears as if it were all in a single large database and can be accessed through traditional SQL queries. Changes to the data can be explicitly directed to the appropriate data source.

Structured Query Language (SQL)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 treated 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 base tables. SQL statements are executed by a database manager. One of the functions of the database manager is to transform the specification of a result table into a sequence of internal operations that optimize data retrieval. The transformation occurs in two phases: preparation and 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.

Privileges, authority levels, and database authorities| | | | | Privileges enable users to create or access database resources. Authority levels provide a method of grouping privileges and higher-level database manager maintenance and utility operations. Database authorities enable users to perform activities at the database level. Privileges, authority levels, and database authorities can be used together to control access to the database manager and its database Copyright IBM Corp. 1993 - 2004

1

Structured Query Language (SQL)| | | | | | | | | | | objects. Users can access only those objects for which they have the required privilege, authority level, or database authority, which DB2 Universal Database (DB2 UDB) determines when it performs an authorization check for an authenticated user. The database manager requires that each user be specifically authorized, either implicitly or explicitly, to use each database function needed to perform a specific task. Explicit authorities or privileges are granted to the user (GRANTEETYPE of U in the database catalogs). Implicit authorities or privileges are granted to a group to which the user belongs (GRANTEETYPE of G in the database catalogs). Thus, to create a table, a user must be authorized to create tables; to alter a table, a user must be authorized to alter the table, and so on. Figure 1 illustrates the relationship between authorities and their span of control (database, database manager).

Authority levels

Instance

SYSADMSYSCTRL SYSMAINT SYSMON

CUSTOMER Database authorities

EMPLOYEE Database authorities

Figure 1. Hierarchy of Authorities

| | | | | | | | | | | | |

A user or group can have one or more of the following authorities or privileges: v Administrative authority: SYSADM (system administrator) The SYSADM authority level provides control over all the resources created and maintained by the database manager. The system administrator possesses all the authorities of DBADM, SYSCTRL, SYSMAINT, and SYSMON, and the authority to grant and revoke DBADM authority. The user who possesses SYSADM authority is responsible both for controlling the database manager, and for ensuring the safety and integrity of the data. SYSADM authority provides implicit privileges on all objects in the database, control over which users can access the database manager, and the extent of this access. For more information about SYSADM authority, see System administration authority (SYSADM). DBADM (database administrator)

2


Structured Query Language (SQL)| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | The DBADM database authority provides administrative authority over a single database. This database administrator possesses the privileges required to create objects, issue database commands, and access table data. The database administrator can also grant and revoke CONTROL and individual privileges. For more information about DBADM authority, see Database administration authority (DBADM). v System control authority: SYSCTRL (system control) The SYSCTRL authority level provides control over operations that affect system resources. For example, a user with SYSCTRL authority can create, update, stop, or drop a database. This user can also stop an instance, but cannot access table data. Users with SYSCTRL authority also have SYSMON authority. For more information about SYSCTRL authority, see System control authority (SYSCTRL). SYSMAINT (system maintenance) The SYSMAINT authority level provides the authority required to perform maintenance operations on all databases associated with an instance. A user with SYSMAINT authority can update the database configuration, backup a database or table space, restore an existing database, and monitor a database. Like SYSCTRL, SYSMAINT does not provide access to table data. Users with SYSMAINT authority also have SYSMON authority. For more information about SYSMAINT authority, see System maintenance authority (SYSMAINT). v SYSMON (system monitor authority) The SYSMON authority level provides the authority required to use the database system monitor. For more information about SYSMON authority, see System monitor authority (SYSMON) v Database authorities To perform activities such as creating a table or a routine, or for loading data into a table, specific database authorities are required. For more information, see Database authorities. v Privileges: Privileges are required to perform activities on database objects (for example, to create and drop an index). Privileges strictly define the tasks that a user can perform. For example, a user may have the privilege to create an index on a table, but not a trigger on the same table. CONTROL privilege Possessing the CONTROL privilege on an object allows a user to access that database object, and to grant and revoke privileges to or from other users on that object. Note: The CONTROL privilege only apples to tables, views, nicknames, indexes, and packages. If a different user requires the CONTROL privilege to that object, a user with SYSADM or DBADM authority must grant the CONTROL privilege to that object. The CONTROL privilege cannot be revoked from the object owner. In some situations, the creator of an object automatically obtains the CONTROL privilege on that object. For more information, see Object creation, ownership, and privileges. Individual privileges can be granted to allow a user to carry out specific tasks on specific objects.

Chapter 1. Concepts

3

Structured Query Language (SQL)| | | | | | | | | | | | | | | | Users with administrative authority (SYSADM or DBADM) or the CONTROL privilege can grant and revoke privileges to and from users. Individual privileges and database authorities allow a specific function, but do not include the right to grant the same privileges or authorities to other users. The right to grant table, view, schema, package, routine, and sequence privileges to others can be extended to other users through the WITH GRANT OPTION on the GRANT statement. However, the WITH GRANT OPTION does not allow the person granting the privilege to revoke the privilege once granted. You must have SYSADM authority, DBADM authority, or the CONTROL privilege to revoke the privilege. Privileges can also be granted to PUBLIC. PUBLIC privileges apply to all users (authorization names), including any future users, regardless of whether any individual users have previously been granted the privilege. Implicit privileges may be granted to a user who has the privilege to execute a package. While users can run the application, they do not necessarily require explicit privileges on the data objects used within the package. A user or group can be authorized for any combination of individual privileges or authorities. When a privilege is associated with an object, that object must exist. For example, a user cannot be given the SELECT privilege on a table unless that table has previously been created. Note: Care must be taken when an authorization name is given authorities and privileges and there is no user created with that authorization name. At some later time, a user can be created with that authorization name and automatically receive all of the authorities and privileges associated with that authorization name. | | | | | | | | | The REVOKE statement is used to revoke previously granted privileges. In DB2 UDB, the revoking of a privilege from an authorization name revokes the privilege granted by all authorization names. Revoking a privilege from an authorization name does not revoke that same privilege from any other authorization names that were granted the privilege by that authorization name. For example, assume that CLAIRE grants SELECT WITH GRANT OPTION to RICK, then RICK grants SELECT to BOBBY and CHRIS. If CLAIRE revokes the SELECT privilege from RICK, BOBBY and CHRIS still retain the select privilege. Related concepts: v System administration authority (SYSADM) in the Administration Guide: Implementation v System control authority (SYSCTRL) in the Administration Guide: Implementation v System maintenance authority (SYSMAINT) in the Administration Guide: Implementation v Database administration authority (DBADM) in the Administration Guide: Implementation v LOAD authority in the Administration Guide: Implementation v Database authorities in the Administration Guide: Implementation v v v v Schema privileges in the Administration Guide: Implementation Table space privileges in the Administration Guide: Implementation Table and view privileges in the Administration Guide: Implementation Package privileges in the Administration Guide: Implementation

4


Structured Query Language (SQL)v Index privileges in the Administration Guide: Implementation v Sequence privileges in the Administration Guide: Implementation v Controlling access to database objects in the Administration Guide: Implementation v Indirect privileges through a package in the Administration Guide: Implementation v Routine privileges in the Administration Guide: Implementation v Object creation, ownership, and privileges in the Administration Guide: Implementation v System monitor authority (SYSMON) in the Administration Guide: Implementation

SchemasA schema is a collection of named objects. Schemas provide a logical classification of objects in the database. A schema can contain tables, views, nicknames, triggers, functions, packages, and other objects. | | | | | A schema is also an object in the database. It is explicitly created using the CREATE SCHEMA statement with the current user or a specified authorization ID recorded as the schema owner. It can also be implicitly created when another object is created, provided that the user has IMPLICIT_SCHEMA database authority. A schema name is used as the high order part of a two-part object name. If the object is specifically qualified with a schema name when created, the object is assigned to that schema. If no schema name is specified when the object is created, the default schema name is used. | | | | | For example, a user with DBADM authority creates a schema called C for user A:CREATE SCHEMA C AUTHORIZATION A

User A can then issue the following statement to create a table called X in schema C (provided that user A has the CREATETAB database authority):CREATE TABLE C.X (COL1 INT)

Some schema names are reserved. For example, built-in functions belong to the SYSIBM schema, and the pre-installed user-defined functions belong to the SYSFUN schema. When a database is created, all users have IMPLICIT_SCHEMA authority. This allows any user to create objects in any schema not already in existence. An implicitly created schema allows any user to create other objects in this schema.The ability to create aliases, distinct types, functions, and triggers is extended to implicitly created schemas. The default privileges on an implicitly created schema provide backward compatibility with previous versions. If IMPLICIT_SCHEMA authority is revoked from PUBLIC, schemas can be explicitly created using the CREATE SCHEMA statement, or implicitly created by users (such as those with DBADM authority) who have been granted IMPLICIT_SCHEMA authority. Although revoking IMPLICIT_SCHEMA authority from PUBLIC increases control over the use of schema names, it can result in authorization errors when existing applications attempt to create objects.Chapter 1. Concepts

5

SchemasSchemas also have privileges, allowing the schema owner to control which users have the privilege to create, alter, and drop objects in the schema. A schema owner is initially given all of these privileges on the schema, with the ability to grant them to others. An implicitly created schema is owned by the system, and all users are initially given the privilege to create objects in such a schema. A user with SYSADM or DBADM authority can change the privileges held by users on any schema. Therefore, access to create, alter, and drop objects in any schema (even one that was implicitly created) can be controlled. Related concepts: v Schema privileges in the Administration Guide: Implementation

TablesTables are logical structures maintained by the database manager. Tables are made up of columns and rows. The rows are not necessarily ordered within a table (order is determined by the application program). 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 or one of its subtypes. A row is a sequence of values arranged so that the nth value is a value of the nth column of the table. A base table is created with the CREATE TABLE statement and is used to hold persistent user data. A result table is a set of rows that the database manager selects or generates from one or more base tables to satisfy a query. A summary table is a table defined by a query that is also used to determine the data in the table. Summary tables can be used to improve the performance of queries. If the database manager determines that a portion of a query can be resolved using a summary table, the database manager can rewrite the query to use the summary table. This decision is based on database configuration settings, such as the CURRENT REFRESH AGE and the CURRENT QUERY OPTIMIZATION special registers. A table can define the data type of each column separately, or base the types on the attributes of a user-defined structured type. This is called a typed table. A user-defined structured type may be part of a type hierarchy. A subtype inherits attributes from its supertype. Similarly, a typed table can be part of a table hierarchy. A subtable inherits columns from its supertable. Note that the term subtype applies to a user-defined structured type and all user-defined structured types that are below it in the type hierarchy. A proper subtype of a structured type T is a structured type below T in the type hierarchy. Similarly, the term subtable applies to a typed table and all typed tables that are below it in the table hierarchy. A proper subtable of a table T is a table below T in the table hierarchy. A declared temporary table is created with a DECLARE GLOBAL TEMPORARY TABLE statement and is used to hold temporary data on behalf of a single application. This table is dropped implicitly when the application disconnects from the database.

6


Views

ViewsA view provides a different way of looking at the data in one or more tables; it is a named specification of a result table. The specification is a SELECT statement that is run whenever the view is referenced in an SQL statement. A view has columns and rows just like a base table. All views can be used just like base tables for data retrieval. Whether a view can be used in an insert, update, or delete operation depends on its definition. You can use views to control access to sensitive data, because views allow multiple users to see different presentations of the same data. For example, several users may be accessing a table of data about employees. A manager sees data about his or her employees but not employees in another department. A recruitment officer sees the hire dates of all employees, but not their salaries; a financial officer sees the salaries, but not the hire dates. Each of these users works with a view derived from the base table. Each view appears to be a table and has its own name. When the column of a view is directly derived from the column of a base table, that view column inherits any constraints that apply to the base table column. For example, if a view includes a foreign key of its base table, insert and update operations using that view are subject to the same referential constraints as is the base table. Also, if the base table of a view is a parent table, delete and update operations using that view are subject to the same rules as are delete and update operations on the base table. A view can derive the data type of each column from the result table, or base the types on the attributes of a user-defined structured type. This is called a typed view. Similar to a typed table, a typed view can be part of a view hierarchy. A subview inherits columns from its superview. The term subview applies to a typed view and to all typed views that are below it in the view hierarchy. A proper subview of a view V is a view below V in the typed view hierarchy. A view can become inoperative (for example, if the base table is dropped); if this occurs, the view is no longer available for SQL operations.

AliasesAn alias is an alternative name for a table or a view. It can be used to reference a table or a view if an existing table or view can be referenced. An alias cannot be used in all contexts; for example, it cannot be used in the check condition of a check constraint. An alias cannot reference a declared temporary table. Like tables or views, an alias can be created, dropped, and have comments associated with it. However, unlike tables, aliases can refer to each other in a process called chaining. Aliases are publicly referenced names, so no special authority or privilege is required to use them. Access to the table or the view referred to by an alias, however, does require the authorization associated with these objects. There are other types of aliases, such as database and network aliases. Aliases can also be created for nicknames that refer to data tables or views located on federated systems.

Chapter 1. Concepts

7

Indexes

IndexesAn index is an ordered set of pointers to rows in a base table. Each index is based on the values of data in one or more table columns. An index is an object that is separate from the data in the table. When an index is created, the database manager builds this object and maintains it automatically. Indexes are used by the database manager to: v Improve performance. In most cases, access to data is faster with an index. Although an index cannot be created for a view, an index created for the table on which a view is based can sometimes improve the performance of operations on that view. v Ensure uniqueness. A table with a unique index cannot have rows with identical keys.

KeysA key is a set of columns that can be used to identify or access a particular row or rows. The key is identified in the description of a table, index, or referential constraint. The same column can be part of more than one key. A key that is composed of more than one column is called a composite key. In a table with a composite key, the order of the columns within the composite key is not constrained by the order of the columns within the table. The value of 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 primary key means that each component of the value of the foreign key must be equal to the corresponding component of the value of the primary key. A unique key is a key that is constrained so that no two of its values are equal. The columns of a unique key cannot contain null values. The constraint is enforced by the database manager during the execution of any operation that changes data values, such as INSERT or UPDATE. The mechanism used to enforce the constraint is called 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. A primary key is a special case of a unique key. A table cannot have more than one primary key. A foreign key is a key that is specified in the definition of a referential constraint. A partitioning key is a key that is part of the definition of a table in a partitioned database. The partitioning key is used to determine the partition on which the row of data is stored. If a partitioning key is defined, unique keys and primary keys must include the same columns as the partitioning key, but can have additional columns. A table cannot have more than one partitioning key.

ConstraintsA constraint is a rule that the database manager enforces. There are four types of constraints:

8


Constraintsv A unique constraint is a rule that forbids duplicate values in one or more columns within a table. Unique and primary keys are the supported unique constraints. For example, a unique constraint can be defined on the supplier identifier in the supplier table to ensure that the same supplier identifier is not given to two suppliers. v A referential constraint is a logical 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 name changes. You can define a referential constraint stating that the ID of the supplier in a 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 table check constraint sets restrictions on data added to a specific table. For example, a table check constraint can ensure that the salary level for an employee is at least $20,000 whenever salary data is added or updated in a table containing personnel information. v An informational constraint is a rule that can be used by the SQL compiler, but that is not enforced by the database manager. Referential and table check constraints can be turned on or off. It is generally a good idea, for example, to turn off the enforcement of a constraint when large amounts of data are loaded into a database.

Unique constraintsA unique constraint is the rule that the values of a key are valid only if they are unique within a table. Unique constraints are optional and can be defined in the CREATE TABLE or ALTER TABLE statement using the PRIMARY KEY clause or the UNIQUE clause. The columns specified in a unique constraint must be defined as NOT NULL. The database manager uses a unique index to enforce 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 the 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. When a unique constraint is defined in a CREATE TABLE statement, a unique index is automatically created by the database manager and designated as a primary or unique system-required index. When a unique constraint is defined in an ALTER TABLE statement and an index exists on the same columns, that index is designated as unique and system-required. If such an index does not exist, the unique index is automatically created by the database manager and designated as a primary or unique system-required index. Note that there is a distinction between defining a unique constraint and creating a unique index. Although both enforce uniqueness, a unique index allows nullable columns and generally cannot be used as a parent key.

Referential constraintsReferential integrity is the state of a database in which all values of all foreign keys are valid. A foreign keyis a column or a set of columns in a table whose values areChapter 1. Concepts

9

Constraintsrequired to match at least one primary key or unique key value of a row in its parent table. A referential constraint is the rule that the values of the foreign key are valid only if one of the following conditions is true: v They appear as values of a parent key. v Some component of the foreign key is null. The table containing the parent key is called the parent table of the referential constraint, and the table containing the foreign key is said to be a dependent of that table. Referential constraints are optional and can be defined in the CREATE TABLE statement or the ALTER TABLE statement. Referential constraints are enforced by the database manager during the execution of INSERT, UPDATE, DELETE, ALTER TABLE, ADD CONSTRAINT, and SET INTEGRITY statements. Referential constraints with a delete or an update rule of RESTRICT are enforced before all other referential constraints. Referential constraints with a delete or an update rule of NO ACTION behave like RESTRICT in most cases. Note that referential constraints, check constraints, and triggers can be combined. Referential integrity rules involve the following concepts and terminology: Parent key A primary key or a unique key of a referential constraint. Parent row A row that has at least one dependent row. Parent table A table that contains the parent key of a referential constraint. A table can be a parent in an arbitrary number of referential constraints. A table that is the parent in a referential constraint can also be the dependent in a referential constraint. Dependent table A table that contains at least one referential constraint in its definition. A table can be a dependent in an arbitrary number of referential constraints. A table that is the dependent in a referential constraint can also be the parent in a referential constraint. Descendent table A table is a descendent of table T if it is a dependent of T or a descendent of a dependent of T. Dependent row A row that has at least one parent row. Descendent row A row is a descendent of row r if it is a dependent of r or a descendent of a dependent of r. Referential cycle A set of referential constraints such that each table in the set is a descendent of itself. Self-referencing table A table that is a parent and a dependent in the same referential constraint. The constraint is called a self-referencing constraint.

10


ConstraintsSelf-referencing row A row that is a parent of itself.

Insert ruleThe insert rule of a referential constraint is that a non-null 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. This rule is implicit when a foreign key is specified.

Update ruleThe update rule of a referential constraint is specified when the referential constraint is defined. The choices are NO ACTION and RESTRICT. The update rule applies when a row of the parent or a row of the dependent table is updated. In the case of a parent row, when a value in a column of the parent key is updated, the following rules apply: v If any row in the dependent table matches the original value of the key, the update is rejected when the update rule is RESTRICT. v If any row in the dependent table does not have a corresponding parent key when the update statement is completed (excluding AFTER triggers), the update is rejected when the update rule is NO ACTION. In the case of a dependent row, the NO ACTION update rule is implicit when a foreign key is specified. NO ACTION means that a non-null update value of a foreign key must match some value of the parent key of the parent table when the update statement is completed. The value of a composite foreign key is null if any component of the value is null.

Delete ruleThe delete rule of a referential constraint is specified when the referential constraint is defined. The choices are NO ACTION, RESTRICT, CASCADE, or SET NULL. SET NULL can be specified only if some column of the foreign key allows null values. The delete rule of a referential constraint applies when a row of the parent table is deleted. More precisely, the rule applies when a row of the parent table is the object of a delete or propagated delete operation (defined below), and that row has dependents in the dependent table of the referential constraint. Consider an example where P is the parent table, D is the dependent table, and p is a parent row that is the object of a delete or propagated delete operation. The delete rule works as follows: v With RESTRICT or NO ACTION, an error occurs and no rows are deleted. v With CASCADE, the delete operation is propagated to the dependents of p in table D. v With SET NULL, each nullable column of the foreign key of each dependent of p in table 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.

Chapter 1. Concepts

11

ConstraintsThe deletion of a row from parent table P involves other tables and can affect rows of these tables: v If table D is a dependent of P and the delete rule is RESTRICT or NO ACTION, then D is involved in the operation but is not affected by the operation. v If D is a dependent of P and the delete rule is SET NULL, then D is involved in the operation, and rows of D can be updated during the operation. v If D is a dependent of P and the delete rule is CASCADE, then D is involved in the operation and rows of D can be deleted during the operation. If rows of D are deleted, then the delete operation on P is said to be propagated to D. If D is also a parent table, then 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. | | | | | | | | | | | | | | | | The following restrictions apply to delete-connected relationships: v When a table is delete-connected to itself in a referential cycle of more than one table, the cycle must not contain a delete rule of either RESTRICT or SET NULL. v A table must not both be a dependent table in a CASCADE relationship (self-referencing or referencing another table) and have a self-referencing relationship with a delete rule of either RESTRICT or SET NULL. v When a table is delete-connected to another table through multiple relationships where such relationships have overlapping foreign keys, these relationships must have the same delete rule and none of these can be SET NULL. v When a table is delete-connected to another table through multiple relationships where one of the relationships is specified with delete rule SET NULL, the foreign key definition of this relationship must not contain any partitioning key or MDC key column. v When two tables are delete-connected to the same table through CASCADE relationships, the two tables must not be delete-connected to each other where the delete connected paths end with delete rule RESTRICT or SET NULL.

Table check constraintsA table check constraint is a rule that specifies the values allowed in one or more columns of every row in a table. A constraint is optional, and can be defined using the CREATE TABLE or the ALTER TABLE statement. Specifying table check constraints is done through a restricted form of a search condition. One of the restrictions is that a column name in a table check constraint on table T must identify a column of table T. A table can have an arbitrary number of table check constraints. A table check constraint is enforced by applying its search condition to each row that is inserted or updated. An error occurs if the result of the search condition is false for any row. When one or more table check constraints is defined in the ALTER TABLE statement for a table with existing data, the existing data is checked against the new condition before the ALTER TABLE statement completes. The SET INTEGRITY statement can be used to put the table in check pending state, which allows the ALTER TABLE statement to proceed without checking the data.

12


Constraints

Informational constraintsAn informational constraint is a rule that can be used by the SQL compiler to improve the access path to data. Informational constraints are not enforced by the database manager, and are not used for additional verification of data; rather, they are used to improve query performance. Use the CREATE TABLE or ALTER TABLE statement to define a referential or table check constraint, specifying constraint attributes that determine whether or not the database manager is to enforce the constraint and whether or not the constraint is to be used for query optimization. Related reference: v SET INTEGRITY statement in the SQL Reference, Volume 2 v Appendix H, Interaction of triggers and constraints, on page 707

Isolation levelsThe isolation level associated with an application process defines the degree of isolation of that application process from other concurrently executing application processes. The isolation level of an application process therefore specifies: v The degree to which the rows read and updated by the application are available to other concurrently executing application processes. v The degree to which the update activity of other concurrently executing application processes can affect the application. | | | | | | | | | The isolation level for static SQL statements is specified as an attribute of a package and applies to the application processes that use the package. The isolation level is specified in the program preparation process. For dynamic SQL statements, the default isolation level is the isolation level specified for the package preparing the statement. Use of the SET CURRENT ISOLATION statement allows for alternate isolation levels to be specified for dynamic SQL issued within a session. Depending on the type of lock, this limits or prevents access to the data by concurrent application processes. (Declared temporary tables and their rows cannot be locked because they are only accessible to the application that declared them.) The database manager supports three general categories of locks: Share Limits concurrent application processes to read-only operations on the data. Update Limits concurrent application processes to read-only operations on the data, if these processes have not declared that they might update the row. The database manager assumes that the process currently looking at a row may update it. Exclusive Prevents concurrent application processes from accessing the data in any way. Does not apply to application processes with an isolation level of uncommitted read, which can read but not modify the data. Locking occurs at the base table row. The database manager, however, can replace multiple row locks with a single table lock. This is called lock escalation. An application process is guaranteed at least the minimum requested lock level.

Chapter 1. Concepts

13

Isolation levelsThe DB2 Universal Database database manager supports four isolation levels. Regardless of the isolation level, the database manager places exclusive locks on every row that is inserted, updated, or deleted. Thus, all isolation levels ensure that any row that is changed by this application process during a unit of work is not changed by any other application processes until the unit of work is complete. The isolation levels are: v Repeatable Read (RR) This level ensures that: Any row read during a unit of work is not changed by other application processes until the unit of work is complete. The rows are read in the same unit of work as the corresponding OPEN statement. Use of the optional WITH RELEASE clause on the CLOSE statement means that any guarantees against non-repeatable reads and phantom reads no longer apply to any previously accessed rows if the cursor is reopened. Any row changed by another application process cannot be read until it is committed by that application process. The Repeatable Read level does not allow phantom rows to be viewed (see Read Stability). In addition to any exclusive locks, an application process running at the RR level acquires at least share locks on all the rows it references. Furthermore, the locking is performed so that the application process is completely isolated from the effects of concurrent application processes. v Read Stability (RS) Like the Repeatable Read level, the Read Stability level ensures that: Any row read during a unit of work is not changed by other application processes until the unit of work is complete. The rows are read in the same unit of work as the corresponding OPEN statement. Use of the optional WITH RELEASE clause on the CLOSE statement means that any guarantees against non-repeatable reads no longer apply to any previously accessed rows if the cursor is reopened. Any row changed by another application process cannot be read until it is committed by that application process. Unlike Repeatable Read, Read Stability does not completely isolate the application process from the effects of concurrent application processes. At the RS level, application processes that issue the same query more than once may see additional rows caused by other application processes appending new information to the database. These additional rows are called phantom rows. For example, a phantom row can occur in the following situation: 1. Application process P1 reads the set of rows

db2 sql ref i

Documents