Topic
  • 14 replies
  • Latest Post - ‏2014-07-06T03:11:43Z by David_Zhu
Paul.Dunbar
Paul.Dunbar
8 Posts

Pinned topic Stored Procedure editor not deploying in 4.1.0.1

‏2014-01-14T20:24:05Z |

I recently upgraded to Data Studio client 4.1.0.1.  Since then, when I attempt to deploy a stored procedure using the stored procedure editor's Deploy icon, the SQL Results shows that the deploy completed successfully, but when I attempt to run or debug, I get an error message that the stored procedure's timestamp is mismatched with the database.  

 

If I close the editor and re-open the stored procedure from the data source explorer, I see that my changes were not deployed to the database.

If I open a new SQL Script and recreate the procedure with create or replace, the procedure is successfully replaced, but I have to right-click the procedure in the data source explorer and deploy it for debug.

 

I've tried wiping out my workspace folder and starting over, but I get the same issue.

Note:  This is with DB2 9.7 for Windows.

Any advice would be appreciated.

Updated on 2014-01-14T20:36:27Z at 2014-01-14T20:36:27Z by Paul.Dunbar
  • Equal
    Equal
    144 Posts

    Re: Stored Procedure editor not deploying in 4.1.0.1

    ‏2014-01-17T02:22:31Z  

    Hi Paul,

    Could you change the procedure name to one which is not deployed before to avoid conflict. And try deploy and run. There might be something wrong but we didn't capture. Could you please paste the execution result in the status tab? If you could share your SP with before and after version, that would be helpful.

    Thanks,

    Equal

     

  • LukeNumrych
    LukeNumrych
    55 Posts

    Re: Stored Procedure editor not deploying in 4.1.0.1

    ‏2014-01-17T16:25:13Z  
     

    Not trying to hijack the thread, but I am suffering a similar issue, so maybe I'll add my $.02

    Sometimes I can deploy my procedure, quit working for the day, come back to it the next day and I cannot deploy it because 

    "The signature provided in the definition for routine "...." matches the signature of some other routine."

    I have to manually drop the stored procedure in order to be able to re-deploy it.  The deployment will then work fine; however, I then often have to battle another issue - when I try to debug that stored procedure, the debug window will fail to display the parameters tab.  Of course trying to continue without the parameters set results in:

    "Debug: procname

     
    {call procname()}
    No authorized routine named "procname" of type "PROCEDURE" having compatible arguments was found.. SQLCODE=-440, SQLSTATE=42884, DRIVER=4.16.53
    Run of routine failed.
     - Roll back completed successfully.

    "

    Frustrating to say the least.  I am going to try to create a new stored procedure from scratch and just copy the DDL of the old one into it.

    BTW, this is DS 4.1, debugging remotely against DB2 10.1 fp 2 on Windows 2k8 R2.

     

    Updated on 2014-01-17T16:26:46Z at 2014-01-17T16:26:46Z by LukeNumrych
  • David_Zhu
    David_Zhu
    15 Posts

    Re: Stored Procedure editor not deploying in 4.1.0.1

    ‏2014-01-20T07:17:50Z  

    Hi,Paul,

    Could you paste all the message in the status and then we can check what's wrong for your deploy process and this problem happy on all the Stored Procedures in you workspace or just special Stored Procedures?

    Normally, if you already have procedure deployed on you LUW Server and then deploy again. it will generate a drop clause in your status message, if not and only this problem only happen on special routines? could you give me the routine or at least the signature part?

     

    Thanks.

  • David_Zhu
    David_Zhu
    15 Posts

    Re: Stored Procedure editor not deploying in 4.1.0.1

    ‏2014-01-20T07:20:10Z  
     

    Not trying to hijack the thread, but I am suffering a similar issue, so maybe I'll add my $.02

    Sometimes I can deploy my procedure, quit working for the day, come back to it the next day and I cannot deploy it because 

    "The signature provided in the definition for routine "...." matches the signature of some other routine."

    I have to manually drop the stored procedure in order to be able to re-deploy it.  The deployment will then work fine; however, I then often have to battle another issue - when I try to debug that stored procedure, the debug window will fail to display the parameters tab.  Of course trying to continue without the parameters set results in:

    "Debug: procname

     
    {call procname()}
    No authorized routine named "procname" of type "PROCEDURE" having compatible arguments was found.. SQLCODE=-440, SQLSTATE=42884, DRIVER=4.16.53
    Run of routine failed.
     - Roll back completed successfully.

    "

    Frustrating to say the least.  I am going to try to create a new stored procedure from scratch and just copy the DDL of the old one into it.

    BTW, this is DS 4.1, debugging remotely against DB2 10.1 fp 2 on Windows 2k8 R2.

     

    Hi,Luke,

    Could you give us the DDL of the routines which have these problems? you can sent me by notes "zhuzhih@cn.ibm.com". I think it's important for us to track the problem you met.

     

    Thanks.

  • Paul.Dunbar
    Paul.Dunbar
    8 Posts

    Re: Stored Procedure editor not deploying in 4.1.0.1

    ‏2014-01-20T18:13:30Z  
    • David_Zhu
    • ‏2014-01-20T07:17:50Z

    Hi,Paul,

    Could you paste all the message in the status and then we can check what's wrong for your deploy process and this problem happy on all the Stored Procedures in you workspace or just special Stored Procedures?

    Normally, if you already have procedure deployed on you LUW Server and then deploy again. it will generate a drop clause in your status message, if not and only this problem only happen on special routines? could you give me the routine or at least the signature part?

     

    Thanks.

    David and Equal,

    Thanks for your replies.  I'm afraid that I can't provide much more information than that.  I've rolled my installation back to 4.1.0.0 and things are working again.

     

    I can tell you that DS4.1.0.1 was reporting successful deployment (exactly as if everything went through fine), but after refreshing the Stored Procedures for the data source, the old version of the procedure was still in place.  

  • LukeNumrych
    LukeNumrych
    55 Posts

    Re: Stored Procedure editor not deploying in 4.1.0.1

    ‏2014-01-21T15:42:44Z  
    • David_Zhu
    • ‏2014-01-20T07:20:10Z

    Hi,Luke,

    Could you give us the DDL of the routines which have these problems? you can sent me by notes "zhuzhih@cn.ibm.com". I think it's important for us to track the problem you met.

     

    Thanks.

    David - I sent you an email.

  • David_Zhu
    David_Zhu
    15 Posts

    Re: Stored Procedure editor not deploying in 4.1.0.1

    ‏2014-01-22T08:39:06Z  

    David - I sent you an email.

    Thanks. when I find the reason. I'll let you know.

  • Doug.Partch
    Doug.Partch
    4 Posts

    Re: Stored Procedure editor not deploying in 4.1.0.1

    ‏2014-02-24T23:21:34Z  
    • David_Zhu
    • ‏2014-01-20T07:20:10Z

    Hi,Luke,

    Could you give us the DDL of the routines which have these problems? you can sent me by notes "zhuzhih@cn.ibm.com". I think it's important for us to track the problem you met.

     

    Thanks.

    In Data studio, Running User Defined function (SQL) that has a Case Statement will not allow the Input parameter screen or Debugger screen to start, when run from the Data Project Explorer.

     

     

     

     

    Running of User define function even though it said it worked user result in the following.

     

     

     

     

    However running the UDF from the Data Source Explorer works.

     

     

     

    Then again the Debugger will not work…  From the Data Source Explore.

     

  • Doug.Partch
    Doug.Partch
    4 Posts

    Re: Stored Procedure editor not deploying in 4.1.0.1

    ‏2014-02-24T23:23:53Z  
    • David_Zhu
    • ‏2014-01-20T07:20:10Z

    Hi,Luke,

    Could you give us the DDL of the routines which have these problems? you can sent me by notes "zhuzhih@cn.ibm.com". I think it's important for us to track the problem you met.

     

    Thanks.

    Here a simple DDL of where I add a CASE statement the default function.    I get hte same error everyone it is getting where the Parremetter screen will not show up.    If the change hte CASE statement to a IF statement everything works.  Fine....

     

    CREATE FUNCTION FUNCTION2( VARNAME VARCHAR(128) )

    RETURNS INTEGER

    VERSION V1

    DETERMINISTIC

    NO EXTERNAL ACTION

    READS SQL DATA

    BEGIN

    -- ##############################################################

    -- # Returns all tables created by ADMF001 and like VARNAME

    -- ##############################################################

    DECLARE VARCOUNT INTEGER;

    SELECT COUNT(*) INTO VARCOUNT FROM SYSIBM.SYSTABLES

    WHERE CREATOR = 'CSGSADM' AND NAME = VARNAME;

     

    CASE VARCOUNT

    WHEN 0

    THEN SET VARCOUNT = 999;

    WHEN 10

    THEN SET VARCOUNT = 5;

    ELSE SET VARCOUNT = 1;

    END CASE;

     

    RETURN (VARCOUNT);

    END

  • David_Zhu
    David_Zhu
    15 Posts

    Re: Stored Procedure editor not deploying in 4.1.0.1

    ‏2014-03-20T02:28:38Z  

    David and Equal,

    Thanks for your replies.  I'm afraid that I can't provide much more information than that.  I've rolled my installation back to 4.1.0.0 and things are working again.

     

    I can tell you that DS4.1.0.1 was reporting successful deployment (exactly as if everything went through fine), but after refreshing the Stored Procedures for the data source, the old version of the procedure was still in place.  

    Hi,Paul,

     

    Thanks, that's enough, we already resolve it and you can wait new release or request a PRM for this problem.

  • Fandino
    Fandino
    2 Posts

    Re: Stored Procedure editor not deploying in 4.1.0.1

    ‏2014-03-28T17:04:23Z  

    Any update regarding how to resolve this issue or when a fixpack will be available?

    This issue makes Data Studio 4.1.0.1 completely inoperative for data development.

    Regards

  • David.Zhu
    David.Zhu
    67 Posts

    Re: Stored Procedure editor not deploying in 4.1.0.1

    ‏2014-03-29T01:39:21Z  
    • Fandino
    • ‏2014-03-28T17:04:23Z

    Any update regarding how to resolve this issue or when a fixpack will be available?

    This issue makes Data Studio 4.1.0.1 completely inoperative for data development.

    Regards

    Hi,Fandino,

    This problem  is already fixed.  Before we find a public way for  installed users to get the patch between release cycle. you can either open a PMR if you are urgent or wait the new release.

    Thanks.

  • Trentation
    Trentation
    1 Post

    Re: Stored Procedure editor not deploying in 4.1.0.1

    ‏2014-07-04T20:26:39Z  
    • David.Zhu
    • ‏2014-03-29T01:39:21Z

    Hi,Fandino,

    This problem  is already fixed.  Before we find a public way for  installed users to get the patch between release cycle. you can either open a PMR if you are urgent or wait the new release.

    Thanks.

    I'm experiencing this problem, are we still to waiting for the new release? 

  • David_Zhu
    David_Zhu
    15 Posts

    Re: Stored Procedure editor not deploying in 4.1.0.1

    ‏2014-07-06T03:11:43Z  

    I'm experiencing this problem, are we still to waiting for the new release? 

    Yes, new release will coming soon, if you are urgent for patch, you can open a PMR.