I want to set up some triggers on a DB2 box (LUW, 9.7). The DBAs are concerned that if the trigger fires too often, it will adversely affect general performance.
What's a reasonable cheap approach to sizing up performance impact? By "cheap", I mean I don't want to set up complicated monitoring/profiling tools; my impression is that this database was set up with a minimum of logging tools and so forth, and I don't want to change that. I'm thinking more in terms of things like watching for page faults and also things that can be seen with db2top.
The environment:
It's an enterprise-level analytics database. Data is fed into it via replication (Golden Gate, now owned by Oracle). The triggers we're creating track updates. (Upon update, a small record containing the primary key is stored in another table. It's not a great way to do things; we're trying to ship data somewhere else, and right now we're highly constrained and can't use a technically advanced and thorough solution like replication. An alternative is adding a column to record updates, but we've been told not to do that, though that might change.) One reason I'm leery of just adding triggers willy-nilly is I'm concerned I might break the replication process. My current idea is to add a trigger that performs a simple hash on the primary key (which is a BIGINT) and record updates only for a fraction of the rows, so I can get an idea of how often updates occur. Anyway, that's why I'm asking about simple, reasonable metrics to see how triggers affect e.g. inserts into a particular table.
Best Answer
There's no separate "trigger performance impact"; trigger code is essentially compiled into the execution plan of the statement that causes the trigger to fire, so measuring average DML statement response time before and after introduction of triggers should give you a general idea of the impact.
Of course, triggered actions will certainly affect other workloads, not only the triggering actions. I'm sure your DBAs have records of baseline database performance that they can compare to what happens after the triggers are added (presumably on a test system).
As to monitoring tools, DB2 is fully instrumented to provide detailed views of its performance from various aspects, using event monitors and monitor views/stored procedures, so you don't need to set up anything in addition to what's always there.