Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655

    Unanswered: How to Explain Triggers

    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
    Last edited by DBFinder; 11-21-08 at 07:48.

  2. #2
    Join Date
    Dec 2005
    Posts
    273
    [...]
    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
    Last edited by umayer; 11-21-08 at 09:06.

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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.
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    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 !!

  5. #5
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    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.

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •