List of supported statements
This section contains an alphabetical list of the SQL statements that Db2 for z/OS® supports.
| SQL statement | Function |
|---|---|
| ALLOCATE CURSOR statement | Defines and associates a cursor with a result set locator variable |
| ALTER DATABASE statement | Changes the description of a database |
| ALTER FUNCTION statement (external function) | Changes the description of a user-defined external scalar or external table function |
| ALTER FUNCTION statement (compiled SQL scalar function) | Changes the description of a compiled SQL scalar function |
| ALTER FUNCTION statement (inlined SQL scalar function) | Changes the description of an inlined SQL scalar function |
| ALTER FUNCTION statement (SQL table function) | Changes the description of an SQL table function |
| ALTER INDEX statement | Changes the description of an index |
| ALTER MASK statement | Changes the description of a column mask |
| ALTER PERMISSION statement | Changes the description of a row permission |
| ALTER PROCEDURE statement (external procedure) | Changes the description of an external procedure |
| ALTER PROCEDURE statement (SQL - external procedure) (deprecated) | Changes the description of an external SQL procedure |
| ALTER PROCEDURE statement (SQL - native procedure) | Changes the description of or defines additional versions for a native SQL procedure |
| ALTER SEQUENCE statement | Changes the description of a sequence |
| ALTER STOGROUP statement | Changes the description of a storage group |
| ALTER TABLE statement | Changes the description of a table |
| ALTER TABLESPACE statement | Changes the description of a table space |
| ALTER TRIGGER statement (advanced trigger) | Changes the description of an advanced trigger |
| ALTER TRIGGER statement (basic trigger) | Changes the description of a basic trigger |
| ALTER TRUSTED CONTEXT statement | Changes the description of a trusted context |
| ALTER VIEW statement | Regenerates a view |
| ASSOCIATE LOCATORS statement | Gets the result set locator value for each result set returned by a stored procedure |
| BEGIN DECLARE SECTION statement | Marks the beginning of a host variable declaration section |
| CALL statement | Calls a stored procedure |
| CLOSE statement | Closes a cursor |
| COMMENT statement | Adds or replaces a comment to the description of an object |
| COMMIT statement | Ends a unit of recovery and commits the database changes made by that unit of recovery |
| CONNECT statement | Connects the process to a server |
| CREATE ALIAS statement | Defines an alias |
| CREATE AUXILIARY TABLE statement | Defines an auxiliary table for storing LOB data |
| CREATE DATABASE statement | Defines a database |
| CREATE FUNCTION statement (compiled SQL scalar function) | Defines a compiled SQL scalar function |
| CREATE FUNCTION statement (external scalar function) | Defines a user-defined external scalar function |
| CREATE FUNCTION statement (external table function) | Defines a user-defined external table function |
| CREATE FUNCTION statement (inlined SQL scalar function) | Defines an inlined SQL scalar function |
| CREATE FUNCTION statement (sourced function) | Defines a user-defined function that is based on an existing scalar or aggregate function |
| CREATE FUNCTION statement (SQL table function) | Defines a user-defined SQL table function |
| CREATE GLOBAL TEMPORARY TABLE statement | Defines a created temporary table |
| CREATE INDEX statement | Defines an index on a table |
| CREATE LOB TABLESPACE | Defines a LOB table space |
| CREATE MASK statement | Defines a column mask |
| CREATE PERMISSION statement | Defines a row permission |
| CREATE PROCEDURE statement (external procedure) | Defines an external stored procedure |
| CREATE PROCEDURE statement (SQL - external procedure) (deprecated) | Defines an external SQL procedure |
| CREATE PROCEDURE statement (SQL - native procedure) | Defines a native SQL procedure |
| CREATE ROLE statement | Defines a role |
| CREATE SEQUENCE statement | Defines a sequence |
| CREATE STOGROUP statement | Defines a storage group |
| CREATE TABLE statement | Defines a table |
| CREATE TABLESPACE statement | Defines a table space, which includes allocating and formatting the table space |
| CREATE TRIGGER statement (advanced trigger) | Defines an advanced trigger |
| CREATE TRIGGER statement (basic trigger) | Defines a basic trigger |
| CREATE TRUSTED CONTEXT statement | Defines a trusted context |
| CREATE TYPE statement (array type) | Defines an array type |
| CREATE TYPE statement (distinct type) | Defines a distinct type |
| CREATE VARIABLE statement | Defines a global variable |
| CREATE VIEW statement | Defines a view of one or more tables or views |
| DECLARE CURSOR statement | Defines an SQL cursor |
| Defines a declared temporary table | |
| DECLARE STATEMENT statement | Declares names used to identify prepared SQL statements |
| DECLARE TABLE statement | Provides the programmer and the precompiler with a description of a table or view |
| DECLARE VARIABLE statement | Defines a CCSID for a host variable |
| DELETE statement | Deletes one or more rows from a table |
| DESCRIBE CURSOR statement | Puts information about the result set associated with a cursor into a descriptor |
| DESCRIBE INPUT statement | Puts information about the input parameter markers of a prepared statement into a descriptor |
| DESCRIBE OUTPUT statement | Describes the result columns of a prepared statement |
| DESCRIBE PROCEDURE statement | Puts information about the result sets returned by a stored procedure into a descriptor |
| DESCRIBE TABLE statement | Describes the columns of a table or view |
| DROP statement | Removes an object at the current server. Except for storage groups, any objects that are directly or indirectly dependent on that object are also removed. |
| END DECLARE SECTION statement | Marks the end of a host variable declaration section |
| EXCHANGE statement | Exchanges data between the specified base table and an associated clone table |
| EXECUTE | Executes a prepared SQL statement |
| EXECUTE IMMEDIATE statement | Prepares and executes an SQL statement |
| EXPLAIN statement | Obtains information about how an SQL statement would be executed |
| FETCH statement | Positions the cursor, returns data, or both positions the cursor and returns data |
| FREE LOCATOR statement | Removes the association between a LOB locator variable and its value |
| GET DIAGNOSTICS statement | Provides diagnostic information about the last SQL statement that was executed |
| GRANT statement | Grants privileges to authorization IDs |
| GRANT statement (collection privileges) | Grants authority to create a package in a collection |
| GRANT statement (database privileges) | Grants privileges on a database |
| GRANT statement (function or procedure privileges) | Grants privileges on a user-defined function or a stored procedure |
| GRANT statement (package privileges) | Grants authority to bind, execute, or copy a package |
| GRANT statement (plan privileges) | Grants authority to bind or execute an application plan |
| GRANT statement (schema privileges) | Grants privileges on a schema |
| GRANT statement (sequence privileges) | Grants privileges on a user-defined sequence |
| GRANT statement (system privileges) | Grants system privileges |
| GRANT statement (table or view privileges) | Grants privileges on a table or view |
| GRANT statement (type or JAR file privileges) | Grants privileges to use distinct types, array types, or JAR files |
| GRANT statement (variable privileges) | Grants privileges on global variables |
| GRANT statement (use privileges) | Grants authority to use specified buffer pools, storage groups, or table spaces |
| HOLD LOCATOR statement | Allows a LOB locator variable to retain its association with its value beyond a unit of work |
| INCLUDE statement | Inserts declarations into a source program |
| INSERT statement | Inserts one or more rows into a table |
| LABEL statement | Adds or replaces a label on the description of a table, view, alias, or column |
| LOCK TABLE statement | Locks a table or table space partition in shared or exclusive mode |
| MERGE statement | Updates a target (a table or view) using data from a source (result of VALUES clause or table reference) |
| OPEN statement | Opens a cursor |
| PREPARE statement | Prepares an SQL statement (with optional parameters) for execution |
| REFRESH TABLE statement | Refreshes the data in a materialized query table |
| RELEASE statement (connection) | Places one or more connections in the release pending status |
| RELEASE SAVEPOINT statement | Releases a savepoint and any subsequently set savepoints within a unit of recovery |
| RENAME statement | Renames an existing table or index |
| REVOKE statement | Revokes privileges from authorization IDs |
| REVOKE statement (collection privileges) | Revokes authority to create a package in a collection |
| REVOKE statement (database privileges) | Revokes privileges on a database |
| REVOKE statement (function or procedure privileges) | Revokes privileges on a user-defined function or a stored procedure |
| REVOKE statement (package privileges) | Revokes authority to bind, execute, or copy a package |
| REVOKE statement (plan privileges) | Revokes authority to bind or execute an application plan |
| REVOKE statement (schema privileges) | Revokes privileges on a schema |
| REVOKE statement (sequence privileges) | Revokes privileges on a user-defined sequence |
| REVOKE statement (system privileges) | Revokes system privileges |
| REVOKE statement (table or view privileges) | Revokes privileges on a table or view |
| REVOKE (type or JAR file privileges) | Revokes privileges on a user-defined data type or a JAR file |
| REVOKE (variable privileges) | Revokes privileges on global variables |
| REVOKE statement (use privileges) | Revokes authority to use specified buffer pools, storage groups, or table spaces |
| ROLLBACK statement | Ends a unit of recovery and backs out the changes to the database made by that unit of recovery, or partially rolls back the changes to a savepoint within the unit of recovery |
| SAVEPOINT statement | Sets a savepoint within a unit of recovery |
| SELECT statement | Specifies the SELECT statement of the cursor |
| SELECT INTO statement | Specifies a result table of no more than one row and assigns the values to variables |
| SET CONNECTION statement | Establishes the database server of the process by identifying one of its existing connections |
| SET assignment-statement statement | Assigns values to variables and array elements |
| SET CURRENT ACCELERATOR statement | Assigns a value to the CURRENT ACCELERATOR special register |
| SET CURRENT APPLICATION COMPATIBILITY statement | Assigns a value to the CURRENT APPLICATION COMPATIBILITY special register |
| SET CURRENT APPLICATION ENCODING SCHEME | Assigns a value to the CURRENT APPLICATION ENCODING SCHEME special register |
| SET CURRENT DEBUG MODE statement | Assigns a value to the CURRENT DEBUG MODE special register |
| SET CURRENT DECFLOAT ROUNDING MODE statement | Assigns a value to the CURRENT DECFLOAT ROUNDING MODE special register |
| SET CURRENT DEGREE statement | Assigns a value to the CURRENT DEGREE special register |
| SET CURRENT EXPLAIN MODE statement | Assigns a value to the CURRENT EXPLAIN MODE special register |
| SET CURRENT GET_ACCEL_ARCHIVE statement | Assigns a value to the CURRENT GET_ACCEL_ARCHIVE special register |
| SET CURRENT LOCALE LC_CTYPE statement | Assigns a value to the CURRENT LOCALE LC_CTYPE special register |
| SET CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION statement | Assigns a value to the CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION special register |
| SET CURRENT OPTIMIZATION HINT statement | Assigns a value to the CURRENT OPTIMIZATION HINT special register |
| SET CURRENT PACKAGE PATH statement | Assigns a value to the CURRENT PACKAGE PATH special register |
| SET CURRENT PACKAGESET statement | Assigns a value to the CURRENT PACKAGESET special register |
| SET CURRENT PRECISION statement | Assigns a value to the CURRENT PRECISION special register |
| SET CURRENT QUERY ACCELERATION statement | Assigns a value to the CURRENT QUERY ACCELERATION special register |
| SET CURRENT QUERY ACCELERATION WAITFORDATA statement | Assigns a value to the CURRENT QUERY ACCELERATION WAITFORDATA special register |
| SET CURRENT REFRESH AGE statement | Assigns a value to the CURRENT REFRESH AGE special register |
| SET CURRENT ROUTINE VERSION statement | Assigns a value to the CURRENT ROUTINE VERSION special register |
| SET CURRENT RULES statement | Assigns a value to the CURRENT RULES special register |
| SET CURRENT SQLID statement | Assigns a value to the CURRENT SQLID special register |
| SET CURRENT TEMPORAL BUSINESS_TIME statement | Assigns a value to the CURRENT TEMPORAL BUSINESS_TIME special register |
| SET CURRENT TEMPORAL SYSTEM_TIME statement | Assigns a value to the CURRENT TEMPORAL SYSTEM_TIME special register |
| SET ENCRYPTION PASSWORD statement | Assigns a value for the ENCRYPTION PASSWORD and an optional hint for the password |
| SET PATH statement | Assigns a value to the CURRENT PATH special register |
| SET SCHEMA statement | Assigns a value to the CURRENT SCHEMA special register |
| SET SESSION TIME ZONE statement | Assigns a value to the CURRENT SESSION TIME ZONE special register |
| SIGNAL | Signals an error or warning condition and optionally returns the specified message text |
| TRANSFER OWNERSHIP statement | Transfers ownership of a database or system object from one owner to another |
| TRUNCATE statement | Deletes all rows from a base table or declared temporary table |
| UPDATE statement | Updates the values of one or more columns in one or more rows of a table |
| VALUES statement | Provides a way to invoke a user-defined function from a trigger |
| VALUES INTO statement | Assigns values to variables |
| WHENEVER statement | Defines actions to be taken on the basis of SQL return codes |