Accelerating migration from Oracle database-based Pro*C applications to DB2 embedded SQL C

As part of the continuous enhancements happening with respect to Oracle compatibility in DB2®, the number of Oracle Pro*C features supported in DB2 has been on the rise since it began with DB2 Linux®, UNIX®, and Windows® (LUW) 9.7 fixpack 5. With the list of features growing in DB2 10.1, migrating an existing Pro*c application is a challenge. The tips and techniques in this article can help speed up the Pro*c migration. All of these are based on real-time migration projects.

Gaurav Shukla (gashukla@in.ibm.com), DB2 Consultant, IBM

Gaurav Shukla is a DB2 consultant at IBM India Software labs. He is involved in many end-to-end Oracle to DB2 migration engagements, including lot of Pro*C to DB2 embedded SQL SQC projects. Earlier, he was part of the Optim pureQuery (JDBC Client Optimizer) development team.



13 September 2012

Also available in Chinese

Introduction to embedded SQL on DB2 LUW

Embedded SQL is a way of executing SQL statements embedded within a host language application. The embedded SQL database applications, first get a connection to the database and then execute these embedded SQLs. These SQLs could be executed dynamically or statically. Most commonly used host languages are C and C++. Building embedded SQL applications involves two primary steps before the application can be compiled: PREP (or PRECOMPILE) and BIND.

The PREP command is used to read the source code, parse, and convert the embedded SQL into DB2 runtime services API calls and write the output into a new source file. BIND is used to bind the SQL statements in the application to the target database. By default, the binding is one at the PRECOMPILE step itself, but could be deferred as a separate step as well.

Once the binding is complete, the access plans for the SQL statements are generated and stored in a package within the target database. Once the PRECOMPILE and BIND steps are done, the application is ready to be compiled and linked using the host language-specific development tools. The syntax of these steps for embedded SQL support is discussed later. Both 32-bit and 64-bit embedded SQL applications could be developed from embedded SQL source code. In Oracle databases, C/C++-based embedded SQL database application support is known as Pro*C/C++; in DB2 LUW, it is embedded SQL C/C++.

In this article, you will learn some major differences between Embedded SQL C/C++ and Pro*C/C++, but the focus is on tips and techniques for migrating Pro*C/C++-based applications from Oracle to DB2 LUW. Please check the latest DB2 LUW documentation for currently supported features.


Oracle compatibility support on DB2

Usually, the migration of an application from one RDBMS to another RDBMS remains a challenge. However, starting with DB2 LUW 9.7 fixpack 5 onward, new SQL and PL/SQL capabilities were added, which accelerates the migration from an Oracle database to DB2. These capabilities were further enhanced in the latest release of DB2 LUW (DB2 LUW 10.1).

The support for these capabilities is native. DB2 provides native support for the data types, scalar functions, packages, language elements, built-in packages, and the PL/SQL procedural language. With native support, these capabilities are supported in the DB2 engine with the same level of integrity and efficiency as any other DB2 native language element. Hence, they perform with the natural speed and efficiency DB2 offers. Oracle compatibility features eliminate the need to convert most Oracle database objects, including PL/SQLs, and ease the task of migrating the application written for Oracle to DB2.

