DB2 to Oracle maps
- 1 Function mappings
- 2 Other Stuff
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 YEAR(), MONTH(), MINUTE(), MICROSECOND(), etc. functions to extract parts of a datetime. Oracle has similar functions
EXTRACT(YEAR FROM [date]) EXTRACT(HOUR FROM [timestamp])
- Note: the above EXTRACT functions will not work in PeopleTools, however. See PSQuery Tips and Tricks for details.
In DB2, you could use the DAYOFWEEK() and DAYOFWEEK_ISO() functions to determine on what day of the week (M, T, W, etc.) a certain date fell. DAYOFWEEK returns 1 for Sunday, 2 for Monday, ..., 7 for Saturday; while DAYOFWEEK_ISO returns 1 for Monday, ..., 7 for Sunday. In Oracle, to get a numerical representation of the day of the week under the USA standard (week beginning on Sunday), use
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.
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.
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()
UCASE to UPPER
Replace the UCASE() function with the UPPER() function.
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