469,277 Members | 1,964 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,277 developers. It's quick & easy.

Slow Trigger

Hello All,

DB2 UDB 8.1 FP3 for Linux

The table has 4 triggers on the Insert operation.
Three triggers small (200 bytes) and one trigger large (3 ).
In the large trigger there is one long construction if... Elseif...
Endif and a little short if... Endif.
Depending on value of a field in updated the tables, work different (one
or severaled) sections
if... Elseif.
All inquiries taking place in the trigger I work with primary key, and
fulfil very fast, I checked it.

The problem consists in the following: the Insert operation lasts 4-5
seconds, that is very long.

I tried to disable the trigger (in header of the trigger registered a
condition WHEN(1=2), that is the trigger in general to work will not be,
Has not helped. Insert all the same lasts 4-5 seconds. (This moment is
not very clear to me).

Tried to divide the large trigger on the small trigger - too has not
helped. Insert all the same lasts 4-5 seconds.

If to delete of the large trigger or to comment out its body, then
Insert is fulfilled less than second.

As I understand for DB2 it is necessary to customize with what that the
parameter of productivity, but here with what I do not know.
The reading of documentation unfortunately has not helped.; (((

All: Help please to clear up with this problem.

--
Best Regards, Stalker

Origin: The History is Dead
Nov 12 '05 #1
13 3910
Tolik,

The 5sec time you see is likely compile time because DB2 expands the
trigger into the query.
How do you do your inserts? You may want to consider using parameter
markers, so that Db2 can reuse the same insert statement over and over
again.

Cheers
Serge
Nov 12 '05 #2
Serge Rielau wrote:

Tolik,

The 5sec time you see is likely compile time because DB2 expands the
trigger into the query.
How do you do your inserts?
Not absolutely has understood your question.

If you are interested with contents of the trigger, I can it show:

------------------------------------------------------
CREATE TRIGGER DB2AS.FORUPDATESPD_I
AFTER INSERT ON DB2AS.REGCARD
REFERENCING NEW AS NEW_F
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC

declare cFullName varchar(250);
declare cOldFullName varchar(250);

set cFullName =
UPPER(REPLACE(TRANSLATE(NEW_F.FULLNAMEUKR_SPD,'',' .,-_()[]"'),' ', ''));

if new_f.REGTYPE=4 then
update db2as.SPD set
ACTIVESPD=2
where SPD.ID=new_f.SPDCODE;
elseif new_f.REGTYPE=8 then
update db2as.SPD set
SPD.ACTIVESPD=3,
SPD.OFFDATE=new_f.REGDATE
where SPD.ID=new_f.SPDCODE;
update db2as.RESERVEDNAMESDATA
set STATUSNAME=5
where (STATUSNAME=1) and (NAMESCODE=(select RESERVEDNAMES.ID
from db2as.RESERVEDNAMES where
RESERVEDNAMES.FULLNAMEUKR_U=cFullName));
elseif new_f.REGTYPE in (1,2,5) then
if new_f.REGTYPE = 2 then
set cOldFullName = (select FULLNAMEUKR from db2as.SPD where
SPD.ID=new_f.SPDCODE);
set cOldFullName =
UPPER(REPLACE(TRANSLATE(cOldFullName,'','.,-_()[]"'),' ', ''));
if cOldFullName <> cFullName then
update db2as.RESERVEDNAMESDATA set STATUSNAME=6
where (STATUSNAME=1) and (NAMESCODE=(select RESERVEDNAMES.ID from
db2as.RESERVEDNAMES
where
RESERVEDNAMES.FULLNAMEUKR_U=cOldFullName));
end if;
end if;

update db2as.SPD
set FULLNAMEUKR=new_f.FULLNAMEUKR_SPD
where SPD.ID=new_f.SPDCODE;
end if;

if new_f.REGTYPE=2 then
update db2as.SPD set
SPD.REGDATE=new_f.REGDATE,
SPD.REGDATETYPE=2,
SPD.REGAGENCYCODE=new_f.REGAGENCYCODE
where SPD.ID=new_f.SPDCODE;
end if;

if new_f.REGTYPE=5 then
update db2as.SPD set
SPD.ACTIVESPD=1,
SPD.REGNUMBER=new_f.REGNUMBER
where SPD.ID=new_f.SPDCODE;
end if;

update db2as.SPD set
SPD.PINDEX=new_f.PINDEX_SPD,
SPD.AREACODE=new_f.AREACODE_SPD,
SPD.ADISTRICTCODE=new_f.ADISTRICTCODE_SPD,
SPD.CITYCODE=new_f.CITYCODE_SPD,
SPD.CDISTRICTCODE=new_f.CDISTRICTCODE_SPD,
SPD.STREETCODE=new_f.STREETCODE_SPD,
SPD.HOME=new_f.HOME_SPD,
SPD.CORPS=new_f.CORPS_SPD,
SPD.APARTMENT=new_f.APARTMENT_SPD,
SPD.TELEPHONE=new_f.TELEPHONE_SPD,
SPD.FIOBOSS=new_f.FIOBOSS,
SPD.SHORTNAME= new_f.SHORTNAME_SPD
where SPD.ID=new_f.SPDCODE;

END

------------------------------------------------------
You may want to consider using parameter
markers, so that Db2 can reuse the same insert statement over and over
again.


Has not understood, whence in the trigger parameters?

And then it is not clear:
1) Why DB2 wastes time on the trigger, if it is actually disconnected
(WHEN(1=2)).

I tried to divide the large trigger into three small, has not helped.
Tried to increase values of parameters
Stmtheap - up to 30000)
Dbheap - up to 2400
Query_sz_heap up to 20000
The buffer tablespace up to 100000
Too has not helped.
The table, where I put aside money Insert (REGCARD) not large - record
length of 524 bytes and 1500 rows.

Honourly speaking, I already also do not know what still it is necessary
to customize for DB2 what to decide this problem.

--
Best Regards, Stalker

Origin: The History is Dead
Nov 12 '05 #3
Do you:
INSERT INTO DB2AS.REGCARD VALUES (<constant>, <constant> ....)?
many times (e.g. in a loop).
Insetad do soewthing like (you'd need to check the exact syntax):
PREPARE ... "INSERT INTO DB2AS.REGCARD VALUES (?, ? ....)"
(takes 5 seconds)
Then in the loop
EXECUTE .... USING ...
(takes very little time)

Other than that just by looking at your trigger quickly I see you touch
the same table many times.

At least the last three can be merged:
Note that CASE-expression is a lot more light weight to compile than IF
THEN ELSE statement.

update db2as.SPD set
SPD.REGDATE=CASE WHEN new_f.REGTYPE=2
THEN new_f.REGDATE ELSE SPD.REGDATE END,
SPD.REGDATETYPE= CASE WHEN new_f.REGTYPE=2
THEN 2 ELSE SPD.REGDATETYPE END,
SPD.REGAGENCYCODE= CASE WHEN new_f.REGTYPE=2
THEN new_f.REGAGENCYCODE ELSE SPD.REGAGENCYCODE END,
SPD.ACTIVESPD= CASE WHEN new_f.REGTYPE=5
THEN 1 ELSE SPD.ACTIVESPD END,
SPD.REGNUMBER=CASE WHEN new_f.REGTYPE=5
THEN new_f.REGNUMBER ELSE SPD.REGNUMBER END,
SPD.PINDEX=new_f.PINDEX_SPD,
SPD.AREACODE=new_f.AREACODE_SPD,
SPD.ADISTRICTCODE=new_f.ADISTRICTCODE_SPD,
SPD.CITYCODE=new_f.CITYCODE_SPD,
SPD.CDISTRICTCODE=new_f.CDISTRICTCODE_SPD,
SPD.STREETCODE=new_f.STREETCODE_SPD,
SPD.HOME=new_f.HOME_SPD,
SPD.CORPS=new_f.CORPS_SPD,
SPD.APARTMENT=new_f.APARTMENT_SPD,
SPD.TELEPHONE=new_f.TELEPHONE_SPD,
SPD.FIOBOSS=new_f.FIOBOSS,
SPD.SHORTNAME= new_f.SHORTNAME_SPD
where SPD.ID=new_f.SPDCODE;
Nov 12 '05 #4
Serge Rielau wrote:

Do you:
INSERT INTO DB2AS.REGCARD VALUES (<constant>, <constant> ....)?
many times (e.g. in a loop).
Insetad do soewthing like (you'd need to check the exact syntax):
PREPARE ... "INSERT INTO DB2AS.REGCARD VALUES (?, ? ....)"
(takes 5 seconds)
Then in the loop
EXECUTE .... USING ...
(takes very little time)
For me the data entry (Insert) occurs for each document once (not in
cycle), therefore parameters here will not help.
Other than that just by looking at your trigger quickly I see you touch
the same table many times.

At least the last three can be merged:
Note that CASE-expression is a lot more light weight to compile than IF
THEN ELSE statement.

update db2as.SPD set
SPD.REGDATE=CASE WHEN new_f.REGTYPE=2
THEN new_f.REGDATE ELSE SPD.REGDATE END,
SPD.REGDATETYPE= CASE WHEN new_f.REGTYPE=2
THEN 2 ELSE SPD.REGDATETYPE END,
SPD.REGAGENCYCODE= CASE WHEN new_f.REGTYPE=2
THEN new_f.REGAGENCYCODE ELSE SPD.REGAGENCYCODE END,
SPD.ACTIVESPD= CASE WHEN new_f.REGTYPE=5
THEN 1 ELSE SPD.ACTIVESPD END,
SPD.REGNUMBER=CASE WHEN new_f.REGTYPE=5
THEN new_f.REGNUMBER ELSE SPD.REGNUMBER END,
SPD.PINDEX=new_f.PINDEX_SPD,
SPD.AREACODE=new_f.AREACODE_SPD,
SPD.ADISTRICTCODE=new_f.ADISTRICTCODE_SPD,
SPD.CITYCODE=new_f.CITYCODE_SPD,
SPD.CDISTRICTCODE=new_f.CDISTRICTCODE_SPD,
SPD.STREETCODE=new_f.STREETCODE_SPD,
SPD.HOME=new_f.HOME_SPD,
SPD.CORPS=new_f.CORPS_SPD,
SPD.APARTMENT=new_f.APARTMENT_SPD,
SPD.TELEPHONE=new_f.TELEPHONE_SPD,
SPD.FIOBOSS=new_f.FIOBOSS,
SPD.SHORTNAME= new_f.SHORTNAME_SPD
where SPD.ID=new_f.SPDCODE;

The code, indicated by you, is possible and faster than chain if elseif,
but it(he) is less convenient in operation than if elseif.

And in what the problem, what DB2 so slowly compiles (handles) of the
trigger?
For me in systems based on Sybase Adaptive Server Anywhere 8.02 (ASA) is
of the trigger of a much more size and more complex and similar
decelerations in ASA I not to time did not notice.

Can for DB2 is what or the parameters which can affect faster processing
of triggers in DB2 ?
Whether will it be corrected (is improved) in FP5 ?

--
Best Regards, Stalker

Origin: The History is Dead
Nov 12 '05 #5
Tolic,

How many documents do you update per minute/hour/...
DB2 can share the compiled INSERT statement across invocations.

But let me comment on the technology issue:
DB2 for Multiplatforms is very unique in its way o deal with triggers.
A Trigger is expanded much like a view. It is NOT a stored procedure
(like it is in SQL Server/Sybase and just about any other product
including DB2 for z/Series.
What this means is that the INSERT has to compile the trigger.
In Sybase the trigger is compiled with CREATE TRIGGER.
Both models have their own advantages/disadvantages. procedure-approach
gives you compiletime benefits. inlining gives you global optimization
benefits.

So. Will this be fixed? No, it;s a feature. not a bug.
BUT in DB2 Stinger you will be able to CALL a procedure from a trigger
and then you get the best from both worlds. You can place all your logic
into teh store procedure and the trigger is virtually empty but for the
CALL statement.

Cheers
Serge

PS: You will need to accept that different products have different
sweetspots and weaknesses. Either you exploit DB2 or you don't. Don't
expect DB2 to be the same as Sybase/SQL Server.

Nov 12 '05 #6
I'd verify the where clauses to see what's indexed/not indexed and also
runstats, reorg the proper tables to see if it helps.

PM
"Tolik Gusin" <st*****@giac.dp.ua> a ecrit dans le message de
news:40***************@giac.dp.ua...
Hello All,

DB2 UDB 8.1 FP3 for Linux

The table has 4 triggers on the Insert operation.
Three triggers small (200 bytes) and one trigger large (3 ).
In the large trigger there is one long construction if... Elseif...
Endif and a little short if... Endif.
Depending on value of a field in updated the tables, work different (one
or severaled) sections
if... Elseif.
All inquiries taking place in the trigger I work with primary key, and
fulfil very fast, I checked it.

The problem consists in the following: the Insert operation lasts 4-5
seconds, that is very long.

I tried to disable the trigger (in header of the trigger registered a
condition WHEN(1=2), that is the trigger in general to work will not be,
Has not helped. Insert all the same lasts 4-5 seconds. (This moment is
not very clear to me).

Tried to divide the large trigger on the small trigger - too has not
helped. Insert all the same lasts 4-5 seconds.

If to delete of the large trigger or to comment out its body, then
Insert is fulfilled less than second.

As I understand for DB2 it is necessary to customize with what that the
parameter of productivity, but here with what I do not know.
The reading of documentation unfortunately has not helped.; (((

All: Help please to clear up with this problem.

--
Best Regards, Stalker

Origin: The History is Dead

Nov 12 '05 #7
Good point.
Nov 12 '05 #8
Serge Rielau wrote:

Tolic,

How many documents do you update per minute/hour/... 1-2 Document in minute.
DB2 can share the compiled INSERT statement across invocations. So. Will this be fixed? No, it;s a feature. not a bug.
BUT in DB2 Stinger you will be able to CALL a procedure from a trigger
and then you get the best from both worlds. You can place all your logic
into teh store procedure and the trigger is virtually empty but for the
CALL statement.

When the output FP5 is expected ?

--
Best Regards, Stalker

Origin: The History is Dead
Nov 12 '05 #9
Serge Rielau wrote:
I'd verify the where clauses to see what's indexed/not indexed and also
runstats, reorg the proper tables to see if it helps.
Good point.


I put aside money each day complete collection of statistics.
In table REGCARD 1500 of records, in SPD 3500 records, in
RESERVEDNAMESDATA 0 records.
I do not think that the reorganization here will help.

I consider that it is a problem (defect) in DB2. For me of not such
large trigger, what DB2 with them so poorly worked. I expected from the
server of a scale of enterprise greater.

With arrival FP5 (UDB 8.2) certainly it will be possible to bear logic
of triggers in SP SQL, but I think that it too not the best
output(exit), and most likely forced way out.

--
Best Regards, Stalker

Origin: The History is Dead
Nov 12 '05 #10
>1-2 per minute
Then it's worth using parameter markers. Thsi way teh statement will be
shared

W.r.t. FP5, I was referring to DB2 Stinger, not FP5.
I cannot give you an ETA for Stinger.

Cheers
Serge
Nov 12 '05 #11
Your opinions are your own. I have done my best to explain.

Either way FP5 is definutely not V8.2.

Cheers
Sereg
Nov 12 '05 #12
I'ts hard to find exactly where is the problem when we actually don't have
access to the whole picture.

For all i know, you update multiple tables and the same table multiple times
in the same trigger and who knows
what other triggers you defined on them. re : db2as.SPD
For each insert statement you execute, you may generate in reality the
equivalent of 20 statements.
[ conservative evaluation based on your programming style ;-) ]

WIthout the DDL, we can't evaluate problems that can come from other sources
like sequences, generated values, ...

Maybe a Begin Atomic would be allowed in your trigger definition.
(not sure and not sure it would make a difference.)

PM
Nov 12 '05 #13
AK
if the problem id indeed high compile time, then I would wrap the
insert statement in a stored procedure. Different flavors of SQL
require diffrent approaches, that's OK
Nov 12 '05 #14

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

8 posts views Thread by Bryan | last post: by
reply views Thread by JohnO | last post: by
3 posts views Thread by qilinw | last post: by
46 posts views Thread by dunleav1 | last post: by
12 posts views Thread by Andrew C | last post: by
7 posts views Thread by Don Li | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.