DB2自定义函数（C语言）

来源：互联网发布：两个程序员谈恋爱编辑：程序博客网时间：2024/05/17 21:49

1 总体介绍

基于DB2 V9.5

1.1 用户自定义函数（UDF）

DB2 内部提供了大量的函数，但仍然不足以满足业务千奇百怪的需要。不过DB2提供了用户自定义函数（User Defined Function，简称 UDF）功能。DB29.5版本的自定义函数实现，除过DB2自带的 SQL PL，还支持高级语言，包括：

ADO.NET
- .NET Common Language Runtime programming languages
CLI
Embedded SQL
- C
- C++
- COBOL (Only supported for procedures)
JDBC
- Java
OLE
- Visual Basic
- Visual C++
- Any other programming language that supports this API.
OLE DB (Only supported for table functions)
- Any programming language that supports this API.
SQLJ
- Java

1.2 用户自定义函数的分类

用户自定义函数分为三种类型：

（1）标量函数（scalar functions）。返回一个单值的函数称为标量函数。例如，DB2内置了大量标量函数，如abs、length、year、replace、ucase等均属于标量函数。

内置标量函数参考：

http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.luw.sql.ref.doc/doc/c0000757.html

（2）聚集函数（Aggregate functions）。有人称为列函数，从表的一列中进行统计返回单值答案。例如，求某个字段平均值的函数 AVG()。

内置聚集函数参考：

http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.luw.sql.ref.doc/doc/c0000767.html

（3）表函数（Table functions）。返回结果是一个表的函数称为表函数，它将一个表返回至引用它的 SQL语句，只能在 SELECT语句的 FROM子句中引用表函数。此类函数可用于将 SQL语言处理能力应用于非 DB2数据的数据，或将此类数据转换为 DB2表。例如，表函数可以提取一个文件并将它转换成表，将来自互联网的样本数据制成表。这些信息可以与该数据库中的其他表连接。

内置表函数参考：

http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.luw.sql.ref.doc/doc/c0002372.html

2 C语言自定义函数

2.1 建立过程

1、编写c文件

2、编译c文件：/opt/IBM/db2/V9.5/samples/c/bldrtn 无后缀程序名称数据库名称

3、执行sql：CREATEFUNCTION语句，建立自定义函数

4、测试

注：使用实例用户编译，然后可赋权给其他用户调用

2.2 重要概念

2.2.1 SQLUDF_SCRAT高速缓存

一个高速缓存，一般用在表函数，为多次函数调用之间存储状态信息。与create function的SCRATCHPAD参数相对应。

DB2 通过使用所谓的“高速暂存（scratchpad）”提供了在 UDF调用之间传递信息的机制。此外，您可以标识特定调用“类型”；即它是对该 UDF的第一次调用、普通调用还是最后一次（最终）调用。使用高速暂存和调用类型，有可能只对模式编译一次，然后将该已编译模式的内部表示法重用于对该 UDF的所有后续调用。在最后一次调用时，释放在处理期间分配的资源。

在头文件sqludf.h中定义如下：

/* Forthe scratchpad. Note: SQLUDF_SCRAT is a pointer. */
#define SQLUDF_SCRAT (sqludf_scratchpad) /* scratchpad area */

/******************************************************************************
** Structure used for: the scratchpad.
** Note: This structure as provided by DB2 gives the
** overall size/structure of the scratchpad argument.
** Individual UDFs which use a scratchpad may wish to
** override the "data" variable of thisscratchpad
** with variables that make sense for the particular
** UDF.
** Note: Remember that the "data" variableis initialized
** by DB2 to all \0 before the first call.
*******************************************************************************/
SQL_STRUCTURE sqludf_scratchpad
{
sqluint32 length; /* length of scratchpad data */
char data[SQLUDF_SCRATCHPAD_LEN]; /* scratchpad data, init. to */
/* all \0 */
};

参考：

