Strange error in UDF
Rhino
example given in the following DeveloperWorks article:
http://www-106.ibm.com/developerworks/db2/library/techarticle/0205bhogal/0205bhogal.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
--
Rhino
amurchis
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
"amurchis" <amur...@ca.ibm.com> wrote in message
news:4249a...@news3.prserv.net...
> 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.
>
article pretty closely but I knew that println() wouldn't work so I replaced
the println() statements with writes to a flat file. This functions as a
basic log file and I know that the code works because I've used it in many
other UDFs to debug the code. I also had some writes immediately after
entering the method to display the values on the input parameters and they
were written successfully. But the code never gets to the end of the method;
it dies sometime after printing the parameters but before the next write.
As far as error handling goes, all of the code is in a big try block that
catches Exception and simply throws a new SQLException displaying the method
name, the message from the Exception, and SQLstate "38600". If no Exception
is caught, the method ends with
return "An E-mail was sent to " + cardholderEmail
But I've never seen the UDF display either that line nor any exception: when
the UDF, which should finish in a second or two still hadn't come back after
20 minutes and I realized that db2diag.log was growing by leaps and bounds,
I just killed the processes so that I could try to debug the problem. Maybe
I should have just let it end on its own but after 20 minutes, it didn't
seem like it *was* going to end by itself.
> 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.
>
Yeah, I probably needed to put more of the db2diag.log in the note but it
literally goes on for millions of lines and I had no idea how much of the
log was needed to diagnose the problem. I manually edited db2diag.log
afterwards to remove all of those lines but I could run the UDF again to
recreate the error and then post more of the log if you think that would
help. How much of the log would be enough to help you make sense of it? 1000
lines?
> 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.
>
I'm on V8.2 with FP8 installed so we shouldn't have an issue with code
levels.
Rhino