Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2003
    Posts
    10

    Question Unanswered: creating a function in db2

    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

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

Posting Permissions

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