DB2 to Oracle maps
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 ...
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.
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
In DB2, you could use the
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 TO SUBSTR
In Oracle, change the LEFT function to the SUBSTR function. Give it a "1" as the second parameter so it mimics the LEFT function.
SUBSTR(C.POSTAL, 1, 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
SYSDUMMY1 to DUAL
SELECT ... FROM SYSIBM.SYSDUMMY1
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.
We used WITH UR on SQL SELECT statements do eliminate much of the locking done by DB2.
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.
Some queries in DB2 may have the JULIAN_DAY function. This is not necessary in Oracle and needs to be removed.
JULIAN_DAY(B.TERM_BEGIN_DT) - JULIAN_DAY(A.GRADUATION_DT)
B.TERM_BEGIN_DT - A.GRADUATION_DT