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

"...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.

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).

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

SQL Replication is included in most editions of DB2, for capturing changes I personally would prefer the triggers if there are no performance problems.

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

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