469,326 Members | 1,608 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

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

How to monitor db2 executing trigger activity ?

Hi ,
Anyone know how to monitor db2 trigger activity ?

We suffer some trigger issue today 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 event
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 trigger 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
================================================== =============

Apr 25 '07 #1
0 2338

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by chris | last post: by
6 posts views Thread by Clark Sann | last post: by
4 posts views Thread by John | last post: by
12 posts views Thread by Perecli Manole | last post: by
4 posts views Thread by Raj | last post: by
reply views Thread by wugon.net | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by Gurmeet2796 | last post: by
reply views Thread by mdpf | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.