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?

chk tutorials for exam 733 and 735

Many thanks.

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".

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.

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.