http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.luw.apdv.routines.doc/doc/c0023761.html

2.2.2 call-type

主要用在表函数，为表函数调用的初始化、开始、下一行、结束、清理等信息

在头文件sqludf.h中定义如下：

/* Forthe call type. Note: pointer to enum type is dereferenced. */
#define SQLUDF_CALLT (*sqludf_call_type) /* call type (used with scr'pad) */

#define SQLUDF_CALL_TYPE enum sqludf_call_type /*for call type */

/******************************************************************************
** Enumerated type, for call-type argument.
*******************************************************************************/
/* Contains generic internal values; right below, wewill map to */
/* these values for scalar and table function calltypes. */
enum sqludf_call_type
{
SQLUDF_ENUM_CALLTYPE_MINUS2 = -2, /* Used in table func for FIRST call */
SQLUDF_ENUM_CALLTYPE_MINUS1 = -1, /* Used in table func for OPEN call, */
/* and scalar func FIRST call */
SQLUDF_ENUM_CALLTYPE_ZERO = 0, /* Used in table func for FETCH call, */
/* and scalar func NORMAL call */
SQLUDF_ENUM_CALLTYPE_PLUS1 = 1, /* Used in table func for CLOSE call, */
/* and scalar func FINAL call */
SQLUDF_ENUM_CALLTYPE_PLUS2 = 2, /* Used in table func for FINAL call, */
SQLUDF_ENUM_CALLTYPE_PLUS255 = 255, /* Used in table func FINAL_CRA call */
/* and scalar func FINAL_CRA call */
SQLUDF_ENUM_CALLTYPE_END = 0x7FFFFFFF /* Present only to force 4byte type */
};

2.2.3 dbinfo

DB2会传递一个数据结构，其中包含的信息包括当前连接的数据库的名称、应用程序运行时授权 ID、调用此函数的数据库服务器的版本、发布版本和修订级别以及服务器使用的操作系统

The dbinfostructure is a structure that contains database and routine information thatcan be passed to and from a routine implementation as an extra argument if andonly if the DBINFO clause is included in the CREATE statement for the routine.

参考：

http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.luw.apdv.routines.doc/doc/c0023760.html

2.3 编码规范

头文件：

#include <sqludf.h>

函数参数：

按照顺序与create function对应，c函数的参数为：inputParam1、inputParam2….，outputParam1、outputParam2….，还需要针对每一个参数提供指示变量，最后为获取诊断信息的变量SQLUDF_TRAIL_ARGS或SQLUDF_TRAIL_ARGS_ALL（函数使用高速缓存SCRATCHPAD时使用）

SQL_API_RC SQL_API_FNfunction-name ( SQL-arguments,

SQL-argument-inds,

SQLUDF_TRAIL_ARGS )

SQL_API_RCSQL_API_FN

SQL_API_RC andSQL_API_FN are macros that specify the return type and calling convention for aC or C++ user-defined function, which can vary across supported operatingsystems. The use of these macros is required for C and C++ routines. The macrosare declared in embedded SQL application and routine include file sqlsystm.h.

function-name

Name of the C orC++ function within the code file. This value does not have to be the same asthe name of the function specified within the corresponding CREATE FUNCTIONstatement. This value in combination with the library name however must bespecified in the EXTERNAL NAME clause to identify the correct function entrypoint within the library to be used. For C++ routines, the C++ compiler appliestype decoration to the entry point name. Either the type decorated name needsto be specified in the EXTERNAL NAME clause, or the function declaration withinthe source code file should be prefixed with extern "C" as shown inthe following example: extern "C" SQL_API_RC SQL_API_FN OutLanguage(char *, sqlint16 *, char *, char *, char *, char *);

SQL-arguments

C or C++ argumentsthat correspond to the set of SQL parameters specified in the CREATE FUNCTIONstatement.

SQL-argument-inds

