One of the challenges by using SQL in REXX is poor performance. Traditionally I have build the SQL statement to be executed in a variable as specified in the tip in week 14/2016. It works well if you only need to execute the same SQL statement a few times with different "hostvariable" values. It would be nice if it was possible to use something similar to hostvariables in the executable SQL. Then you do not have to build the SQL every time and may also get performance close to static SQL like in COBOL or PL/I.

And it is in fact possible to use hostvariables. It works a little different than static SQL, but it is extremely easy. Just take a look:

/* REXX */
SUBCOM DSNREXX
IF RC THEN X=RXSUBCOM('ADD','DSNREXX','DSNREXX')
CALL SQLEXEC 0, "CONNECT MYDB"
CALL SQLEXEC 0, "EXECSQL SET CURRENT PACKAGESET='DSNREXCS'"

SQLSTMT = 'SELECT CREATOR, NAME',
          'FROM SYSIBM.SYSTABLES',
          'WHERE DBNAME= ? AND TSNAME= ?'
CALL SQLEXEC 0, "EXECSQL DECLARE C1 CURSOR FOR S1"
CALL SQLEXEC 98, "EXECSQL PREPARE S1 INTO :OUTSQLDA FROM :SQLSTMT"

CALL GETTABLENAME 'DSNDB06', 'SYSCOPY'
IF CREATOR <> ''
THEN
  SAY CREATOR'.'NAME

CALL GETTABLENAME 'DSNDB06', 'SYSGPAUT'
IF CREATOR <> ''
THEN
  SAY CREATOR'.'NAME

CALL SQLEXEC 0, "DISCONNECT"
RC=RXSUBCOM('DELETE','DSNREXX','DSNREXX')
EXIT

GETTABLENAME:
  ARG DBNAME, TSNAME
  CALL SQLEXEC 0, "EXECSQL OPEN C1 USING :DBNAME,:TSNAME"
  CALL SQLEXEC 100, "EXECSQL FETCH C1 USING DESCRIPTOR :OUTSQLDA"
  IF SQLCODE = 0
  THEN DO
    CREATOR = OUTSQLDA.1.SQLDATA
    NAME = OUTSQLDA.2.SQLDATA
  END
  ELSE
    CREATOR = ''
  CALL SQLEXEC 0, "EXECSQL CLOSE C1"
EXIT

SQLEXEC:
  ARG ACCEPT, STMT
  ADDRESS DSNREXX STMT
  IF SQLCODE <> 0 & SQLCODE <> ACCEPT
  THEN
    IF WORD(STMT,1) = 'EXECSQL'
    THEN
      SAY WORD(STMT,2)': SQLCODE='SQLCODE
    ELSE
      SAY WORD(STMT,1)': SQLCODE='SQLCODE
RETURN 

Most of the code is similar to the REXX from the previous tip, but there is a significant difference. The PREPARE is executed only once and building the SQL is also executed only once. In the SQL a ? is specified where a hostvariable normally would be used. OPEN is executed for each set of hostvariable values to be used. On the OPEN a USING has been added and following USING the REXX-variables to be used as hostvariables are specified. The first REXX-variable/hostvariable substitutes the first ? and so forth.

As long as you only execute the SQL statement a few times like in the above simple REXX you will not notice any significant difference, but just 100 exections will show the difference. Now there is one reason less to use COBOL or PL/I.