DB2 C language external scalar user-defined function

Sample step of define a C language external scalar user-defined function.

1. Define function implementation using C language

$ cat mymd5.c

#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <time.h>
#include <sqlca.h>
#include <sqludf.h>

#include <openssl/md5.h>

#ifdef __cplusplus
extern "C"
#endif
SQL_API_RC SQL_API_FN md5(SQLUDF_CHAR *input,
                          SQLUDF_CHAR *output,
                          SQLUDF_NULLIND *inputInd,
                          SQLUDF_NULLIND *outputInd,
                          SQLUDF_TRAIL_ARGS)
{

  if (*inputInd == -1) {
    *outputInd = -1;
  }
  else {
    *outputInd = 0;

    MD5_CTX ctx;
    unsigned char md[16];
    char tmp[3]={'\0'};
    int i;
    MD5_Init(&ctx);
    MD5_Update(&ctx, input, strlen(input));
    MD5_Final(md,&ctx);
    for( i=0; i<16; i++ ){
        sprintf(tmp,"%02x",md[i]);
        strcat(output,tmp);
    }
  }
  return;
}

About function declaration definition, please refer to http://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.apdv.routines.doc/doc/c0023763.html

2. Compile function into a shared library

$ cat makefile

DB2INSTDIR=/home/db2inst1

mymd5: mymd5.c
    gcc -I${DB2INSTDIR}/sqllib/include -L${DB2INSTDIR}/sqllib/lib -o $@ -Wl,-rpath,${DB2INSTDIR}/sqllib/lib -ldb2 -ffloat-store -fPIC -shared $< -lcrypto

3. Define DB2 function prototype

$ cat mymd5.sql

CREATE FUNCTION md5(VARCHAR(20))
  RETURNS CHAR(32)
  LANGUAGE C
  PARAMETER STYLE SQL
  NO SQL
  NOT FENCED
  DETERMINISTIC
  RETURNS NULL ON NULL INPUT
  NO EXTERNAL ACTION
  EXTERNAL NAME '/full/path/to/mymd5!md5';

You can also put the shared library into folder /home/db2inst1/sqllib/function, so that the "EXTERNAL NAME" should be written as EXTERNAL NAME 'mymd5!md5'

$ db2 -tf mymd5.sql
DB20000I  The SQL command completed successfully.

4. Verify the function

$ db2 "select md5('123') from sysibm.sysdummy1"

1                              
--------------------------------
202cb962ac59075b964b07152d234b70

  1 record(s) selected.