For eachSQL-argument a null indicator parameter is required to specify whether theparameter value is intended to be interpreted within the routine implementationas a NULL value in SQL. Null indicators must be specified with data typeSQLUDF_NULLIND. This data type is defined in embedded SQL routine include filesqludf.h.

SQLUDF_TRAIL_ARGS

A macro defined inembedded SQL routine include file sqludf.h that once expanded defines theadditional trailing arguments required for a complete parameter style SQLsignature. There are two macros that can be used: SQLUDF_TRAIL_ARGS andSQLUDF_TRAIL_ARGS_ALL. SQLUDF_TRAIL_ARGS when expanded, as defined in sqludf.h,is equivalent to the addition of the following routine arguments:

SQLUDF_CHAR*sqlState,

SQLUDF_CHARqualName,

SQLUDF_CHARspecName,

SQLUDF_CHAR*sqlMessageText,

In general thesearguments are not required or generally used as part of user-defined functionlogic. They represent the output SQLSTATE value to be passed back to thefunction invoker, the input fully qualified function name, input functionspecific name, and output message text to be returned with the SQLSTATE.SQLUDF_TRAIL_ARGS_ALL when expanded, as defined in sqludf.h, is equivalent tothe addition of the following routine arguments:

SQLUDF_CHAR qualName,

SQLUDF_CHAR specName,

SQLUDF_CHAR sqlMessageText,

SQLUDF_SCRAT*scratchpad

SQLUDF_CALLT*callType

If the UDF CREATEstatement includes the SCRATCHPAD clause or the FINAL CALL clause, then themacro SQLUDF_TRAIL_ARGS_ALL must be used. In addition to arguments providedwith SQLUDF_TRAIL_ARGS, this macro also contains pointers to a scratchpadstructure, and a call type value.

参考：

http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.luw.apdv.routines.doc/doc/c0023763.html

SQL与c参数对应列表：

SQL Column Type

C/C++ Data Type

SQL Column Type Description

SMALLINT

sqlint16
SQLUDF_SMALLINT

16-bit signed integer

INTEGER

sqlint32
SQLUDF_INTEGER

32-bit signed integer

BIGINT

sqlint64
SQLUDF_BIGINT

64-bit signed integer

REAL
FLOAT(n) where 1<=n<=24

float
SQLUDF_REAL

Single-precision floating point

DOUBLE
FLOAT
FLOAT(n) where 25<=n<=53

double
SQLUDF_DOUBLE

Double-precision floating point

DECIMAL(p, s)

Not supported.

To pass a decimal value, define the parameter to be of a data type castable from DECIMAL (for example CHAR or DOUBLE) and explicitly cast the argument to this type.

CHAR(n)

char[n+1] where n is large enough to hold the data

1<=n<=254

SQLUDF_CHAR

Fixed-length, null-terminated character string

CHAR(n) FOR BIT DATA

char[n] where n is large enough to hold the data

1<=n<=254

SQLUDF_CHAR

Fixed-length, not null-terminated character string

VARCHAR(n)

char[n+1] where n is large enough to hold the data

1<=n<=32 672

SQLUDF_VARCHAR

Null-terminated varying length string

VARCHAR(n) FOR BIT DATA

struct {
sqluint16 length;
char[n]
}

1<=n<=32 672

SQLUDF_VARCHAR_FBD

Not null-terminated varying length character string

LONG VARCHAR

struct {
sqluint16 length;
char[n]
}

1<=n<=32 700

SQLUDF_LONG

Not null-terminated varying length character string

CLOB(n)

struct {
sqluint32 length;
char data[n];
}

1<=n<=2 147 483 647

SQLUDF_CLOB

Not null-terminated varying length character string with 4-byte string length indicator

BLOB(n)

struct {
sqluint32 length;
char data[n];
}

1<=n<=2 147 483 647

SQLUDF_BLOB

Not null-terminated varying binary string with 4-byte string length indicator

DATE

char[11]
SQLUDF_DATE

