As promised in my previous tip this tip will be about how to use IBM's own REXX interface for DB2 to execute SELECT SQL statements. It is a bit more complicated especially compared to other DB2/REXX interfaces I have tried during the last many years.

Let me get straight to it:

/* 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 SUBSTR(CREATOR, 1, 8), SUBSTR(NAME, 1, 18)",
        "FROM SYSIBM.SYSTABLES",
        "WHERE CREATOR = 'SYSIBM'",
        "  AND TYPE = 'T'"
CALL SQLSELECT

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

SQLSELECT:
  ROWS = 0
  CALL SQLEXEC 0, "EXECSQL DECLARE C1 CURSOR FOR S1"
  CALL SQLEXEC 0, "EXECSQL PREPARE S1 INTO :OUTSQLDA FROM :SQLSTMT"
  CALL SQLEXEC 0, "EXECSQL OPEN C1"
  CALL SQLEXEC 100, "EXECSQL FETCH C1 USING DESCRIPTOR :OUTSQLDA"
  DO WHILE(SQLCODE = 0)
    ROW = ''
    SEP = ''
    DO COLNO = 1 TO OUTSQLDA.SQLD
      ROW = ROW''SEP''OUTSQLDA.COLNO.SQLDATA
      SEP = ' '
    END
    SAY ROW
    CALL SQLEXEC 100, "EXECSQL FETCH C1 USING DESCRIPTOR :OUTSQLDA"
  END
  CALL SQLEXEC 0, "EXECSQL CLOSE C1"
RETURN

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

The quick reader will immediately notice that the above program looks like the one from the previous tip apart from an extra procedure. This procedure handles the execution of a SELECT statement. And furthermore is the only statement that took care of executing the non-SELECT SQL replaced with a call to the new procedure. The even quicker reader will also notice that the two last lines before RETURN in the SQLEXEC procedure has disappeared. They are removed because it would be quite silly to output a lot of FETCH statements as long as they work as expected.

The first thing that happens in the SQLSELECT procedure is a DECLARE which is redundant, but all examples in various IBM manuals contains it. The important point is to use the name C1 for the cursor and S1 for the statement. If you need to have two cursors open at the same time then use C2 and S2 and so forth. After the redundant DECLARE a so-called PREPARE of the SQL statement is carried out. This is the statement that determines the access path for your SELECT statement. In fact it is a mini-version of a BIND of the SELECT. In return the REXX receives a variable which I have decided to call OUTSQLDA. This variable is in fact a STEM variable with all the information needed to communicate the result of your SELECT statement from DB2 to your REXX.

The rest of the program looks like any other PLI or COBOL program that processes a SQL cursor. The big difference lies in the logic used to extract data from the STEM variable which is assigned the contents of a row for each FETCH. In the STEM variable there is a variable for each column in the row. The contents of the first column is available in OUTSQLDA.1.SQLDATA, the next in OUTSQLDA.2.SQLDATA and so forth. Please note my use of a SUBSTR for each of the columns that I retrieve. I do it to make the two columns align nicely beneath each other when the rows are put out. In other words you must format the data yourself if you want to align them as in this example. Fortunately you do not need to take care of data conversion. The interface does that for you.

Besides the information about the contents of the selected columns for each row the variable OUTSQLDA contains other information, just so you know. If you have tried to code dynamic SQL in PLI or COBOL then you may have noticed that there is almost no difference compared to using the DSNREXX interface. IBM has not tried to hide any of the technical details which many of the other REXX interfaces traditionally does. I think it is a pity IBM has not followed this tradition, because it makes their interface hard to use and not very REXX-like.