Results 1 to 4 of 4
  1. #1
    Join Date
    May 2009

    Unanswered: UDFs and Triggers Atomic nature


    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.

  2. #2
    Join Date
    Jan 2003
    Provided Answers: 5
    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.


  3. #3
    Join Date
    Jan 2007
    Jena, Germany
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    May 2009
    Thanks for all responses..


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


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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts