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.

Table 1. SQL Statement and SQL Data Access Classification
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
Start of changecompound (dynamic)End of change Start of change End of change Start of changeY2End of change Start of changeY2End of change Start of changeYEnd of change
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
Start of changeTRANSFER OWNERSHIPEnd of change Start of change End of change Start of change End of change Start of change End of change Start of changeYEnd of change
Start of changeTRUNCATEEnd of change Start of change End of change Start of change End of change Start of change End of change Start of changeYEnd of change
UPDATE       Y
VALUES        
VALUES INTO     Y Y
WHENEVER Y1 Y Y Y
Note:
  1. Although the NO SQL option implies that no SQL statements can be specified, non-executable statements are not restricted.
  2. It depends on the statement being executed. The statement specified for the EXECUTE statement Start of changeor any statement executed within a compound (dynamic) statementEnd of change 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.
  3. 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.