EXPLAIN 558 SET EVENT MONITOR STATE FETCH SET INTEGRITY FLUSH EVENT MONITOR 565 SET PASSTHRU FLUSH PACKAGE CACHE 66 SET PATH FREE LOCATOR SET SCHEMA GRANT (Database Authorities) SET SERVER OPTION GRANT (Index Privileges) SET Variable GRANT(Package Privileges) UPDATE GRANT(Routine Privileges) GRANT(Schema Privileges) GRANT(Sequence Privileges 9368 VALUES VALUES INTO WHENEVER GRANT (Server Privileges). GRANT(Table, View, or Nickname Chapter 2. SQL control statements eges 590 About SQL control statements le Space Privileges SQL Procedure statement NCLUDE ALLOCATE CURSOR statemen INSERT Assignment statement 61 LOCK TABLE 613 ASSOCIATE LOCATORS statement OPEN 615 CASE statement Compound statement(Procedure) REFRESH TABLE 632 FOR statement RELEASE (Connection) 634 GET DIAGNOSTICS statement RELEASE SAVEPOINT GOTO statement RENAME IF statement RENAME TABLESPACE ITERATE statement REVOKE (Database Authorities) LEAVE Statement REVOKE (Index Privileges) LOOP statement 87 REVOKE(Package Privileges) 50 REPEAT statement 789 REVOKE (Routine Privileges) 653 RESIGNAL Statement REVOKE ( Schema Privileges) 657 RETURN statement REVOKE (Server Privileges SIGNAL statement REVOKE (Table, View, or Nickname WHILE Statement Privileg REVOKE (Table Space Privileges) 668 Appendix A DB2 Universal Database ROLLBACK echnical information SAVEPOINT Overview of db2 Universal database SELECT technical information 801 SELECT INTO Categories of DB2 technical information SET CONNECTION SET CURRENT DEFAULT TRANSFORM inting DB2 books from PDF files Ordering printed DB2 books GROUP 683 Accessing online help SET CURRENT DEGREE Finding topics by accessing the DE SET CURRENT EXPLAIN MODE Information Center from a browser 81 SET CURRENT EXPLAIN SNAPSHOT Finding product information by accessing SET CURRENT MAINTAINED TABLE the DB2 Information Center from the TYPES FOR OPTIMIZATION 691 administration tools 814 SET CURRENT PACKAGESET Viewing technical documentation online SET CURRENT QUERY OPTIMIZATIoN 695 directly from the DB2 HTML Documentation SET CURRENT REFRESH AGE SET ENCRYPTION PASSWORD IV SQL Reference
EXPLAIN . . . . . . . . . . . . 558 FETCH . . . . . . . . . . . . . 561 FLUSH EVENT MONITOR . . . . . . 565 FLUSH PACKAGE CACHE . . . . . . 566 FREE LOCATOR . . . . . . . . . . 567 GRANT (Database Authorities) . . . . . 569 GRANT (Index Privileges) . . . . . . . 573 GRANT (Package Privileges) . . . . . . 575 GRANT (Routine Privileges) . . . . . . 579 GRANT (Schema Privileges) . . . . . . 583 GRANT (Sequence Privileges). . . . . . 586 GRANT (Server Privileges). . . . . . . 588 GRANT (Table, View, or Nickname Privileges) . . . . . . . . . . . . 590 GRANT (Table Space Privileges) . . . . . 598 INCLUDE . . . . . . . . . . . . 601 INSERT . . . . . . . . . . . . . 603 LOCK TABLE . . . . . . . . . . . 613 OPEN. . . . . . . . . . . . . . 615 PREPARE . . . . . . . . . . . . 620 REFRESH TABLE . . . . . . . . . . 632 RELEASE (Connection) . . . . . . . . 634 RELEASE SAVEPOINT . . . . . . . . 636 RENAME . . . . . . . . . . . . 637 RENAME TABLESPACE . . . . . . . 640 REVOKE (Database Authorities) . . . . . 642 REVOKE (Index Privileges) . . . . . . 647 REVOKE (Package Privileges). . . . . . 650 REVOKE (Routine Privileges) . . . . . . 653 REVOKE (Schema Privileges) . . . . . . 657 REVOKE (Server Privileges) . . . . . . 660 REVOKE (Table, View, or Nickname Privileges) . . . . . . . . . . . . 662 REVOKE (Table Space Privileges) . . . . 668 ROLLBACK. . . . . . . . . . . . 671 SAVEPOINT . . . . . . . . . . . 674 SELECT . . . . . . . . . . . . . 676 SELECT INTO . . . . . . . . . . . 677 SET CONNECTION . . . . . . . . . 680 SET CURRENT DEFAULT TRANSFORM GROUP . . . . . . . . . . . . . 683 SET CURRENT DEGREE . . . . . . . 685 SET CURRENT EXPLAIN MODE . . . . 687 SET CURRENT EXPLAIN SNAPSHOT . . 689 SET CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION . . . . . . 691 SET CURRENT PACKAGESET . . . . . 693 SET CURRENT QUERY OPTIMIZATION 695 SET CURRENT REFRESH AGE . . . . . 698 SET ENCRYPTION PASSWORD . . . . . 700 SET EVENT MONITOR STATE . . . . . 702 SET INTEGRITY . . . . . . . . . . 704 SET PASSTHRU . . . . . . . . . . 724 SET PATH . . . . . . . . . . . . 726 SET SCHEMA . . . . . . . . . . . 729 SET SERVER OPTION . . . . . . . . 731 SET Variable . . . . . . . . . . . 733 UPDATE. . . . . . . . . . . . . 738 VALUES . . . . . . . . . . . . . 750 VALUES INTO. . . . . . . . . . . 751 WHENEVER . . . . . . . . . . . 753 Chapter 2. SQL control statements . . . 755 About SQL control statements . . . . . 756 SQL procedure statement . . . . . . . 757 ALLOCATE CURSOR statement . . . . . 759 Assignment statement . . . . . . . . 761 ASSOCIATE LOCATORS statement . . . . 762 CASE statement . . . . . . . . . . 764 Compound statement (Procedure) . . . . 767 FOR statement . . . . . . . . . . . 775 GET DIAGNOSTICS statement . . . . . 777 GOTO statement . . . . . . . . . . 780 IF statement . . . . . . . . . . . 782 ITERATE statement . . . . . . . . . 784 LEAVE statement . . . . . . . . . . 785 LOOP statement . . . . . . . . . . 787 REPEAT statement . . . . . . . . . 789 RESIGNAL statement . . . . . . . . 791 RETURN statement . . . . . . . . . 794 SIGNAL statement . . . . . . . . . 796 WHILE statement . . . . . . . . . . 799 Appendix A. DB2 Universal Database technical information . . . . . . . . 801 Overview of DB2 Universal Database technical information . . . . . . . . 801 Categories of DB2 technical information 802 Printing DB2 books from PDF files . . . . 809 Ordering printed DB2 books . . . . . . 810 Accessing online help . . . . . . . . 810 Finding topics by accessing the DB2 Information Center from a browser . . . . 812 Finding product information by accessing the DB2 Information Center from the administration tools . . . . . . . . . 814 Viewing technical documentation online directly from the DB2 HTML Documentation CD. . . . . . . . . . . . . . . 815 iv SQL Reference, Volume 2
Updating the HTML documentation installed Accessible documentation on your machine DB2 tutorials Copying files from the DB2 HTML DB2 Information Center for topics 823 Documentation Cd to a Web Server Troubleshooting DB2 documentation search Appendix B Notices with Netscape 4.x 818 Trademarks 题25 Searching the DB2 documentation Online DB2 troubleshooting information..820 ndex 831 Keyboard Input and Navigation Contacting IBM 843 Accessible display Product information lternative Alert Cues Compatibility with Assistive Technologies 822
Updating the HTML documentation installed on your machine . . . . . . . . . . 816 Copying files from the DB2 HTML Documentation CD to a Web Server. . . . 818 Troubleshooting DB2 documentation search with Netscape 4.x . . . . . . . . . . 818 Searching the DB2 documentation . . . . 819 Online DB2 troubleshooting information . . 820 Accessibility . . . . . . . . . . . 821 Keyboard Input and Navigation . . . . 821 Accessible Display . . . . . . . . 822 Alternative Alert Cues . . . . . . . 822 Compatibility with Assistive Technologies 822 Accessible Documentation . . . . . . 822 DB2 tutorials . . . . . . . . . . . 822 DB2 Information Center for topics . . . . 823 Appendix B. Notices . . . . . . . . 825 Trademarks . . . . . . . . . . . . 828 Index . . . . . . . . . . . . . 831 Contacting IBM . . . . . . . . . . 843 Product information . . . . . . . . . 843 Contents v
VI SQL Refere
vi SQL Reference, Volume 2
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,"Statements"on page 1 contains syntax diagrams, semantic descriptions, rules, and examples of all SQL statements Chapter 2,SQL control statements"on page 755 contains syntax diagrams, semantic descriptions, rules, and examples of SQL procedure statements a brief overview of volume 1 The first volume of the SQL Reference contains information about relational database concepts, language elements, functions, and the forms of queries The specific chapters and appendixes in that volume are briefly described Concepts"discusses the basic concepts of relational databases and SQL Language elements"describes the basic syntax of SQL and the language elements that are common to many SQL statements Functions"contains syntax diagrams, semantic descriptions, rules, and usage examples of SQL column and scalar functions Queries"describes the various forms of a query SQL limits"lists the SQL limitations SQL communications area(SQLCA)"describes the SQLCA structure 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, “Statements” on page 1 contains syntax diagrams, semantic descriptions, rules, and examples of all SQL statements. v Chapter 2, “SQL control statements” on page 755 contains syntax diagrams, semantic descriptions, rules, and examples of SQL procedure statements. A brief overview of Volume 1 The first volume of the SQL Reference contains information about relational database concepts, language elements, functions, and the forms of queries. The specific chapters and appendixes in that volume are briefly described here: v “Concepts” discusses the basic concepts of relational databases and SQL. v “Language elements” describes the basic syntax of SQL and the language elements that are common to many SQL statements. v “Functions” contains syntax diagrams, semantic descriptions, rules, and usage examples of SQL column and scalar functions. v “Queries” describes the various forms of a query. v “SQL limits” lists the SQL limitations. v “SQL communications area (SQLCA)” describes the SQLCA structure. © Copyright IBM Corp. 1993 - 2002 vii
a brief overview of volume 1 SQL descriptor area(SQLDA)"describes the SQLDA structure Catalog views"describes the database catalog views Federated systems"describes options and type mappings for federated "Sample database tables"describes the sample tables used in examples Reserved schema names and reserved words" contains the reserved schema names and the reserved words for the IBM SQL and ISO/ANS SQL99 standards Comparison of isolation levels" contains a summary of the isolation levels 'Interaction of triggers and constraints"discusses the interaction of triggers and referential constraints Explain tables"describes the Explain tables Explain register values"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 Recursion example: bill of materials"contains an example of a recursive query. Exception tables"contains information about user-created tables that are used with the SEt INTEGRiTY statement SQL statements allowed in routines" lists the SQl statements that are allowed to execute in routines with different SQL data access contexts call describes the Call statement that can be invoked from a compiled statement Japanese and traditional-Chinese EUC considerations"lists considerations when using extended UNIX code(EUC)character sets BNF specifications for DATALINKs"contains the Backus-Naur form(BNF) specifications for DATALINKs How to read the syntax diagrams Throughout 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 rk 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 VIll SQL Reference, Volume 2
v “SQL descriptor area (SQLDA)” describes the SQLDA structure. v “Catalog views” describes the database catalog views. v “Federated systems” describes options and type mappings for federated systems. v “Sample database tables” describes the sample tables used in examples. v “Reserved schema names and reserved words” contains the reserved schema names and the reserved words for the IBM SQL and ISO/ANS SQL99 standards. v “Comparison of isolation levels” contains a summary of the isolation levels. v “Interaction of triggers and constraints” discusses the interaction of triggers and referential constraints. v “Explain tables” describes the Explain tables. v “Explain register values” 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. v “Recursion example: bill of materials” contains an example of a recursive query. v “Exception tables” contains information about user-created tables that are used with the SET INTEGRITY statement. v “SQL statements allowed in routines” lists the SQL statements that are allowed to execute in routines with different SQL data access contexts. v “CALL” describes the CALL statement that can be invoked from a compiled statement. v “Japanese and traditional-Chinese EUC considerations” lists considerations when using extended UNIX code (EUC) character sets. v “BNF specifications for DATALINKs” contains the Backus-Naur form (BNF) specifications for DATALINKs. How to read the syntax diagrams Throughout 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. A brief overview of Volume 1 viii SQL Reference, Volume 2