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;
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.