DB2用户自定义函数UDF

1.前言

    由于公司业务需要，最近几天调研了mysql+redis的实现方案，实现方法大体是Mysql+trigger+redis.具体的实现方案详见http://10gt.com/info/151 。在学习的过程中不禁想到了DB2如何用C语言实现用户自定义函数呢？所以就从这个观点出发，调研了一把C语言实现DB2用户自定义函数的方法。

2.环境需求

操作系统：红帽子6企业版本（最小化安装+开发环境）

数据库：DB2 V10.5

3.环境配置步骤

3.1安装操作系统和开发环境

    本文采用的操作系统是红帽子6企业版本，安装系统的时候选择的是最小化安装。安装好以后用如下命令安装的开发环境。

    # mount -oloop /dev/sr0  /mnt

    # vi /etc/yum.repos.d/rhel.repo

[rhel-source]

name=Red Hat Enterprise Linux $releasever - $basearch - Source

#baseurl=ftp://ftp.redhat.com/pub/redhat/linux/enterprise/$releasever/en/os/SRPMS/

baseurl=file:///mnt/

enabled=1

gpgcheck=1

gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-redhat-release

    # yum update

    # yum groupinstall 'Development tools'

    至此，操作系统和开发环境安装好了。

3.2安装DB2

    本文采用的DB2是从IBM官方下的适用版本。可以通过命令行的方式把数据库软件安装到系统中。创建用户、创建实例、创建库没有什么特别的。需要注意的是在安装完数据库软件后需要去如下目录看看开发用的头文件和库文件都存在不。

头文件路径：/opt/ibm/db2/V10.5/include/。这个目录下有大概71个头文件。

库文件路径： /opt/ibm/db2/V10.5/lib64。这个目录下大概有274个头文件。

3.3配置

3.3.1创建C源程序文件。

    首先创建一个c源程序文件，实现在/tmp目录下创建个文件，并写入些内容。起名为test.c内容如下所示：

#include <stdio.h>

#include <stdlib.h>

#include </opt/ibm/db2/V10.5/include/sqludf.h>

//#include <sqlstate.h>

//

void writeLog(double *p1);

void call_os(void);

SQL_API_RC SQL_API_FN product ( SQLUDF_DOUBLE *in1,

                                SQLUDF_DOUBLE *in2,

                                SQLUDF_DOUBLE *outProduct,

                                SQLUDF_NULLIND *in1NullInd,

                                SQLUDF_NULLIND *in2NullInd,

                                SQLUDF_NULLIND *productNullInd,

                                SQLUDF_TRAIL_ARGS )

{

  /* Check that input parameter values are not null 

     by checking the corresponding null indicator values

         0  : indicates parameter value is not NULL 

         -1 : indicates parameter value is NULL 

    If values are not NULL, calculate the product.

    If values are NULL, return a NULL output value. */

  if ((*in1NullInd != -1) &&

       (*in2NullInd != -1))

  {

    *outProduct = (*in1) * (*in2);

    //tl

    writeLog(outProduct);

    call_os();

    //tl

    *productNullInd = 0;  

  }

  else

  {

    *productNullInd = -1; 

  }

  return (0);

}

// 自己写的一个函数，功能就是创建个文件，再往文件中写些东西

void

writeLog(double *p1){

char *Fpath="/tmp/writeLog.log";

char *acttype = "w+";

FILE *fp1 = NULL;

size_t wsz = 0;

size_t nmemb =1;

size_t sz = 100;

fp1 = fopen(Fpath,acttype);

wsz = fwrite(p1,sz,nmemb,fp1);

fclose(fp1);

}

3.3.2创建编译脚本

在IBM DB2 信息中心中给出了脚本的内容，测试过程中稍微做了部分修改。脚本命名为bldrtn，内容为：

#! /bin/sh

#############################################################################

# (c) Copyright IBM Corp. 2007 All rights reserved. 

# 

# The following sample of source code ("Sample") is owned by International 

# Business Machines Corporation or one of its subsidiaries ("IBM") and is 

# copyrighted and licensed, not sold. You may use, copy, modify, and 

# distribute the Sample in any form without payment to IBM, for the purpose of 

# assisting you in the development of your applications.

# 

# The Sample code is provided to you on an "AS IS" basis, without warranty of 

# any kind. IBM HEREBY EXPRESSLY DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR 

# IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF 

# MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. Some jurisdictions do 

# not allow for the exclusion or limitation of implied warranties, so the above 

# limitations or exclusions may not apply to you. IBM shall not be liable for 

