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.
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 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.
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.
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.
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 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, and
distinct types. Comments and labels may be added in the catalog description
of tables, views, aliases, indexes, columns, and distinct types. Table,
view, and distinct type 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 (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.
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.
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.
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.