I am using db2 v8.2.2 and aix 5.2

I used db2 syscat.routine and select BODY columns but did not show me anything and it was empty, please help

Select text from syscat.routines should give you the text for SQL routines.
If theay are not SQL routines, there is no way to query the DB to get this that I know of.

Andy

As you know TEXT is clob column and I know I have UDF like:

create function xxxxxx(4000) for bit data)
returns varchar(4000)
specific cbchr
external name 'yyyyyy!bbbb'
language C
parameter style DB2SQL
not variant
fenced
not null call
no sql
no external action
no scratchpad
no final call;


I expect to see this body, how can I see that?

Here is an example using the CLP:

LCAD-->select text from syscat.routines where routinename = 'UDF_NAME'

TEXT








------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------
CREATE FUNCTION PRODUCTION.UDF_NAME(LAST VARCHAR(25), FIRST VARCHAR(20), MIDDLE VARCHAR(15), SUFFIX VARCHAR(10))
RETURNS VARCHAR(75) LANGUAGE SQL DETERMINISTIC NO EXTERNAL ACTION CALLED ON NULL INPUT
BEGIN ATOMIC
DECLARE OUTTEXT VARCHAR(75);

SET OUTTEXT = '';

IF (LAST IS NOT NULL)
THEN SET OUTTEXT = RTRIM(LAST) || ', ';
END IF;

IF (FIRST IS NOT NULL)
THEN SET OUTTEXT = OUTTEXT || RTRIM(FIRST) || ' ';
END IF;

IF (MIDDLE IS NOT NULL)
THEN SET OUTTEXT = OUTTEXT || RTRIM(MIDDLE) || ' ';
END IF;

IF (SUFFIX IS NOT NULL)
THEN SET OUTTEXT = OUTTEXT || RTRIM(SUFFIX);
END IF;

RETURN OUTTEXT;
END








1 record(s) selected.

LCAD-->

Andy

it does not show me the body of my UFD because the language is c but yours is SQL, am I right ? is there any way I can see the bodies of my UDF or SP.

Thank you so much for your help.

As I stated in my first reply, you cannot get the text of non-SQL routines from syscat. You maybe able to derive the "CREATE FUNCTION" statement from syscat.routines.

Andy

Thank you so much Andy for your help.