DB2 to Oracle maps

PSV9 > Oracle Knowledge Base

Function mappings

STRIP to TRIM

The DB2 Function STRIP needs converted to the Oracle Function TRIM:

STRIP(column_name, L, ‘ ‘ ) to TRIM(LEADING ‘ ‘ from column_name) – trim leading blanks from the column
STRIP(column_name, T, ‘ ‘ ) to TRIM(TRAILING ‘ ‘ from column_name) – trim trailing blanks from the column
STRIP(column_name, B, ‘ ‘ ) to TRIM(BOTH ‘ ‘ from column_name) – trim both leading and trailing blanks from the column

VALUE to COALESCE

Convert the VALUE function to COALESCE (just change VALUE to COALESCE).

SELECT 
NMS.LAST_NAME,
NMS.FIRST_NAME, 
substr(NMS.MIDDLE_NAME,1),   
PDT.SEX,
PDT.BIRTHDATE,
VALUE(DIV.ETHNIC_GROUP,'U'), 
VALUE(EMA.EMAIL_ADDR,' '),   

PDT.FERPA
  
FROM PS_NAMES NMS
...

becomes

SELECT 
NMS.LAST_NAME,
NMS.FIRST_NAME, 
substr(NMS.MIDDLE_NAME,1),   
PDT.SEX,
PDT.BIRTHDATE,
COALESCE(DIV.ETHNIC_GROUP,'U'), 
COALESCE(EMA.EMAIL_ADDR,' '),   

PDT.FERPA
  
FROM PS_NAMES NMS
...

INT/INTEGER to TO_NUMBER

Use of column function INTEGER or INT in DB2 should be converted to TO_NUMBER in Oracle.

TRANSLATE DIFFERENCES

Use of column function TRANSLATE(column name, to-string, from-string, pad-character) in DB2 is a little different in Oracle .

In Oracle, the to-string ('100') and the from-string ('YN ') is swapped.

TRANSLATE(column name,'100', 'YN ') in DB2 will take the value in column name and convert 'YN ' to '100'

TRANSLATE(column name,'YN ', '100') in Oracle will take the value in column name and convert 'YN ' to '100'.

POSSTR to INSTR

Replace the DB2 POSSTR function with INSTR

SELECT INSTR(string, substring, position, occurrence)

DATE FUNCTIONS / DATE MATH

In DB2, you could use the DATE() function to get the date portion of a datetime field. In Oracle, you can use the TRUNC() function to do this

SELECT TRUNC(datetime)

In DB2, you could use the YEAR(), MONTH(), MINUTE(), MICROSECOND(), etc. functions to extract parts of a datetime. Oracle has similar functions

EXTRACT(YEAR FROM [date])
EXTRACT(HOUR FROM [timestamp])

CURRENT DATE

Convert the special register CURRENT DATE (in DB2) to CURRENT_DATE or SYSDATE (in Oracle).

SELECT CURRENT_DATE FROM DUAL;
SELECT SYSDATE FROM DUAL;

Also: CURRENT TIME to CURRENT_TIME

SELECT CURRENT_TIME FROM DUAL;

CURRENT TIMESTAMP to CURRENT_TIMESTAMP

SELECT CURRENT_TIMESTAMP FROM DUAL;

LEFT/RIGHT TO SUBSTR

In Oracle, change the LEFT and RIGHT functions to the SUBSTR function.

When switching from LEFT, give SUBSTR a "1" as the second parameter so it mimics the LEFT function.

For example:

LEFT(C.POSTAL, 5)

becomes

SUBSTR(C.POSTAL, 1, 5)

Give SUBSTR a negative number as the second parameter when replacing the RIGHT function. This will cause it to count back from the end of the string.

For example:

RIGHT(C.POSTAL, 5)

becomes

SUBSTR(C.POSTAL, -5, 5)

REPLACE vs. DECODE or CASE

Multiple calls to the REPLACE function, such as

REPLACE(REPLACE(REPLACE(REPLACE(K.PHONE_TYPE, 'FAX ', 'ZZZZ'), 'CELL', 'AAAA'), 'MAIL', 'AAAB'), 'HOME', 'AAAC')

will still work but can be re-rwritten using DECODE

DECODE(K.PHONE_TYPE, 'FAX ', 'ZZZZ', 'CELL', 'AAAA', 'MAIL', 'AAAB', 'HOME', 'AAAC')

Alternatively, one could use a CASE expression, like:

CASE K.PHONE_TYPE WHEN 'FAX' THEN 'ZZZZ' WHEN 'CELL' THEN 'AAAA'.... END

CHAR/INTEGER to TO_CHAR/TO_NUMBER

Replace CHAR() with TO_CHAR() and INTEGER() with TO_NUMBER()

Other Stuff

SYSDUMMY1 to DUAL

Convert your

SELECT ... FROM SYSIBM.SYSDUMMY1 

to

SELECT ... FROM DUAL.

DUAL in Oracle is like SYSDUMMY1 in DB2; it contains one row and is a simple way in SQL to gather information from the database special registers such as dates/times.

WITH UR

We used WITH UR on SQL SELECT statements do eliminate much of the locking done by DB2.

Tony says

In Oracle, locking is done much differently and for now, I do not foresee an issue with 
locking that would require any special processing.

Oracle will not recognize “WITH UR” so you need to remove this from your SQL statements.

JULIAN_DAY

Some queries in DB2 may have the JULIAN_DAY function. This is not necessary in Oracle and needs to be removed.

For example:

JULIAN_DAY(B.TERM_BEGIN_DT) - JULIAN_DAY(A.GRADUATION_DT)

becomes

B.TERM_BEGIN_DT - A.GRADUATION_DT