Posted by: Craig Hatmaker
Getting data from IBM’s iSeries into XL is normally no different than other platforms. There are exceptions. The iSeries’ multi-member files, such as source files, are problematic for SQL. So are data areas. This post deals with simple Data Areas.
To read simple Data Areas from SQL we need a little help from IBM’s iSeries. We need a “stored procedure” which we must create, which means we must program on the iSeries. This post assumes you have access to your iSeries via a “green screen”. If you don’t know what I’m talking about, bale now because this won’t make any sense to you.
I’m going to use an example to help explain things. In this example imagine we have a data area containing a numeric ID. The steps to create this stored procedure are:
- Code CL source to pass parameters and retrive data area’s contents
- Create a CL Module from the code source
- Create a Service Program from the CL Module.
- Code an SQL Script to create a stored procdure using the Service Program
- Run the SQL script
The first step is to write the CL source. Let’s put it in member GetMyData in QGPL/QCLSRC. Here is the example:
PGM PARM(&ID) DCL VAR(&ID) TYPE(*DEC) LEN(10 0) RTVDTAARA DTAARA(MYDATA) RTNVAR(&ID) ENDPGM
The next step is to create the CL Module. At a command prompt type:
CRTCLMOD MODULE(QGPL/GetMyData) SRCFILE(QGPL/QCLSRC)
After the above executes succesfully, type this command:
CRTSRVPGM SRVPGM(QGPL/GetMyData) MODULE(QGPL/GetMyData) EXPORT(*ALL)
Next we write our SQL Script. Create source member GetMyData in QGPL/QDDSSRC:
Create Function GetMyData() Returns DECIMAL(10, 0) Language CL Specific GetMyData Deterministic No SQL Returns Null on Null Input No External Action Not Fenced External Name 'QGPL/GetMyData(GetMyData)' Parameter Style SQL;
Lastly, we run the SQL script from a command line:
RUNSQLSTM SRCFILE(QGPL/QDDSSRC) SRCMBR(GetMyData)
Now all that is left to do is test it. Using command STRSQL to start an SQL session, run this SQL statement:
Select GetMyData() From SYSIBM/SYSDUMMY1
SYSIBM/SYSDUMMY1 is a special one record file we can use for this sort of thing.
As you can see, there’s not a lot of work involved once you know how. And if you look carefully, you can see many possibilities for doing things far beyond reading simple data areas!
- IBM iSeries Redbooks: http://www.redbooks.ibm.com/portals/systemi
- A Fast Path to AS/400 Client/Server Using AS/400 OLE DB Support: http://publib-b.boulder.ibm.com/abstracts/sg245183.html?Open
- Stored Procedures, Triggers, and User-Defined Functions on DB2: http://www.redbooks.ibm.com/redbooks/pdfs/sg246503.pdf