Guru: Using SQL With Multi-Membered Files

October 29, 2018 Mike Larsen

Recently I worked on a process that required me to update records in a file based on certain criteria. Sounds like a common request, right? Well, there was a twist. The file had multiple members and the update needed to consider all of them. Suddenly, a “simple” request became a bit more challenging.

I knew of a few ways to work with multi-membered files, but I like to lay out all the options before deciding on a game plan. One method I considered was to perform an override of the file (OVRDBF command). That would certainly work, although I have to loop through all the members each time the program ran.

Another way I could work with the members is to use an SQL alias (Figure 1).

Figure 1. Using An Alias To Access A Member Of A File

Exec sql
     Create alias qtemp/SalesHist_January
       for SalesHist(January);                  // file name(member name)

   Exec sql
     Select count(*) into :numberOfItems
      from qtemp/SalesHist_January;

   // drop the alias when i'm done

   Exec sql
     Drop alias qtemp/SalesHist_January;

I created the alias in QTEMP and pointed it to the file and member. When I did that, I was able to access the records in the January member of the SalesHist file. I dropped the alias because I no longer needed it. If accessing the January member were something I had to do regularly, I would have kept it.

Using an alias allowed me to work directly with a single member. This method would also work for my task, but I’d still have to loop through all the members. Additional research showed that I could create an alias for each member and access all members with a union all operator. That seemed more like what I wanted to do since I really wanted to process all the members in one shot. However, the SQL statement could get pretty long if there was a large number of members. Using either of these methods also required me to know the names of the members and that caused additional work.

There was one other way I thought of to tackle this assignment. I used a logical file that was built over all members of the physical file. When I ran an SQL statement over the logical file I was able to access records from all members at one time. That was exactly what I wanted! I didn’t have to do any looping and I didn’t have to know the names of the members. A caveat to this approach is the logical file needs to be rebuilt as new members are added or you won’t be able to access the new members. I chose to go this route and the SQL statement was simple (Figure 2).

Figure 2. Updating Records In All Members

// I can use the logical file to update records in all members at one
// time.

   Exec sql
     Update SalesHist2
      Set HeOpen = substr(HeOpen,1,10) concat 'Y' concat substr(HeOpen,12,29);

Not only can you select and update records in a multi-membered file, you can also insert records into a certain member (Figure 3).

Figure 3. Using An Alias To Insert Records Into A Member

// To insert into a particular member, I can use an Sql alias.

   Exec sql
     Create alias qtemp/saleshist_mike
       for saleshist(mike);

   Exec sql
     Insert into qtemp/saleshist_mike
       values('RT', 1, 20, 18, 09, 15, '000050675305486',
              'open text N 000050675305486', '01', 122.22, 22.22);

   Exec sql
     Drop alias qtemp/saleshist_mike;   

This is very similar to the select statement I showed earlier except that I used the alias to insert a record into the Mike member of the SalesHist file.

I’ve given you several ways to work with membered files. Each of these methods will work just fine; it’s up to you to decide which is right for you.