I have this requirement in which i have to subset various tables in my database. We have a working program right now but it takes for ever to run through all the tables that we use. I am considering an overhaul and am down to 2 approaches. Can some one advise which one would be fast, efficient and take up the least resources.
--> I have a source schema (SOURCEFILE) from where i would retrieve the data. This schema is a full DB.
--> I have a target schema (TARGETFILE) which would be empty replica of the source schema.
--> I have a list file (LISTFILE) which has a list of unique ids that I am going to use to subset.
--> The biggest table that we have in our schema is close to 200 million.
--> The number of unique ids in the LISTFILE can vary from a few thousand records to 10-15% of the actual production size.
1) I am going to create a LF using the JFILE option to connect the SOURCEFILE and LISTFILE. Once the LF is created use a CPYF to copy the data from the LF to TARGETFILE.
2) Use the following INSERT statement to INSERT data into the TARGETFILE:
INSERT INTO TARGETFILE (SELECT * FROM SOURCEFILE WHERE UNIQUEID IN (SELECT UNIQUE ID FROM LISTFILE))
Thanks for advising.