DB2 → Oracle Migration Issues

When migrating resources from a mainframe system to an open system, you may need to modify the application program source code to account for the switchover from a DB2 DBMS to an Oracle DBMS.

This appendix describes some code modification issues arising during a database migration from DB2 to Oracle.

Modifications that must be made to applications for DBMS migration are also covered in the manuals provided by the individual database suppliers.

As a DB2 database is migrated to Oracle, applications are modified as follows:

  • OPTIMIZE … FOR FETCH

    • Type

      OPTIMIZE FOR 1 ROWS FOR FETCH ONLY

    • Change: Comment out

  • SET CURRENT PACKAGE

    • Type

      EXEC SQL
   SET CURRENT PACKAGE = “Character”
END-EXEC.

    • Change: Comment out

  • SET :[Host Variable] = CURRENT TIMESTAMP

    • Type

      EXEC SQL
   SET :[Host Variable] = CURRENT TIMESTAMP
END-EXEC.

    • Change: Comment out and add query

      EXEC SQL
   SELECT TO_CHAR(SYSTIMESTAMP,’YYYY.MM.DD.HH24.MI.SS.FF6’)
   INTO :[Host Variable]
   FROM DUAL
END-EXEC.

  • CURRENT TIMESTAMP

    • Type

      ,[DB Column] = CURRENT TIMESTAMP

    • Change

      ,[DB Column] = SYSTIMESTAMP

  • EVALUATE

    • Type

      EVALUATE
   WHEN [condition]…
END-EVALUATE.

    • Change

      EVALUATE TRUE
   WHEN [condition]…
END-EVALUATE.

      When no condition is specified in the EVALUATE statement, MF-Cobol throws an error. To avoid this, set condition to TRUE when there is no condition.

  • SQLSTATE Define

    • Type

      EXEC SQL
   BEGIN DECLARE SECTION
END-EXEC.

    • If this type exists, insert the SQLSTATE variable in the middle.

      If this type does not exist, declare a SQLSTATE variable.

      EXEC SQL
   BEGIN DECLARE SECTION
END-EXEC.

01 SQLSTATE PIC X(5).

EXEC SQL
   END DECLARE SECTION
END-EXEC

  • SQLCODE Conversion

    • Case 1

      • Type

        IF SQLCODE = -811

      • Change

        IF SQLCODE = -2112

    • Case 2

      • Type

        IF SQLCODE = -803

      • Change

        IF SQLCODE = -1

    • Case 3

      • Type

        WHEN -811 PERFORM [Section Name]

      • Change

        WHEN -2112 PERFORM [Section Name]

    • Case 4

      • Type

        WHEN -803 PERFORM [Section Name]

      • Change

        WHEN -1 PERFORM [Section Name]

        [Note]

        ORA 02112, 00000, "PCC: SELECT..INTO returns too many rows"
ORA 00001, 00000, "unique constraint (%s.%s) violated"

  • SET :[Host Variable] = CURRENT DATE

    • Type

      EXEC SQL
   SET :[Host Variable] = CURRENT DATE
END-EXEC.

    • Change

      EXEC SQL
   SELECT TO_CHAR(SYSDATE,’YYYY-MM-DD’)
   INTO :[Host Variable] FROM DUAL
END-EXEC.

  • CURRENT DATE

    • Type

      [DB Column] = CURRENT DATE

    • Change

      [DB Column] = TO_CHAR(SYSDATE,’YYYY-MM-DD’)

  • SET :[Host Variable] = CURRENT TIME

    • Type

      EXEC SQL
   SET :[Host Variable] = CURRENT TIME
END-EXEC.

    • Change

      EXEC SQL
   SELECT TO_CHAR(SYSDATE,’HH24.MI.SS’)
   INTO :[Host Variable] FROM DUAL
END-EXEC.

  • VALUE Function

    • Case 1

      • Type

        SELECT VALUE(MAX([DB Column],’’)/VALUE(MIN([DB Column]),’’)

      • Change

        SELECT NVL(MAX([DB Column]),’’)/NVL(MIN([DB Column]),’’)

    • Case 2

      • Type

        SELECT VALUE(SUM([DB Column]),0)

      • Change

        SELECT NVL(SUM([DB Column]),0)

  • DECIMAL Function

    • Case 1

      • Type

        SELECT VALUE(SUM([DB Column]),DECIMAL(0,15,0))

      • Change

        SELECT NVL(SUM([DB Column]),0)

    • Case 2

      • Type

        SELECT VALUE(DECIMAL(0,15,0),SUM([DB Column]))

      • Change

        SELECT NVL(0,SUM([DB Column]))

    • Case 3

      • Type

        SELECT VALUE(MAX([DB Column]),DECIMAL(‘’,15,‘’))

      • Change

        SELECT NVL(MAX([DB Column]),’’)

  • INCLUDE SQLCA

    If the SQLCABC, SQLERRML or SQLERRD output methods are used when INCLUDE SQLCA is not specified, the following statement must be inserted in the WORKING-STORAGE SECTION:

    EXEC SQL INCLUDE SQLCA END-EXEC.

  • SQLCODE DISPLAY

    When performing a DEBUG MODE compile, insert a DISPLAY statement in the SQL statement of the source to compile.

    DISPLAY ‘[SOURCE NAME]:LINE:[LINE NO]:[I/U/D/S]:SQLCODE:[SQLCODE]’.
    Parameter Description

    SOURCE NAME

    Name of the source file to compile in DEBUG MODE.

    LINE NO

    Line number to insert the DISPLAY statement.

    I/U/D/S

    INSERT/UPDATE/DELETE/SELECT.

    SQLCODE

    Result code.