REXX applications that issue SQL statements

You can code SQL statements in a REXX programs wherever you can use REXX commands.

Db2 REXX Language Support supports all dynamic SQL statements and the following static SQL statements:

CALL
CLOSE
CONNECT
DECLARE CURSOR
DESCRIBE prepared statement or table
DESCRIBE CURSOR
DESCRIBE INPUT
DESCRIBE PROCEDURE
EXECUTE
EXECUTE IMMEDIATE
FETCH
OPEN
PREPARE
RELEASE connection
SET CONNECTION
SET CURRENT PACKAGE PATH
SET CURRENT PACKAGESET
SET host-variable = CURRENT DATE
SET host-variable = CURRENT DEGREE
SET host-variable = CURRENT MEMBER
SET host-variable = CURRENT PACKAGESET
SET host-variable = CURRENT PATH
SET host-variable = CURRENT SERVER
SET host-variable = CURRENT SQLID
SET host-variable = CURRENT TIME
SET host-variable = CURRENT TIMESTAMP
SET host-variable = CURRENT TIMEZONE

Each SQL statement in a REXX program must begin with EXECSQL, in either upper-, lower-, or mixed-case. One of the following items must follow EXECSQL:

An SQL statement enclosed in single or double quotation marks.
A REXX variable that contains an SQL statement. The REXX variable must not be preceded by a colon.

For example, you can use either of the following methods to execute the COMMIT statement in a REXX program:

EXECSQL "COMMIT"

rexxvar="COMMIT"
EXECSQL rexxvar

The following dynamic statements must be executed using EXECUTE IMMEDIATE or PREPARE and EXECUTE under DSNREXX:

DECLARE GLOBAL TEMPORARY TABLE
SET CURRENT DEBUG MODE
SET CURRENT DECFLOAT ROUNDING MODE
SET CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION
SET CURRENT QUERY ACCELERATION
SET CURRENT REFRESH AGE
SET CURRENT ROUTINE VERSION
SET SCHEMA

You cannot execute a SELECT, INSERT, UPDATE, MERGE, or DELETE statement that contains host variables. Instead, you must execute PREPARE on the statement, with parameter markers substituted for the host variables, and then use the host variables in an EXECUTE, OPEN, or FETCH statement. See Host variables for more information.

An SQL statement follows rules that apply to REXX commands. The SQL statement can optionally end with a semicolon and can be enclosed in single or double quotation marks, as in the following example:

'EXECSQL COMMIT';

Comments

You cannot include REXX comments (/* … */) or SQL comments (--) within SQL statements. However, you can include REXX comments anywhere else in the program.

Delimiters for SQL statements

Delimit SQL statements in REXX program by preceding the statement with EXECSQL. If the statement is in a literal string, enclose it in single or double quotation marks.

Continuation for SQL statements

SQL statements that span lines follow REXX rules for statement continuation. You can break the statement into several strings, each of which fits on a line, and separate the strings with commas or with concatenation operators followed by commas. For example, either of the following statements is valid:

EXECSQL ,
   "UPDATE DSN8C10.DEPT" ,
   "SET MGRNO = '000010'" ,
   "WHERE DEPTNO = 'D11'"

"EXECSQL " || ,
"  UPDATE DSN8C10.DEPT " || ,
"  SET MGRNO = '000010'" || ,
"  WHERE DEPTNO = 'D11'"

Including code

The EXECSQL INCLUDE statement is not valid for REXX. You therefore cannot include externally defined SQL statements in a program.

Margins

Like REXX commands, SQL statements can begin and end anywhere on a line.

You can use any valid REXX name that does not end with a period as a host variable. However, host variable names should not begin with 'SQL', 'RDI', 'DSN', 'RXSQL', or 'QRW'. Variable names can be at most 64 bytes.

Nulls

A REXX null value and an SQL null value are different. The REXX language has a null string (a string of length 0) and a null clause (a clause that contains only blanks and comments). The SQL null value is a special value that is distinct from all nonnull values and denotes the absence of a value. Assigning a REXX null value to a Db2 column does not make the column value null.

Statement labels

You can precede an SQL statement with a label, in the same way that you label REXX commands.

Handling SQL error codes

Rexx applications can request more information about SQL errors from Db2. For more information, see Handling SQL error codes in REXX applications.