Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups 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: 1239
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: 1746
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: 1239
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: 1746
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: 7931
Location: Bellevue, IA

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: 1746
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: 1239
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 Storing huge volume of data, compare ... Pradeep K M All Other Mainframe Topics 3 Mon Jan 16, 2017 5:08 pm
No new posts how to recover an uncataloged VSAM da... archanamuthukrishnan All Other Mainframe Topics 3 Wed Jan 11, 2017 6:18 pm
No new posts HALDB data refresh/copy from producti... vineetanand2007 IMS DB/DC 0 Mon Jan 02, 2017 11:16 am
No new posts JES2 JEC: Use UNIX Pipes to Pass Data... Virendra Shambharkar JCL & VSAM 21 Tue Dec 20, 2016 6:55 pm
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us