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