Null-terminated character string of the following format:

yyyy-mm-dd

TIME

char[9]
SQLUDF_TIME

Null-terminated character string of the following format:

hh.mm.ss

TIMESTAMP

char[27]
SQLUDF_STAMP

Null-terminated character string of the following format:

yyyy-mm-dd-hh.mm.ss.nnnnnn

LOB LOCATOR

sqluint32
SQLUDF_LOCATOR

32-bit signed integer

GRAPHIC(n)

sqldbchar[n+1] where n is large enough to hold the data

1<=n<=127

SQLUDF_GRAPH

Fixed-length, null-terminated double-byte character string

VARGRAPHIC(n)

sqldbchar[n+1] where n is large enough to hold the data

1<=n<=16 336

SQLUDF_GRAPH

Null-terminated, variable-length double-byte character string

LONG VARGRAPHIC

struct {
sqluint16 length;
sqldbchar[n]
}

1<=n<=16 350

SQLUDF_LONGVARG

Not null-terminated, variable-length double-byte character string

DBCLOB(n)

struct {
sqluint32 length;
sqldbchar data[n];
}

1<=n<=1 073 741 823

SQLUDF_DBCLOB

Not null-terminated varying length character string with 4-byte string length indicator

XML AS CLOB

struct {
sqluint32 length;
char data[n];
}

1<=n<=2 147 483 647

SQLUDF_CLOB

Not null-terminated varying length serialized character string with 4-byte string length indicator.

参考：

http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.luw.apdv.routines.doc/doc/r0009730.html

高速缓存：

struct scratchMap {
char *p;
int rowNumber;
};

/* map the scratchpad */
struct scratchMap *scratch = (struct scratchMap *)SQLUDF_SCRAT->data;

参考：

http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.luw.apdv.routines.doc/doc/c0023761.html

其他：

Ø 支持采用嵌入式sql或调用cli方式调用执行sql语句

Ø 不支持printf输出信息，建议写入一个文件、或者写入SQLUDF_TRAIL_ARG中的sqlMessageText字段

Ø 诊断信息长度有限

2.4 CREATE FUNCTION语句

2.4.1 CREATE FUNCTION (标量函数) 子句

>>-CREATE FUNCTION--function-name------------------------------->

>--(--+-------------------------------+--)--●------------------->

      | .-,-------------------------. |

      | V                           | |

      '---| parameter-declaration |-+-'

>--RETURNS--+-| data-type2 |--+------------+----------------------------+-->

            |                 '-AS LOCATOR-'                            |

            '-| data-type3 |--CAST FROM--| data-type4 |--+------------+-'

                                                         '-AS LOCATOR-'

>--●--+-------------------------+--●---------------------------->

      '-SPECIFIC--specific-name-'

>--EXTERNAL--+----------------------+--●------------------------>

             '-NAME--+-'string'---+-'

                     '-identifier-'

(1)

>--LANGUAGE--+-C----+------●--PARAMETER STYLE--+-DB2GENERAL-+--->

             +-JAVA-+                          +-JAVA-------+

             +-CLR--+                          '-SQL--------'

             '-OLE--'

>--●--+------------------------------+--●----------------------->

      '-PARAMETER CCSID--+-ASCII---+-'

                         '-UNICODE-'

   .-NOT DETERMINISTIC-.

>--+-------------------+--●------------------------------------->

   '-DETERMINISTIC-----'

   .-FENCED------------------------.

>--+-------------------------------+--●------------------------->

   +-FENCED--●--+-THREADSAFE-----+-+

   |            '-NOT THREADSAFE-' |

   |                .-THREADSAFE-. |

   '-NOT FENCED--●--+------------+-'

                                      .-READS SQL DATA-.

>--+----------------------------+--●--+----------------+--●----->

   +-RETURNS NULL ON NULL INPUT-+     +-NO SQL---------+

   '-CALLED ON NULL INPUT-------'     '-CONTAINS SQL---'

   .-STATIC DISPATCH-.     .-EXTERNAL ACTION----.

