IBM DB2 Universal database 重重 SQL Reference volume 2 e 8 Sc09484500
IBM® DB2 Universal Database™ SQL Reference Volume 2 Version 8 SC09-4845-00
IBM DB2 Universal database 重重 SQL Reference volume 2 e 8 Sc09484500
IBM® DB2 Universal Database™ SQL Reference Volume 2 Version 8 SC09-4845-00
efore using this information and the product it supports, be sure to read the general information under Notices This document contains proprietary information of IBM. It is provided under a license agreement and is protected by copyright law. The information contained in this publication does not include any product warranties, and any statements provided in this manual should not be interpreted as such. You can order IBM publications online or through your local IBM representative. ToorderpublicationsonlinegototheIbMPublicationsCenteratwww.ibm.com/shop/publications/order To find your local IBM representative, go to the IBM Directory of Worldwide Contacts at To order DB2 publications from DB2 Marketing and Sales in the United States or Canada, call 1-800-IBM-YOU (426-4968) When you send information to IBM, you grant IBM a nonexclusive right to use or distribute the information in any way it believes appropriate without incur ny obligation to you o Copyright International Business Machines Corporation 1993-2002. All rights reserved. US Government Users Restricted Rights- Use, duplication or disclosure restricted by GSA ADP Schedule Contract with IBM Corp
Before using this information and the product it supports, be sure to read the general information under Notices. This document contains proprietary information of IBM. It is provided under a license agreement and is protected by copyright law. The information contained in this publication does not include any product warranties, and any statements provided in this manual should not be interpreted as such. You can order IBM publications online or through your local IBM representative. v To order publications online, go to the IBM Publications Center at www.ibm.com/shop/publications/order v To find your local IBM representative, go to the IBM Directory of Worldwide Contacts at www.ibm.com/planetwide To order DB2 publications from DB2 Marketing and Sales in the United States or Canada, call 1-800-IBM-4YOU (426-4968). When you send information to IBM, you grant IBM a nonexclusive right to use or distribute the information in any way it believes appropriate without incurring any obligation to you. © Copyright International Business Machines Corporation 1993 - 2002. All rights reserved. US Government Users Restricted Rights – Use, duplication or disclosure restricted by GSA ADP Schedule Contract with IBM Corp
Contents About this book vii Compound SQL(Embedded) 130 Who should use this book CONNECT (Type 1) How this book is structured CONNECT(Type 2 a brief overview of volume 1 CREATE ALIAS How to read the syntax diagrams viii CREATE BUFFERPOOL Common syntax elements CREATE DATABASE PARTITION GROUP 158 Function designator xi CREATE DISTINCT TYPE Method designator. CREATE EVENT MONITOR Procedure designator CREATE FUNCTION Conventions used in this manual xvi CREATE FUNCTION (External Scalar) Error conditions xvi CREATE FUNCTION(External Table)... 217 Highlighting convention xvi CREATE FUNCTION (OLE DB External Related documentation CREATE FUNCTION (Sourced or Template) 243 Chapter 1. Statements 1 CREATE FUNCTION (SQL Scalar, Table or How SQL statements are invoked R Embedding a statement in an application CREATE FUNCTION MAPPING program. 7 CREATE INDEX Dynamic preparation and execution CREATE INDEX EXTENSION Static invocation of a select-statement 9 CREATE METHOD Dynamic invocation of a select-statement. .9 CREATE NICKNAME Interactive invocation CREATE PROCEDURE SQL use with other host systems 9 CREATE PROCEDURE (External) SQL return codes CREATE PROCEDURE (SQL) 10 CREATE SCHEMA ALTER BUFFERPOOL 12 CREATE SEQUENCE ALTER DATABASE PARTITION GROUP 15 CREATE SERVER ALTER FUNCTION 19 CREATE TABLE ALTER METHOD 22 CREATE TABLESPACE ALTER NICKNAME CREATE TRANSFORM ALTER PROCEDURE CREATE TRIGGER ALTER SEQUENCE 32 CREATE TYPE(Structured) 427 ALTER SERVER 37 CREATE TYPE MAPPING ALTER TABLE CREATE USER MAPPING ALTER TABLESPACE CREATE VIEW ALTER TYPE( Structured) 83 CREATE WRAPPER ALTER USER MAPPING 92 DECLARE CURSOR ALTER VIEW 95 DECLARE GLOBAL TEMPORARY TABLE 488 ALTER WRAPPER DELETE BEGIN DECLARE SECTION DESCRIBE CALL DISCONNECT 509 CLOSE 512 COMMENT 109 END DECLARE SECTION COMMIT EXECUTE Compound SQL (Dynamic) EXECUTE IMMEDIATE 552 C Copyright IBM Corp 1993-2002
Contents About this book . . . . . . . . . . vii Who should use this book . . . . . . . vii How this book is structured . . . . . . vii A brief overview of Volume 1 . . . . . vii How to read the syntax diagrams . . . . viii Common syntax elements . . . . . . . xi Function designator . . . . . . . . xi Method designator. . . . . . . . . xii Procedure designator. . . . . . . . xiv Conventions used in this manual. . . . . xvi Error conditions . . . . . . . . . xvi Highlighting conventions . . . . . . xvi Related documentation . . . . . . . . xvi Chapter 1. Statements . . . . . . . .1 How SQL statements are invoked . . . . .7 Embedding a statement in an application program. . . . . . . . . . . . . 7 Dynamic preparation and execution . . . 8 Static invocation of a select-statement . . . 9 Dynamic invocation of a select-statement. . 9 Interactive invocation . . . . . . . .9 SQL use with other host systems . . . .9 SQL return codes . . . . . . . . . 10 SQL comments . . . . . . . . . . 10 ALTER BUFFERPOOL . . . . . . . . 12 ALTER DATABASE PARTITION GROUP . . 15 ALTER FUNCTION . . . . . . . . . 19 ALTER METHOD . . . . . . . . . . 22 ALTER NICKNAME . . . . . . . . . 24 ALTER PROCEDURE . . . . . . . . . 28 ALTER SEQUENCE . . . . . . . . . 32 ALTER SERVER. . . . . . . . . . . 37 ALTER TABLE . . . . . . . . . . . 41 ALTER TABLESPACE . . . . . . . . . 75 ALTER TYPE (Structured) . . . . . . . 83 ALTER USER MAPPING. . . . . . . . 92 ALTER VIEW . . . . . . . . . . . 95 ALTER WRAPPER . . . . . . . . . . 97 BEGIN DECLARE SECTION . . . . . . 98 CALL . . . . . . . . . . . . . . 101 CLOSE . . . . . . . . . . . . . 107 COMMENT. . . . . . . . . . . . 109 COMMIT . . . . . . . . . . . . 120 Compound SQL (Dynamic) . . . . . . 123 Compound SQL (Embedded) . . . . . . 130 CONNECT (Type 1) . . . . . . . . . 134 CONNECT (Type 2) . . . . . . . . . 149 CREATE ALIAS . . . . . . . . . . 151 CREATE BUFFERPOOL. . . . . . . . 154 CREATE DATABASE PARTITION GROUP 158 CREATE DISTINCT TYPE . . . . . . . 161 CREATE EVENT MONITOR . . . . . . 172 CREATE FUNCTION . . . . . . . . 188 CREATE FUNCTION (External Scalar) . . . 190 CREATE FUNCTION (External Table) . . . 217 CREATE FUNCTION (OLE DB External Table) . . . . . . . . . . . . . . 235 CREATE FUNCTION (Sourced or Template) 243 CREATE FUNCTION (SQL Scalar, Table or Row) . . . . . . . . . . . . . . 254 CREATE FUNCTION MAPPING . . . . 263 CREATE INDEX . . . . . . . . . . 268 CREATE INDEX EXTENSION . . . . . 277 CREATE METHOD . . . . . . . . . 285 CREATE NICKNAME . . . . . . . . 295 CREATE PROCEDURE . . . . . . . . 296 CREATE PROCEDURE (External) . . . . 297 CREATE PROCEDURE (SQL) . . . . . . 311 CREATE SCHEMA . . . . . . . . . 318 CREATE SEQUENCE . . . . . . . . 322 CREATE SERVER . . . . . . . . . . 328 CREATE TABLE . . . . . . . . . . 332 CREATE TABLESPACE . . . . . . . . 395 CREATE TRANSFORM . . . . . . . . 405 CREATE TRIGGER . . . . . . . . . 414 CREATE TYPE (Structured) . . . . . . 427 CREATE TYPE MAPPING . . . . . . . 456 CREATE USER MAPPING . . . . . . . 461 CREATE VIEW . . . . . . . . . . 470 CREATE WRAPPER . . . . . . . . . 479 DECLARE CURSOR . . . . . . . . . 482 DECLARE GLOBAL TEMPORARY TABLE 488 DELETE . . . . . . . . . . . . . 497 DESCRIBE . . . . . . . . . . . . 504 DISCONNECT . . . . . . . . . . . 509 DROP. . . . . . . . . . . . . . 512 END DECLARE SECTION . . . . . . . 542 EXECUTE . . . . . . . . . . . . 544 EXECUTE IMMEDIATE. . . . . . . . 552 © Copyright IBM Corp. 1993 - 2002 iii
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