Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Load data from production DB2 table to test table

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Satheeshkumar

New User


Joined: 20 Feb 2009
Posts: 51
Location: Chennai

PostPosted: Tue Nov 10, 2009 10:39 pm    Post subject: Load data from production DB2 table to test table
Reply with quote

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
View user's profile Send private message

Terry Heinze

JCL Moderator


Joined: 14 Jul 2008
Posts: 1249
Location: Richfield, MN, USA

PostPosted: Tue Nov 10, 2009 10:40 pm    Post subject:
Reply with quote

What is your definition of "too much"?
Back to top
View user's profile Send private message
Satheeshkumar

New User


Joined: 20 Feb 2009
Posts: 51
Location: Chennai

PostPosted: Tue Nov 10, 2009 10:56 pm    Post subject:
Reply with quote

It is taking long time to extrat or load a data around 12 hours..
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1775
Location: Bloomington, IL

PostPosted: Tue Nov 10, 2009 11:03 pm    Post subject:
Reply with quote

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

  1. you are doing a considerable amount of additional processing that you are not mentioning
  2. you are using a very inefficient unload process
  3. you are working with a very poorly designed table
  4. 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
View user's profile Send private message
Satheeshkumar

New User


Joined: 20 Feb 2009
Posts: 51
Location: Chennai

PostPosted: Tue Nov 10, 2009 11:14 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Terry Heinze

JCL Moderator


Joined: 14 Jul 2008
Posts: 1249
Location: Richfield, MN, USA

PostPosted: Tue Nov 10, 2009 11:20 pm    Post subject:
Reply with quote

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? icon_smile.gif
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1775
Location: Bloomington, IL

PostPosted: Tue Nov 10, 2009 11:55 pm    Post subject:
Reply with quote

Terry Heinze wrote:
Number 4 was "tongue in cheek" right? icon_smile.gif

I'd bet against it, I admit icon_biggrin.gif
Back to top
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 8154
Location: East Dubuque, Illinois, USA

PostPosted: Wed Nov 11, 2009 12:11 am    Post subject:
Reply with quote

Although Ebay does have a 9672 available ... 9672 for sale
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1775
Location: Bloomington, IL

PostPosted: Wed Nov 11, 2009 12:20 am    Post subject:
Reply with quote

FTR, I unloaded a table with about 88,000,000 rows yesterday using BMC UNLOAD+. Wall time was 2:43.26.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Nov 11, 2009 12:26 am    Post subject:
Reply with quote

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
View user's profile Send private message
Terry Heinze

JCL Moderator


Joined: 14 Jul 2008
Posts: 1249
Location: Richfield, MN, USA

PostPosted: Wed Nov 11, 2009 12:28 am    Post subject:
Reply with quote

20 crore is 200 million, not 2 million. Please pay attention to number of zeros.
Back to top
View user's profile Send private message
Ronald Burr

Active User


Joined: 22 Oct 2009
Posts: 293
Location: U.S.A.

PostPosted: Wed Nov 11, 2009 3:19 am    Post subject:
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Insert a Row_number into table useit DB2 2 Tue Sep 19, 2017 1:07 pm
No new posts how to see when the last read access ... Mike 1304 DB2 1 Tue Sep 12, 2017 7:52 pm
No new posts PC (UTF-8) -> z/OS (EBCDIC) -> ... prino All Other Mainframe Topics 4 Fri Sep 01, 2017 1:47 am
No new posts Question related to Data dictionary f... rexx77 SYNCSORT 3 Thu Aug 31, 2017 7:23 am
No new posts Extract Data till prev hour balaji81_k DB2 16 Fri Aug 25, 2017 9:25 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us