Results 1 to 6 of 6
  1. #1
    Join Date
    May 2009
    Posts
    5

    Unanswered: Converting a UDF into a Stored Procedure

    I have a UDB function which is currently returning values within a select statement. No problem until I was asked to enhance this. The UDF selects from a table using a value passed in. If a value is found the corresponding field is returned. If the value is not found - the same value is returned.

    I need to enhance this - when the value coming in is not found - I need to add it to an "empty slot" which is set up ahead of time. I will then create a new empty slot - for use later with this incoming value.

    example - underlying table has
    col1 col2
    A B
    C
    you call the UDF with A as the parameter and B is returned. If you call with X - then X is not found.
    The enhancement would "partner" X with C via and update statement. Then it would insert X without any value in col1 ..... and so on
    The result would be
    col1 col2
    A B
    X C
    X

    I don't know of a way to perform inserts/updates within a function. If there is a way I would like to know how. Otherwise I think I need to convert this into a stored procedure. Which I have done - however - how do I convert the existing SQL to work using the SP instead of the UDF ??

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You can do data modifications in table UDFs.

    Aside from that, a conversion to a stored procedure is pretty much straight forward. The major difference between both is how you use a UDF vs. an SP, i.e. in a query or insert/update/delete statement vs. the CALL statement.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    May 2009
    Posts
    5

    Convertnig UDF to table Function

    I changed my UDF to a table funtion.
    However I can no longer call this UDF using
    "select scramb.scrambfn('MICHAEL',0) from sysibm.sysdummy1"
    I must now use

    "select ta.*,tb.* from table ( scramb.scrambfnt('MICHAEL',0)) as ta "
    This is ok - EXCEPT -
    I need to call this multiple times
    something like this :
    $=> db2 "select ta.*,tb.* from table ( scramb.scrambfnt('MICHAEL',0)) as ta ,table ( scramb.scrambfnt('JOHN',0)) as tb"

    However I get his error when I do.
    SQL20267N The function "SCRAMB.SCRAMBFNT" (specific "SQL090610150002100")
    modifies SQL data and is invoked in an illegal context. Reason code = "1".
    SQLSTATE=429BL

    Any suggestions?

  4. #4
    Join Date
    Dec 2008
    Posts
    76
    Did you include the MODIFIES SQL DATA statement in the declaration? Plus, I'd change that join to a union.
    Last edited by rdutton; 06-10-09 at 17:13.

  5. #5
    Join Date
    May 2009
    Posts
    5
    Yes I did include modifies sql data. This works if I call it once - works great.

    This works
    select ta.*,' ' from table ( scramb.scrambfnt('MICHAEL',0)) as ta
    ;
    select ' ',tb.* from table ( scramb.scrambfnt('JOHN',0)) as tb
    ;

    This does not work :
    select given_name_one ,ta.* from nucleus.personname
    ,table ( scramb.scrambfnt(GIVEN_name_one,0)) as ta
    where person_name_id = 10001
    ;

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If "does not work" means getting syntax error, you can try:
    select given_name_one ,ta.*
    from nucleus.personname as pn
    ,table ( scramb.scrambfnt(pn.GIVEN_name_one,0)) as ta
    where person_name_id = 10001
    ;

Posting Permissions

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