Delphi, DB2, Oracle compatibility

Introduction
DB2 Compatibility Mode allows to use Oracle PL/SQL server code to run on DB2. Starting from version 9.7 DB2 recognizes two SQL dialects. SQL/PL - native DB2 and PL/SQL - native Oracle. SQL/PL code is compiled into DB2 binary form and run by DB2 engine directly - no emulation, no wrappers. It makes migration from Oracle to DB2 more easy. What's more - it is possible to maintain the same server code for two databases. It could be very interesting option for ISV.
Import : unfortunately Oracle compatibility mode is not available for DB2 Express version (free). But one can download and install 90-day trial version of full DB2 release.
Also application client code is Oracle compatibility enabled. For instance DB2 JDBC driver is also enhanced to give the developer the access to Oracle features, for instance SYS_REFCURSOR OUT parameter in PL/SQL stored procedure.
Unfortunately - Delphi is not on the IBM list of supported languages or application frameworks. But DBExpress DB2 client available with every edition of Delphi can also run application against DB2 enabled for Oracle, although not all features are supported.
What is under test
To prove it I created a simple server code and simple Delphi application. Server code and application code contains only some features related to Oracle.

• Execute methods in Oracle style packages with IN and OUT parameters.
• Execute Oracle PL/SQL stored procedure outside package.
• Execute Oracle PL/SQL UDF
• Query the table having Oracle NUMBER type (added with DB 9.7)
• Query the table having DATE column (there is difference between DB2 and Oracle implementation)
• Read cursor returned from stored procedure.

Delphi code is created and executed as DUNIT test.
The application and server code is available here.
The purpose is to pass all test using the same application and server code for both databases.
Server code and migration
It is very simple and contains three tables and several simple PL/SQL code objects : packages, UDF and stored procedures. The migration of the server code from Oracle to DB2 was very simple - just deploy all object to DB2 without changing single line. Only stored procedure returning SYS_REFCURSOR should have been modified because of limitation related to Delphi client (not server)
Application code and migration
Application code is divided into two parts. One class is a general purpose class used for encapsulating some methods giving access to the database server and the second is the DUNIT test code.
Migration of the application code was more complicated than the server code, more changes were necessary. But - to my thinking - after acquiring know-how and following good programming practices it is possible to migrate application from Delphi and maintain the same application code for both databases.
Connecting to database
It is pretty easy for DB2. Just install DB2 Server Client  and it is ready to use by Delphi application. Oracle is a little bit more complicated if one uses instant-client. After unpacking client bundle it is necessary to manually set ORACLE_HOME variable and %ORACLE_HOME%\network\admin\tnsnames.ora connection profile.
After that the connection code is simple and easy:

procedure DBTest.ConnectOracle;
begin
  With Conn do
  begin
    Conn.DriverName := 'Oracle';
    Params.Values['USER_NAME'] := 'testuser';
    Params.Values['PASSWORD'] := 'testuser';
    Params.Values['DATABASE'] := 'TEST';
  end;
end;

procedure DBTest.ConnectDB2;
begin
  With Conn do
  begin
    DriverName := 'DB2';
    VendorLib := 'db2cli.dll';
    LibraryName := 'dbxdb2.dll';
    GetDriverFunc := 'getSQLDriverDB2';
    Params.Values['USER_NAME'] := 'db2inst1';
    Params.Values['PASSWORD'] := 'db2inst1';
    Params.Values['DATABASE'] := 'tsample';
  end;
end;

Calling stored procedure
For some reason to run stored procedure on Oracle requires ParamCheck attribute set as true and while running on DB2 it should be set as false. The solution is to create construct method for creating TSqlStoredProc class which has this attribute set accordingly and reuse this method accross application.

function DBTest.getSP(packName: String; procName: String): TSQLStoredProc;
var
  SP: TSQLStoredProc;
begin
  SP := TSQLStoredProc.Create(Nil);
  with SP do
  begin
    SQLConnection := Conn;
    SchemaName := '';
    PackageName := packName;
    StoredProcName := procName;
    case T of
      Occcracle:
        ParamCheck := true;
      DB2:
        ParamCheck := false;
    end;
    Params.Clear;s
  end;ddd
  result := SP;
end;

Keeping this limitation in mind there is  no problem with running PL/SQL stored procedure on DB2 database the same way as on Oracle. IN/OUT parameters are working as expected. Also stored PL/SQL procedure outside package can be called by omitting PackagName atrribute.
Example

procedure DBTest.addMessageToPackageLog(mess: String);
var
  SP: TSQLStoredProc;
begin
  SP := getSP('A_TESTLOG', 'ADD_MESSAGE');
  With SP do
  begin
    Params.CreateParam(TFieldType.ftString, 'MESS', TParamType.ptInput);
    ParamByName('MESS').AsString := mess;
    ExecProc;
  end;

PL/SQL UDF function
Any PL/SQL UDF function can be used as a part of SQL statement in DB2 also.
UDF Example

CREATE OR REPLACE FUNCTION ORACLE_FUNC 
RETURN INTEGER
AS
VARCOUNT INTEGER := 123;
BEGIN
    RETURN VARCOUNT;
END;

Statement

procedure TestDBTest.Testfunc;
var   Q: TSQLQuery;
  res : integer;
begin
  FDBTest.connect;
  { important to add ORACLE_FUNC alias, DB2 returns column number }
  Q := FDBTest.getQ('SELECT ORACLE_FUNC AS ORACLE_FUNC FROM DUAL');
  with Q do begin
    Open;
    res := FieldByName('ORACLE_FUNC').AsInteger;
    CheckEquals(123,res);
  end;

  FDBTest.disconnect;
