Re: [Bulk] Trigger performance not good

Hi, again, Glenn:

I forgot to mention, but I hope it is fairly obvious to all, that you would not want to use "data queues" with any "before triggers."

Also, I noticed that Glenn mentioned in another reply the use of several UDFs. How many UDFs are involved? Realize that each UDF also involves a CALL, though it might not be a full external program call ... it could be a call to a service program procedure entry point, which has lower overhead. It depends on how you define your UDFs.

Hope that helps,

Mark S. Waterbury

> On 4/28/2015 11:17 AM, Mark S Waterbury wrote:

Hi, Glenn:

With row-level triggers, the DB2 database will have to issue a full EXTERNAL program call for each record, to call the *PGM of the trigger.

It is known that full external program calls are much more expensive in terms of overhead than internal procedure calls, and so it is easy to imagine where some of this extra overhead comes from.

As others have mentioned, one way to minimize the impact on the applications using the tables or files that have triggers on them is to keep the triggers as short as possible and have them simply write the relevant information to a data queue, and then you can have one or more programs waiting on the data queue to service any requests that come in. In this way, you shift much of the overhead from the sequential path that is part of the WRITE or UPDATE to the original table or file, over to a separate "server job".

This technique of using data queues in this way has been around since System/38 CPF and those programs running as a server job were often called (colloquially) a "never ending program" (or NEP).

I hope that helps,

Mark S. Waterbury