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 = ...
//Load the object from the AS400 side
As400Obj obj = (AS400Obj)results.get(0);
//compose Oracle object
OracleObj oraObj = new OraObj();
//load the ora object into a list
//if the batch size threshhold is met clear the session, flush it and flush the oraObjs List
//Here is where I need to obtain another hibernate session that is configured for Oracle so that I can do something like:
//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,