Embedded SQL over multiple physical file members

23730 pts.
Tags:
Embedded SQL
iSeries
OVRDBF
Physical File
RPG
SQL
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.

Answer Wiki

Thanks. We'll let you know when a new response is added.

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

Discuss This Question: 9  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • philpl1jb
    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.
    49,850 pointsBadges:
    report
  • Gilly400
    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.
    23,730 pointsBadges:
    report
  • Chandlerbing818
    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
    40 pointsBadges:
    report
  • Cwc
    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.
    4,290 pointsBadges:
    report
  • Cwc
    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.
    4,290 pointsBadges:
    report
  • Gili400
    I did that back in 1996. You have to creat a logical file. Gili
    40 pointsBadges:
    report
  • Gilly400
    Hi, Unfortunately, I don't have the option of creating a logical... Regards, Martin Gilbert.
    23,730 pointsBadges:
    report
  • Cwc
    Why can't you create a logical? Just wondering...
    4,290 pointsBadges:
    report
  • Gilly400
    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.
    23,730 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following