MVSFORUMS.com :: View topic - DB2 Clone Table Question

through

#1: DB2 Clone Table Question Author: ed.sam13,

Posted: Sun Oct 18, 2015 10:47 pm
—
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?

#2: Author: ed.sam13,

Posted: Wed Oct 21, 2015 5:06 pm
—
Seems like there is no other way around this...........

#3: Author: William Collins,

Posted: Thu Oct 22, 2015 1:21 am
—
How long does the process take, to unload, prepare, and reload?

#4: Author: ed.sam13,

Posted: Thu Oct 22, 2015 11:37 am
—
It takes close to 8 hours end to end. All the unload/load and process files are on tape.

#5: Author: kolusu, Location: San Jose

Posted: Thu Oct 22, 2015 11:44 am
—

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.

#6: Author: NASCAR9, Location: California

Posted: Fri Oct 23, 2015 9:49 am
—
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.

MVSFORUMS.com -> Database

output generated using printer-friendly topic mod. All times are GMT - 5 Hours

Page 1 of 1