C Table UDF to join flat file with your SQL

by | Jun 12, 2008

Sometimes, it may be necessary to expose data in your flat file with the relational data. There might be some situations where there is no option to load that data in a table. But still, you need a way to use this flat file data in your SQL statements.

DB2 provides the concept of a table function that you can write. Here is one C table UDF example that reads a file and returns that data through your SQL statement.

Download sample code ufd.zip

udfreadfile.c

#include <stdio.h>
#include <stdarg.h>
#include <sqludf.h> 

#define  DEBUG 0

struct SCRATCHDATA
{
  long idx;
  FILE *p;
};


void hardw(char *msg, ...)
{
   FILE *p;
   char name[500];
   va_list argp;

   if (DEBUG) 
   {
      strcpy(name, getenv("HOME"));
      if (name == NULL)
      {
         strcpy(name, "/tmp/hard.txt");
      } else
      {
         strcat(name, "/sqllib/hard.txt");
      }
      p = fopen(name, "a");
      if (p == NULL)
      {
         p = fopen("/tmp/hard.txt", "a");
      }
      if (p != NULL)
      {
         va_start(argp, msg);
         vfprintf(p, msg, argp);
         va_end(argp);
         fprintf(p, "\n");
         fflush( p);
         fclose(p);
      } 
   }
} 

void SQL_API_FN  readFile(SQLUDF_VARCHAR *fileName, 
              SQLUDF_VARCHAR *cm15, 
              SQLUDF_NULLIND *fileNameInd, 
              SQLUDF_NULLIND *cm15IND, 
              SQLUDF_TRAIL_ARGS_ALL)
{
  char myData[100];
  long i = 0;
  char errMsg[1000];
  struct SCRATCHDATA *sp;
  sp = (struct SCRATCHDATA *) SQLUDF_SCRAT->data;

  switch (SQLUDF_CALLT)
  {
  case SQLUDF_TF_FIRST:
      hardw("in SQLUDF_TF_FIRST ******");
      if (*fileNameInd == -1)
      {
         strcpy( SQLUDF_STATE, "38777");
         sprintf (errMsg, "Null file name.");
         strcpy( SQLUDF_MSGTX, errMsg);
         break;
      }
      sp->p = fopen(fileName, "r");
      if (sp->p == NULL)
      {
         strcpy( SQLUDF_STATE, "38777");
         sprintf (errMsg, "Given file can not be opened", fileName);
         strcpy( SQLUDF_MSGTX, errMsg);
         hardw("File '%s'can not be opened", fileName);
         break;
      }
      sp->idx = 0;
    case SQLUDF_TF_OPEN:
      hardw("in SQLUDF_TF_OPEN ******");
      break;
    case SQLUDF_TF_FETCH:
      hardw("in SQLUDF_TF_FETCH %ld ******", sp->idx);
      if (fgets(myData, 100, sp->p) == NULL)
      {
         strcpy( SQLUDF_STATE, "02000");
         break;
      }
      myData[strlen(myData)-1] = '\0';
      strcpy(cm15, myData);
      *cm15IND = 0;
      sp->idx++;
      break;
    case SQLUDF_TF_CLOSE:
       hardw("in SQLUDF_TF_CLOSE ******");
       fclose(sp->p);
      break;
    case SQLUDF_TF_FINAL:
      /* close the file */
      hardw("in SQLUDF_TF_FINAL ******");
      sp->idx = 0;
      break;
  }
}

udfreadfile.exp

readFile

How to catalog Table UDF (udfcat)

The catalog definition to register C table UDF in DB2 catalog is given below:

#! /bin/ksh
export ${FENCED:="FENCED NOT THREADSAFE"}
export ${SHLIBNAME:="udfreadfile"}
TOK=$(date +"%y%m%d%H%M%S")
export SHLIBNAME=udfreadfile$TOK
rm -f ~/sqllib/function/udfreadfile*
cp -f udfreadfile ~/sqllib/function/$SHLIBNAME

db2 -tv <<!EOF

CONNECT TO $1;

DROP FUNCTION READ_FILE;

CREATE FUNCTION READ_FILE
(
  FILE_NAME       VARCHAR(128)
)
RETURNS TABLE (c1 varchar(15))
EXTERNAL NAME '${SHLIBNAME}!readFile'
SPECIFIC READFILE
LANGUAGE C
PARAMETER STYLE DB2SQL
${FENCED}
RETURNS NULL ON NULL INPUT
NOT DETERMINISTIC
NO SQL
NO EXTERNAL ACTION
SCRATCHPAD
FINAL CALL
DISALLOW PARALLEL;

