Actions allowed on SQL statements

Specific Db2 statements can be executed, prepared interactively or dynamically, or processed by the requester, the server, or the precompiler or coprocessor.

The following table shows whether a specific Db2 statement can be executed, prepared interactively or dynamically, or processed by the requester, the server, or the precompiler or coprocessor. The letter Y means yes.

Table 1. Actions allowed on SQL statements in Db2 for z/OS®
SQL statement Executable Interactively or dynamically prepared Processed by
Requesting system Server Precompiler or coprocessor
ALLOCATE CURSOR1 Y Y Y    
ALTER2 Y Y   Y  
ASSOCIATE LOCATORS1 Y Y Y    
BEGIN DECLARE SECTION         Y
CALL1 Y     Y  
CLOSE Y     Y  
COMMENT Y Y   Y  
COMMIT8 Y Y   Y  
CONNECT Y   Y    
CREATE2 Y Y   Y  
DECLARE CURSOR         Y
DECLARE GLOBAL
TEMPORARY TABLE
 Y Y   Y  
DECLARE STATEMENT         Y
DECLARE TABLE         Y
DECLARE VARIABLE         Y
DELETE Y Y   Y  
DESCRIBE prepared statement or table Y     Y  
DESCRIBE CURSOR Y   Y    
DESCRIBE INPUT Y     Y  
DESCRIBE PROCEDURE Y   Y    
DROP2 Y Y   Y  
END DECLARE SECTION         Y
EXECUTE Y     Y  
EXECUTE IMMEDIATE Y     Y  
EXPLAIN Y Y   Y  
FETCH Y     Y  
FREE LOCATOR1 Y Y   Y  
GET DIAGNOSTICS Y     Y  
GRANT2 Y Y   Y  
HOLD LOCATOR1 Y Y   Y  
INCLUDE         Y
INSERT Y Y   Y  
LABEL Y Y   Y  
LOCK TABLE Y Y   Y  
MERGE Y Y   Y  
OPEN Y     Y  
PREPARE Y     Y4  
REFRESH TABLE Y Y   Y  
RELEASE connection Y   Y    
RELEASE SAVEPOINT Y Y   Y  
RENAME2 Y Y   Y  
REVOKE2 Y Y   Y  
ROLLBACK8 Y Y   Y  
SAVEPOINT Y Y   Y  
SELECT INTO Y     Y  
SET CONNECTION Y   Y    
SET CURRENT APPLICATION ENCODING SCHEME Y   Y    
SET CURRENT DEBUG MODE Y Y   Y  
SET CURRENT DECFLOAT ROUNDING MODE Y Y   Y  
SET CURRENT DEGREE Y Y   Y  
SET CURRENT GET_ACCEL_ARCHIVE Y Y   Y  
SET CURRENT LC_CTYPE Y Y   Y  
SET CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION Y Y   Y  
SET CURRENT OPTIMIZATION HINT Y Y   Y  
SET CURRENT PACKAGE PATH Y   Y    
SET CURRENT PACKAGESET Y     Y  
SET CURRENT PRECISION Y Y   Y  
SET CURRENT QUERY ACCELERATION Y Y   Y  
SET CURRENT REFRESH AGE Y Y   Y  
SET CURRENT ROUTINE VERSION Y Y   Y  
SET CURRENT RULES Y Y   Y  
SET CURRENT SQLID5 Y Y   Y  
SET host-variable = CURRENT APPLICATION ENCODING SCHEME Y   Y    
SET host-variable = CURRENT DATE Y     Y  
SET host-variable = CURRENT DEGREE Y     Y  
SET host-variable = CURRENT MEMBER Y     Y  
SET host-variable = CURRENT PACKAGESET Y    Y    
SET host-variable = CURRENT PATH Y     Y  
SET host-variable = CURRENT QUERY OPTIMIZATION LEVEL Y     Y  
SET host-variable = CURRENT SERVER Y    Y    
SET host-variable = CURRENT SQLID Y     Y  
SET host-variable = CURRENT TIME Y     Y  
SET host-variable = CURRENT TIMESTAMP Y     Y  
SET host-variable = CURRENT TIMEZONE Y     Y  
SET PATH Y Y   Y  
SET SCHEMA Y Y   Y  
SET transition-variable = CURRENT DATE Y     Y  
SET transition-variable = CURRENT DEGREE Y     Y  
SET transition-variable = CURRENT PATH Y     Y  
SET transition-variable = CURRENT QUERY OPTIMIZATION LEVEL Y     Y  
SET transition-variable = CURRENT SQLID Y     Y  
SET transition-variable = CURRENT TIME Y     Y  
SET transition-variable = CURRENT TIMESTAMP Y     Y  
SET transition-variable = CURRENT TIMEZONE Y     Y  
SIGNAL6 Y     Y  
TRUNCATE Y Y   Y  
UPDATE Y Y   Y  
VALUES6 Y     Y  
VALUES INTO7 Y     Y  
WHENEVER         Y
Note:
  1. The statement can be dynamically prepared. It cannot be issued dynamically.
  2. The statement can be dynamically prepared only if DYNAMICRULES run behavior is implicitly or explicitly specified.
  3. The statement can be dynamically prepared, but only from an ODBC or CLI driver that supports dynamic CALL statements.
  4. The requesting system processes the PREPARE statement when the statement being prepared is ALLOCATE CURSOR or ASSOCIATE LOCATORS.
  5. The value to which special register CURRENT SQLID is set is used as the SQL authorization ID for dynamic SQL statements only when DYNAMICRULES run behavior is in effect. The CURRENT SQLID value is ignored for the other DYNAMICRULES behaviors.
  6. This statement can be used only in the triggered action of a trigger.
  7. Local special registers can be referenced in a VALUES INTO statement if it results in the assignment of a single host-variable, not if it results in setting more than one value.
  8. Some processing also occurs at the requester.