MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

DB2 Clone Table Question

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database
View previous topic :: View next topic  
Author Message
ed.sam13
Beginner


Joined: 09 Aug 2010
Posts: 31
Topics: 11

PostPosted: Sun Oct 18, 2015 10:47 pm    Post subject: DB2 Clone Table Question Reply with quote

We have a DB2 table on z/OS with 100M rows. There is a Java spring batch process which gets a file from another system processes these updates and update/inserts into this table.

But once in a year there will be a 50M update file for this table. It doesnt make sense to have an application program to do 50M updates. So we created a process on the Mainframe which will do the following.
1) Unload all rows from the table.
2) Do a normal Cobol program to do file matching between the input file with 50M rows and the unload file, apply all update/inserts and create a load file with 100M rows.
3) Do Load/Replace of the full table.

It was quite effective but the drawback is that this table can be updated thru real time also. So during the time between Steps 1 and 3 above it loses all the updates. To solve this problem, we are planning the following approach.

1) Setup a Clone for this DB2 table.
2) Unload from the base table. Also capture the timestamp when the unload was taken into a file.
3) Have a Cobol program to do file matching between the input file with 50M rows and the 100M rows from the unload file and create a load file.
4) Run LOAD/REPLACE on the Clone.
5) Open a Cursor on the base table with Repeatable Read Isolation level to extract all rows which were updated since the timestamp from Step 2.
6) Insert/Update these into the Clone table.
7) Do EXCHANGE DATA BETWEEN Clone and Base.

But the question is For Step 6, because there can be no indices on the clone table will the updates take longer? How do I get around this. Is there another option to solve this problem?
Back to top
View user's profile Send private message
ed.sam13
Beginner


Joined: 09 Aug 2010
Posts: 31
Topics: 11

PostPosted: Wed Oct 21, 2015 5:06 pm    Post subject: Reply with quote

Seems like there is no other way around this...........
Back to top
View user's profile Send private message
William Collins
Supermod


Joined: 03 Jun 2012
Posts: 437
Topics: 0

PostPosted: Thu Oct 22, 2015 1:21 am    Post subject: Reply with quote

How long does the process take, to unload, prepare, and reload?
Back to top
View user's profile Send private message
ed.sam13
Beginner


Joined: 09 Aug 2010
Posts: 31
Topics: 11

PostPosted: Thu Oct 22, 2015 11:37 am    Post subject: Reply with quote

It takes close to 8 hours end to end. All the unload/load and process files are on tape.
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 11813
Topics: 74
Location: San Jose

PostPosted: Thu Oct 22, 2015 11:44 am    Post subject: Reply with quote

ed.sam13 wrote:
It takes close to 8 hours end to end. All the unload/load and process files are on tape.


ed.sam13,

How long does the LOAD alone takes to complete?

Ideally I would direct the real time updates during the load to a temporary table/file while the load is being done and then perform the updates after the load is completed.
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
Back to top
View user's profile Send private message Send e-mail Visit poster's website
NASCAR9
Intermediate


Joined: 08 Oct 2004
Posts: 267
Topics: 50
Location: California

PostPosted: Fri Oct 23, 2015 9:49 am    Post subject: Reply with quote

In the past we loaded hundreds of millions of rows quite often. It takes quite a while and DB2 wants to use as much of the machine as it can get its hands on. That being said, we would run our loads during off hours.
_________________
Thanks,
NASCAR9
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group