The above is a script so make sure you set execute bit on it. i.e. chmod +x udfcat

How to compile?

On your platform, copy bldrtn from your ~/sqllib/sample/c dir to your local directory and use this makefile to compile this program.

DB2PATH=${HOME}/sqllib

BLDRTN=./bldrtn

ALIAS=sample
UID=
PWD=

COPY=cp
ERASE=rm -f

all : \
	srv

srv : \
	rtn

rtn : \
	udfreadfile

clean : 
	$(ERASE) *.o

#****************************************************************************
#		   2h - make cleanall
#****************************************************************************

cleanall : \
	clean
	$(ERASE) udfreadfile
	$(ERASE) $(DB2PATH)/function/udfreadfile

udfreadfile : udfreadfile.c
	$(BLDRTN) udfreadfile $(ALIAS)
	./udfcat $(ALIAS) > udfcat.log

How this UDF works?

The sample code given in this UDF reads a flat file having a column of data. The size of the data is 15 character and file can have as many rows as possible.

The UDF defines a scratchpad memory area where we store the file pointer so that it can be used for each row. When this UDF is executed for each row, the different segment of code are executed in certain order.

For example:

SQLUDF_TF_FIRST is used to initialize variables or open a file pointer. This will be executed only once.
SQLUDF_TF_FETCH is used for each row execution. Remember, there is no while loop to go through the contents of the file since different code segments are used for different events.
Use SQLUDF_TF_CLOSE or SQLUDF_TF_FINAL to free up file pointer or free up memory.

FINAL CALL is used in UDF definition so that DB2 can invoke calls for SQLUDF_TF_FIRST and SQLUDF_TF_FINAL code segments. SCRATCHPAD is used to store file pointer and make sure that the size of the SCRATCHPAD is sufficient enough to hold the structure. The default size is only 100 bytes.

How to invoke the table UDF?

SELECT * FROM TABLE (db2inst1.read_file('$HOME/tableudf/datafilename.txt'))

You can use this UDF in your query to join it with other tables etc or use it in a sub-query.

UDF to show database directory

Date: May 27, 2025

Here is a table UDF that lists the database directory using Db2 API call.

File Name: listdb.c
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sqludf.h>
#include <db2ApiDf.h>

