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