471,852 Members | 919 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,852 software developers and data experts.

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:
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
new as nw_row
for each row
mode db2sql
insert into DB2.NEWSQ1 (ID, NAME)
values ( (nw_row.ID+1000),
nw_row.NAME) ;

3. create event and activate event
create event monitor failtrigger for
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

do anyone know how to monitor db2 trigger executing activity ?
================================================== ===
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 2462

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
reply views Thread by NeoPa | last post: by
reply views Thread by YellowAndGreen | last post: by

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.