end;

One has to remember that column name in this type of  a statement is different in DB2 and Oracle. So to keep the same statement text for both platforms it is necessary to use alias for column name.

NUMBER data type
To use NUMBER data type in DB2 DBExpress client it is necessary to update db2ini.cli file and set MapDecimalFloatDescribe parameter.
Example:

[TSAMPLE]
MapDecimalFloatDescribe = 3
DBALIAS=TSAMPLE
PWD=db2inst1
UID=db2inst1

It will map DECFLOAT datatype (NUMBER) to float data type in Delphi. One has to remember about the risk related to the loss of precision.
For some reason DBExpress requires for NUMBER stored procedure parameter ftBCD for Oracle and ftFloat for DB2. It can be resolved by introducing a function:

function DBTest.getBCDType : TFieldType;
begin
  case T of
  Oracle : result := ftBCD;
  DB2: result := ftFloat;
  end;
end;

After that call sequence can look like:

function DBTest.getNumberOfMessage: integer;
var
  SP: TSQLStoredProc;
begin
  SP := getSP('A_TESTLOG', 'GET_NUM');
  With SP do
  begin
    Params.CreateParam(getBCDType, 'NUM', TParamType.ptOutput);
    ExecProc;
    result := ParamByName('NUM').AsInteger;
  end;
end;

NUMBER datatype in different forms (NUMBER, NUMBER(10), NUMBER(14,4)) works as expected.
DATE data type
DATE data type behaves differently in DB2 and Oracle. In DB2 it keeps year, month and day, in Oracle also hours, minute and millisecond. But in Oracle Compatibility Mode the DATE behaves the same way as in Oracle.
ftCursor
The stored procedure:

CREATE OR REPLACE PROCEDURE PROC_CURSOR (VARRESULT OUT SYS_REFCURSOR)
AS
STMT VARCHAR2(2000);
BEGIN
  A_TESTNUM.PREPARE_DATA; 
  OPEN VARRESULT FOR SELECT * FROM TABLE_NUM;
END;

This stored procedure can be deployed and used in DB2 without any problems. But DBExpress DB2 client does not support ftCursor output parameter although it supports returning cursor from native SQL/PL stored procedure. Unfortunately, there is no way to transform PL/SQL cursor to returning cursor in DB2 SQL/PL- even by means of C++/Java/SQL stored procedure just to keep original PL/SQL procedure untouched.
In order to avoid code duplicating (two versions of the cursor stored procedure) small refactoring is necessary in the server and client code to minimize code branching.
Server code:

CREATE OR REPLACE PROCEDURE PROC_CURSOR (VARRESULT OUT SYS_REFCURSOR)
AS
STMT VARCHAR2(2000);
BEGIN
  PREPARE_PROC_CURSOR(STMT);
  OPEN VARRESULT FOR STMT;
END;
@

CREATE OR REPLACE PROCEDURE PREPARE_PROC_CURSOR (STMT OUT VARCHAR) AS
BEGIN
  A_TESTNUM.PREPARE_DATA; 
  STMT :=  'SELECT * FROM TABLE_NUM';
END; 
@

CREATE OR REPLACE PROCEDURE INSERT_NUMS(NUM1 IN A_TESTNUM.N1%TYPE, NUM2 IN A_TESTNUM.N2%TYPE,NUM3 IN A_TESTNUM.N3%TYPE) AS
  BEGIN
    A_TESTNUM.PA_INSERT_NUMS(NUM1,NUM2,NUM3); 
  END INSERT_NUMS;
@

CREATE PROCEDURE DB2_PROC_CURSOR ()
        DYNAMIC RESULT SETS 1
P1: BEGIN
        -- Declare cursor
        DECLARE STMTA VARCHAR2(2000);
        BEGIN
            CALL PREPARE_PROC_CURSOR(STMTA);
        PREPARE STMT_P FROM STMTA;
    END;        
    BEGIN
          DECLARE cursor1 CURSOR WITH RETURN for STMT_P;
          OPEN cursor1;
        END;
END P1

Assuming that the most important logic is hidden in preparing statement text this part of the code can be reused. Only small part responsible for creating a cursor is database dependent. But this parts are pure mechanical ones and there is nothing special there.
Also calling sequence at the application side should be modified.

function TestDBTest.getCursorSP: TSQLStoredProc;
var
  SP: TSQLStoredProc;
begin
  case FDBTest.getT of
    Oracle:
      begin
        SP := FDBTest.getSP('PROC_CURSOR');
        SP.Params.CreateParam(TFieldType.ftCursor, 'VARRESULT',
          TParamType.ptOutput);
      end;
    db2:
      SP := FDBTest.getSP('DB2_PROC_CURSOR');
  end;
  result := SP;
end;

It is bad news. But good news is that the rest of the code (Open, Next, Eof) is exactly the same for both platforms.
Devart DBExpress
Devart DBExpress client for Oracle client (purchasable) covers much more functionality than the standard DBExpress client delivered with Delphi. For instance BOOLEAN datatype parameters in stored procedure are supported. Also it is possible to call UDF directly (not as a part of SELECT statement) and retrieve the result. This functionality is not supported by DB2 DBExpress client.  Migrating application heavily dependent on this additional functionality requires much more effort.
Conclusion
Although one cannot say that migrating Delphi application from Oracle to DB2 is a piece of cake it is astonishing that so much can be achieved even the if DBExpress client for DB2 is not Oracle Compatibility Mode enabled. What is more important - ISV can maintain the same code for both platforms just to cover both market shares.
The Grande Finale
DB2 and Oracle