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 |
- The blocked form of this statement is not supported.
- These statements cannot be run directly if they contain host variables; they must be the object of a PREPARE and then an EXECUTE.
- 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.
- See Handling errors and warnings in REXX applications that use SQL for more information.