IBM DB2 9.7
for Linux, UNIX, and Windows
SQL Reference, Volume 2
Updated September, 2010
Version 9 Release 7
SC27-2457-02
IBM DB2 9.7
for Linux, UNIX, and Windows
SQL Reference, Volume 2
Updated September, 2010
Version 9 Release 7
SC27-2457-02
Note
Before using this information and the product it supports, read the general information under Appendix B, “Notices,” on
page 1223.
Edition Notice
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 IBM Corporation 1993, 2010.
US Government Users Restricted Rights – Use, duplication or disclosure restricted by GSA ADP Schedule Contract
with IBM Corp.
Contents
About this book ...........vii
Who should use this book .........vii
How this book is structured.........vii
How to read the syntax diagrams ......viii
Conventions used in this manual .......x
Error conditions ............x
Highlighting conventions .........x
Related documentation ...........x
SQL statements ...........1
How SQL statements are invoked .......10
Embedding a statement in an application
program ..............10
Dynamic preparation and execution .....11
Static invocation of a select-statement.....11
Dynamic invocation of a select-statement . . . 11
Interactive invocation ..........12
SQL use with other host systems ......12
Detecting and processing error and warning
conditions in host language applications.....12
SQL comments .............13
Conditional compilation in SQL .......14
About SQL control statements ........17
References to SQL parameters, SQL variables,
and global variables ..........17
References to SQL labels .........18
References to SQL condition names .....18
References to SQL statement names .....18
References to SQL cursor names ......19
Function, method, and procedure designators . . . 20
ALLOCATE CURSOR ...........24
ALTER AUDIT POLICY ..........26
ALTER BUFFERPOOL ...........29
ALTER DATABASE PARTITION GROUP ....32
ALTER DATABASE............36
ALTER FUNCTION ...........41
ALTER HISTOGRAM TEMPLATE .......43
ALTER INDEX .............45
ALTER METHOD ............46
ALTER MODULE ............47
ALTER NICKNAME ...........54
ALTER PACKAGE ............63
ALTER PROCEDURE (external) .......66
ALTER PROCEDURE (sourced)........69
ALTER PROCEDURE (SQL) .........71
ALTER SECURITY LABEL COMPONENT ....73
ALTER SECURITY POLICY .........76
ALTER SEQUENCE ...........80
ALTERSERVER.............84
ALTER SERVICE CLASS ..........87
ALTER TABLE .............95
ALTER TABLESPACE ..........142
ALTER THRESHOLD ..........155
ALTER TRUSTED CONTEXT ........166
ALTER TYPE (structured) .........174
ALTER USER MAPPING .........181
ALTER VIEW .............184
ALTER WORK ACTION SET ........186
ALTER WORK CLASS SET .........199
ALTER WORKLOAD...........204
ALTER WRAPPER ...........217
ALTER XSROBJECT ...........219
ASSOCIATE LOCATORS .........220
AUDIT ...............222
BEGIN DECLARE SECTION ........226
CALL ................228
CASE ................236
CLOSE ...............239
COMMENT ..............241
COMMIT...............254
Compound SQL ............256
Compound SQL (inlined) .........257
Compound SQL (embedded) ........262
Compound SQL (compiled) ........266
CONNECT (type 1) ...........281
CONNECT (type 2) ...........288
CREATE ALIAS ............296
CREATE AUDIT POLICY .........300
CREATE BUFFERPOOL ..........303
CREATE DATABASE PARTITION GROUP . . . 307
CREATE EVENT MONITOR ........309
CREATE EVENT MONITOR (activities) ....327
CREATE EVENT MONITOR (locking) .....336
CREATE EVENT MONITOR (package cache)
statement...............340
CREATE EVENT MONITOR (statistics).....346
CREATE EVENT MONITOR (threshold violations) 357
CREATE EVENT MONITOR (unit of work) . . . 367
CREATE FUNCTION...........372
CREATE FUNCTION (external scalar) .....373
CREATE FUNCTION (external table) .....400
CREATE FUNCTION (OLE DB external table) . . 420
CREATE FUNCTION (sourced or template) . . . 429
CREATE FUNCTION (SQL scalar, table, or row) 443
CREATE FUNCTION MAPPING .......458
CREATE GLOBAL TEMPORARY TABLE ....462
CREATE HISTOGRAM TEMPLATE ......474
CREATE INDEX ............476
CREATE INDEX EXTENSION ........494
CREATE METHOD ...........500
CREATE MODULE ...........506
CREATE NICKNAME ..........508
CREATE PROCEDURE ..........521
CREATE PROCEDURE (external).......522
CREATE PROCEDURE (sourced) .......538
CREATE PROCEDURE (SQL) ........544
CREATE ROLE .............554
CREATE SCHEMA ...........555
CREATE SECURITY LABEL COMPONENT . . . 558
CREATE SECURITY LABEL ........561
CREATE SECURITY POLICY ........563
CREATE SEQUENCE...........565
© Copyright IBM Corp. 1993, 2010 iii
