By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,004 Members | 1,253 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,004 IT Pros & Developers. It's quick & easy.

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

P: n/a
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.

Feb 20 '07 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Serman D. wrote:
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
Why are you compiling (and later linking) with "utilapi.c"? Just get rid of
it.
$ gcc $EXTRA_C_FLAGS -fpic -I$DB2PATH/include -I$DB2V9LIBS/include -c
regex1.c -D_REENTRANT
Remove the -I$DB2V9LIBS/include thing. It is not needed.
# 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
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.
# 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>
You may want to add the following here:

#include <stdio.h>
#include <string.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;
}
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
Feb 20 '07 #2

P: n/a

Thank you Knut. Your comments are very useful.

--
Serman D.

Feb 20 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.