SQL statements in Db2 for z/OS
This section contains syntax diagrams, semantic descriptions, rules, and examples of the use of the SQL statements.
By using SQL statements, you can accomplish activities in the following categories, among others:
- Define, modify, or drop data objects such as tables by using various forms of CREATE, ALTER, and DROP statements. These statements are formally categorized as SQL schema statements, and sometimes informally as Data Definition Language (DDL) statements.
- Retrieve, insert, update, or delete data in tables by using SELECT, INSERT, UPDATE, and MERGE statements. These statements are formally categorized as SQL data statements, and sometimes informally as Data Manipulation Language (DML) statements.
Authorize users to access specific resources such as tables or views by using various forms of GRANT, REVOKE, and TRANSFER OWNERSHIP statements. These statements are formally categorized as SQL schema statements, but they are also sometimes informally categorized as Data Control Language (DCL) statements.
For similar information about the use of SQL procedure language (SQL PL) statements, see SQL procedural language (SQL PL).
More about SQL statements in Db2 for z/OS
List of SQL statements
| SQL statement | Function |
|---|---|
| ALLOCATE CURSOR | Defines and associates a cursor with a result set locator variable |
| CREATE DATABASE | Changes the description of a database |
| ALTER FUNCTION (external scalar) | 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 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 | 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 (advanced trigger) |
Changes the description of an advanced trigger |
| ALTER TRIGGER (basic trigger) | Changes the description of a basic trigger |
| ALTER TRUSTED CONTEXT | Changes the description of a trusted context |
| ALTER VIEW statement | 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 (source) | 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) (deprecated) | Defines an external SQL procedure |
| CREATE PROCEDURE (SQL - native) | Defines a native SQL procedure |
| CREATE ROLE | Defines a role |
| CREATE SEQUNCE | Defines a sequence |
| CREATE STOGROUP | Defines a storage group |
| CREATE SYNONYM (deprecated) | Defines an alternative name for a table or view |
| CREATE TABLE statement | Defines a table |
| CREATE TABLESPACE | Defines a table space, which includes allocating and formatting the table space |
| CREATE TRIGGER (advanced trigger) |
Defines an advanced trigger |
| CREATE TRIGGER (basic trigger) | Defines a basic trigger |
| CREATE TRUSTED CONTEXT | Defines a trusted context |
| CREATE TYPE (array type) | Defines an array type |
| CREATE TYPE (distinct type) | 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 |
| DECLARE GLOBAL TEMPORARY TABLE | Defines a declared temporary table |
| DECLARE STATEMENT | Declares names used to identify prepared SQL statements |
| DECLARE | 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 (overview) | Grants privileges to authorization IDs |
| GRANT statement (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 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 or table reference) |
| 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 (general info) | Revokes privileges from authorization IDs |
| REOVKE (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 statement (system privileges) | Revokes system privileges |
| REOVOKE (table or view privileges) | Revokes privileges on a table or view |
| REVOKSE (type or JAR 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 DEGRESS | 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 statement | 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 PACKAGE SET | Assigns a value to the CURRENT PACKAGESET special register |
| SET CURRENT PRECISION | Assigns a value to the CURRENT PRECISION special register |
| SET CURRET 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 CURRENTRULES | 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 |
| TRANSFER OWNERSHIP statement | Transfers ownership of a database or system object from one owner to another |
| 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 |