Hello All,

I need to replace in program (Java) whole DB2 table content. So, I have data in program like 10000 rows, and must replace existing table with new rows. The main problem is that the table is replicating to another DB.

The constraint is that maximum I'm allowed by Q-Rep person to delete-insert 2000 rows in a time, and each delete-insert must be commited, so replication overhead does not happen.

Suggested to me plan is:
1) delete 2000 rows, commit
2) ..... repeat
3) delete 2000 rows, commit
4) [table is empty]
5) insert 2000 rows, commit
6) .... repeat
7) insert 2000 rows, commit

My concern is ACID principle - what happen if program fails, or DB2 fails at step 3, or at step 5. I will have old data corrupted, and no new data inserted yet.

What can be solution to this problem? Using some utility table to mark if the process finished, so it's safe to use data in the table? Any ideas/suggestions? This is more programmatic problem, I am afraid that I can not much change infrastructure (replication). However, I can add some columns to the table if needed.

Thank you,
Archie

I don't know Q-Rep well enough, but a requirement like "max 2000 changes" strikes me as odd. It should be able to handle more changes.

Yes, I think it's not a problem to handle more than 2000, but this is what I was given as a constraint. So, I must do my best with the given restrictions. I understand that solution will have some limitations, compared to normal delete-inserts in a single transaction. What I need - minimize potential problems, for example, provide info that table was not replaced correctly so it's not safe to use it, etc..

Maybe you can use a view on the target system like this:
- replication works on base tables and in the target system, the base table is not directly queried
- when you start the replication cycle, you drop the view on the target system
- then you perform your scenario
- once everything is completed successfully, re-create the view

All application on the target system only work with the view. If the view is not present, all operations will fail due to the missing object.