UDF Design Question

[Rhino]

Since I haven't been able to find out yet how to get my Eclipse debugger to
step through my Java UDF code, I am adding old-style File I/O debugging to
some of my UDFs. I'm not sure of the best way to design this though and am
looking for some suggestions.

Since my class might contain several UDF methods, for instance, several
versions of the same basic UDF but with different signatures for different
situations, e.g. count(needle, haystack), count(needle, haystack, start),
and count(needle, haystack, start, finish), any or all of which might want
to do logging, I've decided to create separate methods for each of the
following tasks: create/open the log file; write to the log file; close the
log file.

Now, what should I do if one of the methods that works with the log throws
an exception? I'm trying to figure out if it is best to display an error
message and stacktrace on the console and then do a System.exit() or if
there is a better approach? I'm not clear on what behaviour DB2 wants in
this case but I'd like to give as clear and specific message as I can about
the problem. In other words, I'd much rather see this when I run the query:

UDF count(needle, haystack, 7) failed; log file could not be opened due
to security violation

than this

UDF failed.

Could any of you Java UDF experts please share your experience on this
point?

--
Rhino
---
rhino1 AT sympatico DOT ca
"There are two ways of constructing a software design. One way is to make it
so simple that there are obviously no deficiencies. And the other way is to
make it so complicated that there are no obvious deficiencies." - C.A.R.
Hoare

[amurchis]

I don't believe there's a way to debug a JAVA UDF in DB2... procedures,
yes. I believe that support is in development, though I can't say when
it will be released. There is a chance the Stored Procedure Debugger
MIGHT work for JAVA UDFs similar to how to works for JAVA procedures,
but I doubt it and I'm unfamiliar with that tool.

Do NOT use any System.exit() or similar calls inside a UDF or procedure
(collectively "routines") . This will bring down the process and may
cause unexpected errors/hangs in DB2 processing -- the DB2 documentation
specifically FORBIDS calling any type of exit() call inside a routine.
If your routine simply throws an exception that you do not handle (or
handle and then throw again), your client will receive a -4302 SQLCODE
error, which is probably the best behaviour to have in this case. The
stack traceback associated with the exception will be written to the
db2diag.log, and you should be able to determine from that stack
traceback what function you were executing when the exception occured.
In other words, DON'T catch any exceptions caused by writing to your
diagnostic file; just let DB2 handle it and it should write the
information you require to the db2diag.log automatically.

Cheers!

amurchis
Rhino wrote:

Since I haven't been able to find out yet how to get my Eclipse debugger to
step through my Java UDF code, I am adding old-style File I/O debugging to
some of my UDFs. I'm not sure of the best way to design this though and am
looking for some suggestions.

Since my class might contain several UDF methods, for instance, several
versions of the same basic UDF but with different signatures for different
situations, e.g. count(needle, haystack), count(needle, haystack, start),
and count(needle, haystack, start, finish), any or all of which might want
to do logging, I've decided to create separate methods for each of the
following tasks: create/open the log file; write to the log file; close the
log file.

Now, what should I do if one of the methods that works with the log throws
an exception? I'm trying to figure out if it is best to display an error
message and stacktrace on the console and then do a System.exit() or if
there is a better approach? I'm not clear on what behaviour DB2 wants in
this case but I'd like to give as clear and specific message as I can about
the problem. In other words, I'd much rather see this when I run the query:

UDF count(needle, haystack, 7) failed; log file could not be opened due
to security violation

than this

UDF failed.

Could any of you Java UDF experts please share your experience on this
point?

[Rhino]

Thanks for the reply!

I've never managed to get the stored procedure debugger to work despite a
few attempts but the manual swears that debugging of UDFs *is* possible; see
my other thread from a couple of days ago. I just can't find out how to get
it to work.

Anyway, I've been using File I/O techniques to debug. Your remarks were
quite helpful in reminding me what I *should* be doing in my UDFs ;-)

