SQL statement data access classification for routines

Certain SQL statements can be executed in a stored procedure or in a user-defined function. Whether the statements can be executed depends on the level of SQL data access with which the routine is defined.

The following table indicates whether an SQL statement (specified in the first column) is allowed to execute in a routine with the specified SQL data access classification. The letter Y means yes.

In general, if an executable SQL statement is encountered in a routine defined as NO SQL, SQLSTATE 38001 is returned. If the routine is defined to allow some level of SQL access, SQL statements that are not supported in any context return SQLSTATE 38003. SQL statements not allowed for routines defined as CONTAINS SQL return SQLSTATE 38004, and SQL statements not allowed for READS SQL DATA return SQLSTATE 38002.

Table 1. SQL data access classification for routines
SQL statement Level of SQL data access
NO SQL CONTAINS SQL READS SQL DATA MODIFIES SQL DATA
ALLOCATE CURSOR     Y Y
ALTER       Y
ASSOCIATE LOCATORS     Y Y
BEGIN DECLARE SECTION Y1 Y Y Y
CALL   Y2 Y2 Y2
CLOSE     Y Y
COMMENT       Y
COMMIT   Y Y Y
CONNECT   Y Y Y
CREATE       Y
DECLARE CURSOR Y1 Y Y Y
DECLARE GLOBAL TEMPORARY TABLE       Y
DECLARE STATEMENT Y1 Y Y Y
DECLARE TABLE Y1 Y Y Y
DECLARE VARIABLE Y1 Y Y Y
DELETE       Y
DESCRIBE     Y Y
DESCRIBE CURSOR     Y Y
DESCRIBE INPUT     Y Y
DESCRIBE OUTPUT     Y Y
DESCRIBE PROCEDURE     Y Y
DESCRIBE TABLE     Y Y
DROP       Y
END DECLARE SECTION Y1 Y Y Y
EXCHANGE       Y
EXECUTE   Y3 Y3 Y
EXECUTE IMMEDIATE   Y3 Y3 Y
EXPLAIN       Y
FETCH     Y Y
FREE LOCATOR   Y 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 connection   Y Y Y
RELEASE SAVEPOINT       Y
RENAME       Y
REVOKE       Y
ROLLBACK   Y Y Y
ROLLBACK TO SAVEPOINT       Y
SAVEPOINT       Y
SELECT INTO     Y Y
SET CONNECTION   Y Y Y
SET host-variable   Y4 Y Y
SET special register   Y Y Y
SET transition-variable Assignment   Y4 Y Y
SIGNAL   Y Y Y
TRUNCATE       Y
UPDATE       Y
VALUES     Y Y
VALUES INTO   Y4 Y Y
WHENEVER Y1 Y Y Y
Notes:
  1. Although the SQL option implies that no SQL statements can be specified, non-executable statements are not restricted.
  2. The stored procedure that is called must have the same or more restrictive level of SQL data access than the current level in effect. For example, a routine defined as MODIFIES SQL DATA can call a stored procedure defined as MODIFIES SQL DATA, READS SQL DATA, CONTAINS SQL, or NO SQL. A routine defined as CONTAINS SQL can call a procedure defined as CONTAINS SQL or NO SQL.
  3. The statement specified for the EXECUTE statement must be a statement that is allowed for the particular level of SQL data access in effect. For example, if the level in effect is READS SQL DATA, the statement must not be an INSERT, UPDATE, MERGE, or DELETE statement.
  4. The statement is supported only if it does not contain a subquery or query-expression.