Hibernate version: 2.16

Mapping documents: N/A

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

We are using 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 :)

Can anyone please share any experience or approach to doing this with Hibernate. Please keep in mind that we are dealing with two sessions. One on the DB2 side and one on the Oracle side.

Thanks in advance

Have a look at http://blog.hibernate.org/cgi-bin/blosxom.cgi/Gavin%20King/batch.html

Michael:

Thanks for your reply. Yes I looked at that page and I am using that approach to retrieve the data.

What I need now is how to save the objects that will be persisted on Oracle within the same transaction that is pulling the records from DB2.

This is a bit of pseucode of what I am doing

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.

Thanks in advance,

Juan Alvarado

You have to use XA, you need a Transaction Manager, etc. - read up on that.

How about using a specific import tool ? I can't name any specific tools right now, but I am sure there are some that you could use.
This would be more simple if your don't need any specific schema changes ...

Why are you writing programs to do this ? Oracle provide migration utilties (based on ODBC I think), and you can use the iSeries / AS/400 data transfer functions in Client Access to extract your data as CSV files or whatever.


The only issues you may have are if you have referential constraints on the Oracle side. THe usual approach is to drop all the constraints, load up all the data, then add back the constraints. This of course assumes that your incoming data is clean.