Re: Trigger performance not good

Hi, Glenn:

I see you are using SQL to define your UDFs, so AFAIK, they will always generate a *SRVPGM, for better performance. It is only when you create an "external" UDF where you write your own code (in RPGLE, or C, etc.) and define it as a UDF that it could be either a *PGM or a *SRVPGM entry point, as is the case with stored procedures.

I am not sure why we cannot define a "trigger" to use a service program procedure, for improved performance.

HTH,

Mark S. Waterbury

> On 4/28/2015 12:44 PM, Glenn Gundermann wrote:

Hi Mark,

Here's a typical UDF. Feel free to comment. Thanks!

CREATE OR REPLACE FUNCTION get_colour_desc (colour_code NUMERIC(3,0) )
RETURNS CHARACTER(25)
LANGUAGE SQL
RETURNS NULL ON NULL INPUT
DETERMINISTIC
DISALLOW PARALLEL
NOT FENCED
RETURN (
SELECT clrn
FROM ipcolor
WHERE cclr = colour_code
FETCH FIRST 1 ROW ONLY ) ;


Yours truly,

Glenn Gundermann
Email: glenn.gundermann@xxxxxxxxx
Work: (416) 675-9200 ext. 89224
Cell: (416) 317-3144


On 28 April 2015 at 12:30, Mark S Waterbury <mark.s.waterbury@xxxxxxxxxxxxx>
wrote:

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