# any damages you suffer as a result of using, copying, modifying or 

# distributing the Sample, even if IBM has been advised of the possibility of 

# such damages.

#############################################################################

# SCRIPT: bldrtn

# Builds C routines (stored procedures or UDFs) for Linux

# Usage: bldrtn <prog_name> [ <db_name> ]

# Select the compiler to use

CC=gcc

#CC=xlc_r

# Set DB2PATH to where DB2 will be accessed.

# The default is the standard instance path.

DB2PATH=$HOME/sqllib

# Default compiler/linker settings

EXTRA_C_FLAGS=""

SHARED_LIB_FLAG=""

# Figure out which Linux architecture we are on

HARDWAREPLAT=`uname -m`

# Default to native bitwidth for the platform

if [ "$HARDWAREPLAT" = "x86_64" ] || [ "$HARDWAREPLAT" = "ppc64" ] || 

   [ "$HARDWAREPLAT" = "s390x" ] || [ "$HARDWAREPLAT" = "ia64" ]

then

  BITWIDTH=64

else

  # x86 is the only native 32-bit platform

  BITWIDTH=32

fi

# Uncomment the next line to force a 32-bit application compile/link

#BITWIDTH=32

# Set flags for 32-bit compilation on non-native 32-bit platforms

if [ $BITWIDTH = "32" ]

then

  LIB="lib32"

  if [ "$HARDWAREPLAT" = "s390x" ]

  then

    EXTRA_C_FLAGS="-m31"

  else

    if [ "$HARDWAREPLAT" = "ia64" ]

    then

      # DB2 does not support 32-bit applications on Linux on IA64

      BITWIDTH=64

    else

      EXTRA_C_FLAGS="-m32"

    fi

  fi

fi

# Set flags for 64-bit compilation

if [ $BITWIDTH = "64" ]

then

  LIB="lib64"

  if [ "$HARDWAREPLAT" != "ia64" ]

  then

    # gcc on ia64 does not support the -m64 flag

    EXTRA_C_FLAGS="-m64"

  fi

fi

if [ "$CC" = "xlc_r" ]

then

  SHARED_LIB_FLAG="-qmkshrobj"

else

  SHARED_LIB_FLAG="-shared"

  EXTRA_C_FLAGS="$EXTRA_C_FLAGS -fpic"

fi

LINK_FLAGS="$EXTRA_C_FLAGS $SHARED_LIB_FLAG"

# Set the runtime path.

EXTRA_LFLAG="-Wl,-rpath,$DB2PATH/$LIB"

# If an embedded SQL program, precompile and bind it.

if [ -f $1".sqc" ]

then

  ./embprep $1 $2

fi

# Compile the program.

$CC $EXTRA_C_FLAGS  -I/opt/ibm/db2/V10.5/include -c $1.c -D_REENTRANT

# Link the program and create a shared library

$CC $LINK_FLAGS -o $1 $1.o $EXTRA_LFLAG -L'/opt/ibm/db2/V10.5/lib64' -ldb2 -lpthread

# Copy the shared library to the function subdirectory.

# The user must have write permission to this directory.

rm -f $DB2PATH/function/$1

cp $1 $DB2PATH/function

3.3.3创建用户自定义函数

    创建之前先链接到数据库，本文中的库名为basedb。则命令为：

    $ db2 CONNECT TO BASEDB;

    编辑创建脚本文件，文件命名为createudf.sql。脚本内容为：

CREATE FUNCTION product(in1 DOUBLE,in2 DOUBLE)

RETURNS DOUBLE

LANGUAGE C

PARAMETER STYLE SQL

NO SQL

FENCED THREADSAFE

DETERMINISTIC

RETURNS NULL ON NULL INPUT

NO EXTERNAL ACTION

EXTERNAL NAME 'test!product';

    创建函数，命令如下：

    $ db2 -tvf createudf.sql

3.3.4修改实例的参数。

在测试的时候把如下参数关闭。

    $ db2 UPDATE DBM CFG USING KEEPFENCED NO

4.测试

4.1链接到数据库

    $ db2 CONNECT TO BASEDB;

4.2执行函数

    $ db2 "values product(1,1)";

4.3查看日志文件

    $ cat /tmp/writeLog.log

    看这个文件中是否已经有内容了。

5.总结

    本文所举的例子还非常肤浅，不过个人有个美好的想法。就是通过用户自定义函数扩展DB2的功能。尽管里面还有很多需要考虑的地方，比如用户权限，用户自定义函数的性能等问题。

算是抛砖引玉吧，有时间再好好研究一下。