Results 1 to 13 of 13
  1. #1
    Join Date
    May 2012
    Posts
    4

    Unanswered: Triggers Parallel Processing

    Hi,

    We have two Db2 database in MVS which we would like to keep in sync.

    So what we are planning is having triggers set on the set of tables on one of the database and then when an insert,update or delete is made then the triggers would carry over the changes and make necessary updates on the other database.

    1) Is this feasible for large volume of data like 1000ds of inserts/updates made on different tables on DBA1 so that it can be carried forward to DBA2

    Can anyone suggest any better approaches?

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Have a look at the various replication tools if asynchronous synchronization is good enough. If not, you may want to revisit your design to avoid the data duplication/redundancy.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    More efficient than triggers would be to let all applications send their INSERT/UPDATE/DELETE stmts to both servers. Is that feasible?
    Alternatively, force all applicative write access through stored procedures which again guarantee identical statements sent to both servers.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  4. #4
    Join Date
    May 2012
    Posts
    4
    Thanks for your replies.

    We don't want to use replication tools like q-rep etc.... Is there any alternative way to do in mainframe ... Can you please explain more on some of the techniques which we can use?

  5. #5
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    * Dayly full unload at one side; load-replace at the other side
    * Triggers (as you suggested)
    * Row change timestamp at one side, which will allow for unload of only changed rows; hourly or dayly incremental inload, MERGE at the other side
    * ...
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Could you share a bit more background info why you are duplicating the data?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Apr 2012
    Posts
    7
    Can you share your thoughts regarding
    >>We don't want to use replication tools like q-rep etc.
    Because from the topic the replication should be obviously better decision then triggers

  8. #8
    Join Date
    May 2012
    Posts
    4
    We have one transactional database(comprising around 100 tables) and we would be creating a reporting database(comprising around 20 odd tables).

    Hence we would be storing some data which is really needed for reporting purposes just to share off the load on the transactional database.

    What my approach is that we can have triggers placed on all the tables to capture the changes and then simultaneously update the reporting database via stored procedure calls.

    Please let me know if it would work for huge volumne of data daily.

    Note: Both the db needs to be in synch as close(time) as possible.

    Please suggest any better approach apart from using qrep..since we dont have budget.

  9. #9
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    what about SQL Replication ? Not sure about cost implications on mainframe DB2 (On LUW no additional license required)

    This performs better than using triggers but data on the second server will be lagging by a few minutes.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  10. #10
    Join Date
    Apr 2012
    Posts
    7
    Triggers is obviously worse in reporting db scenario, because reporting is not usually to be done on the records that has just been processed, and SQL replication gives just a minute (even less) lag. Replication is safer not to lose some data modifications. Basically triggers is hidden logic and can cause some difficulties when altering tables. As for additional products - AFAIK db2 v8 for z/Series had in built replication engine, think the same for your version.

  11. #11
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Triggers can be a performance issue. For example, if a reporting application would use isolation level SERIALIZABLE (aka REPEATABLE READ), it may hold locks on the table, blocking the trigger in another transaction to do its insert. That will hold up your OLTP workload, which is sometimes considered as be a bit problematic.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  12. #12
    Join Date
    May 2012
    Posts
    4
    Okay .. Thanks for your inputs..

    So can we achieve this by reading the db2 log information of the transaction tables.. and then applying the changes onto the reporting database?

    Please let me know your inputs on the same..

  13. #13
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Reading the logs? That's exactly what most replication products do.
    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
  •