SQL statements

This topic contains tables that list the SQL statements classified by type.

  • SQL schema statements (Table 1)
  • SQL data change statements (Table 2)
  • SQL data statements (Table 3)
  • SQL transaction statements (Table 4)
  • SQL connection statements (Table 5)
  • SQL dynamic statements (Table 6)
  • SQL session statements (Table 7)
  • SQL embedded host language statements (Table 8)
  • SQL control statements (Table 9)
Table 1. SQL schema statements
SQL Statement Purpose
ALTER AUDIT POLICY statement Modifies the definition of an audit policy at the current server.
ALTER BUFFERPOOL statement Changes the definition of a buffer pool.
ALTER DATABASE statement Adds new storage paths to the collection of paths that are used for automatic storage table spaces.
ALTER EVENT MONITOR statement Changes the definition of a TABLE or UNFORMATTED EVENT TABLE event monitor.
ALTER DATABASE PARTITION GROUP statement Changes the definition of a database partition group.
ALTER FUNCTION statement Modifies an existing function by changing the properties of the function.
ALTER HISTOGRAM TEMPLATE statement Modifies the template describing the type of histogram that can be used to override one or more of the default histograms of a service class or a work class.
ALTER INDEX statement Changes the definition of an index.
ALTER MASK statement Changes the definition of a column mask.
ALTER METHOD statement Modifies an existing method by changing the method body associated with the method.
ALTER MODULE statement Changes the definition of a module.
ALTER NICKNAME statement Changes the definition of a nickname.
ALTER PACKAGE statement Alters bind options for a package at the current server without having to bind or rebind the package.
ALTER PERMISSION statement Changes the definition of a row permission.
ALTER PROCEDURE (external) statement Modifies an existing external procedure by changing the properties of the procedure.
ALTER PROCEDURE (sourced) statement Modifies an existing sourced procedure by changing the data type of one or more parameters of the sourced procedure.
ALTER PROCEDURE (SQL) statement Modifies an existing SQL procedure by changing the properties of the procedure.
ALTER SCHEMA statement Modifies an existing schema by changing the data capture attribute of the schema.
ALTER SECURITY LABEL COMPONENT statement Modifies a security label component.
ALTER SECURITY POLICY statement Modifies a security policy.
ALTER SEQUENCE statement Changes the definition of a sequence.
ALTER SERVER statement Changes the definition of a data source in a federated system.
ALTER SERVICE CLASS statement Changes the definition of a service class.
ALTER STOGROUP statement Changes the definition of a storage group.
ALTER TABLE statement Changes the definition of a table.
ALTER TABLESPACE statement Changes the definition of a table space.
ALTER THRESHOLD statement Changes the definition of a threshold.
ALTER TRIGGER statement Changes the definition of a trigger.
ALTER TRUSTED CONTEXT statement Changes the definition of a trusted context at the current server.
ALTER TYPE (structured) statement Changes the definition of a structured type.
ALTER USAGE LIST statement Changes the definition of a usage list.
ALTER USER MAPPING statement Changes the definition of a user authorization mapping.
ALTER VIEW statement Changes the definition of a view by altering a reference type column to add a scope.
ALTER WORK ACTION SET statement Adds, alters, or drops work actions within a work action set.
ALTER WORK CLASS SET statement Adds, alters, or drops work classes within a work class set.
ALTER WORKLOAD statement Changes a workload.
ALTER WRAPPER statement Updates the options that, along with a wrapper module, are used to access data sources of a specific type.
ALTER XSROBJECT statement Enables or disables decomposition support for a specific XML schema.
AUDIT statement Determines the audit policy that is to be used for a particular database or database object at the current server.
COMMENT statement Replaces or adds a comment to the description of an object.
CREATE ALIAS statement Defines an alias for a module, nickname, sequence, table, view, or another alias.
CREATE AUDIT POLICY statement Defines an auditing policy at the current server.
CREATE BUFFERPOOL statement Defines a new buffer pool.
CREATE DATABASE PARTITION GROUP statement Defines a database partition group.
CREATE EVENT MONITOR statement Specifies events in the database to monitor.
CREATE EVENT MONITOR (activities) statement Specifies activity events in the database to monitor.
CREATE EVENT MONITOR (change history) statement Specifies change history events in the database to monitor.
CREATE EVENT MONITOR (locking) statement Specifies locking events in the database to monitor.
CREATE EVENT MONITOR (package cache) statement Specifies package cache statement events in the database to monitor.
CREATE EVENT MONITOR (statistics) statement Specifies statistics events in the database to monitor.
CREATE EVENT MONITOR (threshold violations) statement Specifies threshold violation events in the database to monitor.
CREATE EXTERNAL TABLE statement Defines an external table.
CREATE FUNCTION statement Registers a user-defined function.
CREATE FUNCTION statement Registers a user-defined function.
CREATE FUNCTION (aggregate interface) statement Registers a user-defined aggregate function at the current server.
CREATE FUNCTION (external scalar) statement Registers a user-defined external scalar function.
CREATE FUNCTION (external table) statement Registers a user-defined external table function.
CREATE FUNCTION (OLE DB external table) statement Registers a user-defined OLE DB external table function.
CREATE FUNCTION (sourced or template) statement Registers a user-defined sourced function or a function template.
CREATE FUNCTION (SQL scalar, table, or row) statement Defines a user-defined SQL function.
CREATE FUNCTION MAPPING statement Defines a function mapping.
CREATE GLOBAL TEMPORARY TABLE statement Defines a created temporary table.
CREATE HISTOGRAM TEMPLATE statement Defines a template describing the type of histogram that can be used to override one or more of the default histograms of a service class or a work class.
CREATE INDEX statement Defines an index on a table.
CREATE INDEX EXTENSION statement Defines an extension object for use with indexes on tables with structured or distinct type columns.
CREATE MASK statement Defines a column mask.
CREATE METHOD statement Defines a method body to associate with a previously defined method specification.
CREATE MODULE statement Defines a module.
CREATE NICKNAME statement Defines a nickname.
CREATE PERMISSION statement Defines a row permission.
CREATE PROCEDURE statement Defines a procedure.
CREATE PROCEDURE (external) statement Defines an external procedure.
CREATE PROCEDURE (sourced) statement Defines a procedure (the sourced procedure) that is based on another procedure (the source procedure). In a federated system, a federated procedure is a sourced procedure whose source procedure is at a supported data source.
CREATE PROCEDURE (SQL) statement Defines an SQL procedure.
CREATE ROLE statement Defines a role at the current server.
CREATE SCHEMA statement Defines a schema.
CREATE SECURITY LABEL COMPONENT statement Defines a component that is to be used as part of a security policy.
CREATE SECURITY LABEL statement Defines a security label.
CREATE SECURITY POLICY statement Defines a security policy.
CREATE SEQUENCE statement Defines a sequence.
CREATE SERVER statement Defines a data source to a federated database.
CREATE SERVICE CLASS statement Defines a service class.
CREATE STOGROUP statement Defines a new storage group within the database.
CREATE SYNONYM statement Defines a synonym for a module, nickname, sequence, table, view, or another synonym.
CREATE TABLE statement Defines a table.
CREATE TABLESPACE statement Defines a table space.
CREATE THRESHOLD statement Defines a threshold.
CREATE TRANSFORM statement Defines transformation functions.
CREATE TRIGGER statement Defines a trigger.
CREATE TRUSTED CONTEXT statement Defines a trusted context at the current server.
CREATE TYPE statement Defines a user-defined data type at the current server.
CREATE TYPE (array) statement Defines an array type.
CREATE TYPE (cursor) statement Defines a cursor type.
CREATE TYPE (distinct) statement Defines a distinct data type.
CREATE TYPE (row) statement Defines a row type.
CREATE TYPE (structured) statement Defines a structured data type.
CREATE TYPE MAPPING statement Defines a mapping between data types.
CREATE USAGE LIST statement Defines a usage list in order to monitor all unique sections (DML statements) that have referenced a particular table or index during their execution.
CREATE USER MAPPING statement Defines a mapping between user authorizations.
CREATE VARIABLE statement Defines a global variable.
CREATE VIEW statement Defines a view of one or more table, view or nickname.
CREATE WORK ACTION SET statement Defines a work action set and work actions within the work action set.
CREATE WORK CLASS SET statement Defines a work class set.
CREATE WORKLOAD statement Defines a workload.
CREATE WRAPPER statement Registers a wrapper.
DROP statement Deletes objects in the database.
GRANT (database authorities) statement Grants authorities on the entire database.
GRANT (exemption) statement Grants an exemption on an access rule for a specified label-based access control (LBAC) security policy.
GRANT (global variable privileges) statement Grants one or more privileges on a created global variable.
GRANT (index privileges) statement Grants the CONTROL privilege on indexes in the database.
GRANT (module privileges) statement Grants privileges on a module.
GRANT (package privileges) statement Grants privileges on packages in the database.
GRANT (role) statement Grants roles to users, groups, or to other roles.
GRANT (routine privileges) statement Grants privileges on a routine (function, method, or procedure).
GRANT (schema privileges and authorities) statement Grants privileges on a schema.
GRANT (security label) statement Grants a label-based access control (LBAC) security label for read access, write access, or for both read and write access.
GRANT (sequence privileges) statement Grants privileges on a sequence.
GRANT (server privileges) statement Grants privileges to query a specific data source.
GRANT (SETSESSIONUSER privilege) statement Grants the privilege to use the SET SESSION AUTHORIZATION statement.
GRANT (table space privileges) statement Grants privileges on a table space.
GRANT (table, view, or nickname privileges) statement Grants privileges on tables, views and nicknames.
GRANT (workload privileges) statement Grants the USAGE privilege on a workload.
GRANT (XSR object privileges) statement Grants the USAGE privilege on an XSR object.
REFRESH TABLE statement Refreshes the data in a materialized query table.
RENAME statement Renames an existing table.
RENAME STOGROUP statement Renames an existing storage group.
RENAME TABLESPACE statement Renames an existing table space.
REVOKE (database authorities) statement Revokes authorities from the entire database.
REVOKE (exemption) statement Revokes the exemption on an access rule for a specified label-based access control (LBAC) security policy.
REVOKE (global variable privileges) statement Revokes one or more privileges on a created global variable.
REVOKE (index privileges) statement Revokes the CONTROL privilege on given indexes.
REVOKE (module privileges) statement Revokes privileges on a module.
REVOKE (package privileges) statement Revokes privileges from given packages in the database.
REVOKE (role) statement Revokes roles from users, groups, or other roles.
REVOKE (routine privileges) statement Revokes privileges on a routine (function, method, or procedure).
REVOKE (schema privileges and authorities) statement Revokes privileges on a schema.
REVOKE (security label) statement Revokes a label-based access control (LBAC) security label for read access, write access, or for both read and write access.
REVOKE (sequence privileges) Revokes privileges on a sequence.
REVOKE (server privileges) statement Revokes privileges to query a specific data source.
REVOKE (SETSESSIONUSER privilege) statement Revokes the privilege to use the SET SESSION AUTHORIZATION statement.
REVOKE (table space privileges) statement Revokes the USE privilege on a given table space.
REVOKE (table, view, or nickname privileges) statement Revokes privileges from given tables, views or nicknames.
REVOKE (workload privileges) statement Revokes the USAGE privilege on a workload.
REVOKE (XSR object privileges) statement Revokes the USAGE privilege on an XSR object.
SET INTEGRITY statement Sets the set integrity pending state and checks data for constraint violations.
TRANSFER OWNERSHIP statement Transfers ownership of a database object.
Table 2. SQL data change statements
SQL Statement Purpose
DELETE statement Deletes one or more rows from a table.
INSERT statement Inserts one or more rows into a table.
MERGE statement Updates a target (a table or view) using data from a source (result of a table reference).
TRUNCATE statement Deletes all rows from a table.
UPDATE statement Updates the values of one or more columns in one or more rows of a table.
Table 3. SQL data statements
SQL Statement Purpose
ALLOCATE CURSOR statement Allocates a cursor for the result set identified by the result set locator variable.
ASSOCIATE LOCATORS statement Gets the result set locator value for each result set returned by a procedure.
CLOSE statement Closes a cursor.
DECLARE CURSOR statement Defines an SQL cursor.
FETCH statement Assigns values of a row to host variables.
FLUSH AUTHENTICATION CACHE statement Removes cached users in the authentication cache
FLUSH BUFFERPOOLS statement Writes out the dirty pages in the buffer pools to disk.
FLUSH EVENT MONITOR statement Writes out the active internal buffer of an event monitor.
FLUSH FEDERATED CACHE statement The FLUSH FEDERATED CACHE statement flushes the federated cache, allowing fresh metadata to be obtained the next time an SQL statement is issued against the remote table or view using a federated three part name.
FLUSH OPTIMIZATION PROFILE CACHE statement Removes the cached optimization profiles.
FLUSH PACKAGE CACHE statement Removes all cached dynamic SQL statements currently in the package cache.
FREE LOCATOR statement Removes the association between a locator variable and its value.
LOCK TABLE statement Either prevents concurrent processes from changing a table or prevents concurrent processes from using a table.
OPEN statement Prepares a cursor that will be used to retrieve values when the FETCH statement is issued.
SELECT INTO statement Specifies a result table of no more than one row and assigns the values to host variables.
SET variable statement Assigns values to variables.
VALUES INTO statement Specifies a result table of no more than one row and assigns the values to host variables.
Table 4. SQL transaction statements
SQL Statement Purpose
COMMIT statement Terminates a unit of work and commits the database changes made by that unit of work.
RELEASE SAVEPOINT statement Releases a savepoint within a transaction.
ROLLBACK statement Terminates a unit of work and backs out the database changes made by that unit of work.
SAVEPOINT statement Sets a savepoint within a transaction.
Table 5. SQL connection statements
SQL Statement Purpose
CONNECT (type 1) statement Connects to an application server according to the rules for remote unit of work.
CONNECT (type 2) statement Connects to an application server according to the rules for application-directed distributed unit of work.
DISCONNECT statement Terminates one or more connections when there is no active unit of work.
RELEASE (connection) statement Places one or more connections in the release-pending state.
SET CONNECTION statement Changes the state of a connection from dormant to current, making the specified location the current server.
Table 6. SQL dynamic statements
SQL Statement Purpose
DESCRIBE statement Obtains information about an object.
DESCRIBE INPUT statement Obtains information about the input parameter markers of a prepared statement.
DESCRIBE OUTPUT statement Obtains information about a prepared statement or information about the select list columns in a prepared SELECT statement.
EXECUTE statement Executes a prepared SQL statement.
EXECUTE IMMEDIATE statement Prepares and executes an SQL statement.
PREPARE statement Prepares an SQL statement (with optional parameters) for execution.
Table 7. SQL session statements
SQL Statement Purpose
DECLARE GLOBAL TEMPORARY TABLE statement Defines a declared temporary table.
EXPLAIN statement Captures information about the chosen access plan.
SET COMPILATION ENVIRONMENT statement Changes the current compilation environment in the connection to match the values contained in the compilation environment provided by a deadlock event monitor.
SET CURRENT DECFLOAT ROUNDING MODE statement Verifies that the specified rounding mode is the value that is currently set for the CURRENT DECFLOAT ROUNDING MODE special register.
SET CURRENT DEFAULT TRANSFORM GROUP statement Changes the value of the CURRENT DEFAULT TRANSFORM GROUP special register.
SET CURRENT DEGREE statement Changes the value of the CURRENT DEGREE special register.
SET CURRENT EXPLAIN MODE statement Changes the value of the CURRENT EXPLAIN MODE special register.
SET CURRENT EXPLAIN SNAPSHOT statement Changes the value of the CURRENT EXPLAIN SNAPSHOT special register.
SET CURRENT FEDERATED ASYNCHRONY statement Changes the value of the CURRENT FEDERATED ASYNCHRONY special register.
SET CURRENT IMPLICIT XMLPARSE OPTION statement Changes the value of the CURRENT IMPLICIT XMLPARSE OPTION special register.
SET CURRENT ISOLATION statement Changes the value of the CURRENT ISOLATION special register.
SET CURRENT LOCALE LC_MESSAGES statement Changes the value of the CURRENT LOCALE LC_MESSAGES special register.
SET CURRENT LOCALE LC_TIME statement Changes the value of the CURRENT LOCALE LC_TIME special register.
SET CURRENT LOCK TIMEOUT statement Changes the value of the CURRENT LOCK TIMEOUT special register.
SET CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION statement Changes the value of the CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION special register.
SET CURRENT MDC ROLLOUT MODE statement Assigns a value to the CURRENT MDC ROLLOUT MODE special register.
SET CURRENT OPTIMIZATION PROFILE statement Assigns a value to the CURRENT OPTIMIZATION PROFILE special register.
SET CURRENT PACKAGE PATH statement Assigns a value to the CURRENT PACKAGE PATH special register.
SET CURRENT PACKAGESET statement Sets the schema name for package selection.
SET CURRENT QUERY OPTIMIZATION statement Changes the value of the CURRENT QUERY OPTIMIZATION special register.
SET CURRENT REFRESH AGE statement Changes the value of the CURRENT REFRESH AGE special register.
SET CURRENT SQL_CCFLAGS statement Changes the value of the CURRENT SQL_CCFLAGS special register.
SET CURRENT TEMPORAL BUSINESS_TIME statement Changes the value of the CURRENT TEMPORAL BUSINESS_TIME special register.
SET CURRENT TEMPORAL SYSTEM_TIME statement Changes the value of the CURRENT TEMPORAL SYSTEM_TIME special register.
SET ENCRYPTION PASSWORD statement Sets the password for encryption.
SET EVENT MONITOR STATE statement Activates or deactivates an event monitor.
SET PASSTHRU statement Opens a session for submitting data source native SQL directly to the data source.
SET PATH statement Changes the value of the CURRENT PATH special register.
SET ROLE statement Verifies that the authorization ID of the session is a member of a specific role.
SET SCHEMA statement Changes the value of the CURRENT SCHEMA special register.
SET SERVER OPTION statement Sets server option settings.
SET SESSION AUTHORIZATION statement Changes the value of the SESSION USER special register.
SET USAGE LIST STATE statement Manages the state of a usage list and the associated data and memory.
Table 8. SQL embedded host language statements
SQL Statement Purpose
BEGIN DECLARE SECTION statement Marks the beginning of a host variable declaration section.
END DECLARE SECTION statement Marks the end of a host variable declaration section.
GET DIAGNOSTICS statement Used to obtain information about the previously executed SQL statement.
INCLUDE statement Inserts code or declarations into a source program.
RESIGNAL statement Used to resignal an error or warning condition.
SIGNAL statement Used to signal an error or warning condition.
WHENEVER statement Defines actions to be taken on the basis of SQL return codes.
Table 9. SQL control statements
SQL Statement Purpose
CALL statement Calls a procedure.
CASE statement Selects an execution path based on multiple conditions.
Compound SQL statement Encloses SQL statements with BEGIN and END keywords.
Compound SQL (inlined) statement Combines one or more other SQL statements into an dynamic block.
Compound SQL (embedded) statement Combines one or more other SQL statements into an executable block.
Compound SQL (compiled) statement Groups other statements together in an SQL procedure.
FOR statement Executes a statement or group of statements for each row of a table.
GOTO statement Used to branch to a user-defined label within an SQL procedure.
IF statement Selects an execution path based on the evaluation of a condition.
ITERATE statement Causes the flow of control to return to the beginning of a labelled loop.
LEAVE statement Transfers program control out of a loop or a compound statement.
LOOP statement Repeats the execution of a statement or a group of statements.
PIPE statement Returns a row from a compiled table function.
REPEAT statement Executes a statement or group of statements until a search condition is true.
RESIGNAL statement Used to resignal an error or warning condition.
RETURN statement Used to return from a routine.
SIGNAL statement Used to signal an error or warning condition.
WHILE statement Repeats the execution of a statement or group of statements while a specified condition is true.