IBM Docs – Migration from DB2 –> ORACLE

IBM Docs – Migration from DB2 –> ORACLE

Hi,

within our last big project, we had the challenge to transfer the IBM Docs database from DB2 to ORACLE. Within this database comments and other document related data is stored.

Officially there is no script available to perform this move using DBT (remove constraints / transfer / reapply constraints). We looked into the database and figured out how to perform this task using DBT – so we are not dependent on any other products. We migrated from DEV (IBM Docs CR1) to PROD (IBM Docs CR1)

The following procedure is officially NOT supported by IBM:

1.Create the source databases on ORACLE

sqlplus "USER/PWD@ORACLE_SID"
 @createDb.sql
 ./updateDBSchema.sh

 #UPDATE TO 2.0 CR1
 updateDBSchema.sh

2.Drop constraints

 DELETE FROM CONCORDDB.PRODUCT;
 ALTER TABLE CONCORDDB.TASK DROP CONSTRAINT CONSTRAINT_TASK;
 ALTER TABLE CONCORDDB.TASKHISTORY DROP CONSTRAINT CONSTRAINT_TASKHIS;

3. Create dbt transfer xml file (Oracle RAC config taken into consideration in this example):

<dbTransfer xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<database role="source"
driver="com.ibm.db2.jcc.DB2Driver"
url="jdbc:db2://DB2_SERVER:50001/CONCORD"
userId="docinst1"
schema="CONCORDDB"
dbType="DB2"/>
<database role="target"
driver="oracle.jdbc.driver.OracleDriver"
url="jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=ORACLE_SERVER_1)(port=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=ORACLE_SERVER_2)(port=1521)))(FAILOVER=on)(LOAD_BALANCE=off)(CONNECT_TIMEOUT=10)(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORACLE_SID)))"
userId="DOCSUSER"
schema="CONCORDDB"
dbType="oracle"/>
</dbTransfer>

4.Transfer the data

6.0-Wizards/jvm/linux/jre/bin/java -Xms3072m -Xmx8192m -cp /MIGRATION/_DOCS/DBT_HOME/dbt.jar:/MIGRATION/_DOCS/DBT_HOME/jdbc/ojdbc7.jar:/MIGRATION/_DOCS/DBT_HOME/jdbc/db2jcc4.jar com.ibm.wps.config.db.transfer.CmdLineTransfer -logDir /MIGRATION/_DOCS/DBT_HOME/logs -xmlfile /MIGRATION/_DOCS/DBT_HOME/concord.xml -sourcepassword xxx -targetpassword xxx

5.Reapply the constraints

ALTER TABLE CONCORDDB.TASK ADD CONSTRAINT CONSTRAINT_TASK FOREIGN KEY (ASSOCIATION_ID) REFERENCES CONCORDDB.ASSOCIATEDWITH (ASSOCIATION_ID) ON DELETE CASCADE;
ALTER TABLE CONCORDDB.TASKHISTORY ADD CONSTRAINT CONSTRAINT_TASKHIS FOREIGN KEY (TASK_ID) REFERENCES CONCORDDB.TASK (TASK_ID) ON DELETE CASCADE;

That`s it

All content was available during our tests and we created an easy and reproducible procedure when transferring databases from one to the other vendor.

Leave a Reply

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