For the record, I tried the UDF (just using the "VALUES" SQL statement,
not inside the trigger) and it worked fine for me. Mind you, I'm using
a patched DB2 that has some known JAVA SP/UDF problems (APARs) fixed --
LI70535 (affecting FP6-FP8) in particular, which IBM should be releasing
as part of FP9 -- and on AIX, not Windows.
There are some problems with his code from the article though. For one,
you cannot print information using println() like he does. The FMP is a
daemon process; it has no STDOUT/STDERR, and hence those messages have
nowhere to appear. What I suspect is happening is you're getting an
exception, and the code is trying to handle it but the println is
causing problems -- try taking that out and seeing what happens. I'd
have code the UDF to simply let the exception happen, in which case the
trigger will received a -4302 SQLCODE and the message & call stack from
the exception (rather than just the message his design will return) will
be written to the db2diag.log. You'd need to change the design of the
trigger to handle the -4302 if you had more than one record to update,
but I think knowing the entire exception would be more useful than the
partial message is more useful.
Other than that... sqloJVMvfprintf() is a function registered with the
JVM when it is started up by DB2. It's intent is that if the JVM has a
message it itself needs to report (ie, not just something from
System.out) then it will be routed to the db2diag.log. However, I
cannot make heads or tails of the messages it printed out below.
I should point out though that JDK 1.4.1 is only supported on V82 (ie,
FP7) or higher. I don't know what level you're on, but this should be
addressed if you're pre-V82.
Rhino wrote:[color=blue]
> I am trying to write a trigger/UDF combination that closely follows the
> example given in the following DeveloperWorks article:
>
http://www-106.ibm.com/developerwork...205bhogal.html
>
> Unfortunately, when my UDF starts to compose the email, something goes wrong
> and DB2 begins writing to db2diag.log - and writing, and writing, and
> writing. Millions of lines get written if I don't kill the processes.
>
> I've enclosed the first few entries from db2diag.log below. I was wondering
> if anyone could enlighten me on how to interpret this information? I'd like
> to understand why DB2 is writing millions of lines to db2diag.log so that I
> can try to figure out what's wrong. It looks like some keep of a memory
> issue but I don't understand why memory should be an issue; I'm running XP
> Pro and have 512MB of memory. I've occasionally had Java Heap Size problems
> in the past but they didn't spew millions of lines into db2diag.log.
>
> I'm pretty sure my UDF source code is just fine for two reasons: it closely
> follows the example in the article and, as a test, I invoked it as a regular
> method from a regular Java application and it worked perfectly. Also, I have
> some diagnostics in the UDF so that I know the UDF started to execute.
>
> If anyone can shed any light on this, I'd really love to hear what's going
> on here.
>
>
> 2005-03-25-08.44.49.444000-300 E4439129H498 LEVEL: Warning
> PID : 540 TID : 3036 PROC : db2syscs.exe
> INSTANCE: DB2 NODE : 000 DB : SAMPLE
> APPHDL : 0-61 APPID: *LOCAL.DB2.050325134447
> FUNCTION: DB2 UDB, SQO Memory Management, sqloMemLogPoolConditions, probe:20
> DATA #1 : <preformatted>
> Configured heap limit exceeded for Database Monitor Heap (MON_HEAP_SZ).
> Allocating additional memory from the overflow buffer.
>
> 2005-03-25-08.45.00.470000-300 I4439629H296 LEVEL: Warning
> PID : 3764 TID : 3804 PROC : db2fmp.exe
> INSTANCE: DB2 NODE : 000
> FUNCTION: DB2 UDB, oper system services, sqloJVMvfprintf, probe:20
> MESSAGE : JVMDG315: JVM Requesting Heap dump file
>
> 2005-03-25-08.45.00.500000-300 I4439927H317 LEVEL: Warning
> PID : 3764 TID : 3804 PROC : db2fmp.exe
> INSTANCE: DB2 NODE : 000
> FUNCTION: DB2 UDB, oper system services, sqloJVMvfprintf, probe:20
> MESSAGE : // Version: J2RE 1.4.1 IBM Windows 32 build cn1411-20040301a
>
> 2005-03-25-08.45.00.540000-300 I4440246H269 LEVEL: Warning
> PID : 3764 TID : 3804 PROC : db2fmp.exe
> INSTANCE: DB2 NODE : 000
> FUNCTION: DB2 UDB, oper system services, sqloJVMvfprintf, probe:20
> MESSAGE : 0x00aa0200
>
> 2005-03-25-08.45.00.540000-300 I4440517H267 LEVEL: Warning
> PID : 3764 TID : 3804 PROC : db2fmp.exe
> INSTANCE: DB2 NODE : 000
> FUNCTION: DB2 UDB, oper system services, sqloJVMvfprintf, probe:20
> MESSAGE : [364208]
>
> 2005-03-25-08.45.00.540000-300 I4440786H263 LEVEL: Warning
> PID : 3764 TID : 3804 PROC : db2fmp.exe
> INSTANCE: DB2 NODE : 000
> FUNCTION: DB2 UDB, oper system services, sqloJVMvfprintf, probe:20
> MESSAGE : byte[]
>
> 2005-03-25-08.45.00.540000-300 I4441051H269 LEVEL: Warning
> PID : 3764 TID : 3804 PROC : db2fmp.exe
> INSTANCE: DB2 NODE : 000
> FUNCTION: DB2 UDB, oper system services, sqloJVMvfprintf, probe:20
> MESSAGE : 0x00af90b0
>
>[/color]
