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

    Thumbs up Unanswered: Error with User Defined Function

    I created a java class having method used in DB2. I got this error message:

    DBA2191E SQL execution error.

    com.ibm.db.DataException: A database manager error occurred. : [IBM][CLI Driver][DB2/NT] SQL4304N Java stored procedure or user-defined function "DB2ADMIN.PCASE1", specific name "SQL020624174700478" could not load Java class "UDFpos", reason code "1". SQLSTATE=42724

    Please HELP !

    BELOW is the instructions I followed from previous help in this forum:

    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"

    __________________
    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 Last edited by WPSullivan on 06-04-2002 at 08:49 PM

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: Error with User Defined Function

    Copy the class file to a directory in the CLASSPATH , preferably to sqllib/function directory .

    Then issue the SELECT and it should work fine ...

    Cheers

    Sathyaram


    Originally posted by phonhonguyen
    I created a java class having method used in DB2. I got this error message:

    DBA2191E SQL execution error.

    com.ibm.db.DataException: A database manager error occurred. : [IBM][CLI Driver][DB2/NT] SQL4304N Java stored procedure or user-defined function "DB2ADMIN.PCASE1", specific name "SQL020624174700478" could not load Java class "UDFpos", reason code "1". SQLSTATE=42724

    Please HELP !

    BELOW is the instructions I followed from previous help in this forum:

    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"

    __________________
    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 Last edited by WPSullivan on 06-04-2002 at 08:49 PM

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: Re: Error with User Defined Function

    Try setting the dbm parameter JDK11_PATH to C:/Program Files/sqllib/java/jdk
    Compile the UDF using the javac in the above path/bin and copy the .class file to the sqllib/function directory ...

    Don't forget to stop and start the instance after changing the dbm cfg ...

    HTH

    Cheers

    Sathyaram
    Originally posted by sathyaram_s
    Copy the class file to a directory in the CLASSPATH , preferably to sqllib/function directory .

    Then issue the SELECT and it should work fine ...

    Cheers

    Sathyaram



Posting Permissions

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