UDF call to C wrapper for PCRE library fails - SQL0444N, Reason code: "5", SQLSTATE=42724

Hi all,

I'm trying to complete the samples from the excellent 2003
developerWorks article "Bringing the Power of Regular Expression
Matching to SQL" by Knut Stolze:

http://tinyurl.com/3bhrnn

I've managed to compile and link the C code from "Listing 5", the
regexpSimple() wrapper (see link). The build options are pretty much
the result of trial-and-error using the samples in
/home/db2inst1/sqllib/samples/c/. Any advice on how to successfully
call the C wrapper from a DB2 UDF is appreciated.

# Environment
DB2/LINUX 9.1.0
gcc (GCC) 3.4.6 20060404 (Red Hat 3.4.6-3)

# Flags
$ EXTRA_LFLAG="-Wl,-rpath,$DB2PATH/lib"
$ DB2PATH=/home/db2inst1/sqllib
$ DB2V9LIBS=/opt/ibm/db2/V9.1
$ SHARED_LIB_NAME=regexUdf

# Compile the program.
$ gcc $EXTRA_C_FLAGS -fpic -I$DB2PATH/include -I$DB2V9LIBS/include -c
utilapi.c -D_REENTRANT
$ gcc $EXTRA_C_FLAGS -fpic -I$DB2PATH/include -I$DB2V9LIBS/include -c
regex1.c -D_REENTRANT

# Link the program and create a shared library
$ gcc $EXTRA_C_FLAGS -shared -o $SHARED_LIB_NAME regex1.o utilapi.o
$EXTRA_LFLAG -L$DB2PATH/lib -ldb2 -lpthread

# Copy and chown shared library
$ cp $SHARED_LIB_NAME $DB2PATH/function
$ chown db2inst1: $DB2PATH/function/$SHARED_LIB_NAME
$ ls -l /home/db2inst1/sqllib/function/regexUdf
-rwxr-xr-x 1 db2inst1 db2grp1 10624 Feb 20 10:16 /home/db2inst1/
sqllib/function/regexUdf

However, calling this function from DB2 fails with "SQL0444N":

$ sudo su - db2inst1
$ db2 "connect to sample user db2inst1"
Enter current password for db2inst1:

Database Connection Information

Database server = DB2/LINUX 9.1.0
SQL authorization ID = DB2INST1
Local database alias = SAMPLE

$ db2 -t -f /home/db2inst1/test/db2/regex/regex1.sql
DB20000I The SQL command completed successfully.

$ db2 "select FIRSTNME, LASTNAME from employee where regex1('\w+',
FIRSTNME) = 1"

FIRSTNME LASTNAME
------------ ---------------
SQL0444N Routine "REGEX1" (specific name "REGEXSIMPLE") is
implemented with
code in library or path ".../sqllib/function/regexUdf", function
"regexpSimple" which cannot be accessed. Reason code: "5".
SQLSTATE=42724

$ cat /home/db2inst1/test/db2/regex/regex1.sql
CREATE FUNCTION regex1(pattern VARCHAR(2048), string CLOB(10M))
RETURNS INTEGER
SPECIFIC regexSimple
EXTERNAL NAME 'regexUdf!regexpSimple'
LANGUAGE C
PARAMETER STYLE DB2SQL
DETERMINISTIC
NOT FENCED
RETURNS NULL ON NULL INPUT
NO SQL
NO EXTERNAL ACTION
ALLOW PARALLEL;

$ cat /home/db2inst1/test/db2/regex/regex1.c
/*
http://www-128.ibm.com/developerwork...301stolze.html

listing5

A small chunk of C code, which implements the UDF entry point. This
entry point is called by DB2 during the query execution for each
row to be matched against the pattern. Listing 5 is an example what
this code could look like. For the description of the pcre_*
function and macros, refer to the documentation of the PCRE
library. For compiling the C code and building the shared
libraries, refer to the DB2 Application Development Guide.

*/

