Application Development Guide

Appendixes

Appendix A. Supported SQL Statements

Table 37:

• Lists all the supported SQL statements in DB2 Universal Database for Linux, OS/2, UNIX, and Windows 32-bit operating systems
• Indicates (with an 'X') if they can be executed dynamically
• Indicates (with an 'X') if they are supported by the command line processor (CLP)
• Indicates (with an 'X' or DB2 CLI function name) if the statement can be executed using the DB2 Call Level Interface (DB2 CLI)
• Indicates (with an 'X') if the statement can be executed in an SQL procedure

You can use Table 37 as a quick reference aid. For a complete discussion of all the statements, including their syntax, refer to the SQL Reference.

Table 37. SQL Statements (DB2 Universal Database)

SQL Statement	Dynamic1	Command Line Processor (CLP)	Call Level Interface3 (CLI)	SQL Procedure
ALLOCATE CURSOR				X
assignment statement				X
ASSOCIATE LOCATORS				X
ALTER { BUFFERPOOL, NICKNAME,10 NODEGROUP, SERVER,10 TABLE, TABLESPACE, USER MAPPING,10 TYPE, VIEW  }	X	X	X
BEGIN DECLARE SECTION2
CALL		X9	X4	X
CASE statement				X
CLOSE		X	SQLCloseCursor(), SQLFreeStmt()	X
COMMENT ON	X	X	X	X
COMMIT	X	X	SQLEndTran, SQLTransact()	X
Compound SQL (Embedded)			X4
compound statement				X
CONNECT (Type 1)		X	SQLBrowseConnect(), SQLConnect(), SQLDriverConnect()
CONNECT (Type 2)		X	SQLBrowseConnect(), SQLConnect(), SQLDriverConnect()
CREATE { ALIAS, BUFFERPOOL, DISTINCT TYPE, EVENT MONITOR, FUNCTION, FUNCTION MAPPING,10 INDEX, INDEX EXTENSION, METHOD, NICKNAME,10 NODEGROUP, PROCEDURE, SCHEMA, TABLE, TABLESPACE, TRANSFORM TYPE MAPPING,1 TRIGGER, USER MAPPING,10 TYPE, VIEW, WRAPPER10 }	X	X	X	X11
DECLARE CURSOR2		X	SQLAllocStmt()	X
DECLARE GLOBAL TEMPORARY TABLE	X	X	X	X
DELETE	X	X	X	X
DESCRIBE8		X	SQLColAttributes(), SQLDescribeCol(), SQLDescribParam()6
DISCONNECT		X	SQLDisconnect()
DROP	X	X	X	X11
END DECLARE SECTION2
EXECUTE			SQLExecute()	X
EXECUTE IMMEDIATE			SQLExecDirect()	X
EXPLAIN	X	X	X	X
FETCH		X	SQLExtendedFetch()7, SQLFetch(), SQLFetchScroll()7	X
FLUSH EVENT MONITOR	X	X	X
FOR statement				X
FREE LOCATOR			X4	X
GET DIAGNOSTICS				X
GOTO statement				X
GRANT	X	X	X	X
IF statement				X
INCLUDE2
INSERT	X	X	X	X
ITERATE				X
LEAVE statement				X
LOCK TABLE	X	X	X	X
LOOP statement				X
OPEN		X	SQLExecute(), SQLExecDirect()	X
PREPARE			SQLPrepare()	X
REFRESH TABLE	X	X	X
RELEASE		X		X
RELEASE SAVEPOINT	X	X	X	X
RENAME TABLE	X	X	X
RENAME TABLESPACE	X	X	X
REPEAT statement				X
RESIGNAL statement				X
RETURN statement				X
REVOKE	X	X	X
ROLLBACK	X	X	SQLEndTran(), SQLTransact()	X
SAVEPOINT	X	X	X	X
select-statement	X	X	X	X
SELECT INTO				X
SET CONNECTION		X	SQLSetConnection()
SET CURRENT DEFAULT TRANSFORM GROUP	X	X	X	X
SET CURRENT DEGREE	X	X	X	X
SET CURRENT EXPLAIN MODE	X	X	X, SQLSetConnectAttr()	X
SET CURRENT EXPLAIN SNAPSHOT	X	X	X, SQLSetConnectAttr()	X
SET CURRENT PACKAGESET
SET CURRENT QUERY OPTIMIZATION	X	X	X	X
SET CURRENT REFRESH AGE	X	X	X	X
SET EVENT MONITOR STATE	X	X	X	X
SET INTEGRITY	X	X	X
SET PASSTHRU10	X	X	X	X
SET PATH	X	X	X	X
SET SCHEMA	X	X	X	X
SET SERVER OPTION10	X	X	X	X
SET transition-variable5	X	X	X	X
SIGNAL statement				X
SIGNAL SQLSTATE5	X	X	X
UPDATE	X	X	X	X
VALUES INTO				X
WHENEVER2
WHILE statement				X
Notes: You can code all statements in this list as static SQL, but only those marked with X as dynamic SQL. You cannot execute this statement. An X indicates that you can execute this statement using either SQLExecDirect() or SQLPrepare() and SQLExecute(). If there is an equivalent DB2 CLI function, the function name is listed. Although this statement is not dynamic, with DB2 CLI you can specify this statement when calling either SQLExecDirect(), or SQLPrepare() and SQLExecute(). You can only use this within CREATE TRIGGER statements. You can only use the SQL DESCRIBE statement to describe output, whereas with DB2 CLI you can also describe input (using the SQLDescribeParam() function). You can only use the SQL FETCH statement to fetch one row at a time in one direction, whereas with the DB2 CLI SQLExtendedFetch() and SQLFetchScroll() functions, you can fetch into arrays. Furthermore, you can fetch in any direction, and at any position in the result set. The DESCRIBE SQL statement has a different syntax than that of the CLP DESCRIBE command. For information on the DESCRIBE SQL statement, refer to the SQL Reference. For information on the DESCRIBE CLP command, refer to the Command Reference. When CALL is issued through the command line processor, only certain procedures and their respective parameters are supported (see Installing, Replacing, and Removing JAR Files). Statement is supported only for federated database servers. SQL procedures can only issue CREATE and DROP statements for indexes, tables, and views.