Switch from Oracle to DB2

[Tavish Muldoon]

At a high level - what would be involved in switching from Oracle to DB2?

Pretty vague, I know - but anyone have experience with this kind of migration?

Pointers? Things to look for?

Tmuld

[Jan Suchanek]

Hi,

Tavish Muldoon wrote:

At a high level - what would be involved in switching from Oracle to DB2?

Pretty vague, I know - but anyone have experience with this kind of migration?

Pointers? Things to look for?
There is a (free) migration tool from IBM for the migration from Oracle
to DB2. They showed it on a migration workshop and it seem to perform
very well (at least for simple databases). You find it on the IBM webpage.

Tmuld

HTH, Jan

[RdR]

Generally straightforward but some small little issues that may need some
work like Oracle sees zero length varchars as NULLs but DB2 sees it as not
NULL. From Oracle to DB2, this is not a problem but during the migration
period wherein both systems should be Master databases, synchronizing data
that are zero length varchars in DB2, Oracle will not put them on a
non-NULLable field because Oracle thinks it is a NULL.

Generally you need to plan on the following:

1) Need a tool to synchronize data Oracle to DB2. Producing a flat file in
Oracle then FTP to the DB2 box and using DB2 tools to load the data in DB2
should do the job.
2) If this is not a one time migration and you need some time wherein both
databases are being updated on both sides, you also need a tool to
synchronize data both ways. You need more of a replication tool. DPROP may
handle this but that was not the tool we used. Note the zero length varchar
DB2 mapping to an Oracle Not NULL counterpart I mentioned above.
3) Need to pay specific attention to different database structures on both
sides, a mapping tool should help ease up this process. If the table
structures are the same on both sides, then this should not be a bog
concern.
4) Go to this website, http://troels.arvin.dk/db/rdbms/ this should guide
you on the differences in SQL implementation on Oracle and DB2 (among
others).
5) Needless to say, backup so if something goes wrong, you can always go
back to the state you were in prior to doing the migration.

Thanks,

Johnnywhizkid

"Tavish Muldoon" <tm******@spliced.com> wrote in message
news:e2**************************@posting.google.c om...

At a high level - what would be involved in switching from Oracle to DB2?

Pretty vague, I know - but anyone have experience with this kind of migration?
Pointers? Things to look for?

Tmuld

[Paul Samundar]

Here is URL for migration toolkit ...

http://www-306.ibm.com/software/data/db2/migration/

Jan Suchanek <ja**********@gmx.de> wrote in message news:<41********@news.uni-ulm.de>...

Hi,

Tavish Muldoon wrote:

At a high level - what would be involved in switching from Oracle to DB2?

Pretty vague, I know - but anyone have experience with this kind of migration?

Pointers? Things to look for?

There is a (free) migration tool from IBM for the migration from Oracle
to DB2. They showed it on a migration workshop and it seem to perform
very well (at least for simple databases). You find it on the IBM webpage.

Tmuld

HTH, Jan

[Serge Rielau]

Tavish Muldoon wrote:

At a high level - what would be involved in switching from Oracle to DB2?

Pretty vague, I know - but anyone have experience with this kind of migration? Oh yeah.. Lotsa... Pointers? Things to look for?

Make sure you have some sort of QA on the Oracle side that you can port.
Otherwise you'll have a hell of a time proving that the DB2 side is
doing what the Oracle side is doing (and live data is a BAD(tm) choice
because it's not reproducible).

The migration toolkit (MTK) will try it's best to provide a correct(!)
translation, including emulating all those quirks (like the '' = NULL).

Debug for correctness first.

You may be off by an order of magnitude in speed at first.
That is OK for an Oracle emulation run by DB2.
Then you start tuning those areas that you care about (not everything is
time-critical).
Look at the PL/SQL code then look at the SQL PL (code):
Most of the fluff added by MTK can likely be removed (like Java UDF
handling slight semantic differences).
Often the default exception handlers are a drag.
Once you removed the fluff. If performance isn't on par yet you look at
the algorithm. It may be tuned specifically to work around some Oracle X
shortcoming that DB2 deosn't have or works around differently.
that's where the fun starts and you will really need to learn DB2.

Here are a couple of articles directly coming from migration experience:
http://www-106.ibm.com/developerwork...dm-0409rielau/
http://www-106.ibm.com/developerwork...dm-0406rielau/

The second article was born in a Orcale to DB2 + DPF warehouse migration.
We started of with 30x slower (emulation). And ended up ~2x faster
(apples-oranges hardware though). The point is, unless the app is
trivial it doesn't coem for free but if you stick with it the end result
will be worth it.

Try to get into the migration workshop. It'll give you a head start.

feel free to bug this newsgroup :-)

Cheers
Serge

[AK]

that's what I posted a year ago:

1. Schema conversion.
I downloaded Migration Toolkit from
http://www-3.ibm.com/software/data/d...src_d=99999999
Migration from SQL Server seems to be in better shape, migration from
Oracle is in beta yet.
I wasn't able to have Migration toolkit move data properly.

2. Connectivity
DB2 uses only operating system autentication (similar to Windows
authenication for MS SQL Server ), it does not have its own
authenication.
.... but we need to convert schema name to uppercase when selecting
from catalog views, like htis: WHERE TABSCHEMA=UPPER('schemaname')

3. Different system (catalog) views (DB@ catalog views are documented
in Appendix D of SQL Reference)

