WITH UR, CS, RS, RR - Isolation Level - IBM DB2 to Oracle Migration

In DB2, you can specify the isolation level clause in a SELECT statement:

DB2:

  -- Uncommitted read (mostly used to avoid read locks)
  SELECT name FROM cities WITH UR;
 
  -- Cursor stability (read committed)
  SELECT name FROM cities WITH CS;

Oracle does not allow reading uncommitted rows and WITH UR clause should be removed from the SELECT statement. Often it is not a problem as WITH UR is mostly used to prevent any read locks in DB2, while in Oracle readers are never blocked anyway.

WITH CS means to read committed rows, and this is the default in Oracle, so this clause can be also removed.

Oracle:

  -- Uncommitted read not possible, but reader never locked anyway
  SELECT name FROM cities;
 
  -- Read committed
  SELECT name FROM cities;

• IBM DB2 to Oracle Migration Tools and Reference

SQLines offers services and tools to help you migrate databases and applications. For more information, please contact us at support@sqlines.com.