Migrating data from one database vendor to a diff vendor

[juan110470]

We are in the process of migrating some data from a legacy DB2 database on an AS400 over to oracle 10g.

We are using spring/hibernate to pull the data out from DB2 with a hibernate session specific to that environment, and then using BeanUtils we compose the objects for the Oracle environment and the use Hibernate with a session specific to Oracle to save the data.

This has worked just fine with small resultsets. The problem that I am now having is that some of the tables on DB2 have way too many rows to load in a single query. I need to be able to iterate through the resultset with a ScrollableResults object and then say after 100 objects have been loaded in a List of Oracle objects, I need to save those Oracle objects to the Oracle database. Of course all this has to happen in a single transaction

This is a bit of pseudocode of what we need:

List oraObjs = new ArrayList();
Session session = as400Session
ScrollableResults results = ...
while(results.next() ){

//Load the object from the AS400 side
As400Obj obj = (AS400Obj)results.get(0);
//compose Oracle object
OracleObj oraObj = new OraObj();
BeanUtils.copyProperties(oraObj, obj);

//load the ora object into a list
oraObjs.add(oraObj);

//if the batch size threshhold is met clear the session, flush it and flush the oraObjs List
session.flush();
session.clear();
oraObjs.clear();
}

//Here is where I need to obtain another hibernate session that is configured for Oracle so that I can do something like:

oraDao.saveObjs(oraObjs);

//I'd like for this to occur within the same transaction so that if something goes wrong on the AS400 side, nothing will be committed on the oracle side.

Can anyone share with me any experience and spring configuration related to this.

Thanks in advance,

[aimbastari]

For data migrations I use tools specifically designed for that purpose. They are relavtively inexpensive ($500.00) and they get the job done with 0 headaches. Migrate indexes, tables, rows and views flawlessly.
Then I use ORM for what it was designed to do.

Check these guys out.

http://www.swissql.com

Alex

[juan110470]

I agree with you, but since there doesn't seem to be a whole lot of support for DB2 on an AS400 system so we are going with Hibernate to eliminate some of the tedious JDBC handling.

If you or anyone knows of any migration tools for DB2 on AS400 please let me know.

Thanks

[Rod Johnson]

I have to agree with Alex. If you can't find a suitable third party tool, why not extract/load to a file?

[juan110470]

We are dealing with tables on DB2 on AS400 with 170 million records. I don't think an extract file would be the solution here. Some migration specific tool is really what is needed here but we are in a position where funding is very limited. That is why I started using a tool like Hibernate. For tables with 50,000 records or less it is quite manageable. The problem arises with anything over 50,000.

With the usage of a ScrollableResultset, I can batch process the records, but for 170 million records, I don't see how this is going to be accomplished. God knows what the memory requirements for that are.