Seek newbee Java UDF installation tip

[gimme_this_gimme_that]

I'm driving Windows XP and could use a tip on installing a function
written in Java.

1. I created the a Java jar file, named UdfUtils.jar, from the Java
source file shown below:
2. I copied the jar file to c:\Program Files\IBM\SQLLIB\FUNCTION
3. From the db2 command line I exectued call sqlj.refresh_classes()
4. I defined a function using the ddl also shown below

Now ...

When executing the statement

select RegExp('.*doc.*',resume) from {username}.EMP_RESUME

nothing happens.

( {username} is my DB2 username).
I have a hunch that the source of the problem is that the jar file and
class are never found - e.g. call.refresh_classes() isn't working for
me.
Also, separately, I've tried :

call.sqlj.install_jar('file:c:/pathtomyjar/jars/UdfUtils.jar','SAMPLE')

to locate my jar. I tried many variations of the above but must be
missing some syntax thing.
Thanks in advance.

package com.rhi.bbb.udf.utils;

import java.sql.Clob;

import java.util.regex.Pattern;
import java.util.regex.Matcher;

import COM.ibm.db2.app.UDF;

public class RegExp extends UDF {

public static int Match(java.lang.String pattern, java.sql.Clob clob) {
{
try
{
Pattern p = Pattern.compile(pattern);
Matcher m = p.matcher(
clob.getSubString(Long.parseLong("1"), (int)clob.length()) );
if ( m.matches() ) {
return 1;
}
return 0;
}
catch (Exception e)
{
return 0;
}
}
}
CREATE FUNCTION RegExp(pattern VARCHAR(2048), string CLOB(10M))
RETURNS INTEGER
FENCED
VARIANT
NO SQL
LANGUAGE JAVA
PARAMETER STYLE JAVA
EXTERNAL NAME 'com.rhi.bbb.udf.utils.RegExp!Match'

[Knut Stolze]

gi*******************@yahoo.com wrote:

I'm driving Windows XP and could use a tip on installing a function
written in Java.

1. I created the a Java jar file, named UdfUtils.jar, from the Java
source file shown below:
2. I copied the jar file to c:\Program Files\IBM\SQLLIB\FUNCTION
3. From the db2 command line I exectued call sqlj.refresh_classes()
4. I defined a function using the ddl also shown below

Now ...

When executing the statement

select RegExp('.*doc.*',resume) from {username}.EMP_RESUME

nothing happens.
What means "nothing happens"? Does DB2 just sit there and you wait? Or
does an error come back?
( {username} is my DB2 username).
I have a hunch that the source of the problem is that the jar file and
class are never found - e.g. call.refresh_classes() isn't working for
me.

If the class is not found, you'll get an error message saying so right away.
So I assume you experience a hang and that is most probably due to the code
in your function.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[gimme_this_gimme_that]

No hang. The method always returns null.

[Knut Stolze]

gi*******************@yahoo.com wrote:

No hang. The method always returns null.

Does it return a SQL NULL or a 0 (zero)? I'd suspect the latter because
this is what you are returing when an exception occurs. The
java.util.regex pattern is available since Java 1.4. Are you sure that
you're using this version with your DB2 instance?

I would recommend that you do not intercept an exception and simply return 0
without providing some more error information to DB2. You can either
simply pass on the exception, or you use the setSQLstate() and the
setSQLmessage() methods inherited from the UDF class. At the current
stage, I'd probably co with this:

public class RegExp extends UDF {

public static int Match(java.lang.String pattern, java.sql.Clob clob) {
{
Pattern p = Pattern.compile(pattern);
Matcher m = p.matcher(
clob.getSubString(Long.parseLong("1"), (int)clob.length()) );
if ( m.matches() ) {
return 1;
}
return 0;
}
}

Call the function - it should give you some error about an exception, I
guess. Then have a look at the db2diag.log file where you will find the
exception and stack dump.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany