Trouble transferring Oracle –> DB2 database IC4.5 – IC5.0

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:

“SQLCODE=-407, SQLSTATE=23502”

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

Db_Transfer1_oracle

The createDb.sql script for DB2

Db_Transfer1_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:

oracle-db2-migration-createDb

After migration you need to apply the “Files-, Wikis-, BlogsFixup.sql” file to readd the NOT NULL definitions:

oracle-db2-migration-fixup

That`s it.

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 😉

2 thoughts on “Trouble transferring Oracle –> DB2 database IC4.5 – IC5.0

  1. For IBM Connections I used DATA Studio and detected as you the same error in the scripts + the null issue in db2, I manage to solve theses by editing the createDb script and fixing the null with some other scripts. glad that the ibm support managed to help you, hope they didn’t take to much times 😉

    For CCM, you can do it by yourself but this is not a supported way.

    The only way is to engage the services of IBM called : ECM Platform Conversion Services.

    Here is an extract from the white paper the support gave to me :

    IBM Enterprise Content Management (ECM) Software Services
    offers Platform Conversion Services that provide a fail-safe
    methodology for protecting your data during the transition to a
    new platform solution.
    Services include:
     Converting to a new
     Operating System,
     Database, or
     Directory Service
     Performing a System Merge or System Split

    • Hi Ben,

      thanks for you comment.
      We involved the IBM service guys for one migration of filenet from Oracle to DB2.
      This is really a pain and I cannot recommend this. Maybe this works better for another projects but this time there was a lot of trouble doing that.

Leave a Reply

Your email address will not be published. Required fields are marked *