473,834 Members | 1,764 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 4116
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.FORUPDATE SPD_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(T RANSLATE(NEW_F. FULLNAMEUKR_SPD ,'','.,-_()[]"'),' ', ''));

if new_f.REGTYPE=4 then
update db2as.SPD set
ACTIVESPD=2
where SPD.ID=new_f.SP DCODE;
elseif new_f.REGTYPE=8 then
update db2as.SPD set
SPD.ACTIVESPD=3 ,
SPD.OFFDATE=new _f.REGDATE
where SPD.ID=new_f.SP DCODE;
update db2as.RESERVEDN AMESDATA
set STATUSNAME=5
where (STATUSNAME=1) and (NAMESCODE=(sel ect RESERVEDNAMES.I D
from db2as.RESERVEDN AMES where
RESERVEDNAMES.F ULLNAMEUKR_U=cF ullName));
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.SP DCODE);
set cOldFullName =
UPPER(REPLACE(T RANSLATE(cOldFu llName,'','.,-_()[]"'),' ', ''));
if cOldFullName <> cFullName then
update db2as.RESERVEDN AMESDATA set STATUSNAME=6
where (STATUSNAME=1) and (NAMESCODE=(sel ect RESERVEDNAMES.I D from
db2as.RESERVEDN AMES
where
RESERVEDNAMES.F ULLNAMEUKR_U=cO ldFullName));
end if;
end if;

update db2as.SPD
set FULLNAMEUKR=new _f.FULLNAMEUKR_ SPD
where SPD.ID=new_f.SP DCODE;
end if;

if new_f.REGTYPE=2 then
update db2as.SPD set
SPD.REGDATE=new _f.REGDATE,
SPD.REGDATETYPE =2,
SPD.REGAGENCYCO DE=new_f.REGAGE NCYCODE
where SPD.ID=new_f.SP DCODE;
end if;

if new_f.REGTYPE=5 then
update db2as.SPD set
SPD.ACTIVESPD=1 ,
SPD.REGNUMBER=n ew_f.REGNUMBER
where SPD.ID=new_f.SP DCODE;
end if;

