For many years I have avoided to write a tip about execution of SQL to DB2 from REXX even though I have done it a lot. The reason is that there is an ocean of different varieties of REXX interfaces to DB2. At some point IBM entered the battlefield and made their interface DSNREXX which is available on all DB2 installations. Unfortunately DSNREXX is not nearly as good as many of the other interfaces I have tried. But now I can at least write a tip about execution of SQL from a REXX that ought to work on all DB2 installations (unless the DB2 system programmers on purpose has removed it).

A bit out of the ordinary I have chosen to split this tip in two parts. I will start with the easy part, namely how to execute a SQL statement that is not a SELECT. There is a quite significant difference in complexity. And here comes the example:

/* 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="INSERT INTO MYUSER.MYSYSTABLES SELECT * FROM SYSIBM.SYSTABLES",
        " WHERE CREATOR = 'SYSIBM'"
CALL SQLEXEC 0, "EXECSQL "SQLSTMT

CALL SQLEXEC 0, "EXECSQL COMMIT"
CALL SQLEXEC 0, "DISCONNECT"
RC=RXSUBCOM('DELETE','DSNREXX','DSNREXX')
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
  ELSE
    SAY STMT
RETURN

The REXX consists of the program itself and a procedure to call the DSNREXX interface. This division makes it a lot easier to generalise error handling and the program becomes a lot easier to read. The drawback is poor performance if you want to execute many SQL statements as the procedure SQLEXEC is quite expensive to execute.

For starters the REXX interface for DB2 must make itself known to REXX. This happens in the first two statements in the program. Next thing for the program is to connect to the DB2 system that should execute the specified SQL statement. In the example the DB2 system is called MYDB. You can simply replace MYDB with the name of your DB2 or with a variable to make the program independent of the DB2 system. The last detail before your REXX is ready to execute some SQL is the SET CURRENT PACKAGESET. As far as I can see this statement is not necessary. It depends a bit on how your installation has chosen to implement the DSNREXX interface. You can do your own experiments regarding this statement.

Finally we are ready to execute a SQL statement. As mentioned before this REXX can execute almost all kind of dynamic SQL except SELECT statements. Here I have made a table called MYUSER.SYSTABLES with the same layout as SYSIBM.SYSTABLES to which I copy all rows having creator SYSIBM in SYSIBM.SYSTABLES. When you use it all you have to do is assign your SQL statement to SQLSTMT and execute it. Please remark the "almost" because there are a few exceptions which you can read about in Application programming and SQL Guide.

When the program has finished executing SQL statements it is a good idea to perform a COMMIT even though it is not required. Then the program makes a disconnect from DB2 and finally the relation between the program and the DSNREXX interface is removed. This way you make a nice cleanup. After the disconnect you can connect to another DB2 system and execute SQL on that system. This is a flexibility that is difficult to acheive in COBOL or PLI.