>--+-----------------+--●--+--------------------+--●------------>

                           '-NO EXTERNAL ACTION-'

   .-NO SCRATCHPAD----------.     .-NO FINAL CALL-.

>--+------------------------+--●--+---------------+--●---------->

   |             .-100----. |     '-FINAL CALL----'

   '-SCRATCHPAD--+--------+-'

                 '-length-'

                             .-NO DBINFO-.

>--+-------------------+--●--+-----------+--●------------------->

   +-ALLOW PARALLEL----+     '-DBINFO----'

   '-DISALLOW PARALLEL-'

>--+-----------------------------+--●--------------------------->

   '-TRANSFORM GROUP--group-name-'

>--+-----------------------------------------------+--●--------->

   '-PREDICATES--(--| predicate-specification |--)-'

   .-INHERIT SPECIAL REGISTERS-.

>--+---------------------------+--●----------------------------><

parameter-declaration

|--+----------------+--| data-type1 |--+------------+-----------|

   '-parameter-name-'                  '-AS LOCATOR-'

data-type1, data-type2, data-type3, data-type4

|--+-| built-in-type |----+-------------------------------------|

   +-distinct-type-name---+

   +-structured-type-name-+

   '-REF--(--type-name--)-'

built-in-type

|--+-+-SMALLINT----+-------------------------------------------------------+--|

   | +-+-INTEGER-+-+                                                       |

   | | '-INT-----' |                                                       |

   | '-BIGINT------'                                                       |

   |                  .-(5,0)-------------------.                          |

   +-+-+-DECIMAL-+-+--+-------------------------+--------------------------+

   | | '-DEC-----' |  |          .-,0-------.   |                          |

   | '-+-NUMERIC-+-'  '-(integer-+----------+-)-'                          |

   |   '-NUM-----'               '-,integer-'                              |

   |          .-(53)------.                                                |

   +-+-FLOAT--+-----------+--+---------------------------------------------+

   | |        '-(integer)-'  |                                             |

   | +-REAL------------------+                                             |

   | |         .-PRECISION-. |                                             |

   | '-DOUBLE--+-----------+-'                                             |

   |                    .-(1)-------.                                      |

   +-+-+-+-CHARACTER-+--+-----------+----------+--+--------------------+-+-+

   | | | '-CHAR------'  '-(integer)-'          |  |  (2)               | | |

   | | '-+-VARCHAR----------------+--(integer)-'  '-------FOR BIT DATA-' | |

   | |   '-+-CHARACTER-+--VARYING-'                                      | |

   | |     '-CHAR------'                                                 | |

   | |                                  .-(1M)-------------.             | |

   | '-+-CLOB------------------------+--+------------------+-------------' |

   |   '-+-CHARACTER-+--LARGE OBJECT-'  '-(integer-+---+-)-'               |

   |     '-CHAR------'                             +-K-+                   |

   |                                               +-M-+                   |

   |                                               '-G-'                   |

   |            .-(1)-------.                                              |

   +-+-GRAPHIC--+-----------+-------+--------------------------------------+

   | |          '-(integer)-'       |                                      |

   | +-VARGRAPHIC--(integer)--------+                                      |

   | |         .-(1M)-------------. |                                      |

   | '-DBCLOB--+------------------+-'                                      |

   |           '-(integer-+---+-)-'                                        |

   |                      +-K-+                                            |

   |                      +-M-+                                            |

   |                      '-G-'                                            |

   |                          .-(1M)-------------.                         |

   +-+-BLOB----------------+--+------------------+-------------------------+

   | '-BINARY LARGE OBJECT-'  '-(integer-+---+-)-'                         |

   |                                     +-K-+                             |

   |                                     +-M-+                             |

   |                                     '-G-'                             |

   +-+-DATE------+---------------------------------------------------------+

   | +-TIME------+                                                         |

   | '-TIMESTAMP-'                                                         |

   +-XML-------------------------------------------------------------------+

   | .-SYSPROC.-.                   (3) (4)                                |

   '-+----------+--DB2SECURITYLABEL----------------------------------------'

