Appendix B. 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.

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.
```
  Note
  
  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.

Prev		Next
Appendix A. EBCDIC → ASCII Conversion Issues	Home	Appendix C. Schema File Structure

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.