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:
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.