Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2002
    Posts
    8

    Unanswered: Any UDF Example ?

    Hello All !
    I am new with DB2. I would appreciate very much if anyone can give me a working example to create and make it work with DB2 a User defined Function in Java or OLE with Visual Basic. I am using DB2 in WIN2000.

    Thanks a lot

  2. #2
    Join Date
    Oct 2001
    Posts
    68
    First off, you need to define your UDFs to DB2. The example I have posted here is to convert string/character data to proper case (i.e: "This Is Proper Case" from "THIS IS PROPER CASE").

    Step 1. Define the UDF. (You must be CONNECTed to the target database)

    CREATE FUNCTION PCASE1 ( VARCHAR(100) )
    RETURNS VarChar(100)
    EXTERNAL NAME 'UDFpos!pCase1'
    LANGUAGE java
    PARAMETER STYLE db2general
    DETERMINISTIC
    FENCED
    NOT NULL CALL
    NO SQL
    NO EXTERNAL ACTION
    NO SCRATCHPAD
    NO FINAL CALL
    ALLOW PARALLEL
    NO DBINFO;

    Step 2. Create the Java Class and Method. Note: Save this file as UDFpos.java. It must be the same name as the Class.

    import java.lang.*; // for String class
    import COM.ibm.db2.app.*; // UDF and associated classes

    /**
    * Java user-defined functions are in this class
    */
    class UDFpos extends UDF {
    // Scalar UDF LCASE1 returns String (Works 7.2.3/XP)
    public void lCase1(String inputString,
    String outString) throws Exception
    {
    try {
    set(2, inputString.toLowerCase());
    } catch (Exception e) {
    throw e;
    }
    }

    // Scalar UDF PCASE1 returns String (Works 7.2.3/XP)
    public void pCase1(String inputString,
    String outString) throws Exception
    {
    int iI, iJ; // Index Variables
    String sResult; // Final Result
    String cLastChar;
    try {
    cLastChar=" "; iI=0; sResult="";
    while (iI < inputString.length()) {
    if (cLastChar.compareTo(" ")>0) {
    cLastChar=inputString.substring(iI,iI+1).toLowerCa se();
    } else {
    cLastChar=inputString.substring(iI,iI+1).toUpperCa se();
    }
    sResult = sResult + cLastChar;
    iI++;
    }
    set(2, sResult);
    } catch (Exception e) {
    throw e;
    }
    }

    // This one uses return but does not work on 7.2.3/XP
    public String lCase2(String inputString) throws Exception
    {
    try {
    return(inputString.toLowerCase());
    } catch (Exception e) {
    throw e;
    }
    }

    }

    Step 3. Test it.

    db2 "select pcase1(COL) from SCHEMA.TABLE"
    Last edited by WPSullivan; 06-04-02 at 17:49.
    William P.O'Sullivan

    IBM Certified Solutions Expert - DB2 UDB V7.1 Database Administration for Unix, Linux, Windows and OS/2
    OS/390, AIX, Linux and Windows2000

Posting Permissions

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