I'm going to go clean up those UDFs now ;-) I just have a couple small ones
that are in the "sandbox" while I try to figure out exactly how to do
everything.

Rhino

"amurchis" <am******@ca.ib m.com> wrote in message
news:42******** @news3.prserv.n et...

I don't believe there's a way to debug a JAVA UDF in DB2... procedures,
yes. I believe that support is in development, though I can't say when
it will be released. There is a chance the Stored Procedure Debugger
MIGHT work for JAVA UDFs similar to how to works for JAVA procedures,
but I doubt it and I'm unfamiliar with that tool.

Do NOT use any System.exit() or similar calls inside a UDF or procedure
(collectively "routines") . This will bring down the process and may
cause unexpected errors/hangs in DB2 processing -- the DB2 documentation
specifically FORBIDS calling any type of exit() call inside a routine.
If your routine simply throws an exception that you do not handle (or
handle and then throw again), your client will receive a -4302 SQLCODE
error, which is probably the best behaviour to have in this case. The
stack traceback associated with the exception will be written to the
db2diag.log, and you should be able to determine from that stack
traceback what function you were executing when the exception occured.
In other words, DON'T catch any exceptions caused by writing to your
diagnostic file; just let DB2 handle it and it should write the
information you require to the db2diag.log automatically.

Cheers!

amurchis
Rhino wrote:

Since I haven't been able to find out yet how to get my Eclipse debugger to step through my Java UDF code, I am adding old-style File I/O debugging to some of my UDFs. I'm not sure of the best way to design this though and am looking for some suggestions.

Since my class might contain several UDF methods, for instance, several
versions of the same basic UDF but with different signatures for different situations, e.g. count(needle, haystack), count(needle, haystack, start), and count(needle, haystack, start, finish), any or all of which might want to do logging, I've decided to create separate methods for each of the
following tasks: create/open the log file; write to the log file; close the log file.

Now, what should I do if one of the methods that works with the log throws an exception? I'm trying to figure out if it is best to display an error
message and stacktrace on the console and then do a System.exit() or if
there is a better approach? I'm not clear on what behaviour DB2 wants in
this case but I'd like to give as clear and specific message as I can about the problem. In other words, I'd much rather see this when I run the query:
UDF count(needle, haystack, 7) failed; log file could not be opened due to security violation

than this

UDF failed.

Could any of you Java UDF experts please share your experience on this
point?

[amurchis]

Another thing you could do now that I think about it if you're coding in
PARAMETER STYLE JAVA.

Write a JAVA application that calls your UDF method DIRECTLY and passes
in different values. So long as both are compiled in debug mode, you
should just be able to start up the application under the debugger and
have at it.

For instance (pseudocode):

public static void main(string argv[])
{
for (i < number of times you want to execute the UDF)
{
value = yourclassname.y ourmethodname(f irstarg[i],
secondarg[i], ...);
System.out.prin tln(value);
}
}

This is essentially what DB2 would do if you're calling the UDF within a
SELECT query, where <number of times you ant to execute the UDF> is
the number of rows returned by the query.

However, this is useless if you're using PARAMETER STYLE DB2GENERAL due
to how the processing for that style ties back into the DB2 architecture
through the inherited UDF class. In that case what you're doing is
probably the best way to do it.
Rhino wrote:

Thanks for the reply!

I've never managed to get the stored procedure debugger to work despite a
few attempts but the manual swears that debugging of UDFs *is* possible; see
my other thread from a couple of days ago. I just can't find out how to get
it to work.

Anyway, I've been using File I/O techniques to debug. Your remarks were
quite helpful in reminding me what I *should* be doing in my UDFs ;-)

I'm going to go clean up those UDFs now ;-) I just have a couple small ones
that are in the "sandbox" while I try to figure out exactly how to do
everything.

Rhino

"amurchis" <am******@ca.ib m.com> wrote in message
news:42******** @news3.prserv.n et...

