MVSFORUMS.com

ed.sam13 · Beginner Joined: 09 Aug 2010 Posts: 31 Topics: 11

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?

ed.sam13 · Beginner Joined: 09 Aug 2010 Posts: 31 Topics: 11

Seems like there is no other way around this...........

William Collins · Supermod Joined: 03 Jun 2012 Posts: 437 Topics: 0

How long does the process take, to unload, prepare, and reload?

ed.sam13 · Beginner Joined: 09 Aug 2010 Posts: 31 Topics: 11

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

kolusu · Posted: Thu Oct 22, 2015 11:44 am Post subject:

NASCAR9 · Posted: Fri Oct 23, 2015 9:49 am Post subject:

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