predicate-specification

|--WHEN--+- =  -+--+-constant-----------------------+----------->

         +- <> -+  '-EXPRESSION AS--expression-name-'

         +- <  -+

         +- >  -+

         +- <= -+

         '- >= -'

>--+-| data-filter |--+------------------------+-+--------------|

   |                  '-| index-exploitation |-' |

   '-| index-exploitation |--+-----------------+-'

                             '-| data-filter |-'

data-filter

|--FILTER USING--+-function-invocation-+------------------------|

                 '-case-expression-----'

index-exploitation

|--SEARCH BY--+-------+--INDEX EXTENSION--index-extension-name-->

              '-EXACT-'

   .-----------------------.

   V                       |

>----| exploitation-rule |-+------------------------------------|

exploitation-rule

|--WHEN KEY--(--parameter-name1--)------------------------------>

                               .-,---------------.

                               V                 |

>--USE--search-method-name--(----parameter-name2-+--)-----------|

参考：

http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.luw.sql.ref.doc/doc/r0004461.html

2.4.2 CREATE FUNCTION (表函数) 子句

>>-CREATE FUNCTION--function-name------------------------------->

>--(--+-------------------------------+--)--●------------------->

| .-,-------------------------. |

| V ||

'---| parameter-declaration |-+-'

.-,-------------------------------------------.

V |

>--RETURNS TABLE--(----column-name--|data-type2 |--+------------+-+--)-->

'-ASLOCATOR-'

>--●--+-------------------------+--●---------------------------->

'-SPECIFIC--specific-name-'

>--EXTERNAL--+----------------------+--●------------------------>

'-NAME--+-'string'---+-'

'-identifier-'

(1)

>--LANGUAGE--+-C----+------●--PARAMETERSTYLE--+-DB2GENERAL-+--->

+-JAVA-+ '-SQL--------'

+-CLR--+

'-OLE--'

>--●--+------------------------------+--●----------------------->

'-PARAMETER CCSID--+-ASCII---+-'

'-UNICODE-'

.-NOT DETERMINISTIC-.

>--+-------------------+--●------------------------------------->

'-DETERMINISTIC-----'

.-FENCED------------------------.

>--+-------------------------------+--●------------------------->

+-FENCED--●--+-THREADSAFE-----+-+

| '-NOT THREADSAFE-'|

| .-THREADSAFE-.|

'-NOT FENCED--●--+------------+-'

.-RETURNS NULL ON NULL INPUT-. .-READS SQL DATA-.

>--+----------------------------+--●--+----------------+--●----->

'-CALLED ON NULL INPUT-------' +-NO SQL---------+

'-CONTAINS SQL---'

.-STATIC DISPATCH-. .-EXTERNALACTION----.

>--+-----------------+--●--+--------------------+--●------------>

'-NO EXTERNALACTION-'

.-NO SCRATCHPAD----------. .-NO FINAL CALL-.

>--+------------------------+--●--+---------------+--●---------->

| .-100----. | '-FINAL CALL----'

'-SCRATCHPAD--+--------+-'

'-length-'

>--+-DISALLOWPARALLEL------------------------------------------------------------------+-->

| .-DATABASE PARTITIONS-. |

'-ALLOW PARALLEL--EXECUTE ON--ALL--+---------------------+--RESULT TABLEDISTRIBUTED-'

.-NO DBINFO-.

>--●--+-----------+--●--+----------------------+--●------------->

'-DBINFO----' '-CARDINALITY--integer-'

>--+-----------------------------+--●--------------------------->

'-TRANSFORM GROUP--group-name-'

