Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Db2, Oracle, SQL Server

Re: Db2, Oracle, SQL Server

From: <Kenneth>
Date: Sun, 06 Feb 2005 17:49:05 GMT
Message-ID: <>

On Sun, 6 Feb 2005 00:56:51 -0700, "Mark A" <> wrote:

>"Mark D Powell" <> wrote in message
>> The online logging is very different. In DB2 the onlne logs are not
>> circular and are used for rolling back transactions.

>In DB2 the default logging is circular when a database is created, but most
>people use log retain (non-circular) in production so that they can perform
>on-line backups and other on-line utilities. Sometimes circular logging is
>sufficient for data warehouses when a backup can be performed right after
>the data load. Log retain is used for rolling back transactions,
>roll-forward recovery, and replication.

DB2 Circular Logging                       = Oracle NOARCHIVELOG  mode
DB2 Non-circular Logging (log_retain) = Oracle ARCHIVELOG mode.

There is a difference, though, in the *way* that DB2 and Oracle archives logging when in ARCHIVELOG/Non-circular logging :

Oracle : Has a fixed set of Online redo logs, which it writes to circularly (both in ARCHIVELOG and NOARCHIVELOG). When one redo log is filled, a process (archiver) *copies* the contents to an archive log file in a predefined destination, but the online log stays in place, ready for use when Oracle has gone all the way round.

DB2 : When DB2 has filled one log file, it leaves it behind and starts to write to a completely new log file. A program (userexit) then backs up the left file by moving it to it's backup location. DB2 can be configured to preallocate N logfiles to ensure time is not wasted by allocating new files for continous processing.

There is also a difference in the number of online logs :

Oracle : The number of online logs is fixed. It *can* be extended/reduced, but that is a reconfiguration done manually and very rarely.

DB2 : If necessary, the number of online redo logs can grow. It has a preallocated pool of log files (Primay logs), but if necessary it can grow via Secondary logs.

Apart from that, DB2 stores all it's undo info in the transaction logs. It has no rollback segments/undo tablespaces as in Oracle.

In Oracle, Read consistency is a mantra. When transaction A updates a row, noone will ever be allowed to see the new values before committed. Period. You either see the old values before Transaction A or nothing.

In DB2, it's the opposite, so to speak.

You can either choose to see the new uncommitted values by specifying "uncommitted read" in your quer or nothing at all. But there is no way to see the old values.

No surprise, when Oracle developers turn to DB2 and vice versa, big confusion and surprises arise.

Received on Sun Feb 06 2005 - 11:49:05 CST

Original text of this message