Topic
  • 7 replies
  • Latest Post - ‏2012-11-13T21:22:25Z by SystemAdmin
SystemAdmin
SystemAdmin
1632 Posts

Pinned topic Failure to Deploy a UDF

‏2012-10-29T22:55:08Z |
Is it a bug or am I missing something? Unlike deploying Stored Procedures, when deploying a UDF in IBM Data Studio 3.1.1., it fails if I don't drop the existing UDF prior to deployment. This happens despite checking the checkbox indicating dropping the duplicate.

This has never been an issue in DB2 Development Center and it works fine in Data Studio for procedures. What am I missing here?

Thanks for the help!

Alexander Ashkenazi
Updated on 2012-11-13T21:22:25Z at 2012-11-13T21:22:25Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    1632 Posts

    Re: Failure to Deploy a UDF

    ‏2012-10-29T23:08:28Z  
    Hi Alexander,

    Can you provide more info about the UDF? Such as, what its language; what's the database type and version, etc. Thanks!

    Emily
  • SystemAdmin
    SystemAdmin
    1632 Posts

    Re: Failure to Deploy a UDF

    ‏2012-10-29T23:16:35Z  
    Hi Alexander,

    Can you provide more info about the UDF? Such as, what its language; what's the database type and version, etc. Thanks!

    Emily
    Emily,

    This did not happen when deploying just one specific UDF (I am talking about SQL table UDFs here). This happens every time I am deploying a UDF. And my colleagues are telling me they have noticed the same behaviour. We are all using IBM Data Studio 3.1.1.0. Full Client.

    Thank you,
    Alex
  • SystemAdmin
    SystemAdmin
    1632 Posts

    Re: Failure to Deploy a UDF

    ‏2012-10-29T23:18:00Z  
    Emily,

    This did not happen when deploying just one specific UDF (I am talking about SQL table UDFs here). This happens every time I am deploying a UDF. And my colleagues are telling me they have noticed the same behaviour. We are all using IBM Data Studio 3.1.1.0. Full Client.

    Thank you,
    Alex
    Sorry, failed to mention that we are deploying UDFs in DB2 9.7.4 on Unix AIX
    Alex
  • SystemAdmin
    SystemAdmin
    1632 Posts

    Re: Failure to Deploy a UDF

    ‏2012-10-29T23:18:01Z  
    Emily,

    This did not happen when deploying just one specific UDF (I am talking about SQL table UDFs here). This happens every time I am deploying a UDF. And my colleagues are telling me they have noticed the same behaviour. We are all using IBM Data Studio 3.1.1.0. Full Client.

    Thank you,
    Alex
    Sorry, failed to mention that we are deploying UDFs in DB2 9.7.4 on Unix AIX
    Alex
  • SystemAdmin
    SystemAdmin
    1632 Posts

    Re: Failure to Deploy a UDF

    ‏2012-10-30T19:10:07Z  
    Sorry, failed to mention that we are deploying UDFs in DB2 9.7.4 on Unix AIX
    Alex
    Alex,

    I have tried with 2 default table UDFs on DB2 9.7.4 on AIX. I didn't run into the problem you have yet. What error message in the SQL Results View did you get when deploy failed? Did you deploy UDF in Data Project Explorer or in Data Source Explorer? Can you drop the UDF from Data Source Explorer (expand the connection tree, locate the UDF, then right click and select Drop)?

    Thanks,
    Emily
  • SystemAdmin
    SystemAdmin
    1632 Posts

    Re: Failure to Deploy a UDF

    ‏2012-10-30T23:27:56Z  
    Alex,

    I have tried with 2 default table UDFs on DB2 9.7.4 on AIX. I didn't run into the problem you have yet. What error message in the SQL Results View did you get when deploy failed? Did you deploy UDF in Data Project Explorer or in Data Source Explorer? Can you drop the UDF from Data Source Explorer (expand the connection tree, locate the UDF, then right click and select Drop)?

    Thanks,
    Emily
    Emily,

    We are deploying UDF from the Routine Editor, where we get, for example, from the data Source Explorer.
    We choose "Deploy the Routine to the Database Server" --> (check "Drop Duplicates" on the next page) --> Finish.

    Occasionally, everything completes fine, especially on the first execution of deployment after establishing connection with the DB. However, the majority times, here's what we get (*despite choosing "Drop Duplicates"*)

    Deploy FCFM_UDF.TV_SNAPSHOT(INTEGER, DATE ) RETURNS TABLE (i_tv_san INTEGER, INTEGER, INTEGER, VARCHAR(50), SMALLINT, CHAR(2), CHAR(10), SMALLINT, CHAR(2), CHAR(2), CHAR(2), CHAR(2), CHAR(2), INTEGER, CHAR(4), CHAR(4), SMALLINT, CHAR(3), INTEGER, INTEGER, INTEGER, INTEGER, SMALLINT, CHAR(2), CHAR(3), CHAR(4), VARCHAR(35), CHAR(7), VARCHAR(20), VARCHAR(45), CHAR(2), CHAR(3), CHAR(3), VARCHAR(125), CHAR(5), VARCHAR(20), VARCHAR(45), CHAR(2), SMALLINT, CHAR(10), INTEGER, CHAR(1), CHAR(3), CHAR(3), CHAR(10), CHAR(10), CHAR(10), INTEGER)

    Running

    FCFM_UDF.TV_SNAPSHOT - Deploy started.

    Create user-defined function returns SQLCODE: -456, SQLSTATE: 42710.

    FCFM_UDF.TV_SNAPSHOT: 172: In the definition of routine "FCFM_UDF.TV_SNAPSHOT", the SPECIFIC name "TV_SNAPSHOT_V3" already exists in the schema or module.. SQLCODE=-456, SQLSTATE=42710, DRIVER=3.63.108

    In the definition of routine "FCFM_UDF.TV_SNAPSHOT", the SPECIFIC name "TV_SNAPSHOT_V3" already exists in the schema or module.. SQLCODE=-456, SQLSTATE=42710, DRIVER=3.63.108

    FCFM_UDF.TV_SNAPSHOT - Deploy failed.

    FCFM_UDF.TV_SNAPSHOT - Roll back completed successfully.
    I will try to establish a pattern (or the lack of such) as of when I am able to deploy and when it fails. Burt, again, it fails much more often than not.

    And, of course, I can always drop the existing UDF myself and then re-deploy the UDF, which is what I am doing now all the times. It's inconvenient, but it works. The question is, why the Data Studio fails to drop the existing UDF (same signature, same SPECIFIC NAME) on its own, despite me choosing "Drop Duplicates" option.

    Thank You,

    Alex.
  • SystemAdmin
    SystemAdmin
    1632 Posts

    Re: Failure to Deploy a UDF

    ‏2012-11-13T21:22:25Z  
    Emily,

    We are deploying UDF from the Routine Editor, where we get, for example, from the data Source Explorer.
    We choose "Deploy the Routine to the Database Server" --> (check "Drop Duplicates" on the next page) --> Finish.

    Occasionally, everything completes fine, especially on the first execution of deployment after establishing connection with the DB. However, the majority times, here's what we get (*despite choosing "Drop Duplicates"*)

    Deploy FCFM_UDF.TV_SNAPSHOT(INTEGER, DATE ) RETURNS TABLE (i_tv_san INTEGER, INTEGER, INTEGER, VARCHAR(50), SMALLINT, CHAR(2), CHAR(10), SMALLINT, CHAR(2), CHAR(2), CHAR(2), CHAR(2), CHAR(2), INTEGER, CHAR(4), CHAR(4), SMALLINT, CHAR(3), INTEGER, INTEGER, INTEGER, INTEGER, SMALLINT, CHAR(2), CHAR(3), CHAR(4), VARCHAR(35), CHAR(7), VARCHAR(20), VARCHAR(45), CHAR(2), CHAR(3), CHAR(3), VARCHAR(125), CHAR(5), VARCHAR(20), VARCHAR(45), CHAR(2), SMALLINT, CHAR(10), INTEGER, CHAR(1), CHAR(3), CHAR(3), CHAR(10), CHAR(10), CHAR(10), INTEGER)

    Running

    FCFM_UDF.TV_SNAPSHOT - Deploy started.

    Create user-defined function returns SQLCODE: -456, SQLSTATE: 42710.

    FCFM_UDF.TV_SNAPSHOT: 172: In the definition of routine "FCFM_UDF.TV_SNAPSHOT", the SPECIFIC name "TV_SNAPSHOT_V3" already exists in the schema or module.. SQLCODE=-456, SQLSTATE=42710, DRIVER=3.63.108

    In the definition of routine "FCFM_UDF.TV_SNAPSHOT", the SPECIFIC name "TV_SNAPSHOT_V3" already exists in the schema or module.. SQLCODE=-456, SQLSTATE=42710, DRIVER=3.63.108

    FCFM_UDF.TV_SNAPSHOT - Deploy failed.

    FCFM_UDF.TV_SNAPSHOT - Roll back completed successfully.
    I will try to establish a pattern (or the lack of such) as of when I am able to deploy and when it fails. Burt, again, it fails much more often than not.

    And, of course, I can always drop the existing UDF myself and then re-deploy the UDF, which is what I am doing now all the times. It's inconvenient, but it works. The question is, why the Data Studio fails to drop the existing UDF (same signature, same SPECIFIC NAME) on its own, despite me choosing "Drop Duplicates" option.

    Thank You,

    Alex.
    Alex,

    Once you manually drop a UDF, can you deploy this UDF multiple times without encountering the problem any more?

    How was the UDF created originally? Was it created by using an older version of DS or a different tool? I still couldn't reproduce the problem after couple of tries within Data Source Explorer and Routine Editor, when I created the UDF using DS.

    Here is one of the cases that I have tried:
    1. In Data Perspective, made connection within Data Source Explorer. I created and deployed a UDF in the Data Project Explorer.
    2. In Data Source Explorer, expand to the UDF. Right click and select Open with Routine Editor.
    3. Click on the Deploy icon.
    4. Deploy wizard is up. Drop Duplicates is selected.
    5. Click Finish. Deploy was OK.

    Another thing I would like to confirm is the Deploy wizard. Looks like the "Drop Duplicates" option on the 2nd page for you --(check "Drop Duplicates" on the next page). Is it true? I have it on the first page in my DS3.1.1. If it's the case, can you check your DS version?

    Thanks,
    Emily