I don't believe there's a way to debug a JAVA UDF in DB2... procedures,
yes. I believe that support is in development, though I can't say when
it will be released. There is a chance the Stored Procedure Debugger
MIGHT work for JAVA UDFs similar to how to works for JAVA procedures,
but I doubt it and I'm unfamiliar with that tool.

Do NOT use any System.exit() or similar calls inside a UDF or procedure
(collective ly "routines") . This will bring down the process and may
cause unexpected errors/hangs in DB2 processing -- the DB2 documentation
specificall y FORBIDS calling any type of exit() call inside a routine.
If your routine simply throws an exception that you do not handle (or
handle and then throw again), your client will receive a -4302 SQLCODE
error, which is probably the best behaviour to have in this case. The
stack traceback associated with the exception will be written to the
db2diag.log , and you should be able to determine from that stack
traceback what function you were executing when the exception occured.
In other words, DON'T catch any exceptions caused by writing to your
diagnostic file; just let DB2 handle it and it should write the
information you require to the db2diag.log automatically.

Cheers!

amurchis
Rhino wrote:

Since I haven't been able to find out yet how to get my Eclipse debugger
to
step through my Java UDF code, I am adding old-style File I/O debugging
to
some of my UDFs. I'm not sure of the best way to design this though and
am
looking for some suggestions.

Since my class might contain several UDF methods, for instance, several
versions of the same basic UDF but with different signatures for
different
situations , e.g. count(needle, haystack), count(needle, haystack,
start),
and count(needle, haystack, start, finish), any or all of which might
want
to do logging, I've decided to create separate methods for each of the
following tasks: create/open the log file; write to the log file; close
the
log file.

Now, what should I do if one of the methods that works with the log
throws
an exception? I'm trying to figure out if it is best to display an error
message and stacktrace on the console and then do a System.exit() or if
there is a better approach? I'm not clear on what behaviour DB2 wants in
this case but I'd like to give as clear and specific message as I can
about
the problem. In other words, I'd much rather see this when I run the
query:
UDF count(needle, haystack, 7) failed; log file could not be opened
due
to security violation

than this

UDF failed.

Could any of you Java UDF experts please share your experience on this
point?

[amurchis]

WARNING: This assumes you're not using any SQL inside your UDF. If that
is the case, you'll probably need to modify your UDF to take a
connection object as the first parameter (and open the connection inside
your application). Obviously, the "jdbc:default:c onnection" logic will
not work inside your application.

amurchis wrote:

Another thing you could do now that I think about it if you're coding in
PARAMETER STYLE JAVA.

Write a JAVA application that calls your UDF method DIRECTLY and passes
in different values. So long as both are compiled in debug mode, you
should just be able to start up the application under the debugger and
have at it.

For instance (pseudocode):

public static void main(string argv[])
{
for (i < number of times you want to execute the UDF)
{
value = yourclassname.y ourmethodname(f irstarg[i],
secondarg[i], ...);
System.out.prin tln(value);
}
}

This is essentially what DB2 would do if you're calling the UDF within a
SELECT query, where <number of times you ant to execute the UDF> is the
number of rows returned by the query.

However, this is useless if you're using PARAMETER STYLE DB2GENERAL due
to how the processing for that style ties back into the DB2 architecture
through the inherited UDF class. In that case what you're doing is
probably the best way to do it.
Rhino wrote:

Thanks for the reply!

I've never managed to get the stored procedure debugger to work despite a
few attempts but the manual swears that debugging of UDFs *is*
possible; see
my other thread from a couple of days ago. I just can't find out how
to get
it to work.

Anyway, I've been using File I/O techniques to debug. Your remarks were
quite helpful in reminding me what I *should* be doing in my UDFs ;-)

I'm going to go clean up those UDFs now ;-) I just have a couple small
ones
that are in the "sandbox" while I try to figure out exactly how to do
everything.

