1. Schema conversion.
I downloaded Migration Toolkit from
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.
DB2 uses only operating system autentication (similar to Windows
authenication for MS SQL Server ), it does not have its own
... but we need to convert schema name to uppercase when selecting
from catalog views, like htis: WHERE TABSCHEMA=UPPER('schemaname')
in Appendix D of SQL Reference)
SUBSTR - interface same as in Oracle. Documened in SQL Reference
MS SQL Server: CONVERT(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
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
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)
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".
Porting to DB2 Web site
IBM DB2 Universal Database Porting Guide: Oracle to DB2 Version 7.2