Re: Trigger performance not good

[Glenn Gundermann <glenn.gundermann@xxxxxxxxx>]

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