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;

More Information

About SQLines

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

Discussion

, June 12, 2013 3:05 am

what about RS and RR?

You could leave a comment if you were logged in.