| Register | FAQ | Calendar | Search | Today's Posts | Mark Forums Read |
|
#1
|
| 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 ================================================== ============= |
![]() |
| Thread Tools | |
| Display Modes | |