Functions:
SUBSTR - interface same as in Oracle. Documened in SQL Reference
Oracle: TO_NUMBER
MS SQL Server: CONVERT(INT,
DB2: INT(

4. Replaced DELETE <TableName> with DELETE FROM <TableName>. DELETE
FROM works against Oracle and SQL Server too.

5. Fetching first N rows.
In Oracle: RowNum<10
In SQL Server: TOP 10
In DB2: SELECT * fFROM SOME_TABLE WHERE some condition FETCH FIRST 10
ROWS ONLY

6. Replaced INSERT <TableName> with INSERT INTO <TableName>. INSERT
INTO works against Oracle and SQL Server too.

7. Replaced INSERT INTO TableName SELECT Column1, Column2, NULL, NULL
FROM ...
with
INSERT INTO TableName(Column1, Column2) SELECT Column1, Column2 FROM
This works against Oracle and SQL Server too

8. Constraint name length cannot exceed 18 characters, so I changed
all the DROP/ADD CONSTRAINT statements, so that constraint names are
system generated, and retrieved from catalog view (because you need to
know constraint's name to drop it):
Same about index names

9. There are many differences in error messages, such as when index to
be dropped does not exist. They are all (or almost all)
vendor specific.

10. TRUNCATE TABLE won't work against DB2. Used DELETE FROM instead. I
think DROP/CREATE TABLE might work much much faster

11. Since schema name is in lower case, I needed to explicitly convert
it to uppercase when retrieveing from system (catalog) views:
SELECT TYPE FROM SYSCAT.TABCONST WHERE TABSCHEMA = SYSIBM.UPPER('...')
AND TYPE = 'P' AND TABNAME = '...'

12. When an INSERT/UPDATE/DELETE/SELECT statement actually
modifies/fetches no rows, DB2 issues an error ... . Error message
looks like "No row was found for".
wanted to add that an application that was simply ported usually does
not perform well, because there are many significant differences, such
as:
- locking strategy in Oracle is entirely different
- indexing is somewhat different (for instnace, NULLS are not stored
in Oracle indexes)

So, to achieve better performance, we usually have to reconsider some
design decisions.

[Knut Stolze]

AK wrote:

that's what I posted a year ago:

2. Connectivity
DB2 uses only operating system autentication (similar to Windows
authenication for MS SQL Server ), it does not have its own
authenication.
In DB2 V8.2 you can use security exits and do your own user authentication.
Nothing prevents you from managing your users via a database. Have a look
here:

http://publib.boulder.ibm.com/infoce...d/c0011970.htm

So this issue is already dealt with.
... but we need to convert schema name to uppercase when selecting
from catalog views, like htis: WHERE TABSCHEMA=UPPER('schemaname')
If you use delimited schema and table names, then you don't have to do this
conversion. I believe the upper-case might be something that comes from
the SQL standard.
4. Replaced DELETE <TableName> with DELETE FROM <TableName>. DELETE
FROM works against Oracle and SQL Server too.

6. Replaced INSERT <TableName> with INSERT INTO <TableName>. INSERT
INTO works against Oracle and SQL Server too.
DELETE FROM and INSERT INTO are SQL standard.
10. TRUNCATE TABLE won't work against DB2. Used DELETE FROM instead. I
think DROP/CREATE TABLE might work much much faster
You can use LOAD ... REPLACE ... that offers the very same functionality.
Wrapped in a stored procedure, it is even more convenient as is described
here:
http://www.ibm.com/developerworks/db...ein/index.html

Please note that DELETE FROM causes much more log records to be written than
a LOAD REPLACE. And too many log records can cause a log-full error
condition.
11. Since schema name is in lower case, I needed to explicitly convert
it to uppercase when retrieveing from system (catalog) views:
SELECT TYPE FROM SYSCAT.TABCONST WHERE TABSCHEMA = SYSIBM.UPPER('...')
AND TYPE = 'P' AND TABNAME = '...'
Let's say it that way: DB2 converts the schema name to upper case by
default, unless you place double-quotes around it.
12. When an INSERT/UPDATE/DELETE/SELECT statement actually
modifies/fetches no rows, DB2 issues an error ... . Error message
looks like "No row was found for".

No, DB2 raises a warning condition with SQLCODE +100. That's not an error
and is mandated by the SQL standard. Oracle does the same.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[Bob]

Is that migration tool really free? Here is what I read in the url below:
'Migrate Now! for DB2 Universal Database (UDB) V8.1 facilitates the
migration from Oracle, Sybase, Microsoft SQL server, and additional database
platforms to DB2 UDB V8.1 at a special price.'

By the way, there is also another replication solution from StarQuest
(StarQuest Data Replicator -
http://www.starquest.com/Productfolder/infoSQDR.html). DPROP was also
mentioned in another post (but obviously this is not free!).

Bob
"Paul Samundar" <sa******@email.com> a écrit dans le message de
news:fb**************************@posting.google.c om...

Here is URL for migration toolkit ...

http://www-306.ibm.com/software/data/db2/migration/

Jan Suchanek <ja**********@gmx.de> wrote in message

news:<41********@news.uni-ulm.de>...

Hi,

Tavish Muldoon wrote:

At a high level - what would be involved in switching from Oracle to DB2?
Pretty vague, I know - but anyone have experience with this kind of migration?
Pointers? Things to look for?

There is a (free) migration tool from IBM for the migration from Oracle
to DB2. They showed it on a migration workshop and it seem to perform
very well (at least for simple databases). You find it on the IBM webpage.

Tmuld

HTH, Jan

[Serge Rielau]

Bob,

Yes the MTK is free. The offer relates to a special offer for the DB2
license (perhaps amongst other things such as training) if you replace
an existing competitive license.

Cheers
Serge