× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.