How to monitor db2 executing trigger activity ?

This is a discussion on How to monitor db2 executing trigger activity ? within the ibm-db2 forums in Other Databases category; 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 ...

Go Back   Database Forum > Other Databases > ibm-db2

Database Forums

Register FAQ Calendar Search Today's Posts Mark Forums Read
  #1  
Old 04-25-2007, 10:59 PM
Default 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
================================================== =============

Reply With Quote
Reply


Thread Tools
Display Modes



All times are GMT -4. The time now is 09:39 AM.


Powered by vBulletin® Version 3.6.8
Copyright ©2000 - 2009, Jelsoft Enterprises Ltd.
Integrated by bbpixel2009 :: jvbPlugin R1013.368.1

Search Engine Friendly URLs by vBSEO 3.1.0
vB Ad Management by =RedTyger=
In an effort to better serve ads to our visitors, cookies are used on Mydatabasesupport.com. For more information, check out our Privacy Policy.