If you are interested in this topic you would have come across devWorks article posted in link http://www.ibm.com/developerworks/data/library/techarticle/dm-1209oracletodb2/index.html?ca=drs trying to help customer with migration tips for Pro*C to DB2 embedded SQL C applications. There was actually one more article before that but let us not get that back as things become obsolete.
I just wanted to share the extra information that I have to ease the migration and also to tell about new capabilities of DB2 embedded in the light of current subject (there are more than that ofcourse. I will try to break the topic in two parts for ease in understanding and also for you to be able to make use of devWorks article mentioned above:
A) Updation to above devwork article link "Common real-time scenarios and their solutions" section:
Starting in from DB2 V10.1 Fix Pack 2 onwards, when compatibility mode is switched on, the following features are supported: EXEC SQL COMMIT WORK RELEASE; EXEC SQL ROLLBACK WORK RELEASE;
B) Extending to devwork here with to provide more tips for easing the migration from Pro*C to DB2 Embedded SQL C applications:
- Difference in file extensions: '.pc' is the extension for Pro*C code and '.sqc' is file extension for DB2 Embedded SQL C applications. Customers or migration specialist can modify the file extension. One can write a simple script to do that.
- Header file inclusion: Pro*C can have include header files in environment variable "include" and as a parallel in DB2 embedded SQL C, you can use DB2INCLUDE environment variable.
- Difference in SQLCA structure: The SQLCA is an SQL communication area structure used by the database manager to return error and other information to an embedded application program. This structure contains status of every API call and SQL statement issued. There is a structural difference between Oracle and DB2 though, with respect to text associated with the error code. In simpler terms oracle returns error text in sqlca.sqlerrm.sqlerrml and sqlca.sqlerrm.sqlerrmc but when it comes to DB2 error text is returned in sqlca.sqlerrml and sqlca.sqlerrmc.
- Syntax difference EXEC SQL INCLUDE statement: This syntax is supported in DB2 as well, but has some technical differences; without quote header is searched with .h extension, when .h is already present. IN DB2, customers or migration specialist can change "EXEC SQL INCLUDE sample.h;" to "EXEC SQL INCLUDE "sample.h"".
- Difference in Select statement syntax with INTO clause: Pro*C can ignore INTO clause in a select statement but DB2 is strict with INTO clause. So syntax change needed to be done as per DB2 syntax.
- Late validation of database objects: For non-existent table, like temp created during shell script, you can write SQL and Oracle will validate it at run time but DB2 gives error as it strictly checks for the same. To overcome this problem during PRECOMPILE of embedded SQL application users can convert the error into warning if they use PRECOMPILE option "VALIDATE RUN".
- Statement execution for specified connection: EXEC SQL AT syntax and statement level connection setting is not supported in DB2 but you can use DB2 ESQL SET CONNECTION syntax for a block of statements. In DB2 SET CONNECTION statement changes the state of a connection from dormant to current. Multiple connections to the same database can be done in DB2 embedded SQL application as well but you must use multi-threading, which entails using DB2 context management APIs to be thread-safe.
- CURSOR and PREPARE statement names uniqueness: In Pro*C application you can reuse same cursor or prepare statement name multiple times but DB2 embedded SQL application is strict and gives error The cursor "C1" is already defined. Eg: EXEC SQL DECLARE C1 CURSOR FOR sql_stmt; EXEC SQL DECLARE C1 CURSOR FOR sql_stmt;
Application developer is needed to use unique name for PREPARE/CURSOR declarations in DB2 embedded SQL applications.
There might be more differences like this which are needed to be considered but these are the difference which I came across until now and thought of sharing. Hope this blog will help you. Happy migration!!! For any question please comment in this blog.