Combining Records from Multiple Files/Multiple libraries

[Guest.Visitor]

Familiar w/SQL? Just STRSQL and run Selects w/UNION's to bring it all together...The SELECT's pull it all together for you. You just need to wrap this selection in a stmt like: CREATE GLOBAL TEMPORARY TABLE table-name AS ( SELECT A.* FROM ALIB/ORDRMST A UNION ALL SELECT B.* FROM BLIB/ORDRMST B UNION ALL SELECT C.* FROM CLIB/ORDRMST C) WITH DATA WITH REPLACE This will create a table in your Qtemp library containing all the records from all the ORDRMST's in the SELECT stmts...Hope this helps.

[Guest.Visitor]

That's a DECLARE GLOBAL TEMPORARY TABLE... DECLARE GLOBAL TEMPORARY TABLE table-name AS ( SELECT A.* FROM ALIB/ORDRMST A UNION ALL SELECT B.* FROM BLIB/ORDRMST B UNION ALL SELECT C.* FROM CLIB/ORDRMST C) WITH DATA WITH REPLACE

[Guest.Visitor]

And a little closer to what you want to do if I may be so assumptive. You need to bring all the "facility" library data-files into a single or "global" duplicate of the files ie. ORDRMST, etc. for an "ownership's" visibility of his/her/their's vast domain...Let ma also assume you'll want this done after peek hours when you're normally asleep. So you duplicate each file (one copy) into your "global" library, assuming they are all the same format. Let's say however that you create a table/file with a 1A field preceding the otherwise duplicated file/fields. Because, In this example let's say you need a way in the resulting file to see what "library" the record actually came from. (Say you don't have a way to tell in the data and there might be duplicate "order" numbers or something like that. You will want to "schedule" a job to be submitted at your discretion that will simply run the RUNSQLSTM command. You will specify the SQL script member below with multiple SQL stmts separated by semicolons. Hope this is more applicable. Good Luck

---

Code

[austino@aol.com]

If so, and your record formats are all the same, something like the following may suit your needs.

---

Code

[Skalizar]

It's been a long time since I've done this, but if I recall, this doesn't merge all the files, it just reads them in order. So you'll get all the records from the first file in keyed order, then all the records from the second file, etc. You can retrieve which file is being read from the file INFDS.

[austino@aol.com]

"doesn't merge all the files, it just reads them in order" The records will be processed as though they were coming from one combined file. The order they are processed depends entirely on how the keys are defined for the LF. For example, if defined as: K CUSTOMER K ITEM K ORDERDATE Then reading this keyed-logical file will give you the records in the following sequence: ORDERDATE within ITEM within CUSTOMER Remember: The RPG compiler will not allow this type of file due to the fact that it has duplicate record formats. When you compile a pgm with one, OVRDBF to a file that you use only for compiling, one that has the DDS using only one PFILE: PFILE(LIBRARYA/ORDFILE) The pgm will run fine, you just need to "fake out" the compiler. A few other things to remember: - Queries/SQL can be run against this file - UPDDTA (DFU) cannot - Select/Omit may be specified, if desired, in the DDS

[mahasuresh]

Hi All I have a very unique question. Our Data Base consists of Files which reside in multiple libraries. For example ORDRMST existing in several libraries. These libraries are unique for each facility. We want to create a library that houses the combined records of these files from various libraries. Is there any way of accomplishing this, without having to write programs that copy the records from these various libraries. Please let me know. Thanks for all your help - Suresh

[Guest.Visitor]

While you're deciding which one of the above mentioned ways will work best for you...Might as well throw this "approach" in there too. A well written trigger program attached to each of your "facility" library physicals could maintain the "global" or combined data-files where your global data would always be "current". A little more effort up front but when you're done with it...You're done worrying about whether the "job" that does the populating got submitted, finished, blew-up, or whatever every night. Performance wise, your choice may vary. With every comfort, there is a cost.