[Question] how to monitor db2 trigger activity ?

[wugon.net]

Hi ,
Anyone know how to monitor db2 trigger activity ?

We suffer some trigger issue , and we try to monitor trigger's
behavior use event monitor and db2audit, but both tools can not get
trigger's sql statment and cost, have other tools can get trigger's
executing sql statment and cost ?

our test case as follow:
Env:
WIN XP
DB2 V8 + FP13

1. create sample table
create table DB2.NEWS ( ID INT, NAME CHAR(10),T_TIME timestamp ) IN
TBS16K @

create table DB2.NEWSQ1 ( ID INT, NAME CHAR(10),T_TIME timestamp with
default current timestamp) IN TBS16K @

2. create sample trigger
create trigger DB2.NEWSTRG
after insert on DB2.NEWS
referencing
new as nw_row
for each row
mode db2sql
BEGIN ATOMIC
insert into DB2.NEWSQ1 (ID, NAME)
values ( (nw_row.ID+1000),
nw_row.NAME) ;
END @

3. create event and activate
create event monitor failtrigger for
tables,
statements,
transactions
write to file 'd:\event' ;
set event monitor failtrigger state 1 ;

4. insert data to DB2.NEWS
db2 "insert into DB2.NEWS values (1,'aa',current timestamp)"

5. check data
db2 "select * from db2.news"
db2 "select * from db2.newsq1"

6.format event
db2evmon -path d:\event

part of event output:
only can get insert sql statement and Internal rows inserted count
but without trigger sql statment.

we also try db2audit, still can not get executing sql statment.

do anyone know how to monitor db2 trigger executing activity ?
Thanks
================================================== ===
32) Statement Event ...
Appl Handle: 24
Appl Id: *LOCAL.DB2.070425165450
Appl Seq number: 0006

Record is the result of a flush: FALSE
-------------------------------------------
Type : Dynamic
Operation: Execute Immediate
Section : 203
Creator : NULLID
Package : SQLC2E07
Consistency Token : AAAAAcEU
Package Version ID :
Cursor :
Cursor was blocking: FALSE
Text : insert into DB2.NEWS values (1,'aa',current timestamp)
-------------------------------------------
Start Time: 2007-04-26 00:56:19.910300
Stop Time: 2007-04-26 00:56:19.910445
Exec Time: 0.000145 seconds
Number of Agents created: 1
User CPU: 0.000000 seconds
System CPU: 0.000000 seconds
Fetch Count: 0
Sorts: 0
Total sort time: 0
Sort overflows: 0
Rows read: 0
Rows written: 2
Internal rows deleted: 0
Internal rows updated: 0
Internal rows inserted: 1
================================================== =============

[Serge Rielau]

wu*******@gmail.com wrote:

Hi ,
Anyone know how to monitor db2 trigger activity ?

We suffer some trigger issue , and we try to monitor trigger's
behavior use event monitor and db2audit, but both tools can not get
trigger's sql statment and cost, have other tools can get trigger's
executing sql statment and cost ?

Since the triggers in DB2 for LUW are inlined this data simply deosn't
exist.
It's like asking for the execution time of views...

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Knut Stolze]

wu*******@gmail.com wrote:

We suffer some trigger issue

What's the issue?

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

[wugon.net]

On Apr 26, 2:02 am, Serge Rielau <[e-mail]:

[e-mail] wrote:

Hi ,
Anyone know how to monitor db2 trigger activity ?

We suffer some trigger issue , and we try to monitor trigger's
behavior use event monitor and db2audit, but both tools can not get
trigger's sql statment and cost, have other tools can get trigger's
executing sql statment and cost ?

Since the triggers in DB2 for LUW are inlined this data simply deosn't
exist.
It's like asking for the execution time of views...

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Hi Serge,
Thanks your explain.

[wugon.net]

On Apr 26, 4:58 am, Knut Stolze <[e-mail]:

[e-mail] wrote:

We suffer some trigger issue

What's the issue?

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

our report team challenge the db2 trigger transfer incorrect data
impact report result,
follow our data flow :
Tx1 -DB2 Base table -trigger to history table
another AP routine get history data and parse into back end db

report team found back end db data incorrect and challenge the db2
trigger.
We try to monitor trigger executing sql to find out the root cuase for
incorrect report.

Thanks you response.

[Knut Stolze]

wu*******@gmail.com wrote:

On Apr 26, 4:58 am, Knut Stolze <[e-mail]:

>[e-mail] wrote:

We suffer some trigger issue

What's the issue?

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

our report team challenge the db2 trigger transfer incorrect data
impact report result,
follow our data flow :
Tx1 -DB2 Base table -trigger to history table
another AP routine get history data and parse into back end db

report team found back end db data incorrect and challenge the db2
trigger.
We try to monitor trigger executing sql to find out the root cuase for
incorrect report.

If there is indeed incorrect data in the history table, then your trigger
would have a problem.

What you could monitor is the insert/update/delete statement issued against
the "DB2 base table" and also the activity on the history table. Then you
can match both (based on a timestamp, for instance) and figure out where
the problem is with the trigger.
--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

[wugon.net]

On Apr 27, 4:45 am, Knut Stolze <[e-mail]:

[e-mail] wrote:

On Apr 26, 4:58 am, Knut Stolze <[e-mail]:

[e-mail] wrote:
We suffer some trigger issue

What's the issue?

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

our report team challenge the db2 trigger transfer incorrect data
impact report result,
follow our data flow :
Tx1 -DB2 Base table -trigger to history table
another AP routine get history data and parse into back end db

report team found back end db data incorrect and challenge the db2
trigger.
We try to monitor trigger executing sql to find out the root cuase for
incorrect report.

If there is indeed incorrect data in the history table, then your trigger
would have a problem.

What you could monitor is the insert/update/delete statement issued against
the "DB2 base table" and also the activity on the history table. Then you
can match both (based on a timestamp, for instance) and figure out where
the problem is with the trigger.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany- Hide quoted text -

- Show quoted text -

Hi Knut ,
Thanks your advice.