 |
级别： 初级
Knut Stolze, DB2 信息集成, IBM 德国
2003 年 3 月 01 日
作家兼开发人员 Knut Stolze 向您展示了如何使用 DB2 用户自定义函数来执行数据库服务器之外的任务。特别地，他使用 C 语言函数来向您展示如何访问文件和调用其它可执行文件。
简介
有时，您需要从 SQL 语句中访问操作系统功能。例如，您可能希望将一些信息写到文件、与特定的进程建立通信链路或执行驻留在数据库服务器上的另一个程序。IBM® DB2® Universal Database™（UDB）的用户自定义函数（UDF）可以使您方便地完成这些任务。您可以使用各种不同的编程语言（包括 SQL、Java™ 或 C）实现 UDF。在本文中，我将用一些示例向您展示，用 C 编写的 UDF 如何在数据库服务器上被用来直接执行“外部”任务。特别地，我将向您展示如何：
- 访问驻留在数据库服务器上的文件
- 调用另一个可执行文件。
处理文件系统中文件的方法基本上有两种：从文件中读取或写入文件。在 SQL 中使用 UDF，可以轻松地完成这些任务。例如，要读取文件，您可以将该文件的名称指定为 SQL 语句中的表达式，而 UDF 可以将该文件名作为输入，并将文件的内容作为其结果返回。可以象对待 SQL 语句中的任何其它值那样对待这个结果：可以将它插入表、以结果集的形式返回给客户机或由其它函数进一步处理它。同样，通过指定文件名和字符串本身，可以从 SQL 启动将字符串写入到文件的操作。函数打开文件，写入字符串，然后返回成功或错误状态。
从 SQL 调用数据库服务器上的可执行文件或脚本可能会派上用场。例如，您可能希望根据数据库中的事件或条件启动一些任务。譬如，表中的行太多您可能想开始进行备份，或者您可能想使用另一个程序来执行某一任务（譬如向管理员发送电子邮件）。另一种可能的选择是在独立的事务中执行 SQL 脚本。
在我向您展示这些 UDF 之前，让我们简要地回顾一下如何编译 UDF 的 C/C++ 代码并将它链接到共享库，以便 DB2 此后能够使用该函数。
构建用户自定义函数
要构建本文中描述的函数，编译源文件 os_calls.c （请参阅 下载）并将它链接到共享库。如果您愿意，还可以定义导出文件，它定义该库的入口点。您必须根据平台使用不同样式的导出文件。Windows® 系统需要导出文件 library.def 中具有如 清单 1所示的内容。
清单 1. Windows 系统上的导出文件
LIBRARY systemCallUDF
EXPORTS
readFileToClob
readFileToTable
writeFileFromParams
systemCall
|
UNIX® 系统使用 .exp 作为导出文件 library.exp 的扩展名，该文件中的结构非常简单。它只列出将要导出的函数的入口点，而不包含更多的信息。 清单 2显示了这样一个示例。
清单 2. UNIX 和 Linux 系统上的导出文件
readFileToClob
readFileToTable
writeFileFromParams
systemCall
|
使用相应的导出文件和源文件来编译和链接代码。要完成这项任务，您必须确保在用来开发这些函数的系统上安装了 C 编译器和 DB2 应用程序开发客户机（Application Development Client）。注：在打算使用这些函数的 生产系统上不需要有 C 编译器或 DB2 应用程序开发客户机。为了简化构建过程，可以使用实例目录中的 sqllib/samples/c/bldrtn 脚本（bldrtn 表示 构建例程，build routine）。如果使用脚本，就必须提供导出文件。
注：对于版本 DB2 UDB 7.x，在 UNIX 系统上使用 bldudf 工具，在 Windows 系统上使用 bldmudf 工具。语法略有不同；请参考文件中的描述以获取更多详细信息。
要在文件 os_calls.c 中构建 UDF，只要执行：
INSTHOME
/sqllib/samples/c/bldrtn os_calls
|
其中 INSTHOME 是到实例主目录的路径。例如：
c:\\program files\\ibm\\sqllib\\samples\\c\\bldrtn os_calls
|
或
/home/stolze/sqllib/samples/c/bldrtn os_calls
|
该脚本的最后一步将共享库复制到实例目录中的 sqllib/function 目录。
从文件中读取
当读取文件时，如何用 SQL 表示文件内容有几种选择。例如，整个文件内容可以变成一个标量 VARCHAR 或 CLOB 值（二进制文件可以转换成 BLOB），或者如果文件有良好定义的结构，那么该结构就可以被映射成关系表。在第一种情况下，外部的标量函数可以完成该任务；在第二种情况下，我们使用 DB2 表函数来执行从文件内部结构到数据库系统中的表结构的映射。
首先，我们处理将文件的整个内容表示为单个 CLOB 值的基本情况。这意味着读取该文件的整个内容并将它存储到 LOB 值中，然后将该 LOB 值返回到数据库引擎。该函数的输入是绝对文件名，输出是包含结果的 CLOB。使用 清单 3中的 SQL 语句在数据库中创建函数。
清单 3. 创建将文件读入 CLOB 中的函数
CREATE FUNCTION readFile(fileName VARCHAR(255))
RETURNS CLOB(1M)
SPECIFIC readFileToClob
EXTERNAL NAME 'os_calls!readFileToClob'
LANGUAGE C
PARAMETER STYLE SQL
DETERMINISTIC
NOT FENCED
RETURNS NULL ON NULL INPUT
NO SQL
NO EXTERNAL ACTION
NO SCRATCHPAD
ALLOW PARALLEL
NO FINAL CALL;
GRANT EXECUTE ON FUNCTION readFile TO PUBLIC;
|
注：仅有 DB2 UDB V8 支持 GRANT EXECUTE 语句。同样，对于 DB2 UDB V7，必须将 PARAMETERSTYLE SQL 语句替换成 PARAMETER STYLE DB2SQL。
实现该函数的相应 C 代码也很简单，如 清单 4所示。该代码打开指定文件、验证文件的大小以确保所有数据都能复制到 CLOB，然后在关闭该文件之前将数据实际复制到 CLOB。
清单 4. 将文件读入 CLOB 中的 C 代码
#include <stdio.h>
#include <sqludf.h>
void SQL_API_FN readFileToClob(
SQLUDF_VARCHAR *fileName,
SQLUDF_CLOB *fileData, /* output */
/* null indicators */
SQLUDF_NULLIND *fileName_ind,
SQLUDF_NULLIND *fileData_ind,
SQLUDF_TRAIL_ARGS)
{
int rc = 0;
long fileSize = 0;
size_t readCnt = 0;
FILE *f = NULL;
f = fopen(fileName, "r");
if (!f) {
strcpy(SQLUDF_MSGTX, "Could not open file ");
strncat(SQLUDF_MSGTX, fileName,
SQLUDF_MSGTEXT_LEN - strlen(SQLUDF_MSGTX)-1);
strncpy(SQLUDF_STATE, "38100", SQLUDF_SQLSTATE_LEN);
return;
}
rc = fseek(f, 0, SEEK_END);
if (rc) {
sprintf(SQLUDF_MSGTX, "fseek() failed with rc = %d", rc);
strncpy(SQLUDF_STATE, "38101", SQLUDF_SQLSTATE_LEN);
return;
}
/* verify the file size */
fileSize = ftell(f);
if (fileSize > fileData->length) {
strcpy(SQLUDF_MSGTX, "File too large");
strncpy(SQLUDF_STATE, "38102", SQLUDF_SQLSTATE_LEN);
return;
}
/* go to the beginning and read the entire file */
rc = fseek(f, 0, 0);
if (rc) {
sprintf(SQLUDF_MSGTX, "fseek() failed with rc = %d", rc);
strncpy(SQLUDF_STATE, "38103", SQLUDF_SQLSTATE_LEN);
return;
}
readCnt = fread(fileData->data, 1, fileSize, f);
if (readCnt != fileSize) {
/* raise a warning that something weird is going on */
sprintf(SQLUDF_MSGTX, "Could not read entire file "
"(%d vs %d)", readCnt, fileSize);
strncpy(SQLUDF_STATE, "01H10", SQLUDF_SQLSTATE_LEN);
*fileData_ind = -1;
}
else {
fileData->length = readCnt;
*fileData_ind = 0;
}
}
|
在编译并安装了包含 UDF 的共享库之后，让我们执行一些非常基本的成功和错误用例的测试：
/home/stolze $ echo "test" > test_file
/home/stolze $ db2 "VALUES readFile( '/home/stolze/test_file' )"
1
--------------------------------------------------------
test
1 record(s) selected.
/home/stolze $ db2 "VALUES readFile( '/home/stolze/test_file123' )"
1
--------------------------------------------------------
SQL0443N Routine "READFILE" (specific name "READFILETOCLOB") has
returned an error SQLSTATE with diagnostic text "Could not open
file /home/stolze/test_file123". SQLSTATE=38100
|
此后，就可以在 SQL 语句中象使用其它函数一样使用这个 UDF，例如在下列 INSERT 中：
/home/stolze $ db2 "CREATE TABLE test_table ( text CLOB(2M) )"
DB20000I The SQL statement completed successfully.
/home/stolze $ db2 "INSERT INTO test_table VALUES ( readFile( '/home/stolze/test_file' ) )"
DB20000I The SQL statement completed successfully.
/home/stolze $ db2 "SELECT SUBSTR(text, 2, 3) FROM test_table"
1
---
est
1 record(s) selected.
|
使用表函数
接下来将说明如何将具有良好定义结构的文件表示为关系表。在这种情况下，我将其作为 opaque 数据，实现了从文件内容到表的映射，而不是简单地将整个文件复制到单个 LOB 中。
对于我们的示例，假定文件有下列结构：
- 该文件由一系列统一的结构化记录组成。
- 每个记录总共包含 60 字节。
- 前 10 个字节包含（以文本表示的）32 位带符号的整数值。
- 后 50 个字节表示一个定长字符串。
从文件结构到关系结构的简单映射就是把文件中的所有记录作为一个具有两列的表返回。第一列的类型是 INTEGER，第二列的类型是 VARCHAR(50)。文件中的每个记录将成为表中的一行。注：其它文件可能采用不同的文件格式，每种格式都需要属于自己的专门映射。您需要为支持每种映射而实现单独的函数。
定义了映射之后，让我们创建数据库系统中的函数。 清单 5向您展示了创建的方法。
清单 5. 创建读取文件并将它表示为表的函数
CREATE FUNCTION readFileTable(fileName VARCHAR(256))
RETURNS TABLE ( intCol INTEGER, string VARCHAR(50) )
SPECIFIC readFileToTable
EXTERNAL NAME 'os_calls!readFileToTable'
LANGUAGE C
PARAMETER STYLE SQL
DETERMINISTIC
NOT FENCED
RETURNS NULL ON NULL INPUT
NO SQL
NO EXTERNAL ACTION
SCRATCHPAD
NO FINAL CALL
DISALLOW PARALLEL;
GRANT EXECUTE ON FUNCTION readFileTable TO PUBLIC;
|
清单 6展示了实现从文件格式到关系表映射的 C 代码。
清单 6. 将文件内容表示为表的 C 代码
#include <stdio.h>
#include <sqludf.h>
#include <sqlstate.h>
void SQL_API_FN readFileToTable(
SQLUDF_VARCHAR *fileName, /* input */
SQLUDF_INTEGER *intCol, /* output */
SQLUDF_VARCHAR *charCol, /* output */
/* null indicators */
SQLUDF_NULLIND *fileName_ind,
SQLUDF_NULLIND *intCol_ind,
SQLUDF_NULLIND *charCol_ind,
SQLUDF_TRAIL_ARGS_ALL)
{
size_t readCnt = 0;
char intValue[10+1] = { '\\0' };
char strValue[50+1] = { '\\0' };
struct scratchMap {
FILE *f;
int rowNumber;
};
/* map the scratchpad */
struct scratchMap *scratch =
(struct scratchMap *)SQLUDF_SCRAT->data;
*intCol_ind = -1;
*charCol_ind = -1;
switch (SQLUDF_CALLT) {
case SQLUDF_TF_OPEN:
/* open file and store the pointer on the scratchpad */
scratch->f = fopen(fileName, "r");
if (!scratch->f) {
strcpy(SQLUDF_MSGTX, "Could not open file ");
strncat(SQLUDF_MSGTX, fileName, SQLUDF_MSGTEXT_LEN -
strlen(SQLUDF_MSGTX)-1);
strncpy(SQLUDF_STATE, "38200", SQLUDF_SQLSTATE_LEN);
return;
}
scratch->rowNumber = 0;
break;
case SQLUDF_TF_FETCH:
/* count the row */
scratch->rowNumber++;
/* read the integer */
readCnt = fread(intValue, 1, 10, scratch->f);
if (readCnt == 0) {
/* end of file reached */
/* end of file reached */
strncpy(SQLUDF_STATE, SQL_NODATA_EXCEPTION,
SQLUDF_SQLSTATE_LEN);
return;
}
else if (readCnt != 10) {
sprintf(SQLUDF_MSGTX, "Could not read int value "
"in line %d", scratch->rowNumber);
strncpy(SQLUDF_STATE, "38201", SQLUDF_SQLSTATE_LEN);
return;
}
intValue[10] = '\\0';
if (sscanf(intValue, "%d", intCol) != 1) {
sprintf(SQLUDF_MSGTX, "Invalid integer value %s "
" in row %d", intValue, scratch->rowNumber);
strncpy(SQLUDF_STATE, "38202", SQLUDF_SQLSTATE_LEN);
return;
}
*intCol_ind = 0;
/* read the string (allow truncations at EOF) */
readCnt = fread(strValue, 1, 50, scratch->f);
strValue[readCnt] = '\\0';
strcpy(charCol, strValue);
*charCol_ind = 0;
break;
case SQLUDF_TF_CLOSE:
/* close the file */
fclose(scratch->f);
scratch->f = NULL;
scratch->rowNumber = 0;
}
}
|
现在，让我们构造一个符合上面指定文件格式的文件。用此类文件，可以验证 UDF 是否正常运行。注：我们一定不能在文件中附加行结束符，因为这样做可能不符合所要求的文件格式。用于 echo 命令的 -n 选项意味着我们未终止该字符串。生成文件的方式因所用的操作系统而异。
/home/stolze $ echo -n "1234567890" > test_file
/home/stolze $ echo -n "abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwx" >> test_file
/home/stolze $ echo -n " 987654" >> test_file
/home/stolze $ echo -n "12345678901234567890123456789012345678901234567890" >> test_file
/home/stolze $ echo -n " -2" >> test_file
/home/stolze $ echo -n " test string " >> test_file
|
调用表函数的语法与调用标量函数的不同。以下 SELECT 语句演示了如何在 FROM 子句中使用表函数。接下来，您会看到一些非常基本的对该函数的测试。
/home/stolze $ db2 "SELECT * FROM TABLE ( readFileTable( '/home/stolze/test_file' ) ) AS t"
INTCOL STRING
----------- --------------------------------------------------
1234567890 abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwx
987654 12345678901234567890123456789012345678901234567890
-2 test string
3 record(s) selected.
/home/stolze $ db2 "SELECT * FROM TABLE ( readFileTable( '/home/stolze/test_file123' ) ) AS t"
INTCOL STRING
----------- --------------------------------------------------
SQL0443N Routine "READFILETABLE" (specific name "*LETOTABLE") has
returned an error SQLSTATE with diagnostic text "Could not open
file /home/stolze/test_file123". SQLSTATE=38200
/home/stolze $ echo -n "123" > test_file
/home/stolze $ db2 "SELECT * FROM TABLE ( readFileTable( '/home/stolze/test_file' ) ) AS t"
INTCOL STRING
----------- --------------------------------------------------
SQL0443N Routine "READFILETABLE" (specific name
"READFILETOTABLE") has returned an error SQLSTATE with diagnostic
text "Could not read int value in line 1". SQLSTATE=38201
/home/stolze $ touch empty_test
/home/stolze $ db2 "SELECT * FROM TABLE ( readFileTable( '/home/stolze/empty_test' ) ) AS t"
INTCOL STRING
----------- --------------------------------------------------
0 record(s) selected.
|
|
|
写入文件
文章“ 从 SQL 中进行文件输出和调试的 UDF”展示了一种常见情形，在那种情形下如果能够从 SQL 语句内部将数据写到文件将是非常有帮助的。该文章应用了本节所介绍的概念。
写入文件的操作基本上是从文件读取的逆过程。必须实现文件格式与将要写入的信息之间的映射。同样，用户自定义函数可以用很方便的方式执行这些任务。在下列示例中，我假定所采用的文件格式定义与我们上面用过的相同。
- 该文件由一系列统一的结构化记录组成。
- 每个记录总共包含 60 字节。
- 前 10 个字节包含以文本表示的 32 位带符号的整数值。
- 后 50 个字节表示定长字符串。
实现写操作的用户自定义函数接受两个输入参数。第一个参数的类型是 INTEGER，第二个参数的类型是 VARCHAR(50)。我们由此实现了如 清单 7所示的 SQL 语句，用来注册数据库中的函数。
清单 7. 创建将数据写入到文件的函数
CREATE FUNCTION writeFile( fileName VARCHAR(255),
intValue INTEGER, strValue VARCHAR(50) )
RETURNS INTEGER
SPECIFIC writeFile
EXTERNAL NAME 'os_calls!writeFileFromParams'
LANGUAGE C
PARAMETER STYLE SQL
DETERMINISTIC
NOT FENCED
RETURNS NULL ON NULL INPUT
NO SQL
EXTERNAL ACTION
SCRATCHPAD
DISALLOW PARALLEL
FINAL CALL;
GRANT EXECUTE ON FUNCTION writeFile TO PUBLIC;
|
使用高速暂存（scratchpad）来将打开文件描述符从一个 UDF 调用传递到下一个调用。这样，如果要在单个 SQL 语句向文件写入多个记录，我们就可以节省反复打开和关闭文件所用的时间。实现写操作的 C 代码也很简单。 清单 8显示了详细信息。必须对调用类型给予特别关注，但除此以外，只需以正确的格式将数据写入文件。
清单 8. 将数据写入文件的 C 代码
#include <stdio.h>
#include <string.h>
#include <sqludf.h>
void SQL_API_FN writeFileFromParams(
SQLUDF_VARCHAR *fileName, /* input */
SQLUDF_INTEGER *intCol, /* input */
SQLUDF_VARCHAR *charCol, /* input */
SQLUDF_INTEGER *result, /* output */
/* null indicators */
SQLUDF_NULLIND *fileName_ind, SQLUDF_NULLIND *intCol_ind,
SQLUDF_NULLIND *charCol_ind, SQLUDF_NULLIND *result_ind,
SQLUDF_TRAIL_ARGS_ALL)
{
int rc = 0;
int writtenLen = 0;
char row[60+1] = { '\\0' };
struct scratchMap {
FILE *f;
};
/* map the scratchpad */
struct scratchMap *scratch =
struct scratchMap *scratch =
(struct scratchMap *)SQLUDF_SCRAT->data;
*result_ind = -1;
switch (SQLUDF_CALLT) {
case SQLUDF_FIRST_CALL:
/* open file and store the pointer on the scratchpad */
scratch->f = fopen(fileName, "wb");
if (!scratch->f) {
strcpy(SQLUDF_MSGTX, "Could not open file ");
strncat(SQLUDF_MSGTX, fileName, SQLUDF_MSGTEXT_LEN -
strlen(SQLUDF_MSGTX)-1);
strncpy(SQLUDF_STATE, "38300", SQLUDF_SQLSTATE_LEN);
rc = -1;
goto cleanup;
}
}
case SQLUDF_NORMAL_CALL:
if (*intCol < -999999999) {
/* a number of -1000000000 or smaller needs
11 bytes, which is too long for our format */
sprintf(SQLUDF_MSGTX, "The number %d is too long.",
*intCol);
strncpy(SQLUDF_STATE, "38301", SQLUDF_SQLSTATE_LEN);
rc = -2;
goto cleanup;
}
if (strlen(charCol) > 50) {
/* strings must not be longer than 50 characters */
sprintf(SQLUDF_MSGTX, "The string %s is too long.",
charCol);
strncpy(SQLUDF_STATE, "38302", SQLUDF_SQLSTATE_LEN);
rc = -3;
goto cleanup;
}
/* write the given integer and string */
writtenLen = fprintf(scratch->f, "%10d%-50s", *intCol,
charCol);
if (writtenLen != 60) {
sprintf(SQLUDF_MSGTX, "Did not write 60 bytes "
"(wrote %d).", writtenLen);
strncpy(SQLUDF_STATE, "38303", SQLUDF_SQLSTATE_LEN);
rc = -4;
goto cleanup;
}
*result = 0;
*result_ind = 0;
break;
}
cleanup:
switch (SQLUDF_CALLT) {
case SQLUDF_FIRST_CALL:
if (rc == 0) {
break;
}
/* we must close the file descriptor upon an error in
the FIRST call; so we fall through to the final call
processing below */
case SQLUDF_FINAL_CALL:
case SQLUDF_FINAL_CRA:
/* close the file */
fclose(scratch->f);
fclose(scratch->f);
scratch->f = NULL;
}
}
|
编译了代码，并链接到共享库之后，就要测试该函数。将该函数与上面介绍的 UDF readFileTable() 一起使用，我们还可以通过再次从该文件读取数据，并将它表示为数据库中的表，来验证数据是否是以正确的格式写入文件的。
/home/stolze $ db2 "VALUES writeFile( '/home/stolze/write.file', 10, 'some text' )"
1
-----------
0
1 record(s) selected.
/home/stolze $ more write.file
10some text
/home/stolze $ db2 "VALUES writeFile( '/home/stolze/write.file', -1234567890, 'some text' )"
1
-----------
SQL0443N Routine "STOLZE.WRITEFILE" (specific name "WRITEFILE") has
returned an error SQLSTATE with diagnostic text "The number
-1234567890 is too long." SQLSTATE=38301
/home/stolze $ db2 "CREATE TABLE t ( id INTEGER, string VARCHAR(50) )"
/home/stolze $ db2 "INSERT INTO t VALUES ( 1, 'the first text' ), ( 2, 'another text' )"
|
下列 SELECT 语句中的 UDF 执行的文件写操作处理了名为“t”的表中的两行。如果表包含更多行，则其它行也会被写入文件。请注意，我没有在生成的文件 write.file 中设置行结束符。因此，该文件将在向标准输出转储时只显示一个长字符串，并且只在一行上显示。
/home/stolze $ db2 "SELECT writeFile( '/home/stolze/write.file', id, string ) FROM t"
/home/stolze $ more write.file
1the first text 2another text
/home/stolze $ db2 "SELECT * FROM TABLE ( readFileTable( '/home/stolze/write.file' ) ) AS x"
INTCOL STRING
----------- --------------------------------------------------
1 the first text
2 another text
2 record(s) selected.
|
进行系统调用
在本节中，我展示了一个简单的函数，它可以执行数据库服务器上的其它命令。该任务使用了 C 函数 system() 。该 UDF 将要执行的命令作为参数，然后执行它。系统调用的返回码被传回 SQL 函数的调用程序，并在那里进行进一步求值。 清单 9中显示了用来在数据库系统中注册该函数的相应 SQL 语句。
清单 9. 创建执行系统命令的函数
CREATE FUNCTION systemCall( command VARCHAR(2000) )
RETURNS INTEGER
SPECIFIC systemCall
EXTERNAL NAME 'os_calls!systemCall'
LANGUAGE C
PARAMETER STYLE SQL
DETERMINISTIC
NOT FENCED
RETURNS NULL ON NULL INPUT
NO SQL
EXTERNAL ACTION
NO SCRATCHPAD
DISALLOW PARALLEL
FINAL CALL;
GRANT EXECUTE ON FUNCTION systemCall TO PUBLIC;
|
正如我们已经描述的，函数的实现本身非常简单，如 清单 10所示。仅仅将输入参数传递到 system() 调用，然后将结果传递回来。
清单 10. 进行系统调用的函数的 C 代码
#include <stdlib.h>
#include <sqludf.h>
void SQL_API_FN systemCall(
SQLUDF_VARCHAR *command, /* input */
SQLUDF_INTEGER *result, /* output */
/* null indicators */
SQLUDF_NULLIND *command_ind,
SQLUDF_NULLIND *result_ind,
SQLUDF_TRAIL_ARGS)
{
int rc = 0;
if (SQLUDF_NULL(command_ind)) {
*result_ind = -1;
return;
}
/* execute the command */
rc = system(command);
*result_ind = 0;
*result = rc;
} |
与所有新功能一样，必须对它进行测试以确保其能正确运行。要执行的第一个语句在某个目录上运行 ls 命令以列出其内容。输出被重定向到一个文件，可以在结束时检查该文件。
/home/stolze $ ls -F
create_tab.sql lost+found/ os_calls.c os_calls.exp sqllib/
drop_tab.sql misc/ os_calls.def os_calls.o stolze/
/home/stolze $ db2 "VALUES systemCall('ls /home/stolze > /home/stolze/ls.out')"
1
-----------
0
/home/stolze $ cat ls.out
create_tab.sql
drop_tab.sql
lost+found/
ls.out
misc/
os_calls.c
os_calls.def
os_calls.exp
os_calls.o
sqllib/
stolze/stolze/ |
在下一种情况中，我将尝试执行 SQL 语句来创建表。函数调用可以启动，但返回码 1024 表明该语句未能成功执行。我所用的可执行文件是 DB2 命令行进程（CLP）。因此，返回码 1024 对应于 SQLCODE -1024 和消息 SQL1024，它表明数据库连接不存在。因此，我使用了一个小型脚本，它建立到数据库的连接，然后执行了原先的 CREATE TABLE 语句。
/home/stolze $ db2 "VALUES systemCall('db2 \\"create table t ( col int )\\"')"
1
-----------
1024
1 record(s) selected.
/home/stolze $ cat create_tab.sql
connect to test;
create table t ( col int );
connect reset;
/home/stolze $ db2 "VALUES systemCall('db2 -tsf /home/stolze/create_tab.sql')"
1
-----------
0
1 record(s) selected.
/home/stolze $ db2 describe table t
Column Type Type
name schema name Length Scale Nulls
------------------------------ --------- ------------------ -------- ----- ------
COL SYSIBM INTEGER 4 0 Yes
1 record(s) selected. |
请考虑先前那个建立到数据库新连接的示例。从 DB2 UDB 的角度来看，调用 UDF 的连接和使用 SQL 脚本的连接之间没有关系。两个事务是并发执行的，有可能出现常见的并发性问题。
在最后一个示例中，我使用动态复合语句，根据某个表中的信息，执行数据库服务器上的不同命令。在 DB2 编目中检查是否存在命名为 T 的表。如果存在，则删除该表，否则就创建该表。
/home/stolze $ db2 "BEGIN ATOMIC
> IF ( SELECT COUNT(*) FROM syscat.tables WHERE tabname = 'T' ) < 0 THEN
> VALUES systemCall('db2 -tsf /home/stolze/drop_tab.sql');
> ELSE
> VALUES systemCall('db2 -tsf /home/stolze/create_tab.sql');
> VALUES systemCall('db2 -tsf /home/stolze/create_tab.sql');
> END IF;
> END"
DB20000I The SQL command completed successfully.
/home/stolze $ db2 describe table t
Column Type Type
name schema name Length Scale Nulls
------------------------------ --------- ------------------ -------- ----- ------
0 record(s) selected.
SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a
query is an empty table. SQLSTATE=02000
/home/stolze $ db2 "BEGIN ATOMIC
> IF ( SELECT COUNT(*) FROM syscat.tables WHERE tabname = 'T' ) > 0 THEN
> VALUES systemCall('db2 -tsf /home/stolze/drop_tab.sql');
> ELSE
> VALUES systemCall('db2 -tsf /home/stolze/create_tab.sql');
> END IF;
> END"
DB20000I The SQL command completed successfully.
/home/stolze $ db2 describe table t
Column Type Type
name schema name Length Scale Nulls
------------------------------ --------- ------------------ -------- ----- ------
COL SYSIBM INTEGER 4 0 Yes
1 record(s) selected.
|
|
|
需要了解的事情
编写 UDF 时要考虑几个问题。本节描述了几个较重要的考虑事项，但不全面，也决不是结论性的。
开发 UDF
当您开发新的用户自定义函数或修改现有的用户自定义函数时，应该 总是通过在数据库中将它注册为 FENCED 来测试该函数。NOT FENCED 函数可以在 DB2 和它自己之间提供更佳的通信性能，但是函数代码中的错误会对函数本身造成破坏，在运气不好的情况下，还会对 DB2 造成破坏，虽然 DB2 引擎会尝试尽可能地进行自我保护。使用 FENCED 函数保护了 DB2 引擎不受此类问题困扰。
因此，在生产数据库中，在将函数作为 NOT FENCED 运行之前，应该总是对它进行充分的测试。
授权
UDF 是在 DB2 进程内执行的。根据 CREATE FUNCTION 语句的定义，可以使用 DB2 引擎的进程，也可以使用受防护方式的进程。如果您将 UDF 创建为 NOT FENCED，则 DB2 引擎进程将执行该函数；否则，会使用受防护方式的进程。
各个进程空间隐式地定义了用户自定义函数的操作系统权限。如果 UDF 是作为受防护的函数执行的，那么它只对受防护的用户（针对您的实例定义）有权访问的那些文件、目录和其它操作系统资源有访问权。尤其是，如果受防护的用户与实例所有者不是同一用户（推荐配置），那么受防护的 UDF 就不能访问该 DB2 实例的资源或文件。这保护了 DB2 资源不受有错误的或恶意的 UDF 的破坏。
我们上面提供的示例 UDF 的含义很简单。如果您定义函数作为 FENCED 运行，就必须选择该受防护的用户可访问的文件。当写入文件时，受防护的用户必须拥有在希望写入数据的目录中创建文件的特权。同样，如果将函数作为 NOT FENCED 运行，也存在相同约束，但这一次要考虑 DB2 实例所有者的特权。例如， systemCall() UDF 将能够执行为 DB2 实例所有者保留的命令。
文件的路径名称
在我们所演示的所有示例中 UDF 都是将要读取或写入文件的绝对路径作为参数的。有很好的理由不使用相对路径名称。正如已经提到的，UDF 是在 DB2 进程中执行的。相对路径总是相对于启动 DB2 进程的目录。（不必是这个可执行文件在文件系统中驻留的目录。）因此，用户通常不知道相对路径的起点在哪里。即使他们知道，也决不应该使用它，因为将来也许会发生意料之外的故障。使用绝对路径确保了一致而正确的结果。
DISALLOW PARALLEL 和 EXTERNAL ACTION
UDF readFileTable() 、 writeFile() 和 systemCall() 是使用 DISALLOW PARALLEL 子句定义的。告诉 DB2 该函数一定不能并行地执行是很有必要的。就 readFileTable() 而言，该子句防止不同的 DB2 进程多次读取同一行。显然，当写入文件时，您只希望将数据写入一次而不是多次，但如果多个 DB2 进程执行该 SQL 语句时就会发生这种情况。同样，用 systemCall() 函数启动的可执行文件在每次调用中也只应运行一次，而不是并行地运行多次。这些考虑事项对于多节点配置（MPP）特别重要，在这种情况下，为了得出一致的输出，必须只用一个节点处理该 UDF。
对 writeFile() 和 systemCall() 函数还定义了子句 EXTERNAL ACTION，因为这些函数显然执行了某个操作，该操作在数据库管理器的控制之外更改系统的状态。定义了这个子句之后，DB2 就会小心，不更改语句内调用函数的次数。这样就不会忽略任何调用，也不会插入额外的调用。（如果 DB2 的“查询重写”组件尝试转换 SQL 语句中的构造，就可能会发生此类情况。）
使用 LOB 定位器
当处理大对象（LOB）时，可能值得使用定位器。定位器的优点是只按实际需求分配空间。例如，如果您定义了返回参数为 BLOB（100M）的 UDF，那么 DB2 必须确保该 UDF 获得大小为 100M 的缓冲区来写入其结果。如果实际结果只有几百字节，那就浪费了大量空间，更不用说分配 100M 的缓冲区代价是多么高了。
同一 SQL 语句中的多个 UDF
当用上述 writeFile() 函数写入到新文件时，要确保没有文件系统级别上的冲突。这意味着您不应该在同一 SQL 语句中出现的不同 UDF 中对同一文件进行写操作，也不应该并行地运行几个将要写入同一文件的 SQL 语句。例如，不能指望下列 SQL 语句所产生的文件会有什么良好定义的行为：
/home/stolze $ db2 "SELECT writeFile( '/home/stolze/write.file', id, string ), writeFile(
'/home/stolze/write.file', id + 10, string ) FROM t"
/home/stolze $ more write.file
11the first text 12another text
|
在我们的示例中，函数的第二个实例可以写出其结果，而第一个实例的结果轻易地丢失了。在不同的情况下，结果会截然不同。
避免此类问题的方法之一是不将数据写入到同一文件中；即，对 SQL 语句中出现的每个 UDF writeFile() 都使用不同的文件。下列 SQL 语句说明了这一点，并且两次出现的 UDF 的结果都可以在各自的文件中找到。这样首先不会发生并发性问题了。
/home/stolze $ db2 "SELECT writeFile( '/home/stolze/file.1', id, string ), writeFile(
'/home/stolze/file.2', id + 10, UPPER(string) ) FROM t"
/home/stolze $ more file.1
1the first text 2another text
/home/stolze $ more file.2
11the FIRST TEXT 12ANOTHER TEXT
|
此外，您还可以使用同步机制来防止数据丢失。我将此类机制的实现留给有兴趣的用户自己来完成。
大规模并行处理（多分区）
当处理服务器文件系统中的文件时，您必须考虑文件要位于何处。在 DB2 的多分区配置中该问题尤其突出。（这在 V7 中称为 DB2 扩展企业版（Extended Enterprise Edition，EEE）。）在此类环境中您不知道 UDF 将在哪个节点上执行。您必须确保希望用 UDF readFile() 或 readFileTable() 读取的文件是可以从所有节点上使用同一绝对路径名称访问的。或者，当写入文件时，要知道能够在任意节点上创建该文件。
解决这些问题的简便方法是使用共享或网络文件系统。如有必要，可以使用 UNIX 系统上的链接来在所有节点上提供相同的绝对路径名称。
结束语
本文提供了访问数据库服务器文件系统上文件的方法。我展示了一个 UDF readFile() ，它将整个文件读入一个 CLOB 值中，然后展示了一个更高级的表函数 readFileTable() ，它从文件中解析出一个个单独的记录并在数据库中将记录表示为由多个列组成的行。UDF writeFile() 展示了如何用 SQL 语句实现和直接使用其逆操作（将数据写入到文件）。我以简要概述的方式对一些问题作出了结论，在实现访问操作系统资源的用户自定义函数时必须仔细考虑这些问题。
参考资料
一个下载
| 描述 |
文件类型 |
文件大小 |
下载方法 |
| os_calls.zip |
zip |
6 KB |
HTTP  |
关于作者
|
|
|
|
在 Knut Stolze开始从事 DB2 研究时，是作为访问学者加入到 IBM 硅谷实验室的（他在那里从事 DB2 图象扩展器，DB2 Image Extender）。之后，他转向了 DB2 空间扩展器 V8（DB2 Spatial Extender V8），在两年多的时间里，他负责多项增强功能，以改善扩展器的实用性、性能以及与标准的一致性。
目前，他作为德国耶拿（Jena）大学的助教，在联邦数据库领域继续为 IBM 工作。可以通过新闻组 comp.databases.ibm-db2 和 ibm.software.db2.udb.spatial 或 stolze@de.ibm.com与他联系。
|
|
