Hi,

I'm new with DB2, I came from SQL Server so there is some kind of things that is really different.

I'm using DB2 8.2 on Windows.

I've to create an UDF that is called by a trigger, but this kind of implementation lock some dependencies. I cant fix my UDF because its being used by my trigger... or UDFs that use this function..

I think this is because of the BEGIN ATOMIC that cant be removed on a UDF/Trigger, at least when you need declare local variables...

Am I doing something wrong, or this is the implementation of DB2, I can understand why this happen but this turn the maintenance difficult to do, if necessary.

I tried to read a lot of references/redbooks/books about UDFs and stored procedures... but none gets deep on this topic...

Thanks for any help.

It is not because you have BEGIN ATOMIC in your UDF. There are objects that are dependent (references) your UDF. In order to "fix" (maintain/change) your UDF, you have to DROP it first the CREATE it the new way. But with the dependencies, you cannot drop it. You will need to drop the dependent objects (triggers, other UDFs, etc) first, then DROP your UDF. Then you recreate your "fixed" UDF and then all the objects you dropped that are dependent on it.

Andy

An alternative is to use external UDFs written in C or Java. There, you can replace the implementation without having to drop the dependent objects as long as the CREATE FUNCTION statement itself doesn't have to be changed.

Thanks for all responses..

@Andy

Yeah, this is the way that I'm doing stuffs... this is much work but it confirmed my thoughts..

@Stolze

Thanks for the reference.. I'll give a look to external UDFs... using external doesnt lock the compiled code in Java or C?