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

    Unanswered: UDFs, Stored Procedures, and External Stored Procedures

    The DB2 manual (version 9.5 for LUW) doesn't mention Stored Procedures and External Stored Procedures -- at least not in the index -- and only mentions UDFs once with respect to working with views. The DB2 9 Fundamentals Certification Guide talks about all three but fails to sufficiently differentiate between them. For instance, the definition for a UDF of the type Scalar or Table is nearly identical to the definition of an External Stored Procedure. Googling the keywords hasn't turned up much.

    Can someone clear this up for me? What is/are the difference/s between the different routines?

  2. #2
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    chk tutorials for exam 733 and 735
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  3. #3
    Join Date
    May 2009
    Posts
    25
    chk tutorials for exam 733 and 735
    Many thanks.
    Last edited by db2farmer; 05-19-09 at 12:17.

  4. #4
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by db2farmer
    the definition for a UDF of the type Scalar or Table is nearly identical to the definition of an External Stored Procedure.
    They differ in the way they can be used (called).
    A scalar UDF can be called as any scalar built-in function, i.e., inside SQL statements where an expression is expected.
    A scalar UDF takes any number of arguments of any type, but must return a single scalar.
    A stored procedure can only be called by using the CALL statement.
    It does not return a value -- instead, some of its arguments (again scalars of "any" datatype) will be declared as "output" and will be able to return data to the caller.

    There is more to it than this, but consider this "the basics".
    Last edited by Peter.Vanroose; 05-20-09 at 17:54.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  5. #5
    Join Date
    May 2009
    Posts
    25
    Thanks. The references I have all seem to contradict each other on this point. One says a UDF cannot contain SQL, another says they can. One says a UDF cannot return a single row, another says they can. And Googling hasn't cleared things up either.
    Last edited by db2farmer; 05-20-09 at 10:30.

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    A yew years ago, UDFs couldn't contain SQL statements, that's true. But that has changed long ago.

    Scalar UDFs can only return a scalar value - no row comprised of multiple columns. (Since a scalar value is just a special case of a table with a single row and a single column, I wouldn't say that those functions don't return rows.)

    A table function returns a whole table.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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