Rhino

"amurchis" <am******@ca.ib m.com> wrote in message
news:42******** @news3.prserv.n et...

I don't believe there's a way to debug a JAVA UDF in DB2... procedures,
yes. I believe that support is in development, though I can't say when
it will be released. There is a chance the Stored Procedure Debugger
MIGHT work for JAVA UDFs similar to how to works for JAVA procedures,
but I doubt it and I'm unfamiliar with that tool.

Do NOT use any System.exit() or similar calls inside a UDF or procedure
(collectively "routines") . This will bring down the process and may
cause unexpected errors/hangs in DB2 processing -- the DB2 documentation
specifically FORBIDS calling any type of exit() call inside a routine.
If your routine simply throws an exception that you do not handle (or
handle and then throw again), your client will receive a -4302 SQLCODE
error, which is probably the best behaviour to have in this case. The
stack traceback associated with the exception will be written to the
db2diag.log, and you should be able to determine from that stack
traceback what function you were executing when the exception occured.
In other words, DON'T catch any exceptions caused by writing to your
diagnostic file; just let DB2 handle it and it should write the
information you require to the db2diag.log automatically.

Cheers!

amurchis
Rhino wrote:

Since I haven't been able to find out yet how to get my Eclipse
debugger

to

step through my Java UDF code, I am adding old-style File I/O debugging

to

some of my UDFs. I'm not sure of the best way to design this though and

am

looking for some suggestions.

Since my class might contain several UDF methods, for instance, several
versions of the same basic UDF but with different signatures for

different

situations, e.g. count(needle, haystack), count(needle, haystack,

start),

and count(needle, haystack, start, finish), any or all of which might

want

to do logging, I've decided to create separate methods for each of the
following tasks: create/open the log file; write to the log file; close

the

log file.

Now, what should I do if one of the methods that works with the log

throws

an exception? I'm trying to figure out if it is best to display an
error
message and stacktrace on the console and then do a System.exit() or if
there is a better approach? I'm not clear on what behaviour DB2
wants in
this case but I'd like to give as clear and specific message as I can

about

the problem. In other words, I'd much rather see this when I run the

query:

UDF count(needle, haystack, 7) failed; log file could not be opened

due

to security violation

than this

UDF failed.

Could any of you Java UDF experts please share your experience on this
point?

[Rhino]

Hmmm, maybe I was too hasty in taking those System.exit() calls out of my
UDFs....

I was doing a System.exit() within the catch logic for each of the three
methods that work with the log file; createFile(), writeFile() and
closeFile(). Here are all three methods:

static public BufferedWriter createFile(Stri ng path, String fileName) {

/* Define the buffered writer. */
BufferedWriter bufferedWriter = null;

try {
/*
* See if the output directory exists; if it doesn't, create it
and
* any needed parent directories.
*/
File outputPath = new File(path);
System.out.prin tln("outputPath =" + outputPath);
if (!outputPath.ex ists()) {
outputPath.mkdi rs();
}

/* See if the output file exists; if it doesn't, create it. */
outputFile = new File(outputPath , fileName);
System.out.prin tln("outputFile =" + outputFile);
if (!outputFile.ex ists()) {
outputFile.crea teNewFile();
}

/* Create the file, indicating that we will be appending to it.
*/
FileWriter fileWriter = new FileWriter(path + File.separator +
fileName, true);
bufferedWriter = new BufferedWriter( fileWriter);
} catch (SecurityExcept ion s_excp) {
System.out.prin tln("SecurityEx ception encountered. Message: " +
s_excp.getMessa ge());
s_excp.printSta ckTrace();
System.exit(16) ;
} catch (IOException io_excp) {
System.out.prin tln("IOExceptio n encountered. Message: " +
io_excp.getMess age());
io_excp.printSt ackTrace();
System.exit(16) ;
}

return (bufferedWriter );
}
static public void writeToFile(Buf feredWriter outputFile, String
oneLine) {

try {
outputFile.writ e(oneLine);
outputFile.newL ine();
}
catch (IOException io_excp) {
System.out.prin tln("IOExceptio n encountered while writing line '" +
oneLine +
"' to file " + outputFile + ".\nMessage : " + io_excp);
io_excp.printSt ackTrace();
System.exit(16) ;
}

}
static public void closeFile(Buffe redWriter outputFile) {

try {
outputFile.flus h();
outputFile.clos e();
}
catch (IOException io_excp) {
System.err.prin tln("IOExceptio n encountered while closing file " +
outputFile + ".\nMessage : " + io_excp);
io_excp.printSt ackTrace();
System.exit(16) ;
}
}

