IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Load data from production DB2 table to test table


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
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
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: 1248
Location: Richfield, MN, USA

PostPosted: Tue Nov 10, 2009 10:40 pm
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
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: 1787
Location: Bloomington, IL

PostPosted: Tue Nov 10, 2009 11:03 pm
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
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: 1248
Location: Richfield, MN, USA

PostPosted: Tue Nov 10, 2009 11:20 pm
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: 1787
Location: Bloomington, IL

PostPosted: Tue Nov 10, 2009 11:55 pm
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: 8700
Location: Dubuque, Iowa, USA

PostPosted: Wed Nov 11, 2009 12:11 am
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: 1787
Location: Bloomington, IL

PostPosted: Wed Nov 11, 2009 12:20 am
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

Moderator Emeritus


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

PostPosted: Wed Nov 11, 2009 12:26 am
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: 1248
Location: Richfield, MN, USA

PostPosted: Wed Nov 11, 2009 12:28 am
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
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts db2 vs static data COBOL Programming 1
No new posts External data queue (changes?) CLIST & REXX 0
No new posts Pull data using date difference betwe... DB2 6
No new posts fast data scrambling PL/I & Assembler 10
No new posts Unload and Load ISPF Table TSO/ISPF 4
Search our Forums:

Back to Top