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.0704 25165450
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
=============== =============== =============== =============== ===
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.0704 25165450
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
=============== =============== =============== =============== ===
Comment