For enabling one or more DB2 compatibility features, we need to use the DB2_COMPATIBILITY_VECTOR registry variable. It is represented as a hexadecimal value where each bit in the variable corresponds to a DB2 compatibility feature. Using this vector, you can pick and choose the specific features to be enabled (by setting specific bits of the DB2_COMPATIBILITY_VECTOR or to enable all the features, set this registry variable to ORA. This can be set via db2set, and the recommended practice is to set this variable before database creation and keeping the selected compatibility level for the life of the database. A more detailed discussion on this is beyond the scope of this article (see DB2_COMPATIBILITY_VECTOR registry variable for more information).


Pro*C/C++ support

As mentioned, DB2 LUW provides many compatibility features that facilitate migration of Pro*C/C++-based applications from Oracle to embedded SQL C/C++ in DB2. These compatibility features could be enabled by setting the PRECOMPILE option COMPATIBILITY_MODE to ORA. For example, for a file named myapp1.sqc, we enable it by running the following command: db2 PRECOMPILE myapp1.sqc BINDFILE COMPATIBILITY_MODE ORA. For more information about enabling compatibility for migration, see Enabling compatibility features for migration.


Major difference between Oracle Pro*C/C++ and embedded SQL C/C++

Let's examine some major differences between Oracle Pro*C/C++ and embedded SQL C/C++:

  • Static package support in DB2: In DB2 LUW, the SQL statement could be executed in two ways: static and dynamic. For static execution, the statement syntax is known at the PRECOMPILE time (column names and tables referenced in the SQL statement must be known at PRECOMPILE time). PRECOMPILE produces the access plan for the SQL statements and stores it in the packages defined in the database. Oracle Pro*C/C++ statements do not have the capability of the static execution mentioned above. Oracle Pro*C/C++ supports only dynamic SQL statement execution. Dynamically executed SQL statements are built/prepared and executed at runtime. Since the performance of static execution is better than dynamic execution, embedded SQL applications in DB2 LUW perform well.
  • Package maintenance: Since packages created for static execution are stored at the database, any changes in the embedded SQL statements require re-binding the packages. This is only required for static SQL execution and not for a dynamic SQL execution. Also, if the database object reference in the SQL statements stored in the package is altered, the package needs to be re-bound.
  • Database connection required during PRECOMPILE phase: During the PRECOMPILE phase, database connection is required.

Sample Pro*C/C++ to embedded SQL C/C++ migration

NOTE: Examples included here are mostly code snippets and cannot be compiled as stand-alone samples. We are using DB2 LUW 10.1.

  1. Prerequisites:
  2. Migration:
    • Rename the Pro*C/C++ application program to embedded SQL program extension (e.g., for Pro*C code, change the file extension from *.pc to *.sqc, like mv myappl.pc myappl.sqc.
    • Connect to the database using the command
      db2 connect to sample user newton  using password

      .
    • PRECOMPILE the embedded SQL program (specifying bindfile would create bindfile (myapp1.bnd) locally needs to be manually added to the database as a next step: db2 prep myapp1.sqc bindfile COMPATIBILITY_MODE ORA).
    • In case the PRECOMPILE phase reports errors, fix those before proceeding. If PRECOMPILE is successful, a .c file should be generated (myapp1.c) along with the bind file.
    • Now bind the generated bindfile to the database used by the application (it should already be connected as a result of step 2 above).
    • Compile the generated .c program using embedded SQL C-supported compilers (for example, on Linux®, the compilation command would look something like this:
      gcc -o myapp1.exe -I${HOME}/sqllib/include -L${HOME}/sqllib/lib
          -ldb2 myapp1.c
    • Specify proper include and library path above.
    • Terminate the DB2 connection with db2 terminate.
    • The application binary is now ready to use: ./myapp1.exe [argument1, argument2,].

Common real-time scenarios and their solutions

  • Maintaining common code base between DB2 and Oracle: There is often a need to maintain a common code base between Oracle and DB2 LUW. With the addition of Oracle compatibility support in DB2, this has become simple. However, there is a chance that similar support of any function might not be available on DB2. In such a case, make use of the conditional #ifdef pre-processor constructs.
    Listing 1. Sample code for conditional #ifdef pre-processor construct
    for (;;) { 
                printf("fetch\n"); 
                EXEC SQL FETCH cur INTO :c1, :c2;
    #ifdef ORA 
                if (sqlca.sqlcode == 1403) { 
    #elif DB2 
                if (sqlca.sqlcode == 100) { 
    #endif 
                    break; 
                } 
            printf("%d %s\n", c1, c2); 
            } EXEC SQL CLOSE cur; 
    #ifdef ORA
            EXEC SQL COMMIT WORK RELEASE; 
    #elif DB2 
            EXEC SQL COMMIT; 
            EXEC SQL CONNECT RESET; 
    #endif

    Once all such changes are done, PRECOMPILE the program like this (assuming the sample code is named sample_ifdef.sqc):

    db2 "prep sample_ifdef.sqc bindfile package using sample_ifdef preprocessor 
    \"cl /P /DDB2\" COMPATIBILITY_MODE ORA"

    In the case of AIX (xlC), cl /P /DDB2 is replaced with xlC -P -DDB2=1. Please check the compiler option as they could be platform-specific. The P option usually means Preprocessor.

  • Using C macros: If the application uses C macros (#defines), use the following.
    Listing 2. Sample code for application using C macros
    #define _MYSHORT_  short
    #define MAX_LENGTH (128)
    ...
    int main (...)
    {
        EXEC SQL BEGIN DECLARE SECTION
             _MYSHORT_ c1_empno;
             char      c2_empname[MAX_LENGTH+1];
        EXEC SQL END DECLARE SECTION
        ...
    }

    DB2's PRECOMPILE supports the PREPROCESSOR option, which allows the embedded SQL program to parse through C compiler's pre-processor to expand the macros and then invoke embedded SQL parser to process EXEC SQLs. The example below illustrates (on Linux).

    db2 connect to sample
    db2 prep myapp1.sqc bindfile PREPROCESSOR 
    "'gcc -E -o myapp1.i -I${HOME}/sqllib/include'"
    db2 bind macro.bnd
    gcc -o macro -I${HOME}/sqllib/include -L${HOME}/sqllib/lib -ldb2 macro.c
    db2 terminate
  • If the application uses a compound SQL, try the code below.
    Listing 3. Sample code for application using compound SQL
    execute
    DECLARE
            t_x VARCHAR2(10);
        ……
        BEGIN
        SELECT….  INTO t_x  FROM…… ;
        Update ….  WHERE;
        ………..
        commit;
        end;
    end-exec;

    In DB2 embedded SQL C/C++, convert these statements into dynamic SQL, like this:

    strcpy (plsql, "declare ... begin ... end;");
    EXEC SQL PREPARE stmt FROM :plsql;
    EXEC SQL EXECUTE stmt into :xxx, :xxx using :xxx, :xxx;
  • DB2CI support (similar to Oracle's OCI): B2 supports DB2CI, which is similar to Oracle's OCI. Due to this, the majority of the OCI would run as-is on DB2. However, there is a major difference with respect to OCI support. Unlike Oracle, OCI and embedded SQL can't be in the single source file. As a solution, translate the entire source file of the code into DB2CI or embedded SQL C/C++. For more information, see DB2CI application development.
  • Using DESCRIPTOR: If the application is using DESCRIPTORs like this:
    EXEC SQL DECLARE select_stmt  statement
    EXEC SQL ALLOCATE DESCRIPTOR 'out'; 
    EXEC SQL ALLOCATE DESCRIPTOR 'in';

    Then in DB2 embedded SQL C/C++, use the DESCRIBE INPUT/DESCRIBE OUTPUT statements. See DESCRIBE INPUT statement and DESCRIBE OUTPUT statement for more information.

    Similarly, if the application is using DESCRIPTOR syntax: EXEC SQL FETCH select_cursor INTO DESCRIPTOR 'out'. Refer to the USING DESCRIPTORS syntax: FETCH statement.

  • Using context in multi-threaded applications: For multi-threaded applications, with embedded SQL context support, applications can execute SQL statements from multiple threads. A context could be defined as an environment from where application runs all SQL statements and API calls. All connections, units of work, and other database resources are associated with a specific context. So if the application is using context in Pro*C/C++, like this: EXEC SQL CONTEXT USE :my_context; , use context APIs as mentioned in Concurrent transactions and multi-threaded database access in embedded SQL applications.
  • There were lot of Oracle Pro*C/C++ compatibility features added as part of DB2 10.1, including:
    • C-array host variables
    • INDICATOR variable arrays
    • New CONNECT statement syntax
    • Double quotation marks to specify include file names
    • Simple type definition for the VARCHAR type
    • The STATICASDYNAMIC string for the GENERIC option on the BIND command
    • Using string literals with the PREPARE statement
    • The BREAK action in the WHENEVER statement

    See Enabling compatibility features for migration for details.


Common mistakes

  • SQL0805N Package "XXX.DUMMY 0X514175474B644C62" was not found (SQLSTATE=51002): DB2 Embedded SQL needs to bind the application as package in the database server where the application needs to connect during execution runtime. This error presents if DB2 BIND has not been run on the database server. Also, while performing BIND, ensure that the correct database is connected so the package is created in the intended database only.
  • SQL0818N A timestamp conflict occurred. SQLSTATE=51003: Usually occurs when application is recompiled but the bindfile is not refreshed. Perform DB2 BIND using the latest bindfile.

Restrictions


Summary

After following along with this article, you:

  • Know how to proceed on Pro*C/C++ to embedded SQL C/C++ migration
  • Have a better understanding of Pro*C/C++ features supported in DB2
  • Can speed up application migration using the tips and techniques as shared
  • Could try driving your own migrations.

Resources

Learn

Get products and technologies

  • Build your next development project with IBM trial software, available for download directly from developerWorks.
  • Now you can use DB2 for free. Download DB2 Express-C, a no-charge version of DB2 Express Edition for the community that offers the same core data features as DB2 Express Edition and provides a solid base to build and deploy applications.

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=834593
ArticleTitle=Accelerating migration from Oracle database-based Pro*C applications to DB2 embedded SQL C
publish-date=09132012