.-INHERIT SPECIAL REGISTERS-.

>--+---------------------------+--●----------------------------><

parameter-declaration

|--+----------------+--| data-type1|--+------------+-----------|

'-parameter-name-' '-AS LOCATOR-'

data-type1, data-type2

|--+-| built-in-type|----+-------------------------------------|

+-distinct-type-name---+

+-structured-type-name-+

'-REF--(--type-name--)-'

built-in-type

|--+-+-SMALLINT----+-------------------------------------------------------+--|

|+-+-INTEGER-+-+ |

|| '-INT-----' | |

|'-BIGINT------' |

| .-(5,0)-------------------. |

+-+-+-DECIMAL-+-+--+-------------------------+--------------------------+

|| '-DEC-----' | | .-,0-------. | |

|'-+-NUMERIC-+-' '-(integer-+----------+-)-' |

| '-NUM-----' '-,integer-' |

| .-(53)------. |

+-+-FLOAT--+-----------+--+---------------------------------------------+

|| '-(integer)-' | |

|+-REAL------------------+ |

|| .-PRECISION-. | |

|'-DOUBLE--+-----------+-' |

| .-(34)-. |

+-DECFLOAT--+------+----------------------------------------------------+

| '-(16)-' |

| .-(1)-------. |

+-+-+-+-CHARACTER-+--+-----------+----------+--+--------------------+-+-+

|| | '-CHAR------' '-(integer)-' | | (2) | | |

|| '-+-VARCHAR----------------+--(integer)-' '-------FOR BIT DATA-' | |

|| '-+-CHARACTER-+--VARYING-' | |

|| '-CHAR------' | |

|| .-(1M)-------------. ||

|'-+-CLOB------------------------+--+------------------+-------------' |

| '-+-CHARACTER-+--LARGEOBJECT-' '-(integer-+---+-)-' |

| '-CHAR------' +-K-+ |

| +-M-+ |

| '-G-' |

| .-(1)-------. |

+-+-GRAPHIC--+-----------+-------+--------------------------------------+

|| '-(integer)-' | |

|+-VARGRAPHIC--(integer)--------+ |

|| .-(1M)-------------. | |

|'-DBCLOB--+------------------+-' |

| '-(integer-+---+-)-' |

| +-K-+ |

| +-M-+ |

| '-G-' |

| .-(1M)-------------. |

+-+-BLOB----------------+--+------------------+-------------------------+

|'-BINARY LARGE OBJECT-' '-(integer-+---+-)-' |

| +-K-+ |

| +-M-+ |

| '-G-' |

+-+-DATE------+---------------------------------------------------------+

|+-TIME------+ |

|'-TIMESTAMP-' |

+-XML-------------------------------------------------------------------+

|.-SYSPROC.-. (3)(4) |

'-+----------+--DB2SECURITYLABEL----------------------------------------'

参考：

http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.luw.sql.ref.doc/doc/r0004462.html

2.4.3 参数说明

Ø DETERMINISTIC or NOT DETERMINISTIC：指定是否每当以相同的一组输入参数执行函数时，都返回相同的结果。确定性（Deterministic）函数包括数学函数和不依赖于表中数据或变化数据源的函数。

Ø NO SQL, CONTAINS SQL, READS SQL DATA: 指定函数是否执行SQL交互，如何交互。

n NO SQL：外部 UDF 体不包含任何 SQL，或者包含不可执行的SQL语句。（不可执行的SQL语句包括INCLUDE和WHENEVER语句等）。

n CONTAINS SQL：UDF 体包含的可执行SQL语句既不读数据，也不修改数据。

n READS SQL DATA：UDF 体包含的可执行SQL语句读数据，但是不修改数据。

Ø FENCED or NOT FENCED：是否在DB2的进程/内存空间中运行

Ø RETURNS NULL ON NULL INPUT or CALLED ON NULL INPUT：当输入数据为NULL时，函数是否直接返回NULL

