DB2 physical file
how we can create View with Key to more than one PF in Diffrance Lib
exsample
LIBÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â PF File Name
=============================
Lib1                        Custom            (( for HeadOffice ))
Lib2                       Custom             (( for Branch 1 ))
Lib3                        Custom            (( for Branch 2 ))
the above 3 PF have same Fields and Keys
we want to create LF or View with key to based to all 3 PF
thanks
Hodish
Mail : hodish2007@yahoo.com or hodish@yemany.com
Software/Hardware used:
software
Software/Hardware used:
software
ASKED:
October 1, 2010 2:11 PM
UPDATED:
October 6, 2010 12:25 AM
Answer Wiki:
You create a JOIN logical file.
Here is an example joining for different files.
These happen to be in the same library.
You can qualify the library on the file name to get what you want.
<pre>A JDFTVAL
A R CHMWHSR JFILE(CHMBASDB +
A CHMMONDB +
A CHMHSTDB +
A CHMCDTDB)
A J JOIN(1 2)
A JFLD(ACCNO ACCTNB)
A J JOIN(1 3)
A JFLD(ACCNO ACCTNB)
A J JOIN(1 4)
A JFLD(ACCNO ACCTNO)
* FIELDS
A CLNNUM
A MSYS
A MPRN
A MAGT
A ACCNO
A ACCEX
A CAPCCD
A MAPR</pre>
To see all answers submitted to the Answer Wiki: View Answer History.
It wouldn’t seem that a JOIN would be appropriate, though it might be. This might be better suited for a UNION (multi-format) LF, especially if keyed access is expected for the three different files.
I’m not sure what “CUSTOM” means in the question. If that indicates a Customer file and customers may be different across the three groups, or perhaps especially if customer IDs might be duplicated for different customers across the three groups, then a JOIN is trouble.
Perhaps a little more definition of what the business problem is would help make sure. If there is no duplication of identifiers across the groups, it’s not clear why three libraries are involved, though good reasons can easily be imagined.
Tom
You’ve left us short of critical info, like what you’ve got and what you need.
But that never stopped me in the past.
I like Tom’s answer, if you want the records from all three to appear in order then it might look something like this.
Create view myview as
Select * from Lib1.custom
union
Select * from Lib2.custom
union
Select * from Lib3.custom
order by fld1, fld2
Phil
A SQL UNION isn’t quite the same as a multi-format DDS LF (which kind of mimics a SQL UNION). The ability to key the DDS LF has been around for a long time, but it’s not so easy to combine a SQL INDEX with a VIEW.
Tom
That’s true
If the multi-format logical is set to Maintenance Immediate it will access thedata faster than the view.
Doesn’t t the multi-format logical over multiple libraries creates certain backup-restore issues.
Phil
Doesn’t t the multi-format logical over multiple libraries creates certain backup-restore issues.
A PF needs to exist before a LF can reference it. In a restore scenario, all three PFs would need to exist before the LF could be validly restored. If the libraries all needed to be restored, the libraries with the PFs should be restored before the library with the LF is restored. That’s just as true for a “join” LF as for a “union (multi-format)” LF.
That can be tricky when restoring *ALLUSR libraries, for example, and the LF library comes before any of the others in sequence.
But if a key is needed over multiple PFs, there’s not a lot of alternatives. You gotta do what you gotta do.
Tom