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.
