A rather long and windy post, but since the traffic in this group is
moderate for the moment, I'll give it a shot.

9.5 fixpak 5, after a bit of struggling I managed to compile and install
an external java function. Not sure whether it actually works, but it
compiles and runs fine:

db2 "values db2inst1.removeDiacritics('abcde')"

1
--------------------------------------------------
abcde


Ultimately I would like to add a generated column like:

create table T (
x varchar(20) not null,
y generated always as ( db2inst1.removeDiacritics('abcde') )
);

but ...

SQL0548N A check constraint or generated column that is defined with
"REMOVEDIACRITICS" is invalid. SQLSTATE=42621


mutter, ok let's add two before triggers and a check constraint

drop table T @
create table T (
x varchar(20) not null,
y varchar(20) not null
) @


create trigger db2inst1.irb
before insert on T
referencing new as n
for each row
mode db2sql
set y = db2inst1.removeDiacritics(n.x)

@

create trigger db2inst1.urb
before update of x on T
referencing new as n
for each row
mode db2sql
set y = db2inst1.removeDiacritics(n.x)

@

alter table T add constraint TT
check ( y = db2inst1.removeDiacritics(x) ) @

Same error:

SQL0546N The check constraint "TT" is invalid. SQLSTATE=42621



Looking at the error I'm not sure what violation I encounter:

A check constraint in the CREATE TABLE or CREATE NICKNAME, ALTER TABLE,
or ALTER NICKNAME statement is invalid for one or more of the following
reasons:
* the constraint definition contains a subquery
* the constraint definition contains a column function
* the constraint definition contains a host variable
* the constraint definition contains a parameter marker
* the constraint definition contains a special register
* the constraint definition contains a global variable
* the constraint definition contains a variant user defined function
* the constraint definition contains a user defined function with
external action
* the constraint definition contains a user defined function with the
scratchpad option
* the check constraint is part of a column definition, and its
check-condition contains a reference to a column name other than the
column being defined.
* the constraint definition contains a dereference operation or a DEREF
function where the scoped reference argument is other than the object
identifier (OID) column.
* the constraint definition uses a TYPE predicate.
* the constraint definition includes a CAST specification with a SCOPE
clause.
* the functional dependency is defined with attribute ENFORCED.
* a nullable column is specified in the child-set columns of the
functional dependency.
* the constraint definition contains a text search function.
* the constraint definition contains an XML column.
* the constraint definition contains an XMLQUERY or XMLEXISTS
expression.


One obvious violation is:

"the constraint definition contains a column function"

but AFAIK this would also disqualify constraints like:

check ( y = upper(x) )

which works fine.


Has any one succeded enforcing a rule like the one discussed here
without resolving to triggers throwing exceptions?


/Lennart


##

]$ cat nya/util/udf/string/StringUtil.java

package nya.util.udf.string;

import java.sql.SQLException;
import sun.text.Normalizer;
import java.util.regex.Pattern;

public class StringUtil {

private static Pattern DIACRITICS =
Pattern.compile("\\p{InCombiningDiacriticalMarks}+ ");

/**
* Remove all diacritics (é becomes e etc).
*
* @param s String to clean
*/
public static String removeDiacritics(String s)
throws SQLException
{

return s == null ? s : DIACRITICS.matcher(Normalizer.normalize(s,
Normalizer.DECOMP, 0)).replaceAll("");

}
}

##

]$ cat stringutil.ddl
DROP FUNCTION db2inst1.removeDiacritics @
CALL sqlj.remove_jar('stringutil') @
CALL sqlj.install_jar(
'file:/opt/nya/users/db2inst1/nobackup/ltjn/erik/stringutil/stringutil.jar',
'stringutil') @

CREATE FUNCTION db2inst1.removeDiacritics( s varchar(50))
RETURNS varchar(50)
FENCED THREADSAFE
NOT DETERMINISTIC
NO SQL
LANGUAGE JAVA
PARAMETER STYLE JAVA
RETURNS NULL ON NULL INPUT
EXTERNAL NAME
'stringutil:nya.util.udf.string.StringUtil!removeD iacritics'
NO EXTERNAL ACTION
@

##

]$ cat build
#!/bin/sh

rm -f nya/util/udf/string/StringUtil.class
~/sqllib/java/jdk64/bin/javac nya/util/udf/string/StringUtil.java
rm -f stringutil.jar
jar cvf stringutil.jar nya/util/udf/string/StringUtil.class
db2 connect to <db>
db2 -td@ -f stringutil.ddl

On 2011-06-10 22:20, Lennart Jonsson wrote:
- y generated always as ( db2inst1.removeDiacritics('abcde') )
+ y generated always as ( db2inst1.removeDiacritics(x) )

On 6/11/2011 1:25 AM, Lennart Jonsson wrote:
NO SQL NO EXTERNAL ACTION DETERMINISTIC

Cheers
Serge

--
Serge Rielau
SQL Architect DB2 for LUW, IBM Toronto Lab
Blog: tinyurl.com/SQLTips4DB2
Wiki: tinyurl.com/Oracle2DB2Wiki
Twitter: srielau

On Jun 14, 8:22*pm, Serge Rielau <srie... (AT) ca (DOT) ibm.com> wrote:
[...]
Excellent, works like a charm.


/Lennart

Would t not be easer to write a bunch of nested calls to REPLCE() ? It
would be faster than any external procedure. Then take the search
condition and put into ait into a CHECK() in the DDL:

CREATE TABLE Customers
(..
cust_name VARCHAR(25) NOTNULL
CHECK(cust_name = REPLACE (REPLACE ...),
...);

The optimizer can see the logic and use it in DML statements. Now
the \business is done one way, in one place, one time. :First you mop
the floor, then stop the leak.

On 2011-06-16 18:58, --CELKO-- wrote:
In the eye of the beholder I guess.

This functionality is likely spread over several tables, i.e. not one
way (possibly), nor place given your suggestion. A function is a nice
abstraction which encapsulates the logic one way, and in one place.

A generated column in DB2 creates a check constraint which the optimizer
*possibly* could use, and that regardless if the function is implemented
in java or sql.


/Lennart