DB2 Version 9.7 for Linux, UNIX, and Windows

SQL statements that can be executed in routines

Successful execution of SQL statements in routines is subject to restrictions and conditional on certain prerequisites being met. However, it is possible to execute many SQL statements in routines.

If a statement invokes a routine, the effective SQL data access level for the statement will be the greater of:
  • The SQL data access level of the statement from the following table.
  • The SQL data access level of the routine specified when the routine was created.
For example, the CALL statement has an SQL data access level of CONTAINS SQL. However, if a stored procedure defined as READS SQL DATA is called, the effective SQL data access level for the CALL statement is READS SQL DATA.

The following table lists all supported SQL statements, including SQL PL control-statements, and identifies if each SQL statement may be executed within the various types of routines. For each SQL statement listed in the first column, each of the subsequent columns shows an X to indicate if the statement is executable within the routine. The final column identifies the minimum SQL access level required to allow the statement execution to succeed. Unless otherwise noted in a footnote, all of the SQL statements may be executed either statically or dynamically.

Table 1. SQL statements that can be executed in routines
SQL statement Executable in compound SQL (compiled) statements(1) Executable in compound SQL (inlined) statements(2) Executable in external procedures Executable in external functions Minimum required SQL data access level
ALLOCATE CURSOR X   X X MODIFIES SQL DATA
ALTER {BUFFERPOOL, DATABASE PARTITION GROUP, FUNCTION, METHOD, NICKNAME, PROCEDURE, SEQUENCE, SERVER, TABLE, TABLESPACE, TYPE, USER MAPPING, VIEW}     X X MODIFIES SQL DATA
ASSOCIATE LOCATORS X        
AUDIT     X X MODIFIES SQL DATA
BEGIN DECLARE SECTION     X X NO SQL(3)
CALL X X X X READS SQL DATA
CASE X X     CONTAINS SQL
CLOSE X   X X READS SQL DATA
COMMENT ON X   X X MODIFIES SQL DATA
COMMIT X(6)   X(6)   MODIFIES SQL DATA
Compound SQL X X X X CONTAINS SQL
CONNECT(2)          
CREATE {ALIAS, BUFFERPOOL, DATABASE PARTITION GROUP, DISTINCT TYPE, EVENT MONITOR, FUNCTION, FUNCTION MAPPING, GLOBAL TEMPORARY TABLE(11), INDEX(11), INDEX EXTENSION, METHOD, NICKNAME, PROCEDURE, SCHEMA, SEQUENCE, SERVER, TABLE(11), TABLESPACE, TRANSFORM, TRIGGER, TYPE, TYPE MAPPING, USER MAPPING, VIEW(11), WRAPPER } X (8)   X   MODIFIES SQL DATA
DECLARE CURSOR X X X   NO SQL(3)

DECLARE TEMPORARY TABLE

 X   X X MODIFIES SQL DATA
