473,224 Members | 1,386 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

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

[Question] how to monitor db2 trigger activity ?

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.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
6 6724
wu*******@gmail.com wrote:
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 ?
Since the triggers in DB2 for LUW are inlined this data simply deosn't
exist.
It's like asking for the execution time of views...

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Apr 25 '07 #2
wu*******@gmail.com wrote:
We suffer some trigger issue
What's the issue?

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany
Apr 25 '07 #3
On Apr 26, 2:02 am, Serge Rielau <srie...@ca.ibm.comwrote:
wugon....@gmail.com wrote:
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 ?

Since the triggers in DB2 for LUW are inlined this data simply deosn't
exist.
It's like asking for the execution time of views...

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Hi Serge,
Thanks your explain.

Apr 26 '07 #4
On Apr 26, 4:58 am, Knut Stolze <sto...@de.ibm.comwrote:
wugon....@gmail.com wrote:
We suffer some trigger issue

What's the issue?

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany
our report team challenge the db2 trigger transfer incorrect data
impact report result,
follow our data flow :
Tx1 -DB2 Base table -trigger to history table
another AP routine get history data and parse into back end db

report team found back end db data incorrect and challenge the db2
trigger.
We try to monitor trigger executing sql to find out the root cuase for
incorrect report.

Thanks you response.

Apr 26 '07 #5
wu*******@gmail.com wrote:
On Apr 26, 4:58 am, Knut Stolze <sto...@de.ibm.comwrote:
>wugon....@gmail.com wrote:
We suffer some trigger issue

What's the issue?

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

our report team challenge the db2 trigger transfer incorrect data
impact report result,
follow our data flow :
Tx1 -DB2 Base table -trigger to history table
another AP routine get history data and parse into back end db

report team found back end db data incorrect and challenge the db2
trigger.
We try to monitor trigger executing sql to find out the root cuase for
incorrect report.
If there is indeed incorrect data in the history table, then your trigger
would have a problem.

What you could monitor is the insert/update/delete statement issued against
the "DB2 base table" and also the activity on the history table. Then you
can match both (based on a timestamp, for instance) and figure out where
the problem is with the trigger.
--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany
Apr 26 '07 #6
On Apr 27, 4:45 am, Knut Stolze <sto...@de.ibm.comwrote:
wugon....@gmail.com wrote:
On Apr 26, 4:58 am, Knut Stolze <sto...@de.ibm.comwrote:
wugon....@gmail.com wrote:
We suffer some trigger issue
What's the issue?
--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany
our report team challenge the db2 trigger transfer incorrect data
impact report result,
follow our data flow :
Tx1 -DB2 Base table -trigger to history table
another AP routine get history data and parse into back end db
report team found back end db data incorrect and challenge the db2
trigger.
We try to monitor trigger executing sql to find out the root cuase for
incorrect report.

If there is indeed incorrect data in the history table, then your trigger
would have a problem.

What you could monitor is the insert/update/delete statement issued against
the "DB2 base table" and also the activity on the history table. Then you
can match both (based on a timestamp, for instance) and figure out where
the problem is with the trigger.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany- Hide quoted text -

- Show quoted text -
Hi Knut ,
Thanks your advice.

Apr 27 '07 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: WertmanTheMad | last post by:
Ok here goes, another odd SQL Question ....as always.. How do I get ... The value of a paramater passed to say a Trigger OR The SQL Itself Like this , say I have a Trigger set on delete, ...
2
by: datapro01 | last post by:
Running db2 8.1 on aix 5.1. We are testing some Quest database tools and have run into a situation where we keep getting the following error message when trying to use the SQL analysis tool. ...
2
by: Jack David | last post by:
Using the code below I am able to monitor a single directory for a new file and then kick-off a process to deal with the file. The question is??? How would I modify this code to be able to monitor...
1
by: chris | last post by:
Is there a way that i can monitor the use of a file in terms of a timestamp? My goal is to monitor the activity of a file and when it goes beyond 10 minutes of non activity I will shut it down.
4
by: John | last post by:
I'd like to write a programme that runs on a PC with two monitors. The application would be used in a shop, with one monitor for shop assistant and the other for the customer. The two must show...
4
by: Raj | last post by:
Can we create an event monitor for statements in a partitioned environment? CREATE EVENT MONITOR stmt_event FOR STATEMENTS WRITE TO FILE '/home/rajm/event' ON PARTITION 0 GLOBAL DB21034E ...
0
by: wugon.net | last post by:
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...
11
by: jonathan184 | last post by:
Hi I am trying to do a script to monitor a dir where files pass through approx every 5 mins but sometimes files may not come in for hours So the purpose of the script is to monitor by if no files...
0
jackb
by: jackb | last post by:
Hello there. I have setup triggers on MSMQ private queues I wanted to trace MSMQ Trigger activities for the whole day I tried using trigmon.exe but it shows trace log from the time...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

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.