Topic
  • 5 replies
  • Latest Post - ‏2013-12-03T09:16:52Z by SachinHCL
carlolunghi
carlolunghi
11 Posts

Pinned topic How to debug trigger on DS 4.1

‏2013-06-25T22:45:38Z |

Dear Forum,
I'm partecipating in the development of stored procedure and trigger sql blocks by using  DataStudio as IDE. I have troubles in debugging trigger code, more precisely when I need to set breakpoint and execute step by step. The paragraph "Developing Triggers" in http://pic.dhe.ibm.com/infocenter/dstudio/v4r1/index.jsp appears to me not clear to follow.
Could someone give to me any suggestion ?

Furtherly, is the "SQL Procedural Languages: Application Enablement and Support" the only IBM guide to follow? Anyway, titles of books and practical manuals are appreciated.

Thanks and regards.

c.l.

 

  • David_Zhu
    David_Zhu
    15 Posts
    ACCEPTED ANSWER

    Re: How to debug trigger on DS 4.1

    ‏2013-07-04T02:29:50Z  

    Dear David,

    I've appreciated your response, read it many times and executed few tests  but unfortunately I don't succeed into setting one breakpoint inside the (begin atomic - end) body of the trigger.
    The starting point is simple, both in the application than in a test code we have a schema and a table, on which we insert one record;
    a trigger is created in the Data Source Explorer (connection to db -> Schema -> Tables -> Create Trigger: a plan is generated, executed without error; therefore the trigger has been deployed.
    The test makes an insert the 2nd, 3rd, etc. record into the table so far the trigger runs and we can debug it, exactly as recomended by you: <<Then you can choose the created trigger and select the context menu "open with routine editor" to open the trigger,  redeploy it through the deploy button at the top right corner to deploy it in debug model>>.
    Done: It appears correct, the window of the routine editor is here in front of me.
    ... and then you can add breakpoint as other procedure or UDF: where ? in which perspective or window? and how ?
    ... <<Open the sql editor, input one sql which can fire the trigger, select this sql, click the debug button on the top right of sql editor, then it can launch the trigger debug session....>>
    Done, but really the opened panel "Inizializing debug session - starting Session Manager" enters a loop condition and remains blocked (**).
    Note that if you execute the insert from Squirrel the trigger starts and work correctly but without any breakpoint ( *).
    Therefore I think that the problem (*) arises due the not correctly set of debug mode (**) .
    Could you please give me a further comment ?
    Mant thanks,

    c.l.

    Hi,C.L.

    The trigger debuggable can't be inline trigger, from your describe, your trigger include "begin atomic... end", the key work "atomic" means this trigger is inline trigger and can't be debuggable. you can only include you trigger logic in "begin - end"

    When you deploy trigger, you must check the checkbox in the second page of deploy wizard. when finish deploy, you can open the trigger with routine editor and then add the breakpoint in routine editor. For those debugger triggers, you can look a mark to tell you which place can add breakpoint.

    After you add breakpoint you can try debug again in the SQL editor to trigger the debugger process.

    Before you debug trigger, you can first try to debug a procedure to check whehter you network  work as the debugger needed, since it communicte with server through socket, so the firewall or some security software maybe block it.

    Thanks.

  • David.Zhu
    David.Zhu
    67 Posts

    Re: How to debug trigger on DS 4.1

    ‏2013-06-26T16:35:58Z  

    Hi,C.L.

    Now we don't support directly create trigger in routine editor, so you can create a trigger in SQL editor or in the Data Source Explore, navigate to triggers directory through <server>-><Database>->schemas-><schema>->tables-<table>-triggers. In the context menu, choose "create" menu to create a trigger. 

    Then you can choose the created trigger and select the context menu "open with routine editor" to open the trigger, redeploy it through the deploy button at the top right corner to deploy it in debug model and then you can add breakpoint as other procedure or UDF.

    Open the sql editor, input one sql which can fire the trigger, select this sql, click the debug button on the top right of sql editor, then it can launch the trigger debug session.

    Now trigger debugger only support DB2 V10 fp2 and above. At same time, the trigger must non inline trigger which means the trigger body must bin included in begin and end as following.

     

    create trigger test_trigger  after insert on test for each row
    begin
    declare i integer;
    set i = 10;
    end@

    expected above can help you. 

    Thanks.

  • carlolunghi
    carlolunghi
    11 Posts

    Re: How to debug trigger on DS 4.1

    ‏2013-07-03T21:00:16Z  
    • David.Zhu
    • ‏2013-06-26T16:35:58Z

    Hi,C.L.

    Now we don't support directly create trigger in routine editor, so you can create a trigger in SQL editor or in the Data Source Explore, navigate to triggers directory through <server>-><Database>->schemas-><schema>->tables-<table>-triggers. In the context menu, choose "create" menu to create a trigger. 

    Then you can choose the created trigger and select the context menu "open with routine editor" to open the trigger, redeploy it through the deploy button at the top right corner to deploy it in debug model and then you can add breakpoint as other procedure or UDF.

    Open the sql editor, input one sql which can fire the trigger, select this sql, click the debug button on the top right of sql editor, then it can launch the trigger debug session.

    Now trigger debugger only support DB2 V10 fp2 and above. At same time, the trigger must non inline trigger which means the trigger body must bin included in begin and end as following.

     

    create trigger test_trigger  after insert on test for each row
    begin
    declare i integer;
    set i = 10;
    end@

    expected above can help you. 

    Thanks.

    Dear David,

    I've appreciated your response, read it many times and executed few tests  but unfortunately I don't succeed into setting one breakpoint inside the (begin atomic - end) body of the trigger.
    The starting point is simple, both in the application than in a test code we have a schema and a table, on which we insert one record;
    a trigger is created in the Data Source Explorer (connection to db -> Schema -> Tables -> Create Trigger: a plan is generated, executed without error; therefore the trigger has been deployed.
    The test makes an insert the 2nd, 3rd, etc. record into the table so far the trigger runs and we can debug it, exactly as recomended by you: <<Then you can choose the created trigger and select the context menu "open with routine editor" to open the trigger,  redeploy it through the deploy button at the top right corner to deploy it in debug model>>.
    Done: It appears correct, the window of the routine editor is here in front of me.
    ... and then you can add breakpoint as other procedure or UDF: where ? in which perspective or window? and how ?
    ... <<Open the sql editor, input one sql which can fire the trigger, select this sql, click the debug button on the top right of sql editor, then it can launch the trigger debug session....>>
    Done, but really the opened panel "Inizializing debug session - starting Session Manager" enters a loop condition and remains blocked (**).
    Note that if you execute the insert from Squirrel the trigger starts and work correctly but without any breakpoint ( *).
    Therefore I think that the problem (*) arises due the not correctly set of debug mode (**) .
    Could you please give me a further comment ?
    Mant thanks,

    c.l.

  • David_Zhu
    David_Zhu
    15 Posts

    Re: How to debug trigger on DS 4.1

    ‏2013-07-04T02:29:50Z  

    Dear David,

    I've appreciated your response, read it many times and executed few tests  but unfortunately I don't succeed into setting one breakpoint inside the (begin atomic - end) body of the trigger.
    The starting point is simple, both in the application than in a test code we have a schema and a table, on which we insert one record;
    a trigger is created in the Data Source Explorer (connection to db -> Schema -> Tables -> Create Trigger: a plan is generated, executed without error; therefore the trigger has been deployed.
    The test makes an insert the 2nd, 3rd, etc. record into the table so far the trigger runs and we can debug it, exactly as recomended by you: <<Then you can choose the created trigger and select the context menu "open with routine editor" to open the trigger,  redeploy it through the deploy button at the top right corner to deploy it in debug model>>.
    Done: It appears correct, the window of the routine editor is here in front of me.
    ... and then you can add breakpoint as other procedure or UDF: where ? in which perspective or window? and how ?
    ... <<Open the sql editor, input one sql which can fire the trigger, select this sql, click the debug button on the top right of sql editor, then it can launch the trigger debug session....>>
    Done, but really the opened panel "Inizializing debug session - starting Session Manager" enters a loop condition and remains blocked (**).
    Note that if you execute the insert from Squirrel the trigger starts and work correctly but without any breakpoint ( *).
    Therefore I think that the problem (*) arises due the not correctly set of debug mode (**) .
    Could you please give me a further comment ?
    Mant thanks,

    c.l.

    Hi,C.L.

    The trigger debuggable can't be inline trigger, from your describe, your trigger include "begin atomic... end", the key work "atomic" means this trigger is inline trigger and can't be debuggable. you can only include you trigger logic in "begin - end"

    When you deploy trigger, you must check the checkbox in the second page of deploy wizard. when finish deploy, you can open the trigger with routine editor and then add the breakpoint in routine editor. For those debugger triggers, you can look a mark to tell you which place can add breakpoint.

    After you add breakpoint you can try debug again in the SQL editor to trigger the debugger process.

    Before you debug trigger, you can first try to debug a procedure to check whehter you network  work as the debugger needed, since it communicte with server through socket, so the firewall or some security software maybe block it.

    Thanks.

  • carlolunghi
    carlolunghi
    11 Posts

    Re: How to debug trigger on DS 4.1

    ‏2013-07-16T09:35:28Z  
    • David_Zhu
    • ‏2013-07-04T02:29:50Z

    Hi,C.L.

    The trigger debuggable can't be inline trigger, from your describe, your trigger include "begin atomic... end", the key work "atomic" means this trigger is inline trigger and can't be debuggable. you can only include you trigger logic in "begin - end"

    When you deploy trigger, you must check the checkbox in the second page of deploy wizard. when finish deploy, you can open the trigger with routine editor and then add the breakpoint in routine editor. For those debugger triggers, you can look a mark to tell you which place can add breakpoint.

    After you add breakpoint you can try debug again in the SQL editor to trigger the debugger process.

    Before you debug trigger, you can first try to debug a procedure to check whehter you network  work as the debugger needed, since it communicte with server through socket, so the firewall or some security software maybe block it.

    Thanks.

    Finally we moved to the first phase of production, and the chaotic mass of things to do is decreasing.

    Thanks to David, we did debug of triggers just following the described steps.

    The issue can be closed.

    c.l.

  • SachinHCL
    SachinHCL
    1 Post

    Re: How to debug trigger on DS 4.1

    ‏2013-12-03T09:16:52Z  
    • David.Zhu
    • ‏2013-06-26T16:35:58Z

    Hi,C.L.

    Now we don't support directly create trigger in routine editor, so you can create a trigger in SQL editor or in the Data Source Explore, navigate to triggers directory through <server>-><Database>->schemas-><schema>->tables-<table>-triggers. In the context menu, choose "create" menu to create a trigger. 

    Then you can choose the created trigger and select the context menu "open with routine editor" to open the trigger, redeploy it through the deploy button at the top right corner to deploy it in debug model and then you can add breakpoint as other procedure or UDF.

    Open the sql editor, input one sql which can fire the trigger, select this sql, click the debug button on the top right of sql editor, then it can launch the trigger debug session.

    Now trigger debugger only support DB2 V10 fp2 and above. At same time, the trigger must non inline trigger which means the trigger body must bin included in begin and end as following.

     

    create trigger test_trigger  after insert on test for each row
    begin
    declare i integer;
    set i = 10;
    end@

    expected above can help you. 

    Thanks.

    Thanks a lot David ! That worked for me.

    Sincerely, Sachin