Migrating DB2 UDFs from AIX to Linux (RHEL)

Question

I successfully compiled some UDFs on 64-bit Linux that were originally compiled on AIX

Linux is running IBM DB2 v9.5

Here's the function that sometimes returns for some values

#include <cstdlib>
#include <cstdio>
#include <iostream>
#include "cbudf.hpp"


using namespace std;

extern "C"
void SQL_API_FN cbzdt(SQLUDF_VARCHAR_FBD *pArg,
                      SQLUDF_VARCHAR *pResult,
                      SQLUDF_NULLIND *pArgInd,
                      SQLUDF_NULLIND *pResultInd,
                      SQLUDF_CHAR *pSqlState,
                      SQLUDF_VARCHAR *pFuncName,
                      SQLUDF_VARCHAR *pSpecName,
                      SQLUDF_VARCHAR *pMsgTxt)
{
short i;
unsigned long x = 0;

cout << "entered cbzdt";

if (pArg->length < CM_UDF_ZDT_SIZE){
   *pResultInd = CM_UDF_IS_NULL_IND;
   cout << "pArg length is:" << pArg->length << " and that is smaller than CM_UDF_ZDT_SIZE: " << CM_UDF_ZDT_SIZE;
   }
else
   {
   cout << "entered else";
   for (i = 0; i < 4; i++)
      x = (x << 8) | pArg->data[i];
   if (x & 0xff000000){
      *pResultInd = CM_UDF_IS_NULL_IND;
      cout << "x mask is true.. making null";
      }
   else
      {
      cout << "entered nested else";
      *pResultInd = CM_UDF_NOT_NULL_IND;
      CtDate zDate(x);
//      strcpy(pResult, (const char *)(zDate.asString("%Y-%m-%d")));
      strncpy(pResult, (const char *)(zDate.asString("%Y-%m-%d")),11);
      if (pResult[0] == '3'){             // if year is 3000
         pResult[9] = '1';               // fix day
         cout << "pResult =1";
         }
      }
   }
}

And here's the data:

COID_                      TIMESTAMP_                 CHANGE_DATE                
-------------------------- -------------------------- ---------------------------
1814              2010-02-17-15.44.22.995784 x'0025780F000000000804011B'
1814              2010-02-17-15.44.55.583734 x'0025780F000000000804011B'
1814              2010-02-17-15.46.09.929014 x'0025780F000000000804011B'
1324              2009-12-02-21.31.13.058337 x'0025769F000000000804011B'
1324              2009-12-02-21.32.47.387150 x'0025769F000000000804011B'
1324              2009-12-02-21.34.03.020405 x'0025769F000000000804011B'
1324              2009-12-08-19.54.13.492488 x'002576A1000000000804011B'
1324              2009-12-08-19.55.22.041794 x'002576A1000000000804011B'
1358              2009-12-09-15.14.05.593887 x'002576A1000000000804011B'



After function applied:

COID_                      TIMESTAMP_                 3         
-------------------------- -------------------------- ----------
1814              2010-02-17-15.44.22.995784 2011-01-05
1814              2010-02-17-15.44.55.583734 2011-01-05
1814              2010-02-17-15.46.09.929014 2011-01-05
1324              2009-12-02-21.31.13.058337 -         
1324              2009-12-02-21.32.47.387150 -         
1324              2009-12-02-21.34.03.020405 -         
1324              2009-12-08-19.54.13.492488 -         
1324              2009-12-08-19.55.22.041794 -         
1358              2009-12-09-15.14.05.593887 -         

Does anybody have any ideas?

My guess is that this if statement:

if (x & 0xff000000){
          *pResultInd = CM_UDF_IS_NULL_IND;

is returning true on some values. Could it be something related to 64bit?

Also the couts do not print in the DB2 output

Thanks

Answer 1

What are the rules for converting a portion of the binary zDate to a usable date? You could probably come up with an equivalent conversion function in SQL and avoid the external compiled UDF entirely. The HEX function will convert the binary value to a hex string, which you can then iterate through with SUBSTR to build your integer value.

As for the cout messages, I don't think that cout is going to be of much use inside an external UDF. You'll have better luck saving your debugging info into a local file.