Hi,
I am new to db2, although i have worked enough on Oracle before.
I need to know how to create new functions (DB2 call is udf).Typically i have to creae a function that take two varchar arguments, do some sql operations (basically few select things) and return a number.I want only sql to be there. Can somebody tell me entire process, pl assume i know nothing about db2.
I tried to search the net, but found no suitable document that addresses this need.

I will appreciate if someone spends his valuable time to answer this.
A code example will make things very clear.

Regards
Satyendra

Satyendra,

For further information, look in the SQL Reference for CREATE FUNCTION.


Some examples:

CREATE FUNCTION MySchema.MyFunction(var1 varchar(100),var2 varchar)
RETURNS integer language SQL NOT deterministic NO EXTERNAL ACTION READS SQL DATA CALLED ON NULL INPUT
BEGIN ATOMIC
DECLARE value integer;

IF var1 is not NULL
THEN SET (value) = (select col1 from MyTable where (col2 = var1);
THEN SET (value) = (select col3 from MyTable2 where (col4 = var2);

RETURN value;

END@


CREATE FUNCTION MySchema.MyFunction2 (var1 integer)
RETURNS integer language SQL NOT deterministic NO EXTERNAL ACTION READS SQL DATA
RETURN SELECT col1 from MyTable where col4 = var1);


HTH

Andy