troubleshooting Question

CPYF vs Bulk INSERT

Avatar of bhagatali
bhagataliFlag for United States of America asked on
IBM System iCOBOL
5 Comments1 Solution2326 ViewsLast Modified:
Hi,

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.

Setup:
-------

--> 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.

Approaches:
----------------
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.

Regards
Ali.
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 5 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros