Walker News

How To Create DB2 UDF In C Programming Language?

The IBM DB2 allows one to create external user-defined function (UDF) in few of the popular programming languages. In this tutorial, however, the focus is on using C Language to build an external scalar function that convert UNIX epoch time to calendar date.

The sample code used in this guide is tested on RHEL5 with DB2 9.7 FP8.

1 – Write the UDF source file
As you can see in the previous post, a UDF source file can contain a number of user-defined functions. Each of these functions must be defined in the format that is suitable for PARAMETER STYLE SQL signature implementation, i.e.
SQL_API_FN function-name(SQL-arguments,
                         SQL-argument-indicators,
                         SQLUDF_TRAIL_ARGS)

So, this is how the sample source code of ept2dt looks like (let’s name it as udftest.c):
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <time.h>
#include <sqlca.h>
#include <sqludf.h>

#ifdef __cplusplus
extern "C"
#endif
void SQL_API_FN ept2dt(SQLUDF_INTEGER *eT,
                       SQLUDF_INTEGER *uG,
                       SQLUDF_CHAR *tS,
                       SQLUDF_SMALLINT *eTI,
                       SQLUDF_SMALLINT *uGI,
                       SQLUDF_SMALLINT *tSI,
                       SQLUDF_TRAIL_ARGS)
{  
  if (*eTI == -1 || *uGI == -1)
  {
    /*
      If either input value is blank, then
      exit function and return null to caller.
    */
    *tSI = -1;
  }
  else
  {
    time_t ep = *eT;
    struct tm *tms;

    tms = *uG == 0 ? localtime(&ep) : gmtime(&ep);
    strftime(tS, 20, "%F %T", tms);

    // Indicates a value is returned to caller
    *tSI = 0;
  }
}

NOTE:

1. The ept2dt() function requires two input values, i.e. “epoch time” and “0 or 1” (where 0 is to get it resolves epoch time to local time zone; 1 means to return the time in UTC+0 time zone).

2. The two input values are passed to function parameters *eT and *uG respectively, and the return value (which is the date in YYYY-MM-DD HH:MM:SS format) is passed to *tS.

3. The *eTI, *uGI, and *tSI serve as indicators of the input and output parameters, to check for null/blank values.

4. Must include the sqlca.h, sqludf.h, and all other necessary header files (e.g. time.h that includes the definitions of date/time functions used in the sample code).

5. Includes extern "C" for each user-defined function.

2 – Compile the UDF source file
Now, it’s time to build the UDF. Let’s create a shell script (e.g. udftest.build) that calls g++ to compile the UDF into binary file called udftest and then move it to $HOME/ubin directory.

NOTE: Please update the red text, if your DB2 instance home directory is different!
#!/bin/sh

unset DB2INSTD DB2BLD COPT LLIB LOPT UBIN

export DB2INSTD=/home/db2inst1
export DB2BLD=$DB2INSTD/sqllib
export COPT="-O -ffloat-store -fPIC"
export LLIB=$DB2BLD/lib
export LOPT="-Wl,-rpath,$DB2BLD/lib -ldb2"
export UBIN=$HOME/ubin

g++ $COPT -I$DB2BLD/include -c udftest.c
g++ -o udftest udftest.o -shared -L$LLIB $LOPT

[ -d $UBIN ] || mkdir $UBIN
mv udftest $UBIN

3 – Register the UDF to DB2
This is the final step! Let’s create another script file (says, udftest.reg) that connects to database and then execute the “create function” statement to complete the registration.

NOTE: You must pass the database name to the script, e.g. udftest.reg mydb:
#!/bin/sh
source $HOME/.bashrc

unset UBIN

export UBIN=$HOME/ubin

db2 connect to $1

db2 "CREATE FUNCTION ept2dt(INT, INT)
  RETURNS CHAR(19)
  EXTERNAL NAME '$UBIN/udftest!ept2dt'
  LANGUAGE C
  PARAMETER STYLE SQL
  DETERMINISTIC
  NOT FENCED
  NULL CALL
  NO SQL
  NO EXTERNAL ACTION
"

Uses the UDF in SQL statement
Let’s create a test table in the mydb (the database which the UDF is registered in previous step) to verify the ept2dt function:
db2 "CREATE TABLE tst(rec INT, ept INT)"

Then, insert two records (one with empty ept) to tst table:
db2 "INSERT INTO tst VALUES (1,1234567890)"

db2 "INSERT INTO tst (rec) VALUES (2)"

Now, call ept2dt in the SELECT SQL to test the user-defined function that we have created in C Language:
db2 "SELECT rec, ept, ept2dt(ept,0) FROM tst"

Tutorial - DB2 UDF written in C Language

For more info, kindly refer to the official guideline of creating C/C++ routines for IBM DB2.

Custom Search

2017  •  Privacy Policy