REXX stored procedures

A REXX stored procedure is similar to any other REXX procedure and follows the same rules as stored procedures in other languages. A REXX stored procedure receives input parameters, executes REXX commands, optionally executes SQL statements, and returns at most one output parameter. However, a few differences exist.

A REXX stored procedure is different from other REXX procedures in the following ways:

A REXX stored procedure must not execute any of the following DSNREXX commands that are used for the Db2 subsystem thread attachment:
- ADDRESS DSNREXX CONNECT
- ADDRESS DSNREXX DISCONNECT
- CALL SQLDBS ATTACH TO
- CALL SQLDBS DETACH
When you execute SQL statements in your stored procedure, Db2 establishes the connection for you.
A REXX stored procedure must run in a WLM-established stored procedures address space.
A language REXX stored procedure executes in a background TSO/E REXX environment provided by the TSO/E environment service IKJTSOEV.

Unlike other stored procedures, you do not prepare REXX stored procedures for execution. REXX stored procedures run using one of four packages that are bound during the installation of Db2 REXX Language Support. The current isolation level at which the stored procedure runs depends on the package that Db2 uses when the stored procedure runs:

Package name: Isolation level
DSNREXRR: Repeatable read (RR)
DSNREXRS: Read stability (RS)
DSNREXCS: Cursor stability (CS)
DSNREXUR: Uncommitted read (UR)

This topic shows an example of a REXX stored procedure that executes Db2 commands. The stored procedure performs the following actions:

Receives one input parameter, which contains a Db2 command.
Calls the IFI COMMAND function to execute the command.
Extracts the command result messages from the IFI return area and places the messages in a created temporary table. Each row of the temporary table contains a sequence number and the text of one message.
Opens a cursor to return a result set that contains the command result messages.
Returns the unformatted contents of the IFI return area in an output parameter.

The following example shows the definition of the stored procedure.

CREATE PROCEDURE COMMAND(IN CMDTEXT VARCHAR(254), OUT CMDRESULT VARCHAR(32704))
  LANGUAGE REXX
  EXTERNAL NAME COMMAND
  NO COLLID
  ASUTIME NO LIMIT
  PARAMETER STYLE GENERAL
  STAY RESIDENT NO
  RUN OPTIONS 'TRAP(ON)'
  WLM ENVIRONMENT WLMENV1
  SECURITY DB2
  DYNAMIC RESULT SETS 1
  COMMIT ON RETURN NO;

The following example shows the COMMAND stored procedure that executes Db2 commands.

/* REXX */
PARSE UPPER ARG CMD                    /* Get the DB2 command text */
                                       /* Remove enclosing quotation marks  */
IF LEFT(CMD,1) = "'" & RIGHT(CMD,1) = "'" THEN
CMD = SUBSTR(CMD,2,LENGTH(CMD)-2)
ELSE
IF LEFT(CMD,1) = '"' & RIGHT(CMD,1) = '"' THEN
CMD = SUBSTR(CMD,2,LENGTH(CMD)-2)
COMMAND    = SUBSTR("COMMAND",1,18," ")
  /****************************************************************/
  /*  Set up the IFCA, return area, and output area for the       */
  /*  IFI COMMAND call.                                           */
  /****************************************************************/
IFCA = SUBSTR('00'X,1,180,'00'X)
IFCA = OVERLAY(D2C(LENGTH(IFCA),2),IFCA,1+0)
IFCA = OVERLAY("IFCA",IFCA,4+1)
RTRNAREASIZE = 262144 /*1048572*/
RTRNAREA = D2C(RTRNAREASIZE+4,4)LEFT(' ',RTRNAREASIZE,' ')
OUTPUT = D2C(LENGTH(CMD)+4,2)||'0000'X||CMD
BUFFER = SUBSTR(" ",1,16," ")
  /****************************************************************/
  /*  Make the IFI COMMAND call.                                  */
  /****************************************************************/
ADDRESS LINKPGM "DSNWLIR COMMAND IFCA RTRNAREA OUTPUT"
WRC = RC
RTRN=    SUBSTR(IFCA,12+1,4)
REAS=    SUBSTR(IFCA,16+1,4)
TOTLEN = C2D(SUBSTR(IFCA,20+1,4))
  /****************************************************************/
  /*  Set up the host command environment for SQL calls.          */
  /****************************************************************/
