Programming 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 DSN8A10.DEPT" ,
   "SET MGRNO = '000010'" ,
   "WHERE DEPTNO = 'D11'"
"EXECSQL " || ,
"  UPDATE DSN8A10.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 errors and warnings

DB2 does not support the SQL WHENEVER statement in a REXX program. To handle SQL errors and warnings, use the following methods:
  • To test for SQL errors or warnings, test the SQLCODE or SQLSTATE value and the SQLWARN. values after each EXECSQL call. This method does not detect errors in the REXX interface to DB2.
  • To test for SQL errors or warnings or errors or warnings from the REXX interface to DB2, test the REXX RC variable after each EXECSQL call. The following table lists the values of the RC variable.

    You can also use the REXX SIGNAL ON ERROR and SIGNAL ON FAILURE keyword instructions to detect negative values of the RC variable and transfer control to an error routine.

Table 1. REXX return codes after SQL statements
Return code Meaning
0 No SQL warning or error occurred.
+1 An SQL warning occurred.
-1 An SQL error occurred.
-3 The first token after ADDRESS DSNREXX is in error. For a description of the tokens allowed, see Accessing the DB2 REXX language support application programming interfaces.