Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2007
    Posts
    265

    Unanswered: Error using JAVA UDF

    Hi,

    I have a java udf that is giving an error and I'm not sure why --any help would be appreciated!! Btw, it seems like the editor puts a winky smiley whenever there is a ; followed by a ) !!

    here is the java file :

    package pkgname;

    import java.io.Serializable;

    import org.joda.time.DateMidnight;
    import org.joda.time.DateTime;
    import org.joda.time.DateTimeZone;

    public class TzOffset implements Serializable {


    public static int offsetHours(int month, int day, int year, String tz)
    {
    DateTimeZone timeZone = DateTimeZone.forID(tz);
    DateMidnight dm = new DateMidnight(year, month, day, timeZone);
    int offMillis = timeZone.getOffset(dm.getMillis());
    return (offMillis/1000)/3600; // convert from millis to seconds to hours
    }


    public static String asUTC(int month, int day, int year, String tz)
    {
    DateTimeZone timeZone = DateTimeZone.forID(tz);
    DateMidnight dm = new DateMidnight(year, month, day, timeZone);
    DateTime utc = dm.toDateTime(DateTimeZone.forID("UTC"));
    return utc.toString("yyyy-MM-dd-HH.mm.ss.SSS");
    }

    }


    And the corresponding sql function --

    CREATE FUNCTION UTC_DATE(month INTEGER, day INTEGER, year INTEGER, timezone VARCHAR(32))
    RETURNS TIMESTAMP
    LANGUAGE java
    PARAMETER STYLE java
    NO SQL
    FENCED THREADSAFE
    DETERMINISTIC
    RETURNS NULL ON NULL INPUT
    NO EXTERNAL ACTION
    EXTERNAL NAME 'packagename.TzOffset!asUTC'
    @



    Here's what I get when I call the function --

    db2 "select UTC_DATE(8, 1, 2010, 'US/Central') from sysibm.sysdummy1"

    1
    --------------------------
    SQL4306N Java stored procedure or user-defined function "DB2INST1.UTC_DATE",
    specific name "SQL100820213045700" could not call Java method "asUTC",
    signature "(IIILjava/lang/StringLjava/sq". SQLSTATE=42724


    If I look at the db2diag.log file, this is what I see --

    2010-08-20-21.34.05.667293+000 E20185703352G367 LEVEL: Warning
    PID : 11114 TID : 4106451888 PROC : db2fmp (
    INSTANCE: db2inst1 NODE : 000
    FUNCTION: DB2 UDB, BSU Java support, sqlejLogException, probe:10
    MESSAGE : ADM10000W A Java exception has been caught. The Java stack
    traceback has been written to the db2diag.log.

    2010-08-20-21.34.05.667360+000 I20185703720G371 LEVEL: Warning
    PID : 11114 TID : 4106451888 PROC : db2fmp (
    INSTANCE: db2inst1 NODE : 000
    FUNCTION: DB2 UDB, BSU Java support, sqlejLogException, probe:10
    MESSAGE : java.lang.NoSuchMethodError: asUTC
    DATA #1 : Hexdump, 4 bytes
    0xF4C368FC : 0000 0000 ....

    2010-08-20-21.34.05.667431+000 I20185704092G909 LEVEL: Error
    PID : 11114 TID : 4106451888 PROC : db2fmp (
    INSTANCE: db2inst1 NODE : 000
    FUNCTION: DB2 UDB, oper system services, sqlofica, probe:10
    DATA #1 : Hexdump, 136 bytes
    0xF4C371CC : 5351 4C43 4120 2020 8800 0000 2EEF FFFF SQLCA ........
    0xF4C371DC : 4600 4E52 4731 2E55 5443 5F44 4154 45FF F.db2inst1.UTC_DATE.
    0xF4C371EC : 5351 4C31 3030 3832 3032 3133 3034 3537 SQL1008202130457
    0xF4C371FC : 3030 FF61 7355 5443 FF28 4949 494C 6A61 00.asUTC.(IIILja
    0xF4C3720C : 7661 2F6C 616E 672F 5374 7269 6E67 3B29 va/lang/String
    0xF4C3721C : 4C6A 6176 612F 7371 5351 4C45 4A45 5854 Ljava/sqSQLEJEXT
    0xF4C3722C : 0000 0000 0000 0000 0000 0000 0000 0000 ................
    0xF4C3723C : 0000 0000 0000 0000 2020 2020 2020 2020 ........
    0xF4C3724C : 2020 2034 3237 3234 42724


    2010-08-20-21.34.05.667515+000 I20185705002G376 LEVEL: Warning
    PID : 11114 TID : 4106451888 PROC : db2fmp (
    INSTANCE: db2inst1 NODE : 000
    FUNCTION: DB2 UDB, routine_infrastructure, sqlerJavaCallRoutine, probe:30
    MESSAGE : Error from DB2ER CallUDF. RC:
    DATA #1 : Hexdump, 4 bytes
    0xF4C36E2C : 2EEF FFFF ....

    2010-08-20-21.34.05.667694+000 I20185705379G282 LEVEL: Info
    PID : 11114 TID : 4106451888 PROC : db2fmp (
    INSTANCE: db2inst1 NODE : 000
    FUNCTION: DB2 UDB, BSU Java support, sqlejJavaCleanup, probe:410
    MESSAGE : Fell off end of active routine list.

    2010-08-20-21.34.05.667711+000 I20185705662G1005 LEVEL: Error
    PID : 10927 TID : 4095993536 PROC : db2agent (ENERGY1)
    INSTANCE: db2inst1 NODE : 000 DB : ENERGY1
    APPHDL : 0-11 APPID: *LOCAL.db2inst1.100820212937
    FUNCTION: DB2 UDB, oper system services, sqlofica, probe:10
    DATA #1 : Hexdump, 136 bytes
    0xF41CAE84 : 5351 4C43 4120 2020 8800 0000 2EEF FFFF SQLCA ........
    0xF41CAE94 : 4600 4E52 4731 2E55 5443 5F44 4154 45FF F.db2inst1.UTC_DATE.
    0xF41CAEA4 : 5351 4C31 3030 3832 3032 3133 3034 3537 SQL1008202130457
    0xF41CAEB4 : 3030 FF61 7355 5443 FF28 4949 494C 6A61 00.asUTC.(IIILja
    0xF41CAEC4 : 7661 2F6C 616E 672F 5374 7269 6E67 3B29 va/lang/String
    0xF41CAED4 : 4C6A 6176 612F 7371 5351 4C45 4A45 5854 Ljava/sqSQLEJEXT
    0xF41CAEE4 : 0000 0000 0000 0000 0000 0000 0000 0000 ................
    0xF41CAEF4 : 0000 0000 0000 0000 2020 2020 2020 2020 ........
    0xF41CAF04 : 2020 2034 3237 3234 42724

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I think you should look at one of the samples of Java UDFs, which are provided with DB2. In particular, the UDF class must extend a certain superclass.

  3. #3
    Join Date
    Nov 2007
    Posts
    265
    I think the issue has to do with the String return type.. if I call the other function, it works just fine :

    db2 "select TZ_OFFSET(8, 1, 2010, 'US/Central') from sysibm.sysdummy1"

    1
    -----------
    -5

    1 record(s) selected.



    CREATE FUNCTION TZ_OFFSET(month INTEGER, day INTEGER, year INTEGER, timezone VARCHAR(32))
    RETURNS integer
    LANGUAGE java
    PARAMETER STYLE java
    NO SQL
    FENCED THREADSAFE
    DETERMINISTIC
    RETURNS NULL ON NULL INPUT
    NO EXTERNAL ACTION
    EXTERNAL NAME 'packagename.TzOffset!offsetHours'
    @
    Last edited by db2user24; 08-23-10 at 16:23.

  4. #4
    Join Date
    Nov 2007
    Posts
    265
    Hi, we have other java UDF's that work just fine... for example, TZ_OFFSET above returns an integer.. this doesn't extend any superclass so am not really sure what the real issue is..this is the only function that I've ever created that returns a timestamp and it fails.. any other thoughts? Thanks!!

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    How do you reconcile this
    Code:
    public static String asUTC(...) 
    {...
    return utc.toString("yyyy-MM-dd-HH.mm.ss.SSS");
    }
    and this
    Code:
     CREATE FUNCTION UTC_DATE ... RETURNS TIMESTAMP
    ?

Posting Permissions

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