Embedding SQL statements in REXX applications

An SQL statement can be placed anywhere a REXX command can be placed.

Each SQL statement in a REXX procedure must begin with EXECSQL (in any combination of uppercase and lowercase letters), followed by either:

  • The SQL statement enclosed in single or double quotation marks, or
  • A REXX variable containing the statement. Note that a colon must not precede a REXX variable when it contains an SQL statement.

For example:

EXECSQL “COMMIT”

is equivalent to:

rexxvar = “COMMIT”
EXECSQL rexxvar

The command follows normal REXX rules. For example, it can optionally be followed by a semicolon (;) to allow a single line to contain more than one REXX statement. REXX also permits command names to be included within single quotation marks, for example:

'EXECSQL COMMIT'

The SQL/REXX interface supports the following SQL statements:

ALTER FUNCTION
ALTER PROCEDURE
ALTER SEQUENCE
ALTER TABLE
CALL 2
CLOSE
COMMENT
COMMIT
CREATE ALIAS
CREATE FUNCTION
CREATE INDEX
CREATE PROCEDURE
CREATE SCHEMA
CREATE SEQUENCE
CREATE TABLE
CREATE TRIGGER
CREATE TYPE
CREATE VIEW
DECLARE CURSOR  2
DECLARE GLOBAL TEMPORARY TABLE
DELETE 2
DESCRIBE
DESCRIBE TABLE
DROP
EXECUTE
EXECUTE IMMEDIATE
FETCH 1
GRANT
INSERT 1
LABEL
LOCK TABLE
OPEN
PREPARE
REFRESH TABLE
RELEASE SAVEPOINT
RENAME
REVOKE
ROLLBACK
SAVEPOINT
SET CURRENT DECFLOAT ROUNDING MODE
SET CURRENT IMPLICIT XMLPARSE OPTION
SET ENCRYPTION PASSWORD
SET OPTION 3
SET PATH
SET SCHEMA
SET TRANSACTION
SET variable 2
UPDATE 2

The following SQL statements are not supported by the SQL/REXX interface:

ALLOCATE CURSOR
ALLOCATE DESCRIPTOR
ASSOCIATE LOCATORS
BEGIN DECLARE SECTION
CONNECT
DEALLOCATE DESCRIPTOR
DECLARE PROCEDURE
DECLARE STATEMENT
DECLARE VARIABLE
DESCRIBE CURSOR
DESCRIBE INPUT
DESCRIBE PROCEDURE
DISCONNECT
END DECLARE SECTION
FREE LOCATOR
GET DESCRIPTOR
GET DIAGNOSTICS
HOLD LOCATOR
INCLUDE
MERGE
RELEASE
SELECT INTO
SET CONNECTION
SET CURRENT DEGREE
SET DESCRIPTOR
SET RESULT SETS
SET SESSION AUTHORIZATION
SIGNAL
VALUES INTO
WHENEVER4
  1. The blocked form of this statement is not supported.
  2. These statements cannot be run directly if they contain host variables; they must be the object of a PREPARE and then an EXECUTE.
  3. The SET OPTION statement can be used in a REXX procedure to change some of the processing options used for running SQL statements. These options include the commitment control level and date format. See the DB2® for i SQL reference topic for more information about the SET OPTION statement.
  4. See Handling errors and warnings in REXX applications that use SQL for more information.