Re: Gotchas while migrating from DB2 to Oracle.

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Tue, 09 Dec 2003 14:11:22 -0800
Message-ID: <1071007914.888231@yasure>

VC wrote:

> Hello,
>
>
> You wrote earlier:
>
> ----- Original Message -----
> From: "Hemant Shah" <shah_at_typhoon.xnet.com>
> .>As I have said it and Joel also
>

>> suggested the application has worked without any problems for years and

>
> over
>

>> different data access method. Oracle is different so I have to code

>
> around
>

>> that. It would be a nightmare, if I redesign application everytime I have

>
> to
>

>> port to another data access method.

>
>
>
> Unfortunately, the reality is such that Oracle is a fundamentally different
> beast from, say, DB2 with respect to transaction/concurrency mechanism.
> Oracle (as well as Postgres, Firebird/Interbase, etc) belong to a family of
> so-called multi-versioning schedulers whilst DB2, Informix, Microsoft SQL
> Server are locking schedulers. You would'nt've experienced such a shock if
> you'd ported between databases from the same 'family' since all the locking
> schedulers concurrency mechanisms are practically identical to each other.
>
> In view of the above, I do not think you have much choice short of
> re-designing the application, at least the parts relying on the locking
> transaction scheduling, if Oracle is a must. Solid knowledge of the
> concurrency mechanism for both databases is a necessary pre-condition for
> the endeavour to succeed.
>
>
> As to the web pages, this might be useful:
> http://otn.oracle.com/tech/migration/workbench/index.html
>
> Good luck.
>
> Rgds.
>
> "Hemant Shah" <shah_at_typhoon.xnet.com> wrote in message
> news:br590p$12d$1_at_flood.xnet.com...
>

>>Folks,
>>
>>  I had posted article about changing isolation level in Oracle. That

>
> thread
>

>>  was pretty lively. It seems Oracle will not block readers, and that
>>  difference (from DB2) has significant impact on our application.
>>
>>  For people who has used DB2 and Oracle can you please tell me other
>>  differences you have found so that I can be prepared, or point me to a
>>  document/web page.
>>
>>  We are porting our application from DB2 UDB (7.2) to Oracle 9i

>
> (9.2.0.4.0).
>

>>  Thanks.
>>
>>--
>>Hemant Shah                           /"\  ASCII ribbon campaign
>>E-mail: NoJunkMailshah_at_xnet.com       \ /  ---------------------
>>                                       X     against HTML mail
>>TO REPLY, REMOVE NoJunkMail           / \      and postings
>>FROM MY E-MAIL ADDRESS.
>>-----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------
>>I haven't lost my mind,                Above opinions are mine only.
>>it's backed up on tape somewhere.      Others can have their own.

Reads don't block writes

Writes don't block reads

All transactions are atomic ... single commit/rollback for all triggers, procedures, packages, etc. You never write separate code to rollback work done by a trigger.

No block (page) locking

Multiversioning ... look up multiversioning and what the DBMS_FLASHBACK built-in package does and you will get an idea of what is happening. It is an activity impossible in DB2.

Triggers are row level or statement level and before or after DDL Other trigger types include INSTEAD OF, DDL, and SYSTEM.

Many index types not present in DB2 some of which provide critical performance improvement.

Clusters ... a different animal entirely.

The security model is completely different. You will need to learn it. Pay special attention to the SYS_CONTEXT function. It can be a huge asset.

Oracle has table types not present in DB2. Pay special attention to global temporary tables: there are two different types.

Dynamic SQL: DB2 doesn't do dynamic SQL; it turns dynamic SQL into static SQL and runs that which is very different.

All of the DBMS and UTL built in packages.

And very important ... packages. Use them rather than separate procedures and functions for almost everything including defining constants and user-defined data types.

C compilation: In Oracle PL/SQL does not require compilation. As of 9i, however, you can perform native compilation and have your PL/SQL code optionally stored as C libraries.

HTH

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)

Received on Tue Dec 09 2003 - 16:11:22 CST