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!
NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
This topic has been locked.
7 replies Latest Post - 2012-11-13T21:22:25Z by SystemAdmin
Pinned topic Failure to Deploy a UDF
Answered question This question has been answered.
Unanswered question This question has not been answered yet.
Updated on 2012-11-13T21:22:25Z at 2012-11-13T21:22:25Z by SystemAdmin
Re: Failure to Deploy a UDF2012-10-29T23:16:35Z in response to SystemAdminEmily,
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 188.8.131.52. Full Client.
Re: Failure to Deploy a UDF2012-10-30T19:10:07Z in response to SystemAdminAlex,
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)?
Re: Failure to Deploy a UDF2012-10-30T23:27:56Z in response to SystemAdminEmily,
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)
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.
Re: Failure to Deploy a UDF2012-11-13T21:22:25Z in response to SystemAdminAlex,
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?