SQL statement data access classification for routines
Indicates the level of SQL data access that must be specified to use the SQL statement in a routine.
The following table indicates whether an SQL statement (specified in the first column) is allowed to execute in a function or procedure with the specified SQL data access classification. If an executable SQL statement is encountered in a function or procedure defined with NO SQL, SQLSTATE 38001 is returned. For other executions contexts, SQL statements that are not supported in any context return SQLSTATE 38003. For other SQL statements not allowed in a CONTAINS SQL context, SQLSTATE 38004 is returned and in a READS SQL DATA context, SQLSTATE 38002 is returned. During creation of an SQL function or SQL procedure, a statement that does not match the SQL data access classification will cause SQLSTATE 42895 to be returned.
SQL Statement | NO SQL | CONTAINS SQL | READS SQL DATA | MODIFIES SQL DATA |
---|---|---|---|---|
ALLOCATE CURSOR | Y | Y | ||
ALLOCATE DESCRIPTOR | Y | Y | ||
ALTER ... | Y | |||
ASSOCIATE LOCATORS | Y | Y | ||
BEGIN DECLARE SECTION | Y1 | Y | Y | Y |
CALL | Y | Y | Y | |
CLOSE | Y | Y | ||
COMMENT | Y | |||
COMMIT3 | Y | Y | Y | |
compound (dynamic) | Y2 | Y2 | Y | |
CONNECT (type 1 and type 2)3 | ||||
CREATE ... | Y | |||
DEALLOCATE DESCRIPTOR | Y | Y | ||
DECLARE CURSOR | Y1 | Y | Y | Y |
DECLARE GLOBAL TEMPORARY TABLE | Y | |||
DECLARE PROCEDURE | Y1 | Y | Y | Y |
DECLARE STATEMENT | Y1 | Y | Y | Y |
DECLARE VARIABLE | Y1 | Y | Y | Y |
DELETE | Y | |||
DESCRIBE | Y | Y | ||
DESCRIBE CURSOR | Y | Y | ||
DESCRIBE INPUT | Y | Y | ||
DESCRIBE PROCEDURE | Y | Y | ||
DESCRIBE TABLE | Y | Y | ||
DISCONNECT3 | ||||
DROP ... | Y | |||
END DECLARE SECTION | Y1 | Y | Y | Y |
EXECUTE | Y2 | Y2 | Y | |
EXECUTE IMMEDIATE | Y2 | Y2 | Y | |
FETCH | Y | Y | ||
FREE LOCATOR | Y | Y | Y | |
GET DESCRIPTOR | Y | Y | ||
GET DIAGNOSTICS | Y | Y | Y | |
GRANT ... | Y | |||
HOLD LOCATOR | Y | Y | Y | |
INCLUDE | Y1 | Y | Y | Y |
INSERT | Y | |||
LABEL | Y | |||
LOCK TABLE | Y | Y | Y | |
MERGE | Y | |||
OPEN | Y | Y | ||
PREPARE | Y | Y | Y | |
REFRESH TABLE | Y | |||
RELEASE CONNECTION3 | ||||
RELEASE SAVEPOINT | Y | |||
RENAME | Y | |||
REVOKE ... | Y | |||
ROLLBACK3 | Y | Y | Y | |
ROLLBACK TO SAVEPOINT | Y | |||
SAVEPOINT | Y | |||
SELECT INTO | Y | Y | ||
SET CONNECTION3 | ||||
SET CURRENT DEBUG MODE | Y | Y | ||
SET CURRENT DECFLOAT ROUNDING MODE | Y | Y | Y | |
SET CURRENT DEGREE | Y | Y | ||
SET CURRENT IMPLICIT XMLPARSE OPTION | Y | Y | Y | |
SET DESCRIPTOR | Y | Y | ||
SET ENCRYPTION PASSWORD | Y | Y | Y | |
SET OPTION | Y1 | Y | Y | Y |
SET PATH | Y | Y | Y | |
SET RESULT SETS | Y | Y | Y | |
SET SCHEMA | Y | Y | ||
SET SESSION AUTHORIZATION | Y | Y | ||
SET TRANSACTION | Y | Y | Y | |
SET variable | Y | Y | Y | |
SIGNAL | Y | Y | Y | |
TRANSFER OWNERSHIP | Y | |||
TRUNCATE | Y | |||
UPDATE | Y | |||
VALUES | ||||
VALUES INTO | Y | Y | ||
WHENEVER | Y1 | Y | Y | Y |
- Although the NO SQL option implies that no SQL statements can be specified, non-executable statements are not restricted.
- It depends on the statement being executed. The statement specified for the EXECUTE statement or any statement executed within a compound (dynamic) statement must be a statement that is allowed in the context of the particular SQL access level in effect. For example, if the SQL access level in effect is READS SQL DATA, the statement must not be an INSERT, UPDATE, or DELETE.
- Connection management and transaction statements are not allowed in a procedure running on a remote server. COMMIT and ROLLBACK are not allowed in an ATOMIC SQL procedure.