Walker News

Creates A DB2 UDF In C Language For Converting Unix Epoch Time To User-friendly Date Format

The DB2 SQL scalar function created in previous post is almost perfect, except that it cannot resolve the UTC date/time to local time zone.

To make thing perfect, let’s use C programming language to build a DB2 external scalar function for this subject.
The sample code in this tutorial is tested on Red Hat Enterprise Linux platform with DB2 9.7 FP8. It may work on other platforms, except that timegm() function called by the c2em UDF is not portable.

Firstly, create this C source file (let’s call it db2udf.c) to build a user-defined function library that contains 4 external UDF:
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <time.h>
#include <sqlca.h>
#include <sqludf.h>

//----------------------------------------------------

/* 
  Syntax:
    e2c(epoch_time, [0|1]
    where 0=local time; 1=UTC 
  
  Purpose:
    Convert epoch time to date in UTC / localtime zone
*/

#ifdef __cplusplus
extern "C"
#endif

void SQL_API_FN e2c(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)
  {
    *tSI = -1;
  }
  else
  {
    time_t ep = *eT;
    struct tm *tms;

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

    *tSI = 0;
  }
} 

//----------------------------------------------------

/*
  Syntax:
    c2ef('YYYY-MM-DD HH:MM:SS')
  
  Purpose:
    Convert local time to epoch time.
*/

#ifdef __cplusplus
extern "C"
#endif

void SQL_API_FN c2ef(SQLUDF_CHAR *tS,
                     SQLUDF_INTEGER *eT,
                     SQLUDF_SMALLINT *tSI,
                     SQLUDF_SMALLINT *eTI,
                     SQLUDF_TRAIL_ARGS)
{
  if (*tSI == -1)
  {
    *eTI = -1;
  }
  else
 {
    int cnt = 1, i = 1;
    time_t ept;
    struct tm tminfo;
    char *ttk, *dtp, *tmp;

    ttk = strtok(tS, " ");
    while (ttk != NULL)
    {
      i == 1 ? dtp = ttk : tmp = ttk;
      ttk = strtok(NULL, " ");
      i += 1;
    }

    ttk = strtok(dtp, "-");
    while (ttk != NULL)
    {
      switch(cnt)
      {
        case 1:
          tminfo.tm_year = atoi(ttk) - 1900;
          break;
        case 2:
          tminfo.tm_mon = atoi(ttk) - 1;
          break;
        default:
          tminfo.tm_mday = atoi(ttk);
          break;
      }
      ttk = strtok(NULL, "-");
      cnt += 1;
    }

    ttk = strtok(tmp, ":");
    while (ttk != NULL)
    {
      switch(cnt)
      {
        case 4:
          tminfo.tm_hour = atoi(ttk);
          break;
        case 5:
          tminfo.tm_min = atoi(ttk);
          break;
        default:
          tminfo.tm_sec = atoi(ttk);
          break;
      }
      ttk = strtok(NULL, ":");
      cnt += 1;
    }

    ept = mktime(&tminfo);
    *eT = ept;

    *eTI = 0;
  }
}

//----------------------------------------------------

/*
  Syntax:
    c2em('YYYY-MN-DD')
    c2em('YYYY-MM-DD UTC')
  
  Purpose:
    Convert UTC/local midnight time to epoch time.

  WARNING:
    timegm()is not portable/availabe on all platforms!
*/

#ifdef __cplusplus
extern "C"
#endif