I deliberately specified the path to the log file as being on drive Z: of my
Windows XP machine - I don't have a drive Z: - and ran the code exactly as
you see here to see how it would respond to the error. Strangely enough, it
got all the way through the createFile() method without a hiccup but crashed
on a NullPointerExce ption in the writeFile() method; the stacktrace was
written to db2diag.log. I got an SQL4302 in the first statement that tried
to invoke the UDF. I got SQL4301 Reason Code 4 on each subsequent invocation
of the function.

When I followed your advice and removed the System.exit() calls from the
catch logic of each method, I got an SQL0440N (No such function) at each
invocation of the function. That is nonsense because I had just finished
generating the function again and got green lights on every step. Nothing
was written to db2diag.log either.

I like the behaviour of the function a LOT better when the System.exit(16)
was in each catch block!

Do you have any idea how to this is working? I'm baffled by why the
createFile() method is working and that bothers me; it should have failed.
But aside from that, I'm getting behaviour I can live with when I leave the
System.exit() in the catch blocks. The behaviour when I omit them is not
tolerable to me because it is very misleading.

Mind you, I'd prefer different behaviour altogether if I could figure out
how to get it: I'd like to get a specific message that says something like
"I/O Error in function foo(): Couldn't open log file because Drive Z: does
not exist." But If I can't get that, I'll take SQL4302 with a stacktrace in
db2diag.log over a bogus SQL0440N any time ;-)

Any ideas?

Rhino

"Rhino" <rh****@NOSPAM. sympatico.ca> wrote in message
news:wf******** *************@n ews20.bellgloba l.com...

Thanks for the reply!

I've never managed to get the stored procedure debugger to work despite a
few attempts but the manual swears that debugging of UDFs *is* possible; see my other thread from a couple of days ago. I just can't find out how to get it to work.

Anyway, I've been using File I/O techniques to debug. Your remarks were
quite helpful in reminding me what I *should* be doing in my UDFs ;-)

I'm going to go clean up those UDFs now ;-) I just have a couple small ones that are in the "sandbox" while I try to figure out exactly how to do
everything.

Rhino

"amurchis" <am******@ca.ib m.com> wrote in message
news:42******** @news3.prserv.n et...

I don't believe there's a way to debug a JAVA UDF in DB2... procedures,
yes. I believe that support is in development, though I can't say when
it will be released. There is a chance the Stored Procedure Debugger
MIGHT work for JAVA UDFs similar to how to works for JAVA procedures,
but I doubt it and I'm unfamiliar with that tool.

Do NOT use any System.exit() or similar calls inside a UDF or procedure
(collectively "routines") . This will bring down the process and may
cause unexpected errors/hangs in DB2 processing -- the DB2 documentation
specifically FORBIDS calling any type of exit() call inside a routine.
If your routine simply throws an exception that you do not handle (or
handle and then throw again), your client will receive a -4302 SQLCODE
error, which is probably the best behaviour to have in this case. The
stack traceback associated with the exception will be written to the
db2diag.log, and you should be able to determine from that stack
traceback what function you were executing when the exception occured.
In other words, DON'T catch any exceptions caused by writing to your
diagnostic file; just let DB2 handle it and it should write the
information you require to the db2diag.log automatically.