update db2as.SPD set
SPD.PINDEX=new_ f.PINDEX_SPD,
SPD.AREACODE=ne w_f.AREACODE_SP D,
SPD.ADISTRICTCO DE=new_f.ADISTR ICTCODE_SPD,
SPD.CITYCODE=ne w_f.CITYCODE_SP D,
SPD.CDISTRICTCO DE=new_f.CDISTR ICTCODE_SPD,
SPD.STREETCODE= new_f.STREETCOD E_SPD,
SPD.HOME=new_f. HOME_SPD,
SPD.CORPS=new_f .CORPS_SPD,
SPD.APARTMENT=n ew_f.APARTMENT_ SPD,
SPD.TELEPHONE=n ew_f.TELEPHONE_ SPD,
SPD.FIOBOSS=new _f.FIOBOSS,
SPD.SHORTNAME= new_f.SHORTNAME _SPD
where SPD.ID=new_f.SP DCODE;

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=CAS E 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.REGAGENCYCO DE= CASE WHEN new_f.REGTYPE=2
THEN new_f.REGAGENCY CODE ELSE SPD.REGAGENCYCO DE END,
SPD.ACTIVESPD= CASE WHEN new_f.REGTYPE=5
THEN 1 ELSE SPD.ACTIVESPD END,
SPD.REGNUMBER=C ASE WHEN new_f.REGTYPE=5
THEN new_f.REGNUMBER ELSE SPD.REGNUMBER END,
SPD.PINDEX=new_ f.PINDEX_SPD,
SPD.AREACODE=ne w_f.AREACODE_SP D,
SPD.ADISTRICTCO DE=new_f.ADISTR ICTCODE_SPD,
SPD.CITYCODE=ne w_f.CITYCODE_SP D,
SPD.CDISTRICTCO DE=new_f.CDISTR ICTCODE_SPD,
SPD.STREETCODE= new_f.STREETCOD E_SPD,
SPD.HOME=new_f. HOME_SPD,
SPD.CORPS=new_f .CORPS_SPD,
SPD.APARTMENT=n ew_f.APARTMENT_ SPD,
SPD.TELEPHONE=n ew_f.TELEPHONE_ SPD,
SPD.FIOBOSS=new _f.FIOBOSS,
SPD.SHORTNAME= new_f.SHORTNAME _SPD
where SPD.ID=new_f.SP DCODE;
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=CAS E 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.REGAGENCYCO DE= CASE WHEN new_f.REGTYPE=2
THEN new_f.REGAGENCY CODE ELSE SPD.REGAGENCYCO DE END,
SPD.ACTIVESPD= CASE WHEN new_f.REGTYPE=5
THEN 1 ELSE SPD.ACTIVESPD END,
SPD.REGNUMBER=C ASE WHEN new_f.REGTYPE=5
THEN new_f.REGNUMBER ELSE SPD.REGNUMBER END,
SPD.PINDEX=new_ f.PINDEX_SPD,
SPD.AREACODE=ne w_f.AREACODE_SP D,
SPD.ADISTRICTCO DE=new_f.ADISTR ICTCODE_SPD,
SPD.CITYCODE=ne w_f.CITYCODE_SP D,
SPD.CDISTRICTCO DE=new_f.CDISTR ICTCODE_SPD,
SPD.STREETCODE= new_f.STREETCOD E_SPD,
SPD.HOME=new_f. HOME_SPD,
SPD.CORPS=new_f .CORPS_SPD,
SPD.APARTMENT=n ew_f.APARTMENT_ SPD,
SPD.TELEPHONE=n ew_f.TELEPHONE_ SPD,
SPD.FIOBOSS=new _f.FIOBOSS,
SPD.SHORTNAME= new_f.SHORTNAME _SPD
where SPD.ID=new_f.SP DCODE;

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.d p.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
RESERVEDNAMESDA TA 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

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
4841
by: Bryan | last post by:
does anyone know how to make this faster? it seems that str(x) is the slow part. >>> def foo(): .... t1 = time.time() .... x = 19 ** 314159 .... t2 = time.time() .... y = str(x) .... t3 = time.time() .... print y
1
6180
by: Gent | last post by:
am using FOR UPDATE triggers to audit a table that has 67 fields. My problem is that this slows down the system significantly. I have narrowed down the problem to the size (Lines of code) that need to be compiled after the trigger has been fired. There is about 67 IF Update(fieldName) inside the trigger and a not very complex select statement inside the if followed by an insert to the audit table. When I leave only a few IF-s in the...
1
2997
by: Trent | last post by:
Hello,everyone. I'm setting a db2 replication environment using UDB version 8.1.5 running on Windows 2000 servers. The source server is on a Windows server with the capture program running while the target server is on another Windows server running the apply program. This replication set up has a mix of bidirectional and unidirection data exchange. User copy is used for the unidirectional data. I'm having the following issue. 1. It...
0
2482
by: JohnO | last post by:
Thanks to Serge and MarkB for recent tips and suggestions. Ive rolled together a few stored procedures to assist with creating audit triggers automagically. Hope someone finds this as useful as I've found it educational. Note: - I build this for use in a JDEdwards OneWorld environment. I'm not sure how generic others find it but it should be fairly generic. - I use a C stored procedure GETJOBNAME to get some extra audit data,
3
2953
by: qilinw | last post by:
In a winform I want to display database records using datagrid, with dataset for a couple of thousands records it seems very slow. I have read an article in group microsoft.public.dotnet.languages.vb. It uses datareader to solve this problem, it's a good idea. I have translated it in c# and want to run this in a separate thread, but it does not work, i have a blank datagrid. Anyone can help me? Thanks a lot. My source code is...
46
13163
by: dunleav1 | last post by:
I have a process that does inserts that runs 50% slower that Oracle and Mssql. Queries normally take 50% slower than normal. DB2, Oracle, Mssql all are configured on same os, same disk array (raid 1/0 - 15k disks) but DB2 I/O seems to be significantly slower. Tablespaces are SMS with automatic prefetch set. My thought was to create the log buffer array as large as possible so I don't have to switch to disk much. I'm running circular...
12
9577
by: Andrew C | last post by:
Hi, folks. I've recently been doing a few simple tests and experiments. As a result, I've noticed that, in dealing with 'onclick', IE seems less able than Firefox to keep up with rapid clicking. In fact, Firefox seems to be able to keep up with me no matter how rapidly I click, whereas IE... well, doesn't! The following snippet of code demonstrates this (on my machine it does anyway):
9
9315
by: Ots | last post by:
I'm using SQL 2000, which is integrated with a VB.NET 2003 app. I have an Audit trigger that logs changes to tables. I want to apply this trigger to many different tables. It's the same trigger, with the exception of the table name. I could manually change the table name in the trigger and create it, over and over, but I'd like to automate this - by iterating through the collection of tables and passing the tablename to something that...
7
4725
by: Don Li | last post by:
Hi, Env is ms sql server 2000. ddl: create table srchPool(tid int primary key, taid int, s tynyint, uid tynyint); -- and sql server automatically creates a clustered index for the pk dml:
0
9651
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10802
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10516
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10557
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10225
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9340
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development projectŚplanning, coding, testing, and deploymentŚwithout human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7763
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph DuprÚ who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6961
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
3
3085
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.