Ø EXTERNAL ACTION or NO EXTERNAL ACTION：是否应该除过数据库之外的其他内容，必须在操作系统层面写文件等

Ø NO SCRATCHPAD or SCRATCHPAD length：是否指定是否为UDF分配作为持久存储区域的内存，默认length为100。如果指定了SCRATCHPAD子句，DB2就会在首次调用此函数时分配适当数量的内存。在创建和填充暂存区域之后，会在各次函数调用之间保留它的内容—— UDF在一次调用中对暂存区域所做的任何修改都会保留到下一次调用。

Ø CARDINALITY integer：可选参数，指定表函数返回的行数

Ø NO DBINFO or DBINFO：表示在调用函数时是否把 DB2 掌握的信息作为额外参数传递给 UDF（DBINFO表示传递，NO DBINFO表示不传递）。如果指定了 DBINFO子句，那么DB2会传递一个数据结构，其中包含的信息包括当前连接的数据库的名称、应用程序运行时授权 ID、调用此函数的数据库服务器的版本、发布版本和修订级别以及服务器使用的操作系统。

2.4.4 示例

--drop function gprsSplit;
CREATE FUNCTION gprsSplit(recordextensionVARCHAR(1025))
   RETURNS TABLE (idint,busi_typeVARCHAR(10),durationbigint,up_flowbigint,down_flowbigint,feebigint)
   SPECIFIC gprsSplit
   EXTERNAL NAME 'gprsSplit_udf!gprsSplit'
   LANGUAGE C
   PARAMETER STYLE SQL
   DETERMINISTIC
   --NOT DETERMINISTIC
   --FENCED
   NOT FENCED
   RETURNS NULL ON NULL INPUT
   NO SQL
   NO EXTERNAL ACTION
SCRATCHPAD
   NO FINAL CALL
   DISALLOW PARALLEL
   --ALLOW PARALLEL EXECUTE ON ALL DATABASE PARTITIONS RESULT TABLEDISTRIBUTED
   CARDINALITY 2;

2.5 注意事项

当您开发新的用户自定义函数或修改现有的用户自定义函数时，应该总是通过在数据库中将它注册为 FENCED来测试该函数。NOT FENCED函数可以在 DB2和它自己之间提供更佳的通信性能，但是函数代码中的错误会对函数本身造成破坏，在运气不好的情况下，还会对 DB2造成破坏，虽然 DB2引擎会尝试尽可能地进行自我保护。使用 FENCED函数保护了 DB2引擎不受此类问题困扰。

因此，在生产数据库中，在将函数作为 NOTFENCED运行之前，应该总是对它进行充分的测试。

有时候更改函数后不会自动刷新，需要查看DBM参数：KEEPFENCED，建议在开发阶段设置为NO，上线后设置为YES

db2 update dbm cfg using KEEPFENCED NO

参考：

http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.luw.apdv.routines.doc/doc/t0004642.html

2.6 参考资料

1、IBM DB2信息中心V9.5

http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp

2、将正则表达式匹配的强大功能带给 SQL

http://www.ibm.com/developerworks/cn/data/library/techarticles/0301stolze/0301stolze.html

3、自定义函数性能

http://groups.google.com/group/comp.databases.ibm-db2/browse_thread/thread/9e25201f65daa1e6

4、解析 SQL中的字符串

http://www.ibm.com/developerworks/cn/data/library/techarticles/0303stolze/0303stolze1.html

5、从 SQL进行操作系统调用

http://www.ibm.com/developerworks/cn/data/library/techarticles/0303stolze/0303stolze.html

6、用C创建DB2自定义函数遇到的问题

http://bbs.chinaunix.net/thread-1805993-1-1.html

7、DB2 9.5 SQLProcedure Developer认证考试 735 准备，第 3部分: DB2 SQL 函数

http://www.ibm.com/developerworks/cn/data/tutorials/dm0810sanders/section3.html