Chapter 1. Statements This chapter contains syntax diagrams, semantic descriptions, rules, and examples of the use of the SQL statements Table 1. SQL Statements SQL Statement ALTER BUFFERPOOL Changes the definition of a buffer pool ALTER DATABASE Changes the definition of a database partition group. PARTITION GROUP ALTER FUNCTION Modifies an existing function by changing the properties of the function. ALTER METHOD Modifies an existing method by changing the method body associated with the method ALTER NICKNAME Changes the definition of a nickname ALTER PROCEDURE Modifies an existing procedure by changing the properties of 482 ALTER SEQUENCE Changes the definition of a sequence. ALTER SERVER Changes the definition of a data source in a federated system. ALTER TABLE Changes the definition of a table ALTER TABLESPACE Changes the definition of a table space. ALTER TYPE Changes the definition of a structured type (Structured) ALTER USER MAPPING Changes the definition of a user authorization mapping. ALTER VIEW Changes the definition of a view by altering a reference type olumn to add ALTER WRAPPER pdates the options that, along with a wrapper module, are BEGIN DECLARE Marks the beginning of a host variable declaration section. SECTION CALL CLOSE a cursor. MMENT Replaces or adds a comment to the description of an COMMIT Terminates a unit of work and commits the database changes nade by that unit of wor Compound SQL Combines one or more other SQL statements into an dynamic c Copyright IBM Corp. 1993-2002
Chapter 1. Statements This chapter contains syntax diagrams, semantic descriptions, rules, and examples of the use of the SQL statements. Table 1. SQL Statements SQL Statement Function Page ALTER BUFFERPOOL Changes the definition of a buffer pool. 12 ALTER DATABASE PARTITION GROUP Changes the definition of a database partition group. 15 ALTER FUNCTION Modifies an existing function by changing the properties of the function. 19 ALTER METHOD Modifies an existing method by changing the method body associated with the method. 22 ALTER NICKNAME Changes the definition of a nickname. 24 ALTER PROCEDURE Modifies an existing procedure by changing the properties of the procedure. 28 ALTER SEQUENCE Changes the definition of a sequence. 32 ALTER SERVER Changes the definition of a data source in a federated system. 37 ALTER TABLE Changes the definition of a table. 41 ALTER TABLESPACE Changes the definition of a table space. 75 ALTER TYPE (Structured) Changes the definition of a structured type. 83 ALTER USER MAPPING Changes the definition of a user authorization mapping. 92 ALTER VIEW Changes the definition of a view by altering a reference type column to add a scope. 95 ALTER WRAPPER Updates the options that, along with a wrapper module, are used to access data sources of a specific type. 97 BEGIN DECLARE SECTION Marks the beginning of a host variable declaration section. 98 CALL Calls a stored procedure. 101 CLOSE Closes a cursor. 107 COMMENT Replaces or adds a comment to the description of an object. 109 COMMIT Terminates a unit of work and commits the database changes made by that unit of work. 120 Compound SQL (Dynamic) Combines one or more other SQL statements into an dynamic block. 123 © Copyright IBM Corp. 1993 - 2002 1
Statements Table 1. SQL Statements(continued) SOL Statement Function Pa Compound SQL Combines one or more other SQL statements into an executable CONNECT (TyPe 1) Connects to an application server according to the rules for remote unit of work. CONNECT (TyPe 2) Connects to an application server according to the rules for 142 application-directed distributed unit of work CREATE ALIAS Defines an alias for a table, view, or another alias. CREATE BUFFERPOOL Creates a new buffer pool CREATE DATABASE Defines a database partition group PARTITION GROUP CREATE DISTINCT Defines a distinct data type TYPE CREATE EVENT Specifies events in the database to monitor. MONITOR CREATE FUNCTION Registers a user-defined function CREATE FUNCTION Registers a user-defined external scalar function. CREATE FUNCTION egisters a user-defined external table function. (External Table) CREATE FUNCTION Registers a user-defined OLE DB external table function. 235 (OLE DB External Table) CREATE FUNCTION egisters a user-defined sourced function. (Sourced or Template) CREATE FUNCTION Registers and defines a user-defined SQL function. (SQL Scalar, Table or Row) CREATE FUNCTIoN Defines a function mapping 263 MAPPING CREATE INDEX Defines an index on a table CREATE INDEX Defines an extension object for use with indexes on tables with 277 EXTENSION structured or distinct type columns CREATE METHOD Associates a method body with a previously defined method 285 CREATE NICKNAME Defines a nickname CREATE PROCEDURE Registers a stored procedure. CREATE PROCEDURE Registers an external stored procedure. (External) SOL Refe erence, volume 2
Table 1. SQL Statements (continued) SQL Statement Function Page Compound SQL (Embedded) Combines one or more other SQL statements into an executable block. 129 CONNECT (Type 1) Connects to an application server according to the rules for remote unit of work. 134 CONNECT (Type 2) Connects to an application server according to the rules for application-directed distributed unit of work. 142 CREATE ALIAS Defines an alias for a table, view, or another alias. 151 CREATE BUFFERPOOL Creates a new buffer pool. 154 CREATE DATABASE PARTITION GROUP Defines a database partition group. 158 CREATE DISTINCT TYPE Defines a distinct data type. 161 CREATE EVENT MONITOR Specifies events in the database to monitor. 168 CREATE FUNCTION Registers a user-defined function. 188 CREATE FUNCTION (External Scalar) Registers a user-defined external scalar function. 190 CREATE FUNCTION (External Table) Registers a user-defined external table function. 217 CREATE FUNCTION (OLE DB External Table) Registers a user-defined OLE DB external table function. 235 CREATE FUNCTION (Sourced or Template) Registers a user-defined sourced function. 243 CREATE FUNCTION (SQL Scalar, Table or Row) Registers and defines a user-defined SQL function. 254 CREATE FUNCTION MAPPING Defines a function mapping. 263 CREATE INDEX Defines an index on a table. 268 CREATE INDEX EXTENSION Defines an extension object for use with indexes on tables with structured or distinct type columns. 277 CREATE METHOD Associates a method body with a previously defined method specification. 285 CREATE NICKNAME Defines a nickname. 291 CREATE PROCEDURE Registers a stored procedure. 296 CREATE PROCEDURE (External) Registers an external stored procedure. 297 Statements 2 SQL Reference, Volume 2
statements Table 1. SQL Statements(continued) SQL Statement Function P CREATE PROCEDURE Registers an SQL stored procedure 311 CREATE SCHEMA Defines a schema CREATE SEQUENCE Defines a sequence. CREATE SERVER Defines a data source to a federated database CREATE TABLE Defines a table CREATE TABLESPACE Defines a table space CREATE TRANSFORM Defines transformation functions CREATE TRIGGER Defines a trigger CREATE TYPE Defines a structured data type. CREATE TYPE Defines a mapping between data types MAPPING CREATE USER Defines a mapping between user authorizations MAPPING CREATE VIEW Defines a view of one or more table, view or nickname. CREATE WRAPPER DECLARE CURSOR Defines an SQL cursor. DECLARE GLOBAL Defines the Global Temporary Table. TEMPORARY TABLE DELETE Deletes one or more rows from a table DESCRIBE Describes the result columns of a prepared SELECT statement 504 DⅠ SCONNECT Terminates one or more connections when there is no active unit of work DROP Deletes objects in the database. END DECLARE Marks the end of a host variable declaration section. SECTION EXECUTE Executes a prepared SQL statement EXECUTE IMMEDIATE Prepares and executes an SQL statement. EXPLAIN ptures information about the chosen access plan. FETCH Assigns values of a row to host variables. 561 FLUSH EVENT Writes out the active internal buffer of an event monitor 565 IONITOR FLUSH PACKAGE Removes all cached dynamic sql statements currently in the ACHE package cache Chapter 1. Statements 3
Table 1. SQL Statements (continued) SQL Statement Function Page CREATE PROCEDURE (SQL) Registers an SQL stored procedure. 311 CREATE SCHEMA Defines a schema. 318 CREATE SEQUENCE Defines a sequence. 322 CREATE SERVER Defines a data source to a federated database. 328 CREATE TABLE Defines a table. 332 CREATE TABLESPACE Defines a table space. 395 CREATE TRANSFORM Defines transformation functions. 405 CREATE TRIGGER Defines a trigger. 414 CREATE TYPE (Structured) Defines a structured data type. 427 CREATE TYPE MAPPING Defines a mapping between data types. 456 CREATE USER MAPPING Defines a mapping between user authorizations. 461 CREATE VIEW Defines a view of one or more table, view or nickname. 463 CREATE WRAPPER Registers a wrapper. 479 DECLARE CURSOR Defines an SQL cursor. 482 DECLARE GLOBAL TEMPORARY TABLE Defines the Global Temporary Table. 488 DELETE Deletes one or more rows from a table. 497 DESCRIBE Describes the result columns of a prepared SELECT statement. 504 DISCONNECT Terminates one or more connections when there is no active unit of work. 509 DROP Deletes objects in the database. 512 END DECLARE SECTION Marks the end of a host variable declaration section. 542 EXECUTE Executes a prepared SQL statement. 544 EXECUTE IMMEDIATE Prepares and executes an SQL statement. 552 EXPLAIN Captures information about the chosen access plan. 556 FETCH Assigns values of a row to host variables. 561 FLUSH EVENT MONITOR Writes out the active internal buffer of an event monitor. 565 FLUSH PACKAGE CACHE Removes all cached dynamic SQL statements currently in the package cache. 566 Statements Chapter 1. Statements 3
Statements Table 1. SQL Statements(continued) SQL Statement Function Pa FREE LOCATOR Removes the association between a locator variable and its 567 value GRANT(Database Grants authorities on the entire database GRANT (Index Grants the CONTROL privilege on indexes in the database. 573 GRANT(Package Grants privileges on packages in the database. GRANT(Routine Grants privileges on a routine(function, method, or procedure GRANT(Schema Grants privileges on a schema GRANT(Sequence Grants privileges on a sequence GRANT(Server Grants privileges to query a specific data source Privileges GRANT (Table, View, or Grants privileges on tables, views and nicknames Nickname Privileges) GRANT (Table Space Grants privileges on a tablespace Privileg INCLUDE code or declarations into a source program one or more rows into a table LOCK TABLE Either prevents concurrent processes from changing a table or prevents concurrent processes from using a table OPEN Prepares a cursor that will be used to retrieve values when the 615 FETCH statement is issued PREPARE Prepares an SQL statement(with optional parameters)for REFRESH TABLE Refreshes the data in a materialized query table RELEASE(Connection) Places one or more connections in the release-pending state RELEASE SAVEPOINT Releases a savepoint within a transaction. 636 RENAME Renames an existing table RENAME TABLESPACE Renames an existing tablespace REVOKE(Database Revokes authorities from the entire database REVOKE (Index Revokes the CONtROl privilege on given inde Privileges 4 SQL Reference, Volume 2
Table 1. SQL Statements (continued) SQL Statement Function Page FREE LOCATOR Removes the association between a locator variable and its value. 567 GRANT (Database Authorities) Grants authorities on the entire database. 569 GRANT (Index Privileges) Grants the CONTROL privilege on indexes in the database. 573 GRANT (Package Privileges) Grants privileges on packages in the database. 575 GRANT (Routine Privileges) Grants privileges on a routine (function, method, or procedure). 579 GRANT (Schema Privileges) Grants privileges on a schema. 583 GRANT (Sequence Privileges) Grants privileges on a sequence. 586 GRANT (Server Privileges) Grants privileges to query a specific data source. 588 GRANT (Table, View, or Nickname Privileges) Grants privileges on tables, views and nicknames. 590 GRANT (Table Space Privileges) Grants privileges on a tablespace. 598 INCLUDE Inserts code or declarations into a source program. 601 INSERT Inserts one or more rows into a table. 603 LOCK TABLE Either prevents concurrent processes from changing a table or prevents concurrent processes from using a table. 613 OPEN Prepares a cursor that will be used to retrieve values when the FETCH statement is issued. 615 PREPARE Prepares an SQL statement (with optional parameters) for execution. 620 REFRESH TABLE Refreshes the data in a materialized query table. 632 RELEASE (Connection) Places one or more connections in the release-pending state. 634 RELEASE SAVEPOINT Releases a savepoint within a transaction. 636 RENAME Renames an existing table. 637 RENAME TABLESPACE Renames an existing tablespace. 640 REVOKE (Database Authorities) Revokes authorities from the entire database. 642 REVOKE (Index Privileges) Revokes the CONTROL privilege on given indexes. 647 Statements 4 SQL Reference, Volume 2
Table 1. SQL Statements(continued) SQL Statement Function P REVOKE(Package Revokes privileges from given packages in the database REVOKE (Routine Revokes privileges on a routine(function, method, or procedure). REVOKE (Schema Revokes privileges on a schema. REVOKE(Server Revokes privileges to query a specific data source Privileges) REVOKE (Table, View, or Revokes privileges from given tables, views or nicknames. Nickname Privileges) REVOKE(Table Space Revokes the USE privilege on a given table space Privileges ROLLBACK Terminates a unit of work and backs out the database changes made by that unit of work. SAVEPOINT Sets a savepoint within a transaction. 674 SELECT INTO Specifies a result table of no more than one row and assigns SET CONNECTIoN Changes the state of a connection from dormant to current, aking the specified location the current server. SET CURRENT Changes the value of the CURRENT DEFAULT TRANSFORM DEFAULT TRANSFORM GROUP special register. GROUP SET CURRENT DEGREE Changes the value of the CURRENT DEGREE special ter SET CURRENT Changes the value of the CURRENT EXPLAIN MODE special EXPLAIN MODE register. SET CURRENT Changes the value of the CURRENT EXPLAIN SNAPSHOT EXPLAIN SNAPSHOT special register SET CURRENT Changes the value of the CURRENT MAINTAINED TABLE MAINTAINED TABLE TYPES FOR OPTIMIZATION special register TYPES FOR OPTIMIZATION SET CURRENT Sets the schema name for package selection. PACKAGESET SET CURRENT QUERY Changes the value of the CURRENT QUERY OPTIMIZATION OPTIMIZATION SET CURRENT Changes the value of the CURRENT REFRESH AGE special REFRESH AGE SET ENCRYPTION Sets the password for encryption. PASSWORD Chapter 1. Statements 5
Table 1. SQL Statements (continued) SQL Statement Function Page REVOKE (Package Privileges) Revokes privileges from given packages in the database. 650 REVOKE (Routine Privileges) Revokes privileges on a routine (function, method, or procedure). 653 REVOKE (Schema Privileges) Revokes privileges on a schema. 657 REVOKE (Server Privileges) Revokes privileges to query a specific data source. 660 REVOKE (Table, View, or Nickname Privileges) Revokes privileges from given tables, views or nicknames. 662 REVOKE (Table Space Privileges) Revokes the USE privilege on a given table space. 668 ROLLBACK Terminates a unit of work and backs out the database changes made by that unit of work. 671 SAVEPOINT Sets a savepoint within a transaction. 674 SELECT INTO Specifies a result table of no more than one row and assigns the values to host variables. 677 SET CONNECTION Changes the state of a connection from dormant to current, making the specified location the current server. 680 SET CURRENT DEFAULT TRANSFORM GROUP Changes the value of the CURRENT DEFAULT TRANSFORM GROUP special register. 683 SET CURRENT DEGREE Changes the value of the CURRENT DEGREE special register. 685 SET CURRENT EXPLAIN MODE Changes the value of the CURRENT EXPLAIN MODE special register. 687 SET CURRENT EXPLAIN SNAPSHOT Changes the value of the CURRENT EXPLAIN SNAPSHOT special register. 689 SET CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION Changes the value of the CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION special register. 691 SET CURRENT PACKAGESET Sets the schema name for package selection. 693 SET CURRENT QUERY OPTIMIZATION Changes the value of the CURRENT QUERY OPTIMIZATION special register. 695 SET CURRENT REFRESH AGE Changes the value of the CURRENT REFRESH AGE special register. 698 SET ENCRYPTION PASSWORD Sets the password for encryption. 700 Statements Chapter 1. Statements 5