Retrieving Data From All Members With SQL

SET SCHEMA DEV;
CREATE OR REPLACE ALIAS MYMBRDTA_JAN15 FOR MYMBRDTA(JAN15);
CREATE OR REPLACE ALIAS MYMBRDTA_FEB15 FOR MYMBRDTA(FEB15);
CREATE OR REPLACE ALIAS MYMBRDTA_MAR15 FOR MYMBRDTA(MAR15);
CREATE OR REPLACE ALIAS MYMBRDTA_APR15 FOR MYMBRDTA(APR15);
CREATE OR REPLACE ALIAS MYMBRDTA_MAY15 FOR MYMBRDTA(MAY15);
CREATE OR REPLACE ALIAS MYMBRDTA_JUN15 FOR MYMBRDTA(JUN15);

SELECT d.*,'JAN15' MBRNAME, RRN(D) AS MBR_RRN
  FROM MYMBRDTA_JAN15 d
UNION ALL
SELECT d.*,'FEB15' MBRNAME, RRN(D) AS MBR_RRN
  FROM MYMBRDTA_FEB15 d
UNION ALL
SELECT d.*,'MAR15' MBRNAME, RRN(D) AS MBR_RRN
  FROM MYMBRDTA_MAR15 d
UNION ALL
SELECT d.*,'APR15' MBRNAME, RRN(D) AS MBR_RRN
  FROM MYMBRDTA_APR15 d
UNION ALL
SELECT d.*,'MAY15' MBRNAME, RRN(D) AS MBR_RRN
  FROM MYMBRDTA_MAY15 d
UNION ALL
SELECT d.*,'JUN15' MBRNAME, RRN(D) AS MBR_RRN
  FROM MYMBRDTA_JUN15 d

TRANDATE	ORDERNO	AMOUNT	MBRNAME	MBR_RRN
1/31/2015	45263	699.0982	JAN15	226
1/31/2015	45264	3374.99	JAN15	227
1/31/2015	45265	3374.99	JAN15	228
2/1/2015	45266	24509.8281	FEB15	1
2/1/2015	45267	3463.2998	FEB15	2

I tried to incorporate the above query in a view definition but SQL rejected it, apparently because an alias in the query refers to a file member:

SQL State: 55042
Vendor Code: -7030
Message: [SQL7030] Alias MYMBR00007 for table MYMBRDTA in DEV not valid for statement.
Cause . . . . . : The SQL statement cannot be performed on alias MYMBR00007 because
the alias refers to a member of table MYMBRDTA in schema DEV.

A drawback of this technique is that the data is read-only. Further, if the member names or number of members change frequently, then the aliases and parent query will need to be revised as well. Of course, re-creating the aliases can be automated, but it’s just one more infrastructure headache that a future developer may hate you for implementing.

Technique #2: RPG Table Function

Unlike SQL, when RPG reads rows in a file, it can retrieve the row’s associated member name dynamically from the file information data structure. Moreover, RPG can read data from all members when EXTMBR(‘*ALL’) is specified on the F-spec. Therefore, a second way to get member information within SQL is to create an RPG external table function that reads all of the rows on behalf of DB2, including each row’s member name (without resorting to hard-codes).

When reading sample file MyMbrDta in RPG, the F-spec and D-spec (for the file information data structure) are shown here:

FMyMbrDta  IF   E             DISK    INFDS(INFDS) EXTMBR('*ALL')

DdsMbrDta         DS                  LikeRec(MyMbrDtaR)
D MEMBER                129    138   
D DB_RRN                397    400I 0

The file information data structure is used to return two values about each row that is read: member name and relative record number. The complete source code for RPG service program MYMBRDTAR can be found here.

The DB2 function definition is as follows:

CREATE OR REPLACE FUNCTION DEV.MYMBRDTA()
RETURNS TABLE (
TRANSATE DATE,
ORDERNO  INT,
AMOUNT   DEC(13,4),
MBRNAME  CHAR(10),
MBR_RRN  INT
) 
EXTERNAL NAME 'DEV/MYMBRDTAR(READMBRINFO)'
LANGUAGE RPGLE
PARAMETER STYLE DB2SQL
DISALLOW PARALLEL
NOT FENCED
NO EXTERNAL ACTION
NO SQL

Invoking the RPG table function is as easy as:

SELECT *
  FROM TABLE(DEV.MYMBRDTA()) D;

The results match the sample results shown above in the SELECT/UNION ALL example. The drawbacks with this RPG approach are that the data is read-only (unless you want to add a VIEW definition and an INSTEAD OF trigger) and that the service program is an extra layer of overhead for SQL to process. (For example, filtering of member data would be done by SQL after the RPG program already processed it.) However, one advantage of this method is that no code changes are necessary if the members change.

To recap, working with members in SQL is clumsy, but doable. The overall advantage of using these techniques is that once you harness all of the member data, you can benefit from SQL’s easy-to-use features such as joins, sorting, etc.

Michael Sansoterra is a DBA for Broadway Systems in Grand Rapids, Michigan. Send your questions or comments for Mike via the IT Jungle Contact page.