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

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
    ACCEPTED ANSWER

    Re: Failure to Deploy a UDF

    ‏2012-10-29T23:08:28Z  in response to SystemAdmin
    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
      ACCEPTED ANSWER

      Re: Failure to Deploy a UDF

      ‏2012-10-29T23:16:35Z  in response to SystemAdmin
      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
        ACCEPTED ANSWER

        Re: Failure to Deploy a UDF

        ‏2012-10-29T23:18:00Z  in response to SystemAdmin
        Sorry, failed to mention that we are deploying UDFs in DB2 9.7.4 on Unix AIX
        Alex
      • SystemAdmin
        SystemAdmin
        1632 Posts
        ACCEPTED ANSWER

        Re: Failure to Deploy a UDF

        ‏2012-10-29T23:18:01Z  in response to SystemAdmin
        Sorry, failed to mention that we are deploying UDFs in DB2 9.7.4 on Unix AIX
        Alex
        • SystemAdmin
          SystemAdmin
          1632 Posts
          ACCEPTED ANSWER

          Re: Failure to Deploy a UDF

          ‏2012-10-30T19:10:07Z  in response to SystemAdmin
          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
            ACCEPTED ANSWER

            Re: Failure to Deploy a UDF

            ‏2012-10-30T23:27:56Z  in response to SystemAdmin
            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
              ACCEPTED ANSWER

              Re: Failure to Deploy a UDF

              ‏2012-11-13T21:22:25Z  in response to SystemAdmin
              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