Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2010
    Posts
    94

    Unanswered: DB2 trigger performance cost on OLTP

    Hello Gurus,

    I am working on an ETL solution that is to pull changed data from an OLTP environment hosted out of DB2 LUW. There is no CDC solution running - so I was considering defining a simple trigger on the specific tables, I am pulling in for reporting, to capture the primary key of the 'changed' record (I/U/D) in a seperate table.

    Given the simplicity of the trigger, do you think it will significantly slow OLTP operations?? I will of course work with DB staff to assess the impact in a test environment, but wanted to seek your insights before even recommending - to see if there was something too bad about this alternative to CDC.

    When you have a chance! Thanks as usual!!!!

    - G

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    "...significantly slow OLTP operations?"

    Probably not significantly. but that is too broad of a question to answer since we don't know anything about your environment. It certainly might work fine for you, but I would run some tests to determine whether the impact is acceptable.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by getback0 View Post
    do you think it will significantly slow OLTP operations??
    That, of course, depends on what exactly your trigger will do and how your OLTP environment is constrained. The things you may want to look at:
    - peak transaction rate and whether your environment can accommodate additional load;
    - current CPU or I/O bottlenecks;
    - concurrency (locking) issues;
    - application response time requirements (is there any headroom to add another insert/update to each transaction).

  4. #4
    Join Date
    Oct 2010
    Posts
    94
    Thanks to both of you for your insights!

    I have been reading more on this and it seems SQL replication could be a solution too ( to detemine changed data).

    Do you think it makes sense in terms of licensing and performance costs to go the replication way?

    - G

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    SQL Replication is included in most editions of DB2, for capturing changes I personally would prefer the triggers if there are no performance problems.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    We can't tell you if it makes sense for your application. It is a viable solution, as are many other products and a trigger solution. It all depends on your transaction workload and what additional work this would all cause on your database. It does not sound like you are copying everything from your OLTP, based on your comments above. Most of the time I have only seen triggers cause an issue where there is additional logic being oerformed within the trigger. One I implemented many years ago, contained an existence check and was unbearable for one on of our batch processes, others that just did inserts somewhere else or an update to the table being updated, provided no noticeable difference to the application.
    Dave Nance

  7. #7
    Join Date
    Oct 2010
    Posts
    94
    Cool! I am going to try the trigger option and see how it goes in test. Will let you know how I make out. I understand this OLTP has a very low daily transaction volume.


    Thanks as always!
    - G

Posting Permissions

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