1. How to implement Java UDF using CLOB for DB2GENERAL parameter passing?
When you use DB2GENERAL parameter passing in the declaration of the SQL UDF, DB2 will use the legacy driver to map the data types between Java and DB2. If you need to pass CLOB as a parameter in your JAVA UDF, please follow these points.
1. You have decided to use the parameter style for a JAVA UDF as DB2GENERAL.
2. The Java class that you are writing should extend UDF from COM.ibm.db2.app.UDF
3. The return parameter should be the part of Java function arguments.
4. You should use COM.ibm.db2.app.Clob for Clob and not java.sql.Clob
For example, if you want to write a java UDF that will do pattern matching on a CLOB column and returns 1 if a match was found else returns 0.
// File: RegEx.java
package sp;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import COM.ibm.db2.app.UDF;
public class RegEx extends UDF
{
public void MatchClob(String pattern,
COM.ibm.db2.app.Clob clob,
int results) throws Exception
{
if (null == clob || clob.size() == 0)
{
set(3,2);
}
Pattern p = Pattern.compile(pattern);
Long size = new Long(clob.size());
char[] cbuff = new char[size.intValue()];
clob.getReader().read(cbuff);
Matcher m = p.matcher(new String(cbuff));
if (m.matches())
{
set(3,1);
return;
}
set(3,0);
return;
}
}
Create a directory “sp” somewhere and create a file RegEx.java in “sp” folder. Compile this file using either javac or Eclipse or any other IDE of your choice.
Copy this folder “sp” with RegEx.class in sqllib\function or sqllib/function.
Use these SQL to create the user defined function (UDF) and test the function.
drop function MATCH_CLOB;
call sqlj.refresh_classes();
CREATE FUNCTION MATCH_CLOB
(
pattern VARCHAR(4000),
clob CLOB(1M)
)
RETURNS INTEGER
FENCED
VARIANT
NO SQL
LANGUAGE JAVA
PARAMETER STYLE DB2GENERAL
EXTERNAL NAME 'sp.RegEx!MatchClob';
drop table t1;
create table t1 (c1 int not null, clob clob(1m));
insert into t1 values (1, 'Dec1967');
select match_clob('Dec[0-9][0-9][0-9][0-9]', clob) from t1;
Note: You will need to call sqlj.refresh_classes() every time you modify your Java program and copy the new class file in sqllib\function directory.
If you run into some java related errors / issues, please refer to this article.
If you are a C/C++ person and are looking for a similar code, please refer to this article which implements regular expression using C/C++.
2. How to implement Java UDF using CLOB for JAVA Parameter passing?
1. You have decided to use the parameter style as JAVA for an UDF.
2. The Java class that you are writing should not extend UDF as you did in the previous example.
3. You should use java.sql.Clob as the data type for the CLOB.
The same example shown above is modified to use the parameter style Java.
// File: RegExClob.java
package sp;
import java.io.IOException;
import java.sql.SQLException;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
public class RegExClob
{
public static int MatchClob(String pattern,
java.sql.Clob clob)
throws SQLException, IOException
{
if (null == clob || null == pattern ||
clob.length() == 0 || pattern.length() == 0)
{
return -2;
}
Pattern p = Pattern.compile(pattern);
Long size = new Long(clob.length());
char[] cbuff = new char[size.intValue()];
if (clob.getCharacterStream().read(cbuff) == -1)
return -2;
String outStr = new String(cbuff);
Matcher m = p.matcher(outStr);
if (m.matches())
{
return 1;
}
return 1;
}
}
Copy above source in file RegExClob.java in your directory “sp” and compile this. Copy this folder “sp” with RegExClob.class in sqllib\function or sqllib/function.
Use these SQL to create the user defined function (UDF) and test the function.
call sqlj.refresh_classes();
DROP FUNCTION test_JClob;
CREATE FUNCTION test_JClob(pattern VARCHAR(4000), clob CLOB(1M))
RETURNS INTEGER
FENCED
VARIANT
NO SQL
LANGUAGE JAVA
PARAMETER STYLE JAVA
EXTERNAL NAME 'sp.RegExClob!MatchClob'
;
drop table t1;
create table t1 (c1 int not null, clob clob(1m));
insert into t1 values (1, 'abc33');
select test_jclob('\w{3}\d{2}', clob) from t1;
Please note that the java coding is not the same for different parameter passing style. This sometime leads to UDF not functioning properly. Pay attention to the way you want to pass parameters and your java coding.
The question comes as which one is better. Since DB2GENERAL uses CLI underneath, this could give a little performance boost. But, I have not done any performance testing. The parameter style JAVA will use universal JCC Type-IV driver and not the CLI.
3. Regular Expression using VARCHAR in UDF
The following UDF uses VARCHAR as a parameter instead of a CLOB and is similar to the previous example.
package sp;
import java.sql.SQLException;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
public class RegExVarchar
{
public static int MatchVarchar(String pattern,
String varchar) throws SQLException
{
Pattern p = Pattern.compile(pattern);
Matcher m = p.matcher(varchar);
if (m.matches())
{
return 1;
}
return 0;
}
}
Create a file RegExVarchar.java in “sp” folder. Compile this file using either javac or Eclipse.
Copy this folder “sp” with RegExVarchar.class in sqllib\function or sqllib/function.
Use these SQL to create the user defined function and to test it.
drop function MATCH_VARCHAR;
call sqlj.refresh_classes();
CREATE FUNCTION MATCH_VARCHAR
(
pattern VARCHAR(4000),
string VARCHAR(4000)
)
RETURNS INTEGER
FENCED
VARIANT
NO SQL
LANGUAGE JAVA
PARAMETER STYLE JAVA
EXTERNAL NAME 'sp.RegExVarchar!MatchVarchar';
values MATCH_VARCHAR('Dec[0-9][0-9][0-9][0-9]','Dec1987');
If you change the java UDF from FENCED to NOT FENCED, you will need to copy the “sp” folder to sqllib\function\unfenced.