SQL Reference VOLUME 2 Version 8.2 (IBM DB2 Universal Database )

789 Pages·4.523 MB·English

(cid:1)(cid:2)(cid:3)
® ™
IBM DB2 Universal Database
SQL Reference Volume 2
Version 8.2
SC09-4845-01

(cid:1)(cid:2)(cid:3)
® ™
IBM DB2 Universal Database
SQL Reference Volume 2
Version 8.2
SC09-4845-01
Beforeusingthisinformationandtheproductitsupports,besuretoreadthegeneralinformationunderNotices.
ThisdocumentcontainsproprietaryinformationofIBM.Itisprovidedunderalicenseagreementandisprotected
bycopyrightlaw.Theinformationcontainedinthispublicationdoesnotincludeanyproductwarranties,andany
statementsprovidedinthismanualshouldnotbeinterpretedassuch.
YoucanorderIBMpublicationsonlineorthroughyourlocalIBMrepresentative.
v Toorderpublicationsonline,gototheIBMPublicationsCenteratwww.ibm.com/shop/publications/order
v TofindyourlocalIBMrepresentative,gototheIBMDirectoryofWorldwideContactsat
www.ibm.com/planetwide
ToorderDB2publicationsfromDB2MarketingandSalesintheUnitedStatesorCanada,call1-800-IBM-4YOU
(426-4968).
WhenyousendinformationtoIBM,yougrantIBManonexclusiverighttouseordistributetheinformationinany
wayitbelievesappropriatewithoutincurringanyobligationtoyou.
©CopyrightInternationalBusinessMachinesCorporation1993-2004.Allrightsreserved.
USGovernmentUsersRestrictedRights–Use,duplicationordisclosurerestrictedbyGSAADPScheduleContract
withIBMCorp.
Contents
About this book . . . . . . . . . . . v CREATEDATABASEPARTITIONGROUP . . . 156
Whoshouldusethisbook. . . . . . . . . . v CREATEDISTINCTTYPE . . . . . . . . . 158
Howthisbookisstructured . . . . . . . . . v CREATEEVENTMONITOR . . . . . . . . 164
AbriefoverviewofVolume1 . . . . . . . v CREATEFUNCTION. . . . . . . . . . . 180
Howtoreadthesyntaxdiagrams . . . . . . . vi CREATEFUNCTION(ExternalScalar) . . . . . 181
Commonsyntaxelements . . . . . . . . . viii CREATEFUNCTION(ExternalTable) . . . . . 204
Functiondesignator . . . . . . . . . . viii CREATEFUNCTION(OLEDBExternalTable) . . 221
Methoddesignator . . . . . . . . . . . ix CREATEFUNCTION(SourcedorTemplate) . . . 228
Proceduredesignator . . . . . . . . . . xi CREATEFUNCTION(SQLScalar,Table,orRow) 238
Conventionsusedinthismanual . . . . . . . xii CREATEFUNCTIONMAPPING. . . . . . . 247
Errorconditions. . . . . . . . . . . . xii CREATEINDEX . . . . . . . . . . . . 252
Highlightingconventions. . . . . . . . . xii CREATEINDEXEXTENSION. . . . . . . . 261
Relateddocumentation . . . . . . . . . . xiii CREATEMETHOD . . . . . . . . . . . 267
CREATENICKNAME . . . . . . . . . . 273
Statements . . . . . . . . . . . . . 1 CREATEPROCEDURE . . . . . . . . . . 285
CREATEPROCEDURE(External) . . . . . . 286
SupportedSQLstatements. . . . . . . . . . 1
CREATEPROCEDURE(SQL) . . . . . . . . 299
HowSQLstatementsareinvoked . . . . . . . 6
CREATESCHEMA . . . . . . . . . . . 305
Embeddingastatementinanapplicationprogram 6
CREATESEQUENCE. . . . . . . . . . . 308
Dynamicpreparationandexecution . . . . . 7
CREATESERVER . . . . . . . . . . . . 312
Staticinvocationofaselect-statement . . . . . 7
CREATETABLE . . . . . . . . . . . . 316
Dynamicinvocationofaselect-statement. . . . 7
CREATETABLESPACE . . . . . . . . . . 374
Interactiveinvocation . . . . . . . . . . 8
CREATETRANSFORM . . . . . . . . . . 383
SQLusewithotherhostsystems . . . . . . 8
CREATETRIGGER . . . . . . . . . . . 390
SQLreturncodes. . . . . . . . . . . . 8
CREATETYPE(Structured) . . . . . . . . 401
SQLcomments . . . . . . . . . . . . 9
CREATETYPEMAPPING . . . . . . . . . 425
AboutSQLcontrolstatements . . . . . . . . 10
CREATEUSERMAPPING . . . . . . . . . 430
ALLOCATECURSOR . . . . . . . . . . . 11
CREATEVIEW. . . . . . . . . . . . . 432
ALTERBUFFERPOOL. . . . . . . . . . . 13
CREATEWRAPPER . . . . . . . . . . . 446
ALTERDATABASEPARTITIONGROUP . . . . 15
DECLARECURSOR . . . . . . . . . . . 448
ALTERFUNCTION . . . . . . . . . . . 18
DECLAREGLOBALTEMPORARYTABLE . . . 454
ALTERMETHOD . . . . . . . . . . . . 21
DELETE . . . . . . . . . . . . . . . 462
ALTERNICKNAME . . . . . . . . . . . 23
DESCRIBE . . . . . . . . . . . . . . 469
ALTERPROCEDURE . . . . . . . . . . . 30
DISCONNECT . . . . . . . . . . . . . 473
ALTERSEQUENCE . . . . . . . . . . . 33
DROP. . . . . . . . . . . . . . . . 476
ALTERSERVER. . . . . . . . . . . . . 37
ENDDECLARESECTION . . . . . . . . . 500
ALTERTABLE . . . . . . . . . . . . . 40
EXECUTE . . . . . . . . . . . . . . 501
ALTERTABLESPACE . . . . . . . . . . . 72
EXECUTEIMMEDIATE. . . . . . . . . . 507
ALTERTYPE(Structured) . . . . . . . . . 79
EXPLAIN . . . . . . . . . . . . . . 510
ALTERUSERMAPPING. . . . . . . . . . 86
FETCH . . . . . . . . . . . . . . . 515
ALTERVIEW. . . . . . . . . . . . . . 88
FLUSHEVENTMONITOR. . . . . . . . . 518
ALTERWRAPPER . . . . . . . . . . . . 90
FLUSHPACKAGECACHE . . . . . . . . 519
ASSOCIATELOCATORS. . . . . . . . . . 92
FOR . . . . . . . . . . . . . . . . 520
BEGINDECLARESECTION . . . . . . . . 94
FREELOCATOR . . . . . . . . . . . . 523
CALL . . . . . . . . . . . . . . . . 96
GETDIAGNOSTICS . . . . . . . . . . . 524
CASE . . . . . . . . . . . . . . . . 101
GOTO. . . . . . . . . . . . . . . . 527
CLOSE . . . . . . . . . . . . . . . 104
GRANT(DatabaseAuthorities) . . . . . . . 529
COMMENT. . . . . . . . . . . . . . 106
GRANT(IndexPrivileges) . . . . . . . . . 533
COMMIT. . . . . . . . . . . . . . . 116
GRANT(PackagePrivileges) . . . . . . . . 535
CompoundSQL(Dynamic). . . . . . . . . 118
GRANT(RoutinePrivileges) . . . . . . . . 538
CompoundSQL(Embedded) . . . . . . . . 123
GRANT(SchemaPrivileges) . . . . . . . . 542
CompoundSQL(Procedure) . . . . . . . . 127
GRANT(SequencePrivileges). . . . . . . . 545
CONNECT(Type1) . . . . . . . . . . . 135
GRANT(ServerPrivileges). . . . . . . . . 547
CONNECT(Type2) . . . . . . . . . . . 142
GRANT(TableSpacePrivileges) . . . . . . . 549
CREATEALIAS . . . . . . . . . . . . 149
GRANT(Table,View,orNicknamePrivileges) . . 551
CREATEBUFFERPOOL. . . . . . . . . . 152
IF . . . . . . . . . . . . . . . . . 558
© Copyright IBM Corp. 1993 - 2004 iii
INCLUDE . . . . . . . . . . . . . . 560 WHILE . . . . . . . . . . . . . . . 727
INSERT . . . . . . . . . . . . . . . 562
ITERATE. . . . . . . . . . . . . . . 571 Appendix A. DB2 Universal Database
LEAVE . . . . . . . . . . . . . . . 572 technical information . . . . . . . . 729
LOCKTABLE . . . . . . . . . . . . . 574
DB2documentationandhelp . . . . . . . . 729
LOOP. . . . . . . . . . . . . . . . 576
|| DB2documentationupdates . . . . . . . 729
|| MERGE . . . . . . . . . . . . . . . 578
DB2InformationCenter. . . . . . . . . . 730
OPEN. . . . . . . . . . . . . . . . 587
|| DB2InformationCenterinstallationscenarios . . 731
PREPARE . . . . . . . . . . . . . . 592
| InstallingtheDB2InformationCenterusingthe
REFRESHTABLE . . . . . . . . . . . . 601
|| DB2Setupwizard(UNIX) . . . . . . . . . 734
RELEASE(Connection) . . . . . . . . . . 603
| InstallingtheDB2InformationCenterusingthe
RELEASESAVEPOINT . . . . . . . . . . 605
|| DB2Setupwizard(Windows). . . . . . . . 736
RENAME . . . . . . . . . . . . . . 606
InvokingtheDB2InformationCenter . . . . . 738
RENAMETABLESPACE. . . . . . . . . . 608
UpdatingtheDB2InformationCenterinstalledon
REPEAT . . . . . . . . . . . . . . . 609
yourcomputerorintranetserver. . . . . . . 739
RESIGNAL . . . . . . . . . . . . . . 611
| Displayingtopicsinyourpreferredlanguageinthe
RETURN. . . . . . . . . . . . . . . 613
|| DB2InformationCenter. . . . . . . . . . 740
REVOKE(DatabaseAuthorities) . . . . . . . 615
DB2PDFandprinteddocumentation . . . . . 741
REVOKE(IndexPrivileges). . . . . . . . . 619
|| CoreDB2information . . . . . . . . . 741
REVOKE(PackagePrivileges). . . . . . . . 621
Administrationinformation . . . . . . . 741
REVOKE(RoutinePrivileges) . . . . . . . . 624
Applicationdevelopmentinformation . . . . 742
REVOKE(SchemaPrivileges) . . . . . . . . 627
Businessintelligenceinformation. . . . . . 743
|| REVOKE(SequencePrivileges) . . . . . . . 629
DB2Connectinformation . . . . . . . . 743
REVOKE(ServerPrivileges) . . . . . . . . 631
Gettingstartedinformation. . . . . . . . 743
REVOKE(TableSpacePrivileges). . . . . . . 633
Tutorialinformation . . . . . . . . . . 744
REVOKE(Table,View,orNicknamePrivileges) 635
Optionalcomponentinformation. . . . . . 744
ROLLBACK. . . . . . . . . . . . . . 640
Releasenotes . . . . . . . . . . . . 745
SAVEPOINT. . . . . . . . . . . . . . 643
PrintingDB2booksfromPDFfiles . . . . . . 746
SELECT . . . . . . . . . . . . . . . 646
OrderingprintedDB2books . . . . . . . . 746
SELECTINTO . . . . . . . . . . . . . 647
InvokingcontextualhelpfromaDB2tool. . . . 747
SETCONNECTION . . . . . . . . . . . 649
| Invokingmessagehelpfromthecommandline
SETCURRENTDEFAULTTRANSFORMGROUP 651
|| processor. . . . . . . . . . . . . . . 748
SETCURRENTDEGREE . . . . . . . . . 653
| Invokingcommandhelpfromthecommandline
SETCURRENTEXPLAINMODE . . . . . . 655
|| processor. . . . . . . . . . . . . . . 749
SETCURRENTEXPLAINSNAPSHOT. . . . . 658
| InvokingSQLstatehelpfromthecommandline
|| SETCURRENTISOLATION . . . . . . . . 660
|| processor. . . . . . . . . . . . . . . 749
|| SETCURRENTLOCKTIMEOUT . . . . . . 661
DB2tutorials . . . . . . . . . . . . . 749
SETCURRENTMAINTAINEDTABLETYPESFOR
DB2troubleshootinginformation. . . . . . . 750
OPTIMIZATION . . . . . . . . . . . . 663
Accessibility. . . . . . . . . . . . . . 751
|| SETCURRENTPACKAGEPATH . . . . . . 665
Keyboardinputandnavigation . . . . . . 751
SETCURRENTPACKAGESET . . . . . . . 669
Accessibledisplay. . . . . . . . . . . 751
SETCURRENTQUERYOPTIMIZATION . . . . 671
Compatibilitywithassistivetechnologies . . . 752
SETCURRENTREFRESHAGE . . . . . . . 674
Accessibledocumentation . . . . . . . . 752
SETENCRYPTIONPASSWORD . . . . . . . 676
|| Dotteddecimalsyntaxdiagrams . . . . . . . 752
SETEVENTMONITORSTATE . . . . . . . 677
| CommonCriteriacertificationofDB2Universal
SETINTEGRITY . . . . . . . . . . . . 679
|| Databaseproducts. . . . . . . . . . . . 754
SETPASSTHRU . . . . . . . . . . . . 694
SETPATH . . . . . . . . . . . . . . 696
Appendix B. Notices . . . . . . . . 755
SETSCHEMA . . . . . . . . . . . . . 698
Trademarks . . . . . . . . . . . . . . 757
SETSERVEROPTION . . . . . . . . . . 700
|| SETSESSIONAUTHORIZATION . . . . . . 702
Index . . . . . . . . . . . . . . . 759
SETVariable. . . . . . . . . . . . . . 705
SIGNAL . . . . . . . . . . . . . . . 709
UPDATE. . . . . . . . . . . . . . . 712 Contacting IBM . . . . . . . . . . 769
VALUES . . . . . . . . . . . . . . . 722 Productinformation . . . . . . . . . . . 769
VALUESINTO. . . . . . . . . . . . . 723
WHENEVER . . . . . . . . . . . . . 725
iv SQLReference,Volume2
About this book
The SQLReference in its two volumes defines the SQLlanguage 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 SQLstatements (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 “Statements,” on page 1 contains syntax diagrams, semantic descriptions, rules,
and examples of all SQLstatements, including SQLprocedure statements.
A brief overview of Volume 1
The first volume of the SQLReference 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 SQLand the language
elements that are common to many SQLstatements.
v “Functions” contains syntax diagrams, semantic descriptions, rules, and usage
examples of SQLcolumn and scalar functions.
v “Queries” describes the various forms of a query.
v “SQLlimits” lists the SQLlimitations.
v “SQLcommunications area (SQLCA)” describes the SQLCAstructure.
v “SQLdescriptor area (SQLDA)” describes the SQLDAstructure.
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 SQLand ISO/ANSI SQL99
standards.
v “Interaction of triggers and constraints” discusses the interaction of triggers and
referential constraints.
v “Explain tables” describes the Explain tables.
© Copyright IBM Corp. 1993 - 2004 v
A brief overview of Volume 1
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 “Exception tables” contains information about user-created tables that are used
with the SET INTEGRITY statement.
v “SQLstatements allowed in routines” lists the SQLstatements that are allowed
to execute in routines with different SQLdata access contexts.
v “CALL” describes the CALLstatement 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 (cid:1)(cid:1)─── symbol indicates the beginning of a syntax diagram.
The ───(cid:1) symbol indicates that the syntax is continued on the next line.
The (cid:1)─── symbol indicates that the syntax is continued from the previous line.
The ──(cid:1)(cid:3) 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).
(cid:1)(cid:1) required_item (cid:1)(cid:3)
Optional items appear below the main path.
(cid:1)(cid:1) required_item (cid:1)(cid:3)
optional_item
If an optional item appears above the main path, that item has no effect on
execution, and is used only for readability.
optional_item
(cid:1)(cid:1) required_item (cid:1)(cid:3)
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.
vi SQLReference,Volume2
How to read the syntax diagrams
(cid:1)(cid:1) required_item required_choice1 (cid:1)(cid:3)
required_choice2
If choosing one of the items is optional, the entire stack appears below the main
path.
(cid:1)(cid:1) required_item (cid:1)(cid:3)
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
(cid:1)(cid:1) required_item (cid:1)(cid:3)
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.
(cid:1)(cid:1) required_item (cid:6) repeatable_item (cid:1)(cid:3)
If the repeat arrow contains a comma, you must separate repeated items with a
comma.
,
(cid:1)(cid:1) required_item (cid:6) repeatable_item (cid:1)(cid:3)
Arepeat 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.
Sometimes 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:
(cid:1)(cid:1) required_item parameter-block (cid:1)(cid:3)
Aboutthisbook vii
How to read the syntax diagrams
parameter-block:
parameter1
parameter2 parameter3
parameter4
Adjacent segments occurring between “large bullets” (*) may be specified in any
sequence.
(cid:1)(cid:1) required_item item1 * item2 * item3 * item4 (cid:1)(cid:3)
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 elements
The following sections describe a number of syntax fragments that are used in
syntax diagrams. The fragments are referenced as follows:
(cid:1)(cid:1) fragment (cid:1)(cid:3)
Function designator
Afunction designator uniquely identifies a single function. Function designators
typically appear in DDLstatements for functions (such as DROP orALTER).
Syntax:
function-designator:
FUNCTION function-name
( )
,
( (cid:6) data-type )
SPECIFIC FUNCTION specific-name
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 SQLstatements,
the CURRENT SCHEMAspecial register is used as a qualifier for an
unqualified object name. In static SQLstatements, the QUALIFIER
precompile/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.
viii SQLReference,Volume2