void SQL_API_FN c2em(SQLUDF_CHAR *tS,
                     SQLUDF_INTEGER *eT,
                     SQLUDF_SMALLINT *tSI,
                     SQLUDF_SMALLINT *eTI,
                     SQLUDF_TRAIL_ARGS)
{
  if (*tSI == -1)
  {
    *eTI = -1;
  }
  else
  {
    int cnt = 1, ugF = 0;
    time_t ept;
    struct tm tminfo;
    char *ttk;

    // ugF=1 if UTC is found.
    strstr(tS, "UTC") == NULL ? ugF = 0 : ugF = 1;

    ttk = strtok(tS, "-");
    while (ttk != NULL)
    {
      switch(cnt)
      {
        case 1:
          tminfo.tm_year = atoi(ttk) - 1900;
          break;
        case 2:
          tminfo.tm_mon = atoi(ttk) - 1;
          break;
        default:
          tminfo.tm_mday = atoi(ttk);
          break;
      }
      ttk = strtok(NULL, "-");
      cnt += 1;
    }

    tminfo.tm_hour = 0;
    tminfo.tm_min = 0;
    tminfo.tm_sec = 0;

    if (ugF == 1)
      ept = timegm(&tminfo);
    else
      ept = mktime(&tminfo);

    *eT = ept;

    *eTI = 0;
  }
}

//----------------------------------------------------

/* 
  Syntax:
    c2en('now')

  Purpose:
    Convert current local time to epoch time.
*/

#ifdef __cplusplus
extern "C"
#endif

void SQL_API_FN c2en(SQLUDF_CHAR *tS,
                     SQLUDF_INTEGER *eT,
                     SQLUDF_SMALLINT *tSI,
                     SQLUDF_SMALLINT *eTI,
                     SQLUDF_TRAIL_ARGS)
{
  if (*tSI == -1)
  {
    *eTI = -1;
  }
  else
  {
    time_t ept;
   
    time(&ept);
    *eT = ept;
   
    *eTI = 0;
  }
}

Next, create a bash shell script to build this UDF library called db2udf. Let’s name this shell script file as db2udf.build.sh.

NOTE:
1. The red text is the DB2 instance home directory – change this accordingly for your environment.

2. Instead of storing the UDF library file in the default DB2 function directory, it creates $HOME/ubin directory to keep our db2udf library file.
#!/bin/sh
source $HOME/.bashrc

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 db2udf.c
g++ -o db2udf db2udf.o -shared -L$LLIB $LOPT

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

Last step – execute the “create function” statement to register those 4 user-defined functions in the db2udf library to your database (e.g. a DB called mydb). Let’s create another shell script file call db2udf.register.sh (again, change the red text accordingly to your DB name):
#!/bin/sh
source $HOME/.bashrc

unset UBIN

export UBIN=$HOME/ubin

db2 connect to mydb

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

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

db2 "CREATE FUNCTION c2em(CHAR(14))
RETURNS INTEGER
EXTERNAL NAME '$UBIN/db2udf!c2em'
LANGUAGE C
PARAMETER STYLE SQL
DETERMINISTIC
NOT FENCED
NULL CALL
NO SQL
NO EXTERNAL ACTION
"

db2 "CREATE FUNCTION c2en(CHAR(3))
RETURNS INTEGER
EXTERNAL NAME '$UBIN/db2udf!c2en'
LANGUAGE C
PARAMETER STYLE SQL
DETERMINISTIC
NOT FENCED
NULL CALL
NO SQL
NO EXTERNAL ACTION
"

db2 connect reset

Now, you can use those user-defined functions in DB2 SQL, e.g.:
SELECT e2c(86400,1) FROM syscat.functions
SELECT e2c(86400,0) FROM syscat.functions

SELECT c2ef('2014-02-14 13:14:00') FROM syscat.functions

SELECT c2em('2015-01-14') FROM syscat.functions
SELECT c2em('2015-01-14 UTC') FROM syscat.functions

SELECT c2en('now') FROM syscat.functions

Custom Search

  1. Walker 15-02-14@15:58

    If you are NOT using DB2 9.7, you may have to explicitly use DB2 string function, or table column name (that contains the date/time value in format expected by the UDF). For example:

    Uses substr function:

    SELECT c2en(substr('now',1,3)) from syscat.functions
    

    Uses table column named “cdt” of table called testtbl:

    SELECT c2ef(cdt) FROM testtbl
    

2017  •  Privacy Policy