Trouble transferring Oracle –> DB2 database IC4.5 – IC5.0
Last week I had trouble transferring an IBM Connections 4.5 oracle database to DB2. The customer decided to “move away” from Oracle and to DB2… But this seems to be a little bit a problem for at least three of the IBM Connections databases (Files, Wikis and Blogs).
As you may know, you can migrate IBM Connections databases using the dbt.jar between all supported DB vendors (DB2, oracle, MSSql).
The procedure how dbt.jar transfer works is the same as WebSphere Portal also transfers databases from derby to DB2 (initial database transfer):
- Create the target database
- Remove the constraints (foreign keys of the table)
- Transfer the data from “old” to “new” database (table by table)
- Re add the constraints
- That`s it 😉
In case of the three “bad” databases, I got the following error message when transferring the data:
Explanation: SQLSTATE 23502: An insert or update value is null, but the column cannot contain null values."
hmm… I compared both “createDb.sql” scripts and there I found the problem:
The createDb.sql script for ORACLE
The createDb.sql script for DB2
Here an example out of the files createDb.sql scripts. Can you see the difference of attribute definition for SUMMARY?
This means, that the transfer task tries to insert null values from Oracle into DB2. But per definition, DB2 permits those inserts of null values.
IBM also identified the problem and provides scripts you can use to “remove” the non-equal NOT NULL definitions from the DB2 createDb.sql script. After migration there is a script to re add those definitions to match the DB2 rules.
You need to create the databases “files, wikis and blogs” with the following createDb.sql files:
After migration you need to apply the “Files-, Wikis-, BlogsFixup.sql” file to readd the NOT NULL definitions:
Still there is a big question mark how to transfer the Filenet databases from oracle to DB2. Let`s see what a solution we can find here 😉