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

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.

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'
@

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!!

How do you reconcile this
and this
?