DELETE X X X X MODIFIES SQL DATA
DESCRIBE(9)     X X READS SQL DATA
DISCONNECT(4)          
DROP X(8)   X X MODIFIES SQL DATA
END DECLARE SECTION     X X NO SQL(3)
EXECUTE X   X X CONTAINS SQL(5)
EXECUTE IMMEDIATE X   x X CONTAINS SQL(5)
EXPLAIN X   X X MODIFIES SQL DATA
FETCH X   X X READS SQL DATA
FREE LOCATOR     X X CONTAINS SQL
FLUSH EVENT MONITOR     X X MODIFIES SQL DATA
FLUSH PACKAGE CACHE     X X MODIFIES SQL DATA
FOR X X     READS SQL DATA
FREE LOCATOR X   X X CONTAINS SQL
GET DIAGNOSTICS X X     READS SQL DATA
GOTO X X     CONTAINS SQL
GRANT X   X X MODIFIES SQL DATA
IF X X     CONTAINS SQL
INCLUDE     X X NO SQL
INSERT X X X X MODIFIES SQL DATA
ITERATE X X     CONTAINS SQL
LEAVE X X     CONTAINS SQL
LOCK TABLE X   X X CONTAINS SQL
LOOP X X     CONTAINS SQL
MERGE X X X X MODIFIES SQL DATA
OPEN X   X X READS SQL DATA(7)
PREPARE X   X X CONTAINS SQL
REFRESH TABLE     X X MODIFIES SQL DATA
RELEASE(4)          
RELEASE SAVEPOINT X   X X MODIFIES SQL DATA
RENAME TABLE     X X MODIFIES SQL DATA
RENAME TABLESPACE     X X MODIFIES SQL DATA
REPEAT X X     CONTAINS SQL
RESIGNAL X       MODIFIES SQL DATA
RETURN X       CONTAINS SQL
REVOKE     X X MODIFIES SQL DATA
ROLLBACK(6) X   X    
ROLLBACK TO SAVEPOINT X   X X MODIFIES SQL DATA
SAVEPOINT X       MODIFIES SQL DATA
select-statement X   X X READS SQL DATA
SELECT INTO X   X(10) X(10) READS SQL DATA(7)
SET CONNECTION(4)          
SET INTEGRITY     X   MODIFIES SQL DATA
SET special register X X X X CONTAINS SQL
SET variable X X     CONTAINS SQL
SIGNAL X X     MODIFIES SQL DATA
TRANSFER OWNERSHIP     X X MODIFIES SQL DATA
TRUNCATE     X X MODIFIES SQL DATA
UPDATE X X X   MODIFIES SQL DATA
VALUES INTO X   X X READS SQL DATA
WHENEVER X   X   NO SQL(3)
WHILE X X      
Note:
  1. Compound SQL (compiled) statements can be used as the body of SQL procedures, SQL functions, triggers, or as stand-alone statements.
  2. Compount SQL (inline) statements can be used as the body of SQL functions, SQL methods, triggers, or as stand-alone statements.
  3. Although the NO SQL option implies that no SQL statements can be specified, non-executable statements are not restricted.
  4. Connection management statements are not allowed in any routine execution context.
  5. This situation depends on the statement being executed. The statement specified for the EXECUTE statement must be allowed in the context of the particular SQL access level in effect. For example, if the SQL access level READS SQL DATA is in effect, the statement cannot be INSERT, UPDATE, or DELETE.
  6. The COMMIT statement and the ROLLBACK statement (without the TO SAVEPOINT clause) can be used in a stored procedure, but only if the stored procedure is called directly from an application, or indirectly through nested stored procedure calls from an application. If any trigger, function, method, or atomic compound statement is in the call chain to the stored procedure, a COMMIT or a ROLLBACK of a unit of work is not allowed.
  7. If the SQL access level READS SQL DATA is in effect, no SQL data change statement can be embedded in the SELECT INTO statement, or in the cursor referenced by the OPEN statement.
  8. SQL procedures can only issue CREATE and DROP statements for indexes, tables, and views.
  9. The DESCRIBE SQL statement has a different syntax than that of the CLP DESCRIBE command.
  10. This is only supported for embedded SQL routines.
  11. When referenced in an SQL procedure, the statement can only be executed statically.

Errors

Table 1 indicates whether the SQL statement specified by the first column is allowed to execute inside a routine that has the specified SQL data access level. If the statement exceeds the data access level, an error is returned when the routine is executed.
  • If an executable SQL statement is encountered inside a routine defined with the NO SQL data access level, then SQLSTATE 38001 is returned.
  • For other execution contexts, the SQL statements that are unsupported in any context return an SQLSTATE 38003 error.
  • For other SQL statements that are not allowed in a CONTAINS SQL context, SQLSTATE 38004 is returned.
  • In a READS SQL DATA context, SQLSTATE 38002 is returned.
  • During creation of an SQL routine, a statement that does not match the SQL data access level returns an SQLSTATE 42985 error.
Parent topic: Overview of routines