• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • 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:

    • SQL cannot access all data in all members (without some clumsy trickery using aliases which I will demonstrate below). You cannot issue an OVRDBF MBR(*ALL) statement and then query the file with SQL. Nor can you hope to create a logical file that accesses multiple members and then query the logical file using SQL.
    • Because it doesn’t like multi-member files, DB2 for i SQL does not contain a built-in function to indicate what member a table row resides in.

    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:

    • JAN15
    • FEB15
    • MAR15
    • APR15
    • MAY15
    • JUN15

    This multi-member file contains three fields:

    Column

    Definition

    TRANDATE

    DATE

    ORDERNO

    INT

    AMOUNT

    DEC(13,4)

    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:

    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.

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    WorksRight Software

    Do you need area code information?
    Do you need ZIP Code information?
    Do you need ZIP+4 information?
    Do you need city name information?
    Do you need county information?
    Do you need a nearest dealer locator system?

    We can HELP! We have affordable AS/400 software and data to do all of the above. Whether you need a simple city name retrieval system or a sophisticated CASS postal coding system, we have it for you!

    The ZIP/CITY system is based on 5-digit ZIP Codes. You can retrieve city names, state names, county names, area codes, time zones, latitude, longitude, and more just by knowing the ZIP Code. We supply information on all the latest area code changes. A nearest dealer locator function is also included. ZIP/CITY includes software, data, monthly updates, and unlimited support. The cost is $495 per year.

    PER/ZIP4 is a sophisticated CASS certified postal coding system for assigning ZIP Codes, ZIP+4, carrier route, and delivery point codes. PER/ZIP4 also provides county names and FIPS codes. PER/ZIP4 can be used interactively, in batch, and with callable programs. PER/ZIP4 includes software, data, monthly updates, and unlimited support. The cost is $3,900 for the first year, and $1,950 for renewal.

    Just call us and we’ll arrange for 30 days FREE use of either ZIP/CITY or PER/ZIP4.

    WorksRight Software, Inc.
    Phone: 601-856-8337
    Fax: 601-856-9432
    Email: software@worksright.com
    Website: www.worksright.com

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    COMMON:  2016 Annual Meeting & Expo, May 15 - 18, in New Orleans! Great Power Systems event!
    System i Developer:  RPG & DB2 Summit - March 22-24 in Dallas. Check out the session grid!
    Four Hundred Monitor Calendar:  Latest info on national conferences, local events, & Webinars

    Where In The World Is IBM i? A Possibly Coherent Future Power Hybrid System

    2 thoughts on “Retrieving Data From All Members With SQL”

    • Renjith says:
      August 9, 2017 at 2:29 pm

      How about creating a logical file and using that ? That is, if a logical file is created against a Physical file (with 4 or 5 members) , would logical file have records from all the members of Physical file ?

      Reply
    • Mukesh Shah says:
      October 3, 2018 at 12:16 pm

      How about creating it all in a CL-Program? Use DSPMBRD to an Outfile and reading each member’s information and creating the Aliases on-the-fly based on each member name, and doing an INSERT of data for each alias (Member) into a separate table.

      This will eliminate the issue of using UNION ALL, coding an RPG program, external stored procedure; as INSERT will combine data from all the members into the new table and will be available for normal database operations instead of being Read-only variety.

      And if the member names change or new members are added, you’re covering all the bases.

      My 2-cents.

      Reply

    Leave a Reply Cancel reply

Volume 16, Number 05 -- March 1, 2016
THIS ISSUE SPONSORED BY:

WorksRight Software
Rocket Software
Northeast User Groups Conference

Table of Contents

  • Retrieving Data From All Members With SQL
  • Easy Printing From CL
  • The Three Sources Of RUNSQLSTM

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • Unattended IBM i Operations Continue Upward Climb
  • VS Code Is The Full Stack IDE For IBM i
  • Domino Runs on IBM i 7.5, But HCL Still Working on Power10
  • Four Hundred Monitor, March 6
  • IBM i PTF Guide, Volume 25, Number 11
  • You Ought To Be Committed
  • Thoroughly Modern: What You Need to Know About IBM i Security
  • Spring Brings Events To IBM i Community, And More
  • As I See It: AI-AI-O
  • IBM i PTF Guide, Volume 25, Number 10

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2023 IT Jungle