#include <pcre/pcre.h>
#include <sqludf.h>

void regexpSimple(
// input parameters
SQLUDF_VARCHAR *pattern, SQLUDF_CLOB *str,
// output
SQLUDF_INTEGER *match,
// null indicators
SQLUDF_NULLIND *pattern_ind, SQLUDF_NULLIND *str_ind,
SQLUDF_NULLIND *match_ind,
SQLUDF_TRAIL_ARGS)
{
pcre *re = NULL;
const char *error = NULL;
int errOffset = 0;
int rc = 0;

// we assume successful return
*match_ind = 0;

// compile the pattern to its internal representation
re = pcre_compile(pattern, 0 /* default options */, &error,
&errOffset, NULL);
if (re == NULL) {
snprintf(SQLUDF_MSGTX, 70, "Regexp compilation failed at "
"offset %d: %s\n", errOffset, error);
strcpy(SQLUDF_STATE, "38900");
(*pcre_free)(re);
return;
}

// match the string againts the pattern
rc = pcre_exec(re, NULL, str->data, str->length, 0,
0 /* default options */, NULL, 0);
switch (rc) {
case PCRE_ERROR_NOMATCH:
*match = 0;
break;
case PCRE_ERROR_BADOPTION:
snprintf(SQLUDF_MSGTX, 70, "An unrecognized bit was set in the
"
"options argument");
strcpy(SQLUDF_STATE, "38901");
break;
case PCRE_ERROR_NOMEMORY:
snprintf(SQLUDF_MSGTX, 70, "Not enough memory available.");
strcpy(SQLUDF_STATE, "38902");
break;
default:
if (rc < 0) {
snprintf(SQLUDF_MSGTX, 70, "A regexp match error "
"occured: %d", rc);
strcpy(SQLUDF_STATE, "38903");
}
else {
*match = 1;
}
break;
}

// cleanup
(*pcre_free)(re);
return;
}

--
Serman D.

re: UDF call to C wrapper for PCRE library fails - SQL0444N, Reason code: "5", SQLSTATE=42724

Serman D. wrote:
Why are you compiling (and later linking) with "utilapi.c"? Just get rid of
it.
Remove the -I$DB2V9LIBS/include thing. It is not needed.
Since the code does not access any DB2 APIs, you don't need -ldb2 here.

On the other hand, you must specify -lpcre here so that the PCRE library is
used when your shared lib is loaded. Otherwise, PCRE is not loaded,
leading to unresolved symbols and the SQL0444 you are seeing.
http://www-128.ibm.com/developerwork...301stolze.htmlYou may want to add the following here:

#include <stdio.h>
#include <string.h>
Here is what I did just now:

$ gcc -c -o regexUdf.o -I ~/sqllib/include regexUdf.c -D_REENTRANT
$ gcc -o regexUdf -shared -fpic regexUdf.o
$ nm regexUdf
[...]
U pcre_compile
U pcre_exec
U pcre_free
000005ac T regexpSimple

$ cp regexUdf ~/sqllib/function/

$ db2 -tvf regex.sql
CREATE FUNCTION regex1(pattern VARCHAR(2048), string CLOB(10M))
RETURNS INTEGER
SPECIFIC regexSimple
EXTERNAL NAME 'regexUdf!regexpSimple'
LANGUAGE C
PARAMETER STYLE DB2SQL
DETERMINISTIC
NOT FENCED
RETURNS NULL ON NULL INPUT
NO SQL
NO EXTERNAL ACTION
ALLOW PARALLEL
DB20000I The SQL command completed successfully.

$ db2 "values regex1('\\w+', 'abcdef')"

1
-----------
1

1 record(s) selected.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

re: UDF call to C wrapper for PCRE library fails - SQL0444N, Reason code: "5", SQLSTATE=42724

Thank you Knut. Your comments are very useful.

--
Serman D.