Difference between revisions of "DB2 to Oracle maps"

From The University of Akron Support Wiki
Jump to: navigation, search
(DATE FUNCTIONS / DATE MATH)
 
(5 intermediate revisions by 3 users not shown)
Line 66: Line 66:
  
  
In DB2, you could use the
+
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
 +
TO_CHAR([date], D)
 +
 
 
=== CURRENT DATE ===
 
=== CURRENT DATE ===
 
Convert the special register CURRENT DATE (in DB2) to CURRENT_DATE or SYSDATE (in Oracle).  
 
Convert the special register CURRENT DATE (in DB2) to CURRENT_DATE or SYSDATE (in Oracle).  
Line 79: Line 88:
 
  SELECT CURRENT_TIMESTAMP FROM DUAL;
 
  SELECT CURRENT_TIMESTAMP FROM DUAL;
  
=== LEFT TO SUBSTR ===
+
=== LEFT/RIGHT TO SUBSTR ===
In Oracle, change the LEFT function to the SUBSTR function.
+
In Oracle, change the LEFT and RIGHT functions to the SUBSTR function.
Give it a "1" as the second parameter so it mimics the LEFT function.
+
 
 +
When switching from LEFT, give SUBSTR a "1" as the second parameter so it mimics the LEFT function.
  
 
For example:
 
For example:
Line 87: Line 97:
 
becomes
 
becomes
 
  SUBSTR(C.POSTAL, 1, 5)
 
  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 ===
 
=== REPLACE vs. DECODE or CASE ===
Line 95: Line 112:
 
Alternatively, one could use a CASE expression, like:
 
Alternatively, one could use a CASE expression, like:
 
  CASE K.PHONE_TYPE WHEN 'FAX' THEN 'ZZZZ' WHEN 'CELL' THEN 'AAAA'.... END
 
  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.
  
 
== Other Stuff ==
 
== Other Stuff ==

Latest revision as of 14:57, 16 October 2008

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])
  • 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

TO_CHAR([date], D)

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()

UCASE to UPPER

Replace the UCASE() function with the UPPER() function.

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