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.
I take it this multi member file was not a “partitioned table”? I suspect not, as a partitioned table requires DB2 Multisystem which can be purchased for around the price of a new Power 9.
Hi Mike, and thanks for sharing.
I’m running 7.2 versin. And it doesn’t work.
Is it a new feature for 7.3?
I’ve tried with an LF that only shows some columns of the table (all of the them, ar common to all PF files included in the LF).
And, as always, I get the common error: “The file xxxxxxx has more than one format”.
Hi. Sorry for the late response. The files and code I wrote for the article were done on a 7.2 system. The way I did this was to create a physical file and then added physical file members to it. Once I had that in place, I created a logical file over the physical and I was then able to access data from all members via the logical file. Are you able to try it that way on your system?
Let me know if that works for you. If not, perhaps I can email you and try to help out.