第二部分由一小段 C 代码组成,它实现了 UDF 入口点。在查询执行期间,DB2 为每个要与模式匹配的行调用这个入口点。 清单 5中的示例列出了该代码的清单。有关 pcre_* 函数和宏的描述,请参考 PCRE 库的文档。有关 C 代码的编译和共享库的构建,请参考 DB2 Application Development Guide。
#include #include 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;
}
用法示例
下列查询试图从表 strTable 中找出包含注释文本的所有字符串。注释以“#”开头,所以模式是“#”后跟非空文本。
SELECT c1, str
FROM strTable
WHERE regex1('#\\s*\\w+', str) = 1;
结果只包含 c1 = 6 的行。
C1 STR
----------- -------------------------
6 # comment;
1 record(s) selected.
在第二个示例中,我们试图找到这种赋值形式的字符串;即“text = text”。为了进一步缩小范围,我们只查找那些右端为数值的赋值。将十六进制表示法作为有效数值对待。
SELECT c1, str
FROM strTable
WHERE regex1('\\w+\\s*=\\s*(\\d+|0x\\d\\d)', str) = 1;
除了 c1 为 2 或 5 的两行以外,其它行都不包含数值的赋值,因此不会出现在结果中:
C1 STR
----------- -------------------------
2 variable = 1234;
5 myVar = 0x00;
2 record(s) selected.
改进性能
尽管上面的函数按照预期的方式工作,但还可以改进它以获得更佳的性能。注:函数内部的执行完成得越快,DB2 处理整个 SQL 语句的速度也就越快。
SQL 旨在处理多组行,这意味着通常会针对一个模式匹配多个行。在大多数情况下,模式本身对于整个 SQL 语句都是不变的;即,它不会随行的更改而更改。 清单 5 中的 C 代码展示了对每一行都调用函数 pcre_compile() ,该函数将给定模式转换成内部表示法。
DB2 通过使用所谓的“高速暂存(scratchpad)”提供了在 UDF 调用之间传递信息的机制。此外,您可以标识特定调用“类型”;即它是对该 UDF 的第一次调用、普通调用还是最后一次(最终)调用。使用高速暂存和调用类型,有可能只对模式编译一次,然后将该已编译模式的内部表示法重用于对该 UDF 的所有后续调用。在最后一次调用时,释放在处理期间分配的资源。
如 清单 6所示,对 CREATE FUNCTION 语句进行修改,告诉 DB2 向外部 C 代码提供高速暂存和调用类型:
CREATE FUNCTION regex2(pattern VARCHAR(2048), string CLOB(10M))
RETURNS INTEGER
SPECIFIC regexPerf
EXTERNAL NAME 'regexUdf!regexpPerf'
LANGUAGE C
PARAMETER STYLE DB2SQL
DETERMINISTIC
NOT FENCED
RETURNS NULL ON NULL INPUT
NO SQL
NO EXTERNAL ACTION
SCRATCHPAD 50
FINAL CALL
ALLOW PARALLEL;
UDF 入口点看起来很不一样,因为必须改写函数内部的逻辑。参数方面唯一的更改是使用 SQLUDF_TRAIL_ARGS_ALL 代替了 SQLUDF_TRAIL_ARGS ,如 清单 7所示。
#include #include // data structure mapped on the scratchpad for easier use and access
// to the objects
// the size of the scratchpad defined in the CREATE FUNCTION statement
// must be at least as large as sizeof(scratchPadMapping)
struct scratchPadMapping {
pcre *re;
pcre_extra *extra;
const char *error;
int errOffset;
};
void regexpPerf(
// 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_ALL) // SQLUDF_SCRAT & SQLUDF_CALLT
{
int rc = 0;
struct scratchPadMapping *scratch = NULL;
// map the buffer of the scratchpad and assume successful return
scratch = (struct scratchPadMapping *)SQLUDF_SCRAT->data;
*match_ind = 0;
switch (SQLUDF_CALLT) {
case SQLUDF_FIRST_CALL:
// initialize data on the scratchpad
scratch->re = NULL;
scratch->extra = NULL;
scratch->error = NULL;
scratch->errOffset = 0;
// compile the pattern (only in the FIRST call
scratch->re = pcre_compile(pattern, 0 /* default options */,
&scratch->error, &scratch->errOffset, NULL);
if (scratch->re == NULL) {
snprintf(SQLUDF_MSGTX, 70, "Regexp compilation failed at "
"offset %d: %s\\n", scratch->errOffset, scratch->error);
strcpy(SQLUDF_STATE, "38900");
rc = -1;
break;
}
// further analyze the pattern (might return NULL)
scratch->extra = pcre_study(scratch->re,
0 /* default options */, &scratch->error);
/* fall through to NORMAL call because DB2 expects a result
already in the FIRST call */
case SQLUDF_NORMAL_CALL:
// match the current string
rc = pcre_exec(scratch->re, scratch->extra, str->data,
str->length, 0, 0 /* default options */, NULL, 0);
switch (rc) {
case PCRE_ERROR_NOMATCH:
*match = 0;
rc = 0;
break;
case PCRE_ERROR_BADOPTION:
snprintf(SQLUDF_MSGTX, 70, "An unrecognized bit was set "
"in the options argument");
strcpy(SQLUDF_STATE, "38901");
rc = -1;
break;
case PCRE_ERROR_NOMEMORY:
snprintf(SQLUDF_MSGTX, 70, "Not enough memory available.");
strcpy(SQLUDF_STATE, "38902");
rc = -1;
break;
default:
if (rc < 0) {
snprintf(SQLUDF_MSGTX, 70, "A regexp match error "
"occured: %d", rc);
strcpy(SQLUDF_STATE, "38903");
rc = -1;
}
else {
*match = 1;
rc = 0;
}
break;
}
break;
}
// cleanup in FINAL call, or if we encountered an error in
// the FIRST call (DB2 will make a FINAL call if we encounter
// an error in any NORMAL call)
if (SQLUDF_CALLT == SQLUDF_FINAL_CALL ||
(SQLUDF_CALLT == SQLUDF_FIRST_CALL && rc < 0)) {
(*pcre_free)(scratch->re);
(*pcre_free)(scratch->extra);
}
return;
}
为了进一步改进该函数的性能,我添加了对函数 pcre_study() 的调用,该函数是由模式匹配引擎提供的。该函数进一步分析了该模式,并将额外的信息存储在独立的结构中。然后,在实际的匹配期间使用这些额外的信息来加快处理速度。通过使用一个非常简单的模式和大约 4000 行的表,我获得了 5% 的执行时间的改善。当然,模式越复杂,差异将越显著。
内容导航