How to Explain Triggers

DBFinder · #1 (**permalink**) 11-21-08, 06:43

Hello Everybody,

So far I used to test queries for timeron values with Visual Explain.

Now I need to write trigger and test for time cost for performance.

I searched online but could not find relevant info.

Following is my probable code and I tried Access Plan but Got error 'Function Sequence Error HY010.

Will someone help me learn how to use Explain while working with Triggers.

Code:

CREATE TRIGGER TRANS_INSERT
    AFTER INSERT ON TRANS
 	REFERENCING NEW AS N
 	FOR EACH ROW 
    MODE DB2SQL

 BEGIN ATOMIC

  IF N.TRANS_TYPE='I' and N.TRANS_STATUS = 'R' THEN
     
  			Update client_acc
  			 Set pending_payment_total = pending_payment_total - (
							              select amt
							  			  from TRANS
			          where TRANS.trans_num =N.trans_num),
				  pending_payment_count = pending_payment_count - 1
			 where	acc_num = N.ACC_NUM; 
  END IF;
  
  
  IF N.MARKED_STATUS='B' THEN

 

  
   Update client_acc
    Set charge_back_total = charge_back_total + ( 
                            select amt from TRANS
				where TRANS.trans_num =N.trans_num),
		charge_back_count = charge_back_count + 1
		where	acc_num = N.ACC_NUM;
  END IF;                     
   
  
 END @

DB2 V8.2 fp9 on windows 2003

Thanks
DBFinder

umayer · #2 (**permalink**) 11-21-08, 08:02

[...]
Update client_acc
Set pending_payment_total = pending_payment_total - (
select amt
from TRANS
where TRANS.trans_num =N.trans_num),
pending_payment_count = pending_payment_count - 1
where acc_num = N.ACC_NUM;
END IF;
[...]

I'm not sure, but it should be possible to replace the marked part of the query by "N.AMT" ?!

[...]
Update client_acc
Set pending_payment_total = pending_payment_total - N.AMT,
pending_payment_count = pending_payment_count - 1
where acc_num = N.ACC_NUM;
END IF;
[...]

I know, this doesn't answer your question, but it might simplify your trigger

n_i · #3 (**permalink**) 11-21-08, 08:58

Trigger is never executed by itself; it is compiled into the execution plan of the statement that triggers it, in your case an insert into TRANS. To see the complete plan, explain the insert statement.

DBFinder · #4 (**permalink**) 11-21-08, 09:03

Quote:

Originally Posted by umayer

[...]
Update client_acc
Set pending_payment_total = pending_payment_total - (
select amt
from TRANS
where TRANS.trans_num =N.trans_num),
pending_payment_count = pending_payment_count - 1
where acc_num = N.ACC_NUM;
END IF;
[...]

I'm not sure, but it should be possible to replace the marked part of the query by "N.AMT" ?!

[...]
Update client_acc
Set pending_payment_total = pending_payment_total - N.AMT,
pending_payment_count = pending_payment_count - 1
where acc_num = N.ACC_NUM;
END IF;
[...]

I know, this doesn't answer your question, but it might simplify your trigger

Super !
This was done and I emailed to develpment team - for the sake of performance. They did not listen to me.-- Definitely you are right !!

DBFinder · #5 (**permalink**) 11-21-08, 09:06

Quote:

Originally Posted by n_i

Trigger is never executed by itself; it is compiled into the execution plan of the statement that triggers it, in your case an insert into TRANS. To see the complete plan, explain the insert statement.

Thanks for you clue.

Yes I agree. The trigger action should be used as a statement to have it explained !

I never did it before, so wanted have an advice.

stolze · #6 (**permalink**) 11-24-08, 04:25

That is also pointless. The triggered action is compiled into the triggering SQL statement and an overall optimization is applied. Thus, the result could be completely different compared to just explaining the triggered action. What am I getting at? Explain the INSERT statement with the trigger being defined to see what is going on.

Internet Services

Web Development

Digital Marketing

Consumer Tech