How to display source of DB2 SQL UDF

Question

Is there any way to see SQL UDF body in DB2 control center?

Answer 1

Hi,

You can query the catalog views in DB2 to find the source of your UDF.

These views change slightly depending on which version of DB2 you are using.

You can try viewing the TEXT column of SYSCAT.ROUTINES.

Answer 2

ummmm ... is that a no?

Answer 3

I don't use Control Center much because it doesn't do as much as IBM Data Studio or third party DBA/app dev tools for DB2. As of DB2 9.5, there isn't an option in Control Center to show the DDL for procedures and UDFs. Data Studio has the Generate DDL option, though. You wouldn't know from looking at its web pages, but IBM is still offering a no-cost version of Data Studio Administrator that handles basic examination and management of DB2 objects.

The details about Data Studio Administrator are summarized by a couple of IBMers in this forum post: http://www.ibm.com/developerworks/forums/thread.jspa?threadID=263555&tstart=0

The other option is to follow Mark S's recommendation and pull TEXT directly from SYSCAT.ROUTINES.

Answer 4

-- To get the text of UDF
select r.routinename as FunctionName, r.text as FunctionBody
from syscat.routines r
where r.routinetype = 'F' -- Function
and r.origin = 'U' -- User defined