View previous topic :: View next topic
|
Author |
Message |
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.. |
|
Back to top |
|
|
Terry Heinze
JCL Moderator
Joined: 14 Jul 2008 Posts: 1248 Location: Richfield, MN, USA
|
|
|
|
What is your definition of "too much"? |
|
Back to top |
|
|
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.. |
|
Back to top |
|
|
Akatsukami
Global Moderator
Joined: 03 Oct 2009 Posts: 1787 Location: Bloomington, IL
|
|
|
|
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. |
|
Back to top |
|
|
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 |
|
Back to top |
|
|
Terry Heinze
JCL Moderator
Joined: 14 Jul 2008 Posts: 1248 Location: Richfield, MN, USA
|
|
|
|
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? |
|
Back to top |
|
|
Akatsukami
Global Moderator
Joined: 03 Oct 2009 Posts: 1787 Location: Bloomington, IL
|
|
|
|
Terry Heinze wrote: |
Number 4 was "tongue in cheek" right? |
I'd bet against it, I admit |
|
Back to top |
|
|
Robert Sample
Global Moderator
Joined: 06 Jun 2008 Posts: 8700 Location: Dubuque, Iowa, USA
|
|
Back to top |
|
|
Akatsukami
Global Moderator
Joined: 03 Oct 2009 Posts: 1787 Location: Bloomington, IL
|
|
|
|
FTR, I unloaded a table with about 88,000,000 rows yesterday using BMC UNLOAD+. Wall time was 2:43.26. |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19243 Location: Inside the Matrix
|
|
|
|
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. |
|
Back to top |
|
|
Terry Heinze
JCL Moderator
Joined: 14 Jul 2008 Posts: 1248 Location: Richfield, MN, USA
|
|
|
|
20 crore is 200 million, not 2 million. Please pay attention to number of zeros. |
|
Back to top |
|
|
Ronald Burr
Active User
Joined: 22 Oct 2009 Posts: 293 Location: U.S.A.
|
|
|
|
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? |
|
Back to top |
|
|
|