"SUBCOM DSNREXX"                       /* Host cmd env available?  */
IF RC THEN                             /* No--add host cmd env     */
   S_RC = RXSUBCOM('ADD','DSNREXX','DSNREXX')

  /****************************************************************/
  /*  Set up SQL statements to insert command output messages     */
  /*  into a temporary table.                                     */
  /****************************************************************/
SQLSTMT='INSERT INTO SYSIBM.SYSPRINT(SEQNO,TEXT) VALUES(?,?)'
ADDRESS DSNREXX "EXECSQL DECLARE C1 CURSOR FOR S1"
IF SQLCODE ¬= 0 THEN CALL SQLCA
ADDRESS DSNREXX "EXECSQL PREPARE S1 FROM :SQLSTMT"
IF SQLCODE ¬= 0 THEN CALL SQLCA
  /****************************************************************/
  /*  Extract messages from the return area and insert them into  */
  /*  the temporary table.                                        */
  /****************************************************************/
SEQNO = 0
OFFSET = 4+1
DO WHILE ( OFFSET < TOTLEN )
  LEN = C2D(SUBSTR(RTRNAREA,OFFSET,2))
  SEQNO = SEQNO + 1
  TEXT = SUBSTR(RTRNAREA,OFFSET+4,LEN-4-1)
  ADDRESS DSNREXX "EXECSQL EXECUTE S1 USING :SEQNO,:TEXT"
  IF SQLCODE ¬= 0 THEN CALL SQLCA
  OFFSET = OFFSET + LEN
END
  /****************************************************************/
  /*  Set up a cursor for a result set that contains the command  */
  /*  output messages from the temporary table.                   */
  /****************************************************************/
SQLSTMT='SELECT SEQNO,TEXT FROM SYSIBM.SYSPRINT ORDER BY SEQNO'
ADDRESS DSNREXX "EXECSQL DECLARE C2 CURSOR FOR S2"
IF SQLCODE ¬= 0 THEN CALL SQLCA
ADDRESS DSNREXX "EXECSQL PREPARE S2 FROM :SQLSTMT"
IF SQLCODE ¬= 0 THEN CALL SQLCA
  /****************************************************************/
  /*  Open the cursor to return the message output result set to  */
  /*  the caller.                                                 */
  /****************************************************************/
ADDRESS DSNREXX "EXECSQL OPEN C2"
IF SQLCODE ¬= 0 THEN CALL SQLCA
S_RC = RXSUBCOM('DELETE','DSNREXX','DSNREXX') /* REMOVE CMD ENV  */
EXIT SUBSTR(RTRNAREA,1,TOTLEN+4)

  /****************************************************************/
  /*  Routine to display the SQLCA                                */
  /****************************************************************/
SQLCA:
SAY 'SQLCODE ='SQLCODE
SAY 'SQLERRMC ='SQLERRMC
SAY 'SQLERRP ='SQLERRP
SAY 'SQLERRD ='SQLERRD.1',',
            || SQLERRD.2',',
            || SQLERRD.3',',
            || SQLERRD.4',',
            || SQLERRD.5',',
            || SQLERRD.6
SAY 'SQLWARN ='SQLWARN.0',',
            || SQLWARN.1',',
            || SQLWARN.2',',
            || SQLWARN.3',',
            || SQLWARN.4',',
            || SQLWARN.5',',
            || SQLWARN.6',',
            || SQLWARN.7',',
            || SQLWARN.8',',
            || SQLWARN.9',',
            || SQLWARN.10
SAY 'SQLSTATE='SQLSTATE
SAY 'SQLCODE ='SQLCODE
EXIT 'SQLERRMC ='SQLERRMC';' ,
||  'SQLERRP ='SQLERRP';' ,
||  'SQLERRD ='SQLERRD.1',',
            || SQLERRD.2',',
            || SQLERRD.3',',
            || SQLERRD.4',',
            || SQLERRD.5',',
            || SQLERRD.6';' ,
||  'SQLWARN ='SQLWARN.0',',
            || SQLWARN.1',',
            || SQLWARN.2',',
            || SQLWARN.3',',
            || SQLWARN.4',',
            || SQLWARN.5',',
            || SQLWARN.6',',
            || SQLWARN.7',',
            || SQLWARN.8',',
            || SQLWARN.9',',
            || SQLWARN.10';' ,
||  'SQLSTATE='SQLSTATE';'