Retrieving Data From All Members With SQL
March 1, 2016 Michael Sansoterra
Note: The code accompanying this article is available for download here.
Everyone knows that DB2 for i SQL doesn’t play well with legacy multi-member database files. The CREATE ALIAS statement allows SQL to access a particular member of a file. But what if you want to access all members in a file using SQL as though they were a single member? A reader recently inquired about this topic and further wanted to know if there was a built-in SQL function to return each row’s member name.
NOTE: For clarification, I’m not discussing members of a DB2 partitioned table.
Here is my take on both questions:
Even so, if you are set on using SQL for this task, there are two “low-budget” ways around these problems (and for both of them you’ll have to plug your nose and swallow). To illustrate, say you have a legacy file named MyMbrDta that contains the following six members:
This multi-member file contains three fields:
Technique #1: Create and Combine Aliases
To query all six members as though they were one, start by creating an alias for each member in the database file:
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);
Once the aliases are created, each member’s data can be “combined” using the UNION ALL operator such that all member data is extracted from a single query.
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
Each member name is hard-coded as column MBRNAME. For kicks, the relative record number within the member is also returned. This type of query would normally be used in a nested table expression or common table expression.
Partial results of the member extracted at a member boundary look something like this as the query seamlessly combines data from all members:
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.