Adding support for the digit wildcard character in iHub when the metadata database is DB2
In a stand‑alone iHub installation that uses DB2 to store Encyclopedia volume metadata, the pound sign ('#') in iHub is treated as a single alphanumeric wildcard character instead of a digit wildcard character. This limitation exists because DB2 does not support regular expressions in SQL syntax.
The administrator can add regular expression testing capability to DB2 by creating a User Defined Function, or UDF. The following article documents this approach:
http://www.ibm.com/developerworks/data/library/techarticle/dm-1011db2luwpatternmatch/index.html
The solution consists of the following parts:
Create a Java implementation that performs the regular-expression testing functionality. In the article, IBM provides the sample java code for such an implementation. See the Implementation section in the article. IBM also provides the pre-built java package, db2_regex.jar, for download. See the Download section in the article.
Create a UDF to access the external Java method. Use the following sample implementation as an example:
CREATE OR REPLACE FUNCTION REGEXP_LIKE(SOURCE VARCHAR(3000), REGEX VARCHAR(512),
MODE VARCHAR(3))
RETURNS INTEGER
FENCED
NOT DETERMINISTIC
NO SQL
LANGUAGE JAVA
PARAMETER STYLE JAVA
EXTERNAL NAME 'db2_regex:com.ibm.avalanche.udf.regex.Regexp.regexpLike'
NO EXTERNAL ACTION
@
You can create this method in a schema that the Encyclopedia volume schema owner accesses.
After creating the UDF, grant execute privileges to the volume schema user as well as to the iHub application user by executing the following DDL statements:
GRANT EXECUTE ON FUNCTION REGEXP_LIKE( VARCHAR(3000), VARCHAR(512),
VARCHAR(3) )
TO USER /* volume schema user */ WITH GRANT OPTION
GRANT EXECUTE ON FUNCTION REGEXP_LIKE( VARCHAR(3000), VARCHAR(512),
VARCHAR(3) )
TO USER /* iHub application user */
To support the Encyclopedia engine smart search capability to use this regular expression UDF, update the DB2.xml file in the AC_SERVER_HOME\etc\DataStores\DatabaseConfiguration folder. Change the MatchOpMapper section from the following:
<MatchOpMapper SingleMatch="_"
GreedyMatch="%"
DigitMatch="_"
EscapeTemplate="@$"
AdditionalSpecialChars="@">
<FunctionMappings>
<FunctionMapping FunctionName="MATCH">
$P0 LIKE $P1 ESCAPE '@'
</FunctionMapping>
</FunctionMappings>
</MatchOpMapper>
to the following:
<MatchOpMapper SingleMatch="."
GreedyMatch=".*"
DigitMatch="[0-9]"
AdditionalSpecialChars="\^.$|()[]*+?{},">
<FunctionMappings>
<FunctionMapping FunctionName="MATCH">
REGEXP_LIKE
( $P0 , '^' || $P1 || '$$', 'c' ) > 0
</FunctionMapping>
</FunctionMappings>
</MatchOpMapper>
Note that you may need to prefix the UDF REGEXP_LIKE with the schema name, depending on where the function is located.