Embedded SQL over multiple physical file members
Hi,
I'm writing a program to process all the members in a physical file. I've done this using embedded SQL because the selection criteria is quite complex. The physical file belongs to an application which I can't change, so I will always have to deal with multiple members. I also don't have the possibility to build a logical file over the physical.
My problem is how can I process all the members as if they were all contained in one member? If I try using OVRDBF, then the embedded SQL doesn't work. To create an ALIAS for each member is a bit over the top, and I'd have to remove all the ALIAS's once my program is finished. The file is a bit on the large side to try copying everything into another file with one member (and it may have duplicate keys).
Anybody got any other ideas or suggestions?
Thanks in advance,
Martin Gilbert.
Software/Hardware used:
Software/Hardware used:
ASKED:
January 5, 2009 12:56 PM
UPDATED:
February 5, 2009 4:41 PM
Answer Wiki:
Hi Martin,
Unfortunately I dont think this is possible without creating the alias for each member, a bit over the top as you say but the only way I think you can do it. It is similar to what I was trying to do with my recent post and I ended creating a source member with all the create alias statements then runnning it and processing the data that way, by using a temp lib I just deleted it when finished.
Hope this is of some use
Mark
To see all answers submitted to the Answer Wiki: View Answer History.
Without a logical file or a cpyf, I can’t see a way to resolve your need in SQL.
As you say you could create an alias for each member
You would then need to create a union of all alias
As beautiful as SQL is, it’s probably not the right tool for this situation.
Hi,
I’ve used a CPYF to QTEMP for the time being – gets me the result I want, but not really what I had in mind.
If anyone comes up with a better solution for this I’d be glad to hear it.
Thanks,
Martin Gilbert.
Hi
We do a similar thing for implementing mutli-thread processing. Here’s how:
- the first step is to select the qualified into records (using embedded SQL) and write the selected records to a multi-member physical file
- in a subsequent process, use OVRDBF to overwrite to each individual member. Then this same process can be run in multiple copies in parallel, each processing a different member in the PF.
Hope this sheds some light.
Fred
I’m curious why you can’t create a logical file over the physical that includes all members? That seems like a straightforward way of doing this, with less complexity.
I just had a brainstorm. If not being able to change the application which uses the physical file is the problem with creating a logical, how about this:
- Rename the physical file
- Create a logical file over all members of the physical, with LVLCHK(*NO), if necessary
- Name the logical the original name of the physical file
Then, your application program wouldn’t know the difference, theoretically.
I did that back in 1996. You have to creat a logical file. Gili
Hi,
Unfortunately, I don’t have the option of creating a logical…
Regards,
Martin Gilbert.
Why can’t you create a logical? Just wondering…
Hi,
I can’t *safely* create a logical, because I’m trying to access some IBM system files and I can’t guarantee that the system won’t be trying to do something with the files at the same time as I’m trying to use them.
I’m trying to retrieve certain data from the performance collection files for my own analysis.
Regards,
Martin Gilbert.