Execute Immediate in UDF

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • jabernet@swissasp.ch
    #1

    Execute Immediate in UDF

    In DB2 LUW 'till Version 9 it is not possible to execute dynamic SQL
    in a UDF (at least as I understand it; maybe I'm just doing something
    wrong). Is this planed to be included in Future Versions? Somebody has
    an Idea for a workaround to this?

    What I need to do: We have for different customers an individual
    customer table, and we'd like a table UDF to return the records of a
    customer table based on the ID of a customer. This would allow not to
    have to generate dynamic SQL for joining the tables in the
    application, but just to call a function which does this for us.

    Any ideas or hints would be appreciated.

    Regards,
    Janick
    Tags: None
    • Serge Rielau
      #2
      Re: Execute Immediate in UDF

      jabernet@swissa sp.ch wrote:
      In DB2 LUW 'till Version 9 it is not possible to execute dynamic SQL
      in a UDF (at least as I understand it; maybe I'm just doing something
      wrong). Is this planed to be included in Future Versions?
      Future? Yes I can confirm something that fuzzily :-)
      Somebody has an Idea for a workaround to this?
      Create a procedure with the dynamic SQL in it.
      Then CALL the procedure from the UDF.
      Now a quick test reveals that PREPARE/EXECUTE is treated as MODIFIES SQL
      DATA, so you may have to go with a TABLE UDF, since scalar UDF do not
      support MODIFIES SQL DATA.

      Cheers
      Serge
      --
      Serge Rielau
      DB2 Solutions Development
      IBM Toronto Lab

        Comment

        • jabernet@swissasp.ch
          #3
          Re: Execute Immediate in UDF

          On Mar 2, 10:52 pm, Serge Rielau <srie...@ca.ibm .comwrote:
          jaber...@swissa sp.ch wrote:
          In DB2 LUW 'till Version 9 it is not possible to execute dynamic SQL
          in a UDF (at least as I understand it; maybe I'm just doing something
          wrong). Is this planed to be included in Future Versions?
          >
          Future? Yes I can confirm something that fuzzily :-)
          >
          Somebody has an Idea for a workaround to this?
          >
          Create a procedure with the dynamic SQL in it.
          Then CALL the procedure from the UDF.
          Now a quick test reveals that PREPARE/EXECUTE is treated as MODIFIES SQL
          DATA, so you may have to go with a TABLE UDF, since scalar UDF do not
          support MODIFIES SQL DATA.
          >
          Cheers
          Serge
          --
          Serge Rielau
          DB2 Solutions Development
          IBM Toronto Lab
          Thanks for the fast response Serge. That's pretty much what I figured
          I'll have to do.
          Good to know though it will once be simplified in the far future :)

          Regards,
          Janick

            Comment

            Previous template Next
            Working...