SQL Reference

SQL Statements

This chapter contains syntax diagrams, semantic descriptions, rules, and examples of the use of the SQL statements.

Table 18. SQL Statements

SQL Statement	Function
ALTER BUFFERPOOL	Changes the definition of a buffer pool.
ALTER NICKNAME	Changes the definition of a nickname.
ALTER NODEGROUP	Changes the definition of a nodegroup.
ALTER SERVER	Changes the definition of a server.
ALTER TABLE	Changes the definition of a table.
ALTER TABLESPACE	Changes the definition of a table space.
ALTER TYPE (Structured)	Changes the definition of a structured type.
ALTER USER MAPPING	Changes the definition of a user authorization mapping.
ALTER VIEW	Changes the definition of a view by altering a reference type column to add a scope.
BEGIN DECLARE SECTION	Marks the beginning of a host variable declaration section.
CALL	Calls a stored procedure.
CLOSE	Closes a cursor.
COMMENT ON	Replaces or adds a comment to the description of an object.
COMMIT	Terminates a unit of work and commits the database changes made by that unit of work.
Compound SQL (Embedded)	Combines one or more other SQL statements into an executable block.
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 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 DISTINCT TYPE	Defines a distinct data type.
CREATE EVENT MONITOR	Specifies events in the database to monitor.
CREATE FUNCTION	Registers a user-defined function.
CREATE FUNCTION (External Scalar)	Registers a user-defined external scalar function.
CREATE FUNCTION (External Table)	Registers a user-defined external table function.
CREATE FUNCTION (OLE DB External Table)	Registers a user-defined OLE DB external table function.
CREATE FUNCTION (Source or Template)	Registers a user-defined sourced function.
CREATE FUNCTION (SQL Scalar, Table or Row)	Registers and defines a user-defined SQL function.
CREATE FUNCTION MAPPING	Defines a function mapping.
CREATE INDEX	Defines an index on a table.
CREATE INDEX EXTENSION	Defines an extension object for use with indexes on tables with structured or distinct type columns.
CREATE METHOD	Associates a method body with a previously defined method specification.
CREATE NICKNAME	Defines a nickname.
CREATE NODEGROUP	Defines a nodegroup.
CREATE PROCEDURE	Registers a stored procedure.
CREATE SCHEMA	Defines a schema.
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 (Structured)	Defines a structured data type.
CREATE TYPE MAPPING	Defines a mapping between data types.
CREATE USER MAPPING	Defines a mapping between user authorizations.
CREATE VIEW	Defines a view of one or more table, view or nickname.
CREATE WRAPPER	Registers a wrapper.
DECLARE CURSOR	Defines an SQL cursor.
DECLARE GLOBAL TEMPORARY TABLE	Defines the Global Temporary Table.
DELETE	Deletes one or more rows from a table.
DESCRIBE	Describes the result columns of a prepared SELECT statement.
DISCONNECT	Terminates one or more connections when there is no active unit of work.
DROP	Deletes objects in the database.
END DECLARE SECTION	Marks the end of a host variable declaration section.
EXECUTE	Executes a prepared SQL statement.
EXECUTE IMMEDIATE	Prepares and executes an SQL statement.
EXPLAIN	Captures information about the chosen access plan.
FETCH	Assigns values of a row to host variables.
FLUSH EVENT MONITOR	Writes out the active internal buffer of an event monitor.
FREE LOCATOR	Removes the association between a locator variable and its value.
GRANT (Database Authorities)	Grants authorities on the entire database.
GRANT (Index Privileges)	Grants the CONTROL privilege on indexes in the database.
GRANT (Package Privileges)	Grants privileges on packages in the database.
GRANT (Schema Privileges)	Grants privileges on a schema.
GRANT (Server Privileges)	Grants privileges to query a specific data source.
GRANT (Table, View, or Nickname Privileges)	Grants privileges on tables, views and nicknames.
GRANT (Table Space Privileges)	Grants privileges on a tablespace.
INCLUDE	Inserts code or declarations into a source program.
INSERT	Inserts 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 FETCH statement is issued.
PREPARE	Prepares an SQL statement (with optional parameters) for execution.
REFRESH TABLE	Refreshes the data in a summary table.
RELEASE (Connection)	Places one or more connections in the release-pending state.
RELEASE SAVEPOINT	Releases a savepoint within a transaction.
RENAME TABLE	Renames an existing table.
RENAME TABLESPACE	Renames an existing tablespace.
REVOKE (Database Authorities)	Revokes authorities from the entire database.
REVOKE (Index Privileges)	Revokes the CONTROL privilege on given indexes.
REVOKE (Package Privileges)	Revokes privileges from given packages in the database.
REVOKE (Schema Privileges)	Revokes privileges on a schema.
REVOKE (Server Privileges)	Revokes privileges to query a specific data source.
REVOKE (Table, View, or Nickname Privileges)	Revokes privileges from given tables, views or nicknames.
REVOKE (Table Space Privileges)	Revokes the USE privilege on a given table space.
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.
SELECT INTO	Specifies a result table of no more than one row and assigns the values to host variables.
SET CONNECTION	Changes the state of a connection from dormant to current, making the specified location the current server.
SET CURRENT DEFAULT TRANSFORM GROUP	Changes the value of the CURRENT DEFAULT TRANSFORM GROUP special register.
SET CURRENT DEGREE	Changes the value of the CURRENT DEGREE special register.
SET CURRENT EXPLAIN MODE	Changes the value of the CURRENT EXPLAIN MODE special register.
SET CURRENT EXPLAIN SNAPSHOT	Changes the value of the CURRENT EXPLAIN SNAPSHOT special register.
SET CURRENT PACKAGESET	Sets the schema name for package selection.
SET CURRENT QUERY OPTIMIZATION	Changes the value of the CURRENT QUERY OPTIMIZATION special register.
SET CURRENT REFRESH AGE	Changes the value of the CURRENT REFRESH AGE special register.
SET EVENT MONITOR STATE	Activates or deactivates an event monitor.
SET INTEGRITY	Sets the check pending state and checks data for constraint violations.
SET PASSTHRU	Opens a session for submitting a data source's native SQL directly to the data source.
SET PATH	Changes the value of the CURRENT PATH special register.
SET SCHEMA	Changes the value of the CURRENT SCHEMA special register.
SET SERVER OPTION	Sets server option settings.
SET transition-variable	Assigns values to NEW transition variables.
SIGNAL SQLSTATE	Signals an error.
UPDATE	Updates the values of one or more columns in one or more rows of a table.
VALUES INTO	Specifies a result table of no more than one row and assigns the values to host variables.
WHENEVER	Defines actions to be taken on the basis of SQL return codes.