Load data from production DB2 table to test table

Satheeshkumar · New User Joined: 20 Feb 2009 Posts: 51 Location: Chennai

My requirement is that i have to extract 2000000 records from prod table then load the datas into the test table.

I tried to extract & load by using file aid for DB2, but it is taking too much of time. can anyone tell me the another way to accomplish this in less time..

Terry Heinze · Posted: Tue Nov 10, 2009 10:40 pm

What is your definition of "too much"?

Satheeshkumar · New User Joined: 20 Feb 2009 Posts: 51 Location: Chennai

It is taking long time to extrat or load a data around 12 hours..

Akatsukami · Posted: Tue Nov 10, 2009 11:03 pm

For only 2,000,000 rows (not records) it takes around 12 hours? Either:

you are doing a considerable amount of additional processing that you are not mentioning
you are using a very inefficient unload process
you are working with a very poorly designed table
you are running on a 370/185.

Please give sufficient information to eliminate the first and second possibilities. I will accept your bare word that the fourth is not the case.

Satheeshkumar · New User Joined: 20 Feb 2009 Posts: 51 Location: Chennai

I have to extract and load the entire table. table contains 20 crore rows. Can you please tell me the effificient unload and load process

Terry Heinze · Posted: Tue Nov 10, 2009 11:20 pm

As Akatsukami mentioned, one or more of the 4 things listed is most likely the problem. 12 hours is entirely too long to unload 2 million rows. Please respond to Akatsukami's 3 points. Number 4 was "tongue in cheek" right?

Akatsukami · Posted: Tue Nov 10, 2009 11:55 pm

Robert Sample · Posted: Wed Nov 11, 2009 12:11 am

Although Ebay does have a 9672 available ... 9672 for sale

Akatsukami · Posted: Wed Nov 11, 2009 12:20 am

FTR, I unloaded a table with about 88,000,000 rows yesterday using BMC UNLOAD+. Wall time was 2:43.26.

dick scherrer · Posted: Wed Nov 11, 2009 12:26 am

Hello,

I unloaded 2.5 million rows in under 5 minutes (several times).

It may help if you post your unload jcl and control info.

Terry Heinze · Posted: Wed Nov 11, 2009 12:28 am

20 crore is 200 million, not 2 million. Please pay attention to number of zeros.

Ronald Burr · Posted: Wed Nov 11, 2009 3:19 am

Satheeshkumar's FIRST post stated 2000000 ( 2 million ) rows. The SECOND post said to extract OR load was taking 12 hours without mentioning the number of rows. The THIRD post said that the entire table had to be extracted/loaded and tat it ( the entire table ) contained 20 crore ( 200 million ) rows.

From the three posts, I surmise that either a) the 2000000 ( 2 million ) and 20 crore ( 200 million ) reference different tables, b) the large ( 20 crore ) table has 10 partitions, each of which contains 2 million rows, or c) one of the two figures was incorrectly stated.

In any case, I would ask the following:

Is this a pure unload/load or is the unload subject to selection criteria? If so, are all referenced predicate columns indexed or does the unload require a tablespace scan?