Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2010
    Posts
    7

    Question Unanswered: Reliable table content replace by inserting data in multiple transactions

    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

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Apr 2010
    Posts
    7
    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..

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •