Statements

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

The statements are listed in the following table.

How SQL statements are invoked
The SQL statements described in this chapter are classified as executable or nonexecutable. The Invocation section in the description of each statement indicates whether the statement is executable.
SQL diagnostic information
The database manager uses a diagnostics area to store status information and diagnostic information about the execution of an executable SQL statement. When an SQL statement other than GET DIAGNOSTICS or compound-statement is processed, the current diagnostics area is cleared, before processing the SQL statement. As each SQL statement is processed, information about the execution of that SQL statement is recorded in the current diagnostics area as one or more completion conditions or exception conditions.
Detecting and processing error and warning conditions in host language applications
Each host language provides a mechanism for handling diagnostic information:
SQL comments
In most host languages, static SQL statements can include host language or SQL comments. In Java™ and REXX, static SQL statements cannot include host language or SQL comments.
ALLOCATE CURSOR
The ALLOCATE CURSOR statement defines a cursor and associates it with a result set locator variable.
ALLOCATE DESCRIPTOR
The ALLOCATE DESCRIPTOR statement allocates an SQL descriptor.
ALTER FUNCTION (external scalar)
The ALTER FUNCTION (external scalar) statement alters an external scalar function at the current server.
ALTER FUNCTION (external table)
The ALTER FUNCTION (external table) statement alters an external table function at the current server.
ALTER FUNCTION (SQL scalar)
The ALTER FUNCTION (SQL scalar) statement alters an SQL scalar function at the current server.
ALTER FUNCTION (SQL table)
The ALTER FUNCTION (SQL table) statement alters an SQL table function at the current server.
ALTER MASK
The ALTER MASK statement alters 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 (external) statement alters an external procedure at the current server.
ALTER PROCEDURE (SQL)
The ALTER PROCEDURE (SQL) statement alters a procedure at the current server.
ALTER SEQUENCE
The ALTER SEQUENCE statement can be used to change a sequence.
ALTER TABLE
The ALTER TABLE statement alters the definition of a table.
ALTER TRIGGER
The ALTER TRIGGER statement changes the description of the trigger at the current server.
ASSOCIATE LOCATORS
The ASSOCIATE LOCATORS statement gets the result set locator value for each result set returned by a 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 calls a procedure.
CLOSE
The CLOSE statement closes a cursor. If a result table was created when the cursor was opened, that table is destroyed.
COMMENT
The COMMENT statement adds or replaces comments in the catalog descriptions of various database objects.
COMMIT
The COMMIT statement ends a unit of work and commits the database changes that were made by that unit of work.
compound (dynamic)
A compound (dynamic) statement groups other statements together in an executable routine. A compound statement allows the declaration of SQL variables, cursors, and condition handlers.
CONNECT (type 1)
The CONNECT (type 1) statement connects an activation group within an application process to the identified application server using the rules for remote unit of work. This server is then the current server for the activation group. This type of CONNECT statement is used if RDBCNNMTH(*RUW) was specified on the CRTSQLxxx command.
CONNECT (type 2)
The CONNECT (type 2) statement connects an activation group within an application process to the identified application server using the rules for application directed distributed unit of work. This server is then the current server for the activation group. This type of CONNECT statement is used if RDBCNNMTH(*DUW) was specified on the CRTSQLxxx command.
CREATE ALIAS
The CREATE ALIAS statement defines an alias on a table, partition of a table, view, or member of a database file at the current or remote server.
CREATE FUNCTION
The CREATE FUNCTION statement defines a user-defined function at the current server.
CREATE FUNCTION (external scalar)
This CREATE FUNCTION (external scalar) statement defines an external scalar function at the current server. A user-defined external scalar function returns a single value each time it is invoked.
CREATE FUNCTION (external table)
This CREATE FUNCTION (external table) statement defines an external table function at the current server. The function returns a result table.
CREATE FUNCTION (sourced)
This CREATE FUNCTION (sourced) statement defines a user-defined function, based on another existing scalar or aggregate function, at the current server.
CREATE FUNCTION (SQL scalar)
This CREATE FUNCTION (SQL scalar) statement creates an SQL function at the current server. The function returns a single result.
CREATE FUNCTION (SQL table)
This CREATE FUNCTION (SQL table) statement creates an SQL table function at the current server. The function returns a single result table.
CREATE INDEX
The CREATE INDEX statement creates an index on a table at the current server.
CREATE MASK
The CREATE MASK statement creates a column mask for column access control at the current server. A column mask specifies what value should be returned for the specified column.
CREATE PERMISSION
The CREATE PERMISSION statement creates a row permission for row access control at the current server. It determines the rows within a table that are available based on the result of the search-condition.
CREATE PROCEDURE
The CREATE PROCEDURE statement defines a procedure at the current server.
CREATE PROCEDURE (external)
The CREATE PROCEDURE (external) statement defines an external procedure at the current server.
CREATE PROCEDURE (SQL)
The CREATE PROCEDURE (SQL) statement creates an SQL procedure at the current server.
CREATE SCHEMA
The CREATE SCHEMA statement defines a schema at the current server and optionally creates tables, views, aliases, indexes, sequences, and distinct types. Comments and labels may be added in the catalog description of these objects, and privileges can be granted to users.
CREATE SEQUENCE
The CREATE SEQUENCE statement creates a sequence at the application server.
CREATE TABLE
The CREATE TABLE statement defines a table at the current server. The definition must include its name and the names and attributes of its columns. The definition may include other attributes of the table such as primary key.
CREATE TRIGGER
The CREATE TRIGGER statement defines a trigger at the current server.
CREATE TYPE
The CREATE TYPE statement defines a user-defined data type at the current server.
CREATE TYPE (array)
The CREATE TYPE (array) statement defines an array type at the current server.
CREATE TYPE (distinct)
The CREATE TYPE (distinct) statement defines a distinct type at the current server. A distinct type is always sourced on one of the built-in data types.
CREATE VARIABLE
The CREATE VARIABLE statement defines a global variable at the application server.
CREATE VIEW
The CREATE VIEW statement creates a view on one or more tables or views at the current server.
DEALLOCATE DESCRIPTOR
The DEALLOCATE DESCRIPTOR statement deallocates an SQL descriptor.
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 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 of the temporary table. When the application process ends, the temporary table is dropped.
DECLARE PROCEDURE
The DECLARE PROCEDURE statement defines an external procedure.
DECLARE STATEMENT
The DECLARE STATEMENT statement is used for program documentation. It declares names that are used to identify prepared SQL statements.
DECLARE VARIABLE
The DECLARE VARIABLE statement is used to assign a subtype or CCSID other than the default to a host variable.
DELETE
The DELETE statement deletes rows from a table or view. Deleting a row from a view deletes the row from the table on which the view is based if no INSTEAD OF DELETE trigger is defined for this view. If such a trigger is defined, the trigger will be activated instead.
DESCRIBE
The DESCRIBE statement obtains information about a prepared statement.
DESCRIBE CURSOR
The DESCRIBE CURSOR statement gets information about a cursor. The information, such as column information, is put into a descriptor.
DESCRIBE INPUT
The DESCRIBE INPUT statement obtains information about the IN and INOUT parameter markers of a prepared statement.
DESCRIBE PROCEDURE
The DESCRIBE PROCEDURE statement gets information about the result sets returned by a 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 table or view.
DISCONNECT
The DISCONNECT statement ends one or more connections for unprotected conversations.
DROP
The DROP statement drops an object. Objects that are directly or indirectly dependent on that object may also be dropped.
END DECLARE SECTION
The END DECLARE SECTION statement marks the end of an SQL declare section.
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 SQL statements that contain neither variables nor parameter markers.
FETCH
The FETCH statement positions a cursor on a row of the result table. It can return zero, one, or multiple rows, and it assigns the values of the rows returned to variables.
FREE LOCATOR
The FREE LOCATOR statement removes the association between a locator variable and its value.
GET DESCRIPTOR
The GET DESCRIPTOR statement gets information from an SQL descriptor.
GET DIAGNOSTICS
The GET DIAGNOSTICS statement obtains information about the previous SQL statement that was executed.
GRANT (function or procedure privileges)
This form of the GRANT statement grants privileges on a function or procedure.
GRANT (package privileges)
This form of the GRANT statement grants privileges on a package.
GRANT (schema privileges)
This form of the GRANT statement grants privileges on a schema.
GRANT (sequence privileges)
This form of the GRANT statement grants privileges on a sequence.
GRANT (table or view privileges)
This form of the GRANT statement grants privileges on tables or views.
GRANT (type privileges)
This form of the GRANT statement grants privileges on a type.
GRANT (variable privileges)
This form of the GRANT statement grants privileges on a global variable.
GRANT (XML schema privileges)
This form of the GRANT statement grants privileges on an XSR object.
HOLD LOCATOR
The HOLD LOCATOR statement allows a LOB or XML 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. Inserting a row into a view also inserts the row into the table on which the view is based if no INSTEAD OF INSERT trigger is defined on this view. If such a trigger is defined, the trigger will be activated instead.
LABEL
The LABEL statement adds or replaces labels in the catalog descriptions of various database objects.
LOCK TABLE
The LOCK TABLE statement either prevents concurrent application processes from changing a table or prevents concurrent application processes from using a table.
MERGE
The MERGE statement updates a target (a table or view) using data from a source (result of a table reference). Rows in the target that match the input data may be updated or deleted as specified, and rows that do not exist in the target may be inserted as specified. Updating, deleting, or inserting a row in a view updates, deletes, or inserts the row into the tables on which the view is based if no INSTEAD OF trigger is defined on the view.
OPEN
The OPEN statement opens a cursor so that it can be used to fetch rows from its result table.
PREPARE
The PREPARE statement creates an executable form of an SQL statement from a character-string form of the statement. The character-string form is called a statement string, and 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 and then inserts the result rows from the select-statement specified in the definition of the materialized query table.
RELEASE (connection)
The RELEASE 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 work at the current server.
RENAME
The RENAME statement renames a table, view, or index. The name and/or the system object name of the table, view, or index can be changed.
REVOKE (function or procedure privileges)
This form of the REVOKE statement removes the privileges on a function or procedure.
REVOKE (package privileges)
This form of the REVOKE statement removes the privileges on a package.
REVOKE (schema privileges)
This form of the REVOKE statement removes the privileges on a schema.
REVOKE (sequence privileges)
This form of the REVOKE statement removes the privileges on a sequence.
REVOKE (table or view privileges)
This form of the REVOKE statement removes privileges on a table or view.
REVOKE (type privileges)
This form of the REVOKE statement removes the privileges on a type.
REVOKE (variable privileges)
This form of the REVOKE statement removes the privileges on a created global variable.
REVOKE (XML schema privileges)
This form of the REVOKE statement removes the privileges on an XSR object.
ROLLBACK
The ROLLBACK statement is used to back out changes.
SAVEPOINT
The SAVEPOINT statement sets a savepoint within a unit of work to identify a point in time within the unit of work to which relational database changes can be rolled back.
SELECT
The SELECT statement is a form of query. It can be embedded in an SQLJ application program or issued interactively.
SELECT INTO
The SELECT INTO statement produces a result table consisting of at most one row, and assigns the values in that row to variables.
SET CONNECTION
The SET CONNECTION statement establishes the current server of the activation group by identifying one of its existing connections.
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 changes the value of the CURRENT DECFLOAT ROUNDING MODE special register.
SET CURRENT DEGREE
The SET CURRENT DEGREE statement assigns a value to the CURRENT DEGREE special register.
SET CURRENT IMPLICIT XMLPARSE OPTION
The SET CURRENT IMPLICIT XMLPARSE OPTION statement changes the value of the CURRENT IMPLICIT XMLPARSE OPTION special register.
SET DESCRIPTOR
The SET DESCRIPTOR statement sets information in an SQL descriptor.
SET ENCRYPTION PASSWORD
The SET ENCRYPTION PASSWORD statement sets the default password and hint that will be used by the encryption and decryption functions. The password is not associated with authentication and is only used for data encryption and decryption.
SET OPTION
The SET OPTION statement establishes the processing options to be used for SQL statements.
SET PATH
The SET PATH statement changes the value of the CURRENT PATH special register.
SET RESULT SETS
The SET RESULT SETS statement specifies the result sets that can be returned from a procedure.
SET SCHEMA
The SET SCHEMA statement changes the value of the CURRENT SCHEMA special register.
SET SESSION AUTHORIZATION
The SET SESSION AUTHORIZATION statement changes the value of the SESSION_USER and USER special registers. It also changes the name of the user profile associated with the current thread.
SET TRANSACTION
The SET TRANSACTION statement sets the isolation level, read only attribute, or diagnostics area size for the current unit of work.
SET transition-variable
The SET transition-variable statement assigns values to new transition variables.
SET variable
The SET variable statement produces a result table consisting of at most one row and assigns the values in that row to variables.
SIGNAL
The SIGNAL statement signals an error or warning condition. It causes an error or warning to be returned with the specified SQLSTATE and optional condition-information-items.
TRANSFER OWNERSHIP
The TRANSFER OWNERSHIP statement transfers ownership of a database object.
TRUNCATE
The TRUNCATE statement deletes all of the rows from a table.
UPDATE
The UPDATE statement updates the values of specified columns in rows of a table or view. Updating a row of a view updates a row of its base table, if no INSTEAD OF UPDATE trigger is defined on this view. If such a trigger is defined, the trigger will be activated instead.
VALUES
The VALUES statement provides a method for invoking a user-defined function from a trigger. Transition variables can be passed to the user-defined function.
VALUES INTO
The VALUES INTO statement produces a result table consisting of at most one row and assigns the values in that row to variables.
WHENEVER
The WHENEVER statement specifies the action to be taken when a specified exception condition occurs.

Parent topic: Db2 for i SQL reference