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.