Pro*C to DB2 Embedded SQL migration, vars, structures, SP and UDF

Vars and structs

Pro*C Oracle code

#define SIZEKEY 20

int CalculateSum(float *sum,int keyid,struct retinfo *ret)
{

  char key[SIZEKEY+1];
/* Type equivalence key to the string external datatype.*/
  EXEC SQL VAR key is string(SIZEKEY);
  float sumk;
  int failed = 0;

  sprintf(key,"%u",keyid);

  printf("Calculate sum for %s\n",key);

  EXEC SQL WHENEVER SQLERROR DO sql_error("Calculate sum",&failed);
  
  EXEC SQL select sum(num) into :sumk FROM numhistory WHERE numkey = :key  ;

  if (failed) return 0; 
  
  ret->keyid = keyid;
  EXEC SQL select count(num) into :ret->no FROM numhistory WHERE numkey = :key  ;

  if (failed) return 0; 
  
  *sum = sumk;
  return 1;
}

Two problems require attention here while migrating to DB2.
First

EXEC SQL VAR key is string(SIZEKEY);

DB2 Embedded SQL does not support it. All hosts variables should be enclosed in :

EXEC SQL BEGIN DECLARE SECTION;
 .....
EXEC SQL END DECLARE SECTION;

Another

 EXEC SQL select count(num) into :ret->no FROM numhistory WHERE numkey = :key  ;

DB2 does not support struct member as a host variable. So value should be firstly assigned to simple variable and secondly to struct member. So finally DB2 Embedded SQL code equivalent to Pro*C code

int CalculateSum(float *sum,int keyid,struct retinfo *ret) {
  
  struct sqlca sqlca;
  
EXEC SQL BEGIN DECLARE SECTION;
    char key[21];
    float sumk;
    int no;
EXEC SQL END DECLARE SECTION;
  int error = 0;

  sprintf(key,"%u",keyid);

  printf("Calculate sum for %s\n",key);
  
  EXEC SQL select sum(num) into :sumk FROM numhistory WHERE numkey = :key  ;
  EVALRESULT("Calculate sum");
  if (error) return 0;
  ret->keyid = keyid;
  EXEC SQL select count(num) into :no FROM numhistory WHERE numkey = :key  ;
  EVALRESULT("Calculate num");
  ret->no = no;   
  *sum = sumk;

  return 1;
}

SP calling
Pro*C Oracle code

int CallProc(int *res,char* sou) {
  
  int failed = 0;  
  
  printf("Call SP for %s\n",sou);

  EXEC SQL WHENEVER SQLERROR DO sql_error("Call SP",&failed);
  
  EXEC SQL EXECUTE
  BEGIN
    SIMPLEPROC( :res, to_number( :sou ) );
  END;
  END-EXEC;

  if (failed) return 0; 
  
  return 1;
}

DB2 supports notation EXEC SQL EXECUTE ... END-EXEC after setting "Oracle Compatibile Mode" in db2 prep invocation (example) :

db2 prep $1.sqc bindfile SQLWARN NO COMPATIBILITY_MODE ORA

Another problem is related to

SIMPLEPROC( :res, to_number( :sou ) );

DB2 does not support UDF invocation as SP parameter. So this launching sequence should be split into two : UDF invocation and consecutive SP calling.
DB2 equivalent to Pro*C code

int CallProc(int *res,char* sou) {
  
  int error = 0;  
  struct sqlca sqlca;
  
  EXEC SQL BEGIN DECLARE SECTION;
    char psou[100];
    int number;
    int resp;
  EXEC SQL END DECLARE SECTION;

  printf("Call SP par=%s\n",sou);
  
  strcpy(psou,sou);
  
  EXEC SQL VALUES (TO_NUMBER(:psou)) INTO :number;
  EVALRESULT("Call TO_NUMBER");
  if (error) return 0;

  
  EXEC SQL EXECUTE
  BEGIN
    SIMPLEPROC( :resp, :number );
  END;
  END-EXEC;
  
  EVALRESULT("Call SP");

  if (error) return 0; 
  
  *res = resp;
  
  return 1;

}

UDF calling
Pro*C Oracle code

int CallFunc(int *res, int par) {
  int failed = 0;  
  
  printf("Call Func for %u\n",par);

  EXEC SQL WHENEVER SQLERROR DO sql_error("Call UDF",&failed);
  
  EXEC SQL EXECUTE
  BEGIN
    :res := RETPROC( :par );
  END;
  END-EXEC;
  
  if (failed) return 0; 
  
  return 1;

}

DB2 does not support this launching method even in Compatibility Mode. DB2 Embedded SQL equivalent

int CallFunc(int *res, int par) {
  int error = 0;  
  struct sqlca sqlca;
  
  EXEC SQL BEGIN DECLARE SECTION;
    int resp;
    int parp;
  EXEC SQL END DECLARE SECTION;
  
  printf("Call UDF par=%u\n",par);
  
  parp = par;  
  
  EXEC SQL VALUES (RETPROC(:parp)) INTO :resp;
  EVALRESULT("Call UDF");
  if (error) return 0;
  
  *res = resp;
  
  return 1;
}  

Additional remarks

• Full source code (including makefiles and sql) can be found here.
• Next : BLOB handling