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

Pro*C to DB2 Embedded SQL migration, connection

Introduction
Embedded SQL is traditional method of database programming but still very robust and effective. All major SQL vendors supports Embedded SQL technology. Unfortunately, although general structure for different Embedded SQL implementation contains a lot of shared elements migration from one vendor to another is not easy. Nevertheless,  "not easy" does not mean impossible, if one recognizes some common pattern then migration is pretty simple and allows keep the main solution logic almost intact.
Pro*C is Embedded SQL implementation for Oracle, just Embedded SQL for DB2. Below I will describe some common problems and solutions found during migrating Oracle C++ Pro*C code to DB2 starting from connecting to and disconnecting from database.
Oracle Compatibility Mode
Starting from version 9.7 DB2 contains Oracle Compatibility Mode (constantly improved and enhanced in subsequent version up to 10.5) making migration of SQL procedure code almost painless. There is also "compatibility mode" for Pro*C. It contains a lot of useful features but most of the problems should be resolved manually.
Sample program
I created a simple program which connects to and disconnects from database the show the first basic problems. The source code is available here.
Compiling, linking, declaration
Implementation for DB2 and Oracle is different. Below are  examples of make files.
Oracle: mk.m
DB2 : mk.m
Additional differences
DB2 requires all host variables (passing data between SQL and C++ code) being declared in a special section. Example:

  EXEC SQL BEGIN DECLARE SECTION;
    char dbAlias[15];
    char user[128 + 1];
    char pswd[15];
  EXEC SQL END DECLARE SECTION;

Also every procedure containing any Embedded SQL code should have a direct struct sql declaration or EXEC SQL INCLUDE SQLCA clause.

void ConnectTo() 
{    
  struct sqlca sqlca;
...
}

Connecting and disconnecting in Oracle
Oracle : Source file

#include <stdio.h>
#include <string.h>
#include <sqlca.h>
#include <stdlib.h>
#include <sqlda.h>
#include <sqlcpr.h>

#include "../utillib.h"

static char * connection = "test/test@think:1521/testdb";

void sql_error(char *msg,int *failed) 
{ 
    char err_msg[512];
    size_t buf_len, msg_len;

    EXEC SQL WHENEVER SQLERROR CONTINUE;

    printf("\n%s\n", msg);

/* Call sqlglm() to get the complete text of the
 * error message.
 */
    buf_len = sizeof (err_msg);
    sqlglm(err_msg, &buf_len, &msg_len);
    printf("%.*s\n", msg_len, err_msg);

    EXEC SQL ROLLBACK RELEASE;
    *failed = 1;
} 


void ConnectTo() 
{    
    int failed = 0;
    printf("Connection string : %s\n",connection);
 
    EXEC SQL WHENEVER SQLERROR DO sql_error("Connection error",&failed);
     
    EXEC SQL CONNECT :connection;
    if (failed) {
       printf("Not connected\n");
       exit(EXIT_FAILURE);
    }       

    printf("Connected to ORACLE\n"); 
}

void Disconnect() 
{
   EXEC SQL WHENEVER SQLERROR CONTINUE;
   EXEC SQL COMMIT WORK RELEASE;
   printf("\nDisconnected");
}

Migration to DB2
The main problem is hidden in :

    EXEC SQL WHENEVER SQLERROR DO sql_error("Connection error",&failed);
     
    EXEC SQL CONNECT :connection;
    if (failed) {
    ...
    }

In case of any error sql_error routine is called and the routine prints error message and set error variable to 1 just allowing implement a logic for connection failure. This routine can be shared between all SQL invocations and provide common error handling.
Although DB2 Embedded SQL supports declaring custom routine for SQL error handling the routine does not accept any parameter just making direct migration of above code impossible. Passing data using global static variable is very bad programming practice and should be omitted.
The best solution is to use a reusable macro providing common method of error handling.

void SqlInfoPrint(char *appMsg, struct sqlca *pSqlca);

#define EVALRESULT(mess) if (sqlca.sqlcode < 0) { SqlInfoPrint(mess,&sqlca); error = 1; }
    

And DB2 version of Pro*C code above :

EXEC SQL CONNECT TO :dbAlias USER :user USING :pswd;
  EVALRESULT("Database -- connect with userid and password");
  if (error) {
    ...
  }

It requires manual changes but the execution flow is exactly the same. The migrated DB2 code (with implementation of SqlInfoPrint procedure) is here.
Next
Oracle EXEC SQL VAR