Statements

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

How SQL statements are invoked
SQL statements are invoked in different ways depending on whether the statement is an executable or nonexecutable statement or the select-statement.
SQL comments
Static SQL statements can include host language or SQL comments. Dynamic SQL statements can include SQL comments. There are two types of SQL comments, simple comments and bracketed comments.
ALLOCATE CURSOR
The ALLOCATE CURSOR statement defines a cursor and associates it with a result set locator variable.
ALTER DATABASE
The ALTER DATABASE statement changes the description of a database at the current server.
ALTER FUNCTION (external)
The ALTER FUNCTION statement changes the description of a user-defined external scalar function or external table function at the current server.
ALTER FUNCTION (compiled SQL scalar)
The ALTER FUNCTION (compiled SQL scalar) statement changes the description of a user-defined compiled SQL scalar function at the current server. The function options, parameter names, and routine body can be changed and additional versions of the procedure can be defined and maintained using the ALTER FUNCTION statement.
ALTER FUNCTION (inlined SQL scalar)
The ALTER FUNCTION (inlined SQL scalar) statement changes the description of a user-defined inlined SQL scalar function at the current server.
ALTER FUNCTION (SQL table)
The ALTER FUNCTION (SQL table) statement changes the description of a user-defined SQL table function at the current server.
ALTER INDEX
The ALTER INDEX statement changes the description of an index at the current server.
ALTER MASK
The ALTER MASK statement changes a column mask that exists at the current server.
ALTER PERMISSION
The ALTER PERMISSION statement alters a row permission that exists at the current server.
ALTER PROCEDURE (external)
The ALTER PROCEDURE statement changes the description of an external stored procedure at the current server.
ALTER PROCEDURE (SQL - external)
The ALTER PROCEDURE statement changes the description, at the current server, of an external SQL procedure.
ALTER PROCEDURE (SQL - native)
The ALTER PROCEDURE statement changes the definition of an SQL procedure at the current server. The procedure options, parameter names, and routine body can be changed and additional versions of the procedure can be defined and maintained using the ALTER PROCEDURE statement.
ALTER SEQUENCE
The ALTER SEQUENCE statement changes the attributes of a sequence at the current server. Only future values of the sequence are affected by the ALTER SEQUENCE statement.
ALTER STOGROUP
The ALTER STOGROUP statement changes the description of a storage group at the current server.
ALTER TABLE
The ALTER TABLE statement changes the description of a table at the current server.
ALTER TABLESPACE
The ALTER TABLESPACE statement changes the description of a table space at the current server.
ALTER TRIGGER
The ALTER TRIGGER statement changes the description of a trigger at the current server.
ALTER TRUSTED CONTEXT
The ALTER TRUSTED CONTEXT statement modifies the definition of a trusted context at the current server.
ALTER VIEW
The ALTER VIEW statement regenerates a view using an existing view definition at the current server. ALTER VIEW is primarily used during DB2 migration or when DB2 maintenance is applied. To change a view definition (for example, to add additional columns), you must drop the view and create a new view using the CREATE VIEW statement.
ASSOCIATE LOCATORS
The ASSOCIATE LOCATORS statement gets the result set locator value for each result set returned by a stored procedure.
BEGIN DECLARE SECTION
The BEGIN DECLARE SECTION statement marks the beginning of an SQL declare section. An SQL declare section contains declarations of host variables that are eligible to be used as host variables in SQL statements in a program.
CALL
The CALL statement invokes a stored procedure.
CLOSE
The CLOSE statement closes a cursor. If a temporary copy of a result table was created when the cursor was opened, that table is destroyed.
COMMENT
The COMMENT statement adds or replaces comments in the descriptions of various objects in the DB2 catalog at the current server.
COMMIT
The COMMIT statement ends the unit of recovery in which it is executed and a new unit of recovery is started for the process. The statement commits all changes made by SQL schema statements and SQL data change statements during the unit of work.
CONNECT
The CONNECT statement connects an application process to a database server. This server becomes the current server for the process. The CONNECT statement of DB2 for z/OS® is equivalent to CONNECT (Type 2) in IBM DB2 SQL Reference for Cross-Platform Development.
CREATE ALIAS
The CREATE ALIAS statement defines an alias for a table or view. The definition is recorded in the DB2 catalog at the current server.
CREATE AUXILIARY TABLE
The CREATE AUXILIARY TABLE statement creates an auxiliary table at the current server for storing LOB data.
CREATE DATABASE
The CREATE DATABASE statement defines a DB2 database at the current server.
CREATE FUNCTION
The CREATE FUNCTION statement registers a user-defined function with a database server. Each type of function that you can register with this statement is described separately.
CREATE FUNCTION (compiled SQL scalar)
The CREATE FUNCTION (compiled SQL scalar) statement defines a compiled SQL scalar function at the current server and specifies the source statements for the function. The body of the function is written in the SQL procedural language. The function returns a single value each time it is invoked.
CREATE FUNCTION (external scalar)
This CREATE FUNCTION statement registers a user-defined external scalar function with a database server. A scalar function returns a single value each time it is invoked.
CREATE FUNCTION (external table)
This CREATE FUNCTION statement registers a user-defined external table function with a database server. A user-defined external table function can be used in the FROM clause of a subselect. It returns a table to the subselect by returning one row at a time each time it is invoked.
CREATE FUNCTION (inlined SQL scalar)
The CREATE FUNCTION (inlined SQL scalar) statement defines an SQL scalar function at the current server and specifies an SQL procedural language RETURN statement for the body of the function. The function returns a single value each time it is invoked.
CREATE FUNCTION (sourced)
This CREATE FUNCTION statement registers a user-defined function that is based on an existing scalar or aggregate function with a database server.
CREATE FUNCTION (SQL table)
The CREATE FUNCTION (SQL table) statement creates an SQL table function at the current server. The function returns a set of rows.
CREATE GLOBAL TEMPORARY TABLE
The CREATE GLOBAL TEMPORARY TABLE statement creates a description of a temporary table at the current server.
CREATE INDEX
The CREATE INDEX statement creates a partitioning index or a secondary index and an index space at the current server. The columns included in the key of the index are columns of a table at the current server.
CREATE MASK
The CREATE MASK statement creates a column mask at the current server. A column mask is used for column access control and specifies the value that should be returned for a specified column.
CREATE PERMISSION
The CREATE PERMISSION statement creates a row permission for row access control at the current server.
CREATE PROCEDURE
The CREATE PROCEDURE statement registers a stored procedure with a database server. You can register the following types of procedures with this statement, each of which is described separately.
CREATE PROCEDURE (external)
The CREATE PROCEDURE statement defines an external stored procedure at the current server.
CREATE PROCEDURE (SQL - external)
The CREATE PROCEDURE statement defines an external SQL procedure at the current server and specifies the source statements for the procedure. This is the only type of SQL procedure that is available for versions of DB2 prior to Version 9.
CREATE PROCEDURE (SQL - native)
The CREATE PROCEDURE statement defines an SQL procedure at the current server and specifies the source statements for the procedure.
CREATE ROLE
The CREATE ROLE statement creates a role at the current server.
CREATE SEQUENCE
The CREATE SEQUENCE statement creates a sequence at the current server.
CREATE STOGROUP
The CREATE STOGROUP statement creates a storage group at the current server. Storage from the identified volumes can later be allocated for table spaces and index spaces.
CREATE SYNONYM
The CREATE SYNONYM statement defines a synonym for a table or view at the current server.
CREATE TABLE
The CREATE TABLE statement defines a table. The definition must include its name and the names and attributes of its columns. The definition can include other attributes of the table, such as its primary key and its table space.
CREATE TABLESPACE
The CREATE TABLESPACE statement defines a segmented (non-UTS), partitioned (non-UTS), or universal (UTS) table space at the current server.
CREATE TRIGGER
The CREATE TRIGGER statement defines a trigger in a schema and builds a trigger package at the current server.
CREATE TRUSTED CONTEXT
The CREATE TRUSTED CONTEXT statement defines a trusted context at the current server.
CREATE TYPE (distinct)
The CREATE TYPE (distinct) statement defines a distinct type, which is a data type that a user defines. A distinct type must be based on one of the built-in data types.
CREATE VIEW
The CREATE VIEW statement creates a view on tables or views at the current server.
DECLARE CURSOR
The DECLARE CURSOR statement defines a cursor.
DECLARE GLOBAL TEMPORARY TABLE
The DECLARE GLOBAL TEMPORARY TABLE statement defines a declared temporary table for the current application process. The declared temporary table resides in the work file database and its description does not appear in the system catalog. It is not persistent and cannot be shared with other application processes. Each application process that defines a declared temporary table of the same name has its own unique description and instance of the temporary table. When the application process terminates, the temporary table is dropped.
DECLARE STATEMENT
The DECLARE STATEMENT statement is used for application program documentation. It declares names that are used to identify prepared SQL statements.
DECLARE TABLE
The DECLARE TABLE statement is used for application program documentation. It also provides the precompiler with information used to check your embedded SQL statements. (The DCLGEN subcommand can be used to generate declarations for tables and views described in any accessible DB2 catalog.
DECLARE VARIABLE
The DECLARE VARIABLE statement defines a CCSID for a host variable and the subtype of the variable. When it appears in an application program, the DECLARE VARIABLE statement causes the DB2 precompiler to tag a host variable with a specific CCSID. When the host variable appears in an SQL statement, the DB2 precompiler places this CCSID into the structures that it generates for the SQL statement.
DELETE
The DELETE statement deletes rows from a table or view or activates an instead of delete trigger. The table or view can be at the current server or any DB2 subsystem with which the current server can establish a connection. Deleting a row from a view deletes the row from the table on which the view is based if no instead of trigger is defined for the delete operation on this view. If such a trigger is defined, the trigger is activated instead of the delete operation.
DESCRIBE
The DESCRIBE statement obtains information about an object. You can obtain the following types of information with this statement, each of which is described separately.
DESCRIBE CURSOR
The DESCRIBE CURSOR statement obtains information about the result set that is associated with the cursor. The information, such as column information, is put into a descriptor. Use DESCRIBE CURSOR for result set cursors from stored procedures. The cursor must be defined with the ALLOCATE CURSOR statement.
DESCRIBE INPUT
The DESCRIBE INPUT statement obtains information about the input parameter markers of a prepared statement.
DESCRIBE OUTPUT
The DESCRIBE OUTPUT statement obtains information about a prepared statement.
DESCRIBE PROCEDURE
The DESCRIBE PROCEDURE statement gets information about the result sets returned by a stored procedure. The information, such as the number of result sets, is put into a descriptor.
DESCRIBE TABLE
The DESCRIBE TABLE statement obtains information about a designated table or view.
DROP
The 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 deleted. Whenever an object is deleted, its description is deleted from the catalog at the current server, and any packages that refer to the object are invalidated.
END DECLARE SECTION
The END DECLARE SECTION statement marks the end of an SQL declare section.
EXCHANGE
The EXCHANGE statement switches the content of a base table and its associated clone table.
EXECUTE
The EXECUTE statement executes a prepared SQL statement.
EXECUTE IMMEDIATE
EXECUTE IMMEDIATE combines the basic functions of the PREPARE and EXECUTE statements. It can be used to prepare and execute an SQL statement that contains neither host variables nor parameter markers.
EXPLAIN
The EXPLAIN statement obtains information about access path selection for an explainable statement. A statement is explainable if it is a SELECT, MERGE, or INSERT statement, or the searched form of an UPDATE or DELETE statement. The information that is obtained is placed in a set of supplied user tables that are called EXPLAIN tables.
FETCH
The FETCH statement positions a cursor on a row of its result table. It can return zero, one, or multiple rows and assigns the values of the rows to variables if there is a target specification.
FREE LOCATOR
The FREE LOCATOR statement removes the association between a LOB locator variable and its value.
GET DIAGNOSTICS
The GET DIAGNOSTICS statement provides diagnostic information about the last SQL statement (other than a GET DIAGNOSTICS statement) that was executed. This diagnostic information is gathered as the previous SQL statement is executed. Some of the information available through the GET DIAGNOSTICS statement is also available in the SQLCA.
GRANT
The DB2 GRANT statement grants privileges to authorization IDs. There is a separate form of the statement for each of these classes of privilege:
GRANT (collection privileges)
This form of the GRANT statement grants privileges on collections.
GRANT (database privileges)
This form of the GRANT statement grants privileges on databases.
GRANT (function or procedure privileges)
This form of the GRANT statement grants privileges on user-defined functions, cast functions that are generated for distinct types, and stored procedures.
GRANT (package privileges)
This form of the GRANT statement grants privileges on packages.
GRANT (plan privileges)
This form of the GRANT statement grants privileges on plans.
GRANT (schema privileges)
This form of the GRANT statement grants privileges on schemas.
GRANT (sequence privileges)
This form of the GRANT statement grants privileges on a user-defined sequence.
GRANT (system privileges)
This form of the GRANT statement grants system privileges.
GRANT (table or view privileges)
This form of the GRANT statement grants privileges on tables and views.
GRANT (type or JAR file privileges)
This form of the GRANT statement grants the privilege to use distinct types or JAR files.
GRANT (use privileges)
This form of the GRANT statement grants authority to use particular buffer pools, storage groups, or table spaces.
HOLD LOCATOR
The HOLD LOCATOR statement allows a LOB locator variable to retain its association with a value beyond a unit of work.
INCLUDE
The INCLUDE statement inserts application code, including declarations and statements, into a source program.
INSERT
The INSERT statement inserts rows into a table or view or activates the INSTEAD OF INSERT trigger. The table or view can be at the current server or any DB2 subsystem with which the current server can establish a connection. Inserting a row into a view inserts the row into the table on which the view is based if no INSTEAD OF INSERT trigger is defined on the specified view. If an INSTEAD OF INSERT trigger is defined, the trigger is activated instead of the INSERT statement.
LABEL
The LABEL statement adds or replaces labels in the descriptions of tables, views, aliases, or columns in the catalog at the current server.
LOCK TABLE
The LOCK TABLE statement requests a lock on a table or table space at the current server. The lock is not acquired if the process already holds an appropriate lock.
MERGE
The MERGE statement updates a target (a table or view, or the underlying tables or views of a fullselect) using the specified input data. Rows in the target that match the input data are updated as specified, and rows that do not exist in the target are inserted. Updating or inserting a row into a view updates or inserts the row into the tables on which the view is based, if no INSTEAD OF trigger is defined on this view.
OPEN
The OPEN statement opens a cursor so that it can be used to process rows from its result table.
PREPARE
The PREPARE statement creates an executable SQL statement from a string form of the statement. The character-string form is called a statement string. The executable form is called a prepared statement.
REFRESH TABLE
The REFRESH TABLE statement refreshes the data in a materialized query table. The statement deletes all rows in the materialized query table, executes the fullselect in the table definition to recalculate the data from the tables specified in the fullselect, inserts the calculated result into the materialized query table, and updates the catalog for the refresh timestamp and cardinality of the table. The table can exist at the current server or at any DB2 subsystem with which the current server can establish a connection.
RELEASE (connection)
The RELEASE (connection) statement places one or more connections in the release pending state.
RELEASE SAVEPOINT
The RELEASE SAVEPOINT statement releases the identified savepoint and any subsequently established savepoints within a unit of recovery.
RENAME
The RENAME statement renames an existing table or index. An accelerator-only table cannot be renamed.
REVOKE
The REVOKE statement revokes privileges from authorization IDs. There is a separate form of the statement for each of these classes of privilege:
REVOKE (collection privileges)
This form of the REVOKE statement revokes privileges on collections.
REVOKE (database privileges)
This form of the REVOKE statement revokes database privileges.
REVOKE (function or procedure privileges)
This form of the REVOKE statement revokes privileges on user-defined functions, cast functions that were generated for distinct types, and stored procedures.
REVOKE (package privileges)
This form of the REVOKE statement revokes privileges on packages.
REVOKE (plan privileges)
This form of the REVOKE statement revokes privileges on application plans.
REVOKE (schema privileges)
This form of the REVOKE statement revokes privileges on schemas.
REVOKE (sequence privileges)
This form of the REVOKE statement revokes the privileges on a user-defined sequence.
REVOKE (system privileges)
This form of the REVOKE statement revokes system privileges.
REVOKE (table or view privileges)
This form of the REVOKE statement revokes privileges on one or more tables or views.
REVOKE (type or JAR file privileges)
This form of the REVOKE statement revokes the privilege to use distinct types or JAR files.
REVOKE (use privileges)
This form of the REVOKE statement revokes authority to use particular buffer pools, storage groups, or table spaces.
ROLLBACK
The ROLLBACK statement can be used to end a unit of recovery and back out all the relational database changes that were made by that unit of recovery. If relational databases are the only recoverable resources used by the application process, ROLLBACK also ends the unit of work. ROLLBACK can also be used to back out only the changes made after a savepoint was set within the unit of recovery without ending the unit of recovery. Rolling back to a savepoint enables selected changes to be undone.
SAVEPOINT
The SAVEPOINT statement sets a savepoint within a unit of recovery to identify a point in time within the unit of recovery to which relational database changes can be rolled back.
SELECT
The select-statement is the form of a query that can be directly specified in a DECLARE CURSOR statement, or prepared and then referenced in a DECLARE CURSOR statement. It can also be issued interactively using SPUFI or the command line processor which causes a result table to be displayed at your terminal. In any case, the table specified by select-statement is the result of the fullselect.
SELECT INTO
The SELECT INTO statement produces a result table that contains at most one row. The statement assigns the values in that row to host variables. If the table is empty, the statement does not assign values to the host variables.
SET CONNECTION
The SET CONNECTION statement establishes the database server of the process by identifying one of its existing connections.
SET special register
SET statements assign values to special registers, allowing applications to store information for subsequent use.
SET CURRENT APPLICATION ENCODING SCHEME
The SET CURRENT APPLICATION ENCODING SCHEME statement assigns a value to the CURRENT APPLICATION ENCODING SCHEME special register. This special register allows users to control which encoding scheme will be used for dynamic SQL statements after the SET statement has been executed.
SET CURRENT DEBUG MODE
The SET CURRENT DEBUG MODE statement assigns a value to the CURRENT DEBUG MODE special register.
SET CURRENT DECFLOAT ROUNDING MODE
The SET CURRENT DECFLOAT ROUNDING MODE statement assigns a value to the CURRENT DECFLOAT ROUNDING MODE special register. The special register sets the default rounding mode that is used with decimal floating point values (DECFLOAT).
SET CURRENT DEGREE
The SET CURRENT DEGREE statement assigns a value to the CURRENT DEGREE special register.
SET CURRENT EXPLAIN MODE
The SET CURRENT EXPLAIN MODE statement assigns a value to the CURRENT EXPLAIN MODE special register.
SET CURRENT GET_ACCEL_ARCHIVE
The SET CURRENT GET_ACCEL_ARCHIVE statement changes the value of the CURRENT GET_ACCEL_ARCHIVE special register.
SET CURRENT LOCALE LC_CTYPE
The SET CURRENT LOCALE LC_CTYPE statement assigns a value to the CURRENT LOCALE LC_CTYPE special register. The special register allows control over the LC_CTYPE locale for statements that use a built-in function that refers to a locale, such as LCASE, UCASE, and TRANSLATE (with a single argument).
SET CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION
The SET CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION statement changes the value of the CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION special register.
SET CURRENT OPTIMIZATION HINT
The SET CURRENT OPTIMIZATION HINT statement assigns a value to the CURRENT OPTIMIZATION HINT special register.
SET CURRENT PACKAGE PATH
The SET CURRENT PACKAGE PATH statement assigns a value to the CURRENT PACKAGE PATH special register.
SET CURRENT PACKAGESET
The SET CURRENT PACKAGESET statement assigns a value to the CURRENT PACKAGESET special register.
SET CURRENT PRECISION
The SET CURRENT PRECISION statement assigns a value to the CURRENT PRECISION special register.
SET CURRENT QUERY ACCELERATION
The SET CURRENT QUERY ACCELERATION statement changes the value of the CURRENT QUERY ACCELERATION special register.
SET CURRENT REFRESH AGE
The SET CURRENT REFRESH AGE statement changes the value of the CURRENT REFRESH AGE special register.
SET CURRENT ROUTINE VERSION
The SET CURRENT ROUTINE VERSION statement assigns a value to the CURRENT ROUTINE VERSION special register. The special register sets the override value for the version identifier of native SQL procedures when they are invoked.
SET CURRENT RULES
The SET CURRENT RULES statement assigns a value to the CURRENT RULES special register.
SET CURRENT SQLID
The SET CURRENT SQLID statement assigns a value to the CURRENT SQLID special register.
SET ENCRYPTION PASSWORD
The SET ENCRYPTION PASSWORD statement sets the value of the encryption password and, optionally, the password hint. The encryption and decryption built-in functions use this password and password hint for data encryption unless the functions are invoked with an explicitly specified password and hint. The password is not tied to DB2 authentication and is used only for data encryption.
SET host-variable assignment
The SET host-variable assignment statement assigns values, either of expressions or NULL values, to host variables.
SET PATH
The SET PATH statement assigns a value to the CURRENT PATH special register.
SET SCHEMA
The SET SCHEMA statement assigns a value to the CURRENT SCHEMA special register. If the package is bound with the DYNAMICRULES BIND option, this statement does not affect the qualifier that is used for unqualified database object references.
SET SESSION TIME ZONE
The SET SESSION TIME ZONE statement assigns a value to the SESSION TIME ZONE special register.
SET transition-variable assignment
The SET transition-variable assignment statement assigns values, either of expressions or NULL values, to transition variables.
SIGNAL
The SIGNAL statement is used to signal an error. It causes an error to be returned with the specified SQLSTATE and error description.
TRUNCATE
The DB2 TRUNCATE statement deletes all rows for either base tables or declared global temporary tables. The base table can be in a simple table space, a segmented table space, a partitioned table space, or a universal table space. If the table contains LOB or XML columns, the corresponding table spaces and indexes are also truncated.
UPDATE
The UPDATE statement updates the values of specified columns in rows of a table or view or activates an instead of update trigger. Updating a row of a view updates a row of the table on which the view is based if no instead of update trigger is defined for the update operation on the view. If such a trigger is defined, the trigger is activated instead of the UPDATE statement. The table or view can exist at the current server or at any DB2 subsystem with which the current server can establish a connection.
VALUES
The VALUES statement provides a method for invoking a user-defined function from a trigger. Transition variables and transition tables can be passed to the user-defined function.
VALUES INTO
The VALUES INTO statement assigns one or more values to host variables.
WHENEVER
The WHENEVER statement specifies the host language statement to be executed when a specified exception condition occurs.

Parent topic: SQL: The language of DB2