#ifdef __cplusplus
extern "C" {
#endif

struct SCRATCHDATA
{
    FILE *debug_fp;
    int done;          /* Flag to indicate if processing is done */
    int scan_id;       /* Persistent scan ID */
    int num_entries;   /* Number of entries */
    int current_entry; /* Current entry index */
};

void SQL_API_FN list_db(
    SQLUDF_VARCHAR *alias_name,        /* Output: Database alias */
    SQLUDF_VARCHAR *db_name,           /* Output: Database name */
    SQLUDF_NULLIND *alias_name_ind,    /* Null indicator for alias_name */
    SQLUDF_NULLIND *db_name_ind,       /* Null indicator for db_name */
    SQLUDF_TRAIL_ARGS_ALL)             /* Standard table function arguments */
{
    struct db2DbDirInfoV9 *db_entry = NULL; /* Structure for database directory entry */
    db2DbDirOpenScanStruct    open_struct; /* Structure for opening directory scan */
    db2DbDirNextEntryStructV9 next_struct; /* Structure for getting next entry */
    db2DbDirCloseScanStruct   close_struct; /* Structure for closing directory scan */
    struct SCRATCHDATA *sp;
    struct sqlca sqlca;                /* SQLCA for error handling */
    char sqlstate[6] = "00000";        /* SQL state for errors */
    int rc = 0;                        /* Return code */

    sp = (struct SCRATCHDATA *)SQLUDF_SCRAT->data;

    switch (SQLUDF_CALLT)
    {
        /* Initialize Scratchpad */
        case SQLUDF_TF_FIRST:
            sp->scan_id = 0;
            sp->current_entry = 0;
            sp->done = 0;
            sp->debug_fp = fopen("/tmp/list_db.log", "a");
            break;

        case SQLUDF_TF_OPEN:
            open_struct.oHandle = 0; /* Initialize handle */
            open_struct.piPath = NULL;
            rc = db2DbDirOpenScan(db2Version970, &open_struct, &sqlca);
            if (rc != 0 || sqlca.sqlcode != 0) {
                if (sp->debug_fp != NULL) {
                   fprintf(sp->debug_fp, "Failed to open database directory scan: RC=%d, SQLCODE=%d\n", rc, sqlca.sqlcode);
                }
                strcpy(sqlstate, "38999");
                strcpy(SQLUDF_MSGTX, "Failed to open database directory scan");
                strncpy(SQLUDF_STATE, sqlstate, 5);
                sp->done = 1;
            } else {
                sp->scan_id = open_struct.oHandle; /* Store handle */
                sp->num_entries = open_struct.oNumEntries; /* Store number of entries */
                if (sp->debug_fp != NULL) {
                    fprintf(sp->debug_fp, "Opened database directory scan: Handle=%d, num_entries=%d\n", sp->scan_id, sp->num_entries);
                }
            }
            break;

        case SQLUDF_TF_FETCH:
            if (sp->done) {
                /* No more entries - exit FETCH mode */
                strcpy( SQLUDF_STATE, "02000");
                break;
            }
            next_struct.iHandle = sp->scan_id;
            /* Get database entries */
            rc = db2DbDirGetNextEntry(db2Version970, &next_struct, &sqlca);
            if (rc != 0 || sqlca.sqlcode != 0) {
                strcpy(sqlstate, "38999");
                strcpy(SQLUDF_MSGTX, "No more entries in database directory");
                strncpy(SQLUDF_STATE, sqlstate, 5);
                sp->done = 1;
                break;
            } else {
                /* Database name and alias found - set the output var*/
                db_entry = next_struct.poDbDirEntry;
                strncpy(alias_name, db_entry->alias, 8);
                alias_name[8] = '\0';
                *alias_name_ind = 0;
                strncpy(db_name, db_entry->dbname, 8);
                db_name[8] = '\0';
                *db_name_ind = 0;
                if (sp->debug_fp != NULL) {
                    fprintf(sp->debug_fp, "Fetched database entry: alias=%s, dbname=%s\n", alias_name, db_name);
                }
            }
            ++sp->current_entry;
            if (sp->current_entry >= sp->num_entries) {
                /* No more entries - set done flag */
                sp->done = 1;
            }
            break;

        case SQLUDF_TF_CLOSE:
            /* Close the database directory scan */
            close_struct.iHandle = sp->scan_id;
            rc = db2DbDirCloseScan(db2Version970, &close_struct, &sqlca);
            if (rc != 0 || sqlca.sqlcode != 0) {
                strcpy(sqlstate, "38999");
                strcpy(SQLUDF_MSGTX, "Failed to close database directory scan");
                strncpy(SQLUDF_STATE, sqlstate, 5);
                if (sp->debug_fp != NULL) {
                    fprintf(sp->debug_fp, "Failed to close database directory scan\n");
                }
            }
            if (sp->debug_fp != NULL) {
                fclose(sp->debug_fp);
            }
            break;
        default:
            break;
    }
    return;
}

#ifdef __cplusplus
}
#endif%


Makefile

# Db2 installation directory (override with DB2_HOME environment variable or set manually)
DB2_HOME ?= $HOME/sqllib

# Compiler and flags
CC = gcc
CFLAGS = -g -fpic -Wall -I$(DB2_HOME)/include -D_REENTRANT
LDFLAGS = -shared -L$(DB2_HOME)/lib -ldb2 -lpthread

# Target library name
TARGET = udflib

# Source file rn

SRC = listdb.c

# Default target
all: $(TARGET)

# Build the shared library
$(TARGET): $(SRC)
	$(CC) $(CFLAGS) $(SRC) -o $@ $(LDFLAGS)

# Install the library to DB2 function directory
install: $(TARGET)
	./create_fun.sh

# Clean up
clean:
	rm -f $(TARGET)

# Phony targets
.PHONY: all clean install

Register Function

#!/bin/bash
export ${FENCED:="FENCED NOT THREADSAFE"}
TOK=$(date +"%y%m%d%H%M%S")
export SHLIBNAME=udflib$TOK
rm -f ~/sqllib/function/udflib*
cp -f udflib ~/sqllib/function/$SHLIBNAME
db2 CONNECT TO VIZ
db2 -tv <<EOF

CREATE OR REPLACE FUNCTION list_db()
     RETURNS TABLE (
       db_name VARCHAR(128),
       alias_name VARCHAR(128)
     )
EXTERNAL NAME '${SHLIBNAME}!list_db'
SPECIFIC list_db
LANGUAGE C
PARAMETER STYLE DB2SQL
NO SQL
${FENCED}
NO EXTERNAL ACTION
SCRATCHPAD FINAL CALL
NOT DETERMINISTIC
DISALLOW PARALLEL
;
CONNECT RESET;
EOF%

How to call

select * from table (list_db())