I am getting a list of unique records in one hold file and I want to join them up to my master file. This is a one to many relationship. How can we join hold files up to the synonym to filter down the records? The only way I am aware of is if I do a claims dump into a hold file and join from there (not efficient). Isn't IN FILE with 200,000 records not efficient either?
What is the most efficient way to do this? The records in the hold file would be about 200,000 and then records in the Master File are over 5,000,000.
Here's an example of the table structure I have
HOLD FILE 1 1 2 3 4 5 6 7 8 9 10 11 12 Synonym 1 1 1 2 2 3 4 4 4 4 4 5 5 6 6 6 7 7 7 8 9 9 10This message has been edited. Last edited by: Brandon Andrathy,
Where is your data coming from ? RDBMS ?
Is the hold file from the same DB ?
You could try FORMAT SAME_DB, then just join them, if its all the same DB ?
There is always MATCH FILE. We aren't allowed to create temporary HOLD files in the database so all of ours are WebFOCUS hold files. Sometimes, when there is a lot of data in the HOLD file, MATCH FILE has better performance (runtime wise) than JOIN and TABLE. Thing is, if you look at what WebFOCUS is doing, a lot of times it converts JOIN and TABLE to MATCH FILE.
In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
The most efficient way to do this is to make sure both files are in the same DBMS. Otherwise, you might unwittingly cause a scan of the first table before joining it to the second table. If this is an on the fly join, try to push everything into the same DBMS using HOLD FORMAT xyzdbms INDEX joinfieldname
WebFOCUS 8206, Unix, Windows
Thank you very much for your responses!
Babak, I'm thinking your suggestion regarding index might be the way to go. Could you give me an example of what you're thinking with the CAR File code I have below?
TABLE FILE CAR BY COUNTRY WHERE COUNTRY EQ 'FRANCE' OR 'ENGLAND' ON TABLE HOLD AS 'CAR1' END TABLE FILE CAR SUM SALES BY COUNTRY ON TABLE HOLD AS 'CAR2' END JOIN COUNTRY IN CAR1 TO UNIQUE COUNTRY IN CAR2 TAG J0 AS J0 END
Waz, could you also give me an example of what you are thinking with: SAME_DB PERSISTENCE GLOBAL_TEMPORARY
I got the index created on my second hold file with this. But I'm not sure where to go from here.
Sounds like you are dealing with two relational tables.
Here is an example of creating a subquery to be used in a nested subquery SELECT:
You could also join to the table you created a 'SQL_SCRIPT' for.
David, DBINFILE creates exactly the type of SQL I want to see!
Wow, I have used DBINFILE but not with FORMAT SQL_SCRIPT. It's insane how DBINFILE works differently with SQL_SCRIPT.
Thanks a ton for this solution! My Data guy is gonna be happy I won't be causing him headaches with crazy queries lol.
My resulting query looks like something like the following for anyone this might help:
TABLE FILE CAR BY COUNTRY WHERE COUNTRY EQ 'FRANCE' OR 'ENGLAND' ON TABLE HOLD AS 'CAR1' FORMAT SQL_SCRIPT END TABLE FILE CAR SUM SALES BY COUNTRY WHERE DBINFILE(CAR1, COUNTRY, COUNTRY) END
Is there anything I might be able to do to speed up DBINFILE? Maybe make sure indexes are created within the database?
Assuming the data is all on the same DB, the first query (assuming it is coming direct from the DB, no intermediate hold files), you use FORMAT SAME_DB.
This will generate SQL to create a temp table and insert the records into it.
Then its a simple case of a join, how ever you like to do it.
TABLE FILE CAR BY COUNTRY WHERE COUNTRY EQ 'FRANCE' OR 'ENGLAND' ON TABLE HOLD AS 'CAR1' FORMAT SAME_DB END -RUN JOIN COUNTRY IN CAR1 TAG C1 TO UNIQUE COUNTRY IN CAR TAG C2 AS J0 END TABLE FILE CAR1 SUM C2.SALES BY C1.COUNTRY ON TABLE HOLD .... END -RUN
Thank you Waz. I appreciate your response. You've helped me a lot this week .
Closing this post.
|Powered by Social Strata|