Cheers!

amurchis
Rhino wrote:

Since I haven't been able to find out yet how to get my Eclipse debugger

to

step through my Java UDF code, I am adding old-style File I/O

debugging

to some of my UDFs. I'm not sure of the best way to design this though

and

am looking for some suggestions.

Since my class might contain several UDF methods, for instance,

several versions of the same basic UDF but with different signatures for

different situations, e.g. count(needle, haystack), count(needle, haystack, start), and count(needle, haystack, start, finish), any or all of which might want to do logging, I've decided to create separate methods for each of the
following tasks: create/open the log file; write to the log file; close the

log file.

Now, what should I do if one of the methods that works with the log throws an exception? I'm trying to figure out if it is best to display an

error message and stacktrace on the console and then do a System.exit() or if there is a better approach? I'm not clear on what behaviour DB2 wants in this case but I'd like to give as clear and specific message as I can

about the problem. In other words, I'd much rather see this when I run the query:
UDF count(needle, haystack, 7) failed; log file could not be

opened due to security violation

than this

UDF failed.

Could any of you Java UDF experts please share your experience on this
point?

[Rhino]

This is a followup to my own post in case anyone else is monitoring this
thread, either now or in the future.

I figured out why my createFile() method didn't crash. The createNewFile()
method *was* failing, causing an IOException. The IOException was handled by
my catch block but I didn't handle it correctly: I simply displayed a
message via System.out.prin tln() and displayed the stackTrace, neither of
which ever appeared because you can't do console I/O in a UDF. Then, since
the System.exit() was not there, the method ended, returning a null for the
BufferedWriter. Since I wasn't checking to ensure that I had a non-null
BufferedWriter, the code continued until it tried to write a line to the
non-existent log, at which point the runtime NullPointerExce ption occurred
in writeToFile(). That exception DID appear in db2diag.log.

I should have thought about this problem a bit more before asking anyone
else to explain it to me ;-)

---

I've also discovered that if I want to give the user a meaningful message
text, I can do so. In light of my new understanding of my createFile()
method's behaviour, I simply modified the code in my UDF a bit so that it
looked like this:

log = createFile(LOG_ PATH, LOG_FILE);
if (log==null) throw new IllegalArgument Exception("log file is
null");

As a result, when my log file couldn't be created because I'd specified a
non-existent Z drive, the SQL that invoked the function returned:

COM.ibm.db2.jdb c.DB2Exception: [IBM][CLI Driver][DB2/NT] SQL4302N Java
stored procedure or user-defined function "RHINO.COUNTCHA R", specific name
"COUNTCHAR2 " aborted with an exception "log file is null". SQLSTATE=38501
In other words, I *can* make a meaningful message of my own choosing that
will appear if things go wrong in the UDF. This isn't a very GOOD message
yet but I know I can improve on this and get something very close to what I
had wanted.

Rhino

[Knut Stolze]

Rhino wrote:

I've also discovered that if I want to give the user a meaningful message
text, I can do so. In light of my new understanding of my createFile()
method's behaviour, I simply modified the code in my UDF a bit so that it
looked like this:

log = createFile(LOG_ PATH, LOG_FILE);
if (log==null) throw new IllegalArgument Exception("log file is
null");

As a result, when my log file couldn't be created because I'd specified a
non-existent Z drive, the SQL that invoked the function returned:

COM.ibm.db2.jdb c.DB2Exception: [IBM][CLI Driver][DB2/NT] SQL4302N Java
stored procedure or user-defined function "RHINO.COUNTCHA R", specific name
"COUNTCHAR2 " aborted with an exception "log file is null". SQLSTATE=38501

Alternatively, you could also use the "setSQLmess age" and "setSQLstat em"
methods and set your own error message that way. You just have to be awary
that the message must not be longer than 70 bytes.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena