Hi~

I tried to insert more than one million recoreds into one table in a transaction, however,
it failed because the transaction log was full (database : DB2). I searched several forums
and knew that :

"Tranaction log full error pops up when the DML transactions are not commited frequently."

Though allocating more log file (to enlarge LOGPRIMARY) may solve the problem, I am not
allowed to do that.

Is there any configuration of hibernate.cfg.xml that can force my application to commit more often?
Will the property "connection.autocommit" set to "true" helps? (it's not recommended by Hibernate)

Thanks in Advance,
Godspeed



Here is my hibernate.cfg.xml file :

<hibernate-configuration>
<session-factory>
<!-- datasource config FOR DB2 -->
<property name="show_sql">true</property>
<property name="format_sql">false</property>
<property name="connection.profile">DB2</property>
<property name="connection.url">jdbc:db2://192.168.11.11:50000/ALM</property>
<property name="connection.username">alm</property>
<property name="connection.password">alm</property>
<property name="connection.driver_class">com.ibm.db2.jcc.DB2Driver</property>
<property name="dialect">org.hibernate.dialect.DB2Dialect</property>

<property name="jdbc.batch_size">50</property>
<property name="jdbc.fetch_size">25</property>

<property name="cache.use_2nd_level_cache">false</property>
<property name="connection.autocommit">false</property>


<!-- c3p0 config http://www.hibernate.org/214.html -->
<property name="connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>
<property name="hibernate.c3p0.acquire_increment">1</property>
<property name="hibernate.c3p0.idle_test_period">60</property>
<property name="hibernate.c3p0.min_size">1</property>
<property name="hibernate.c3p0.max_size">2</property>
<property name="hibernate.c3p0.max_statements">50</property>
<property name="hibernate.c3p0.timeout">0</property>
<property name="hibernate.c3p0.acquireRetryAttempts">1</property>
<property name="hibernate.c3p0.acquireRetryDelay">250</property>

<!-- Class Mapping Information -->
<mapping resource="tables/DWINTR.hbm.xml" />

</session-factory>
</hibernate-configuration>



And this is the methods in Java code that execute the transaction :

public static void transINTR(String tmpFileName) {
CsvReader csvReader = null;
log.info("開始轉分行檔");

Session session = HibernateHelper.getSessionFactory().openSession();
Transaction tx = session.beginTransaction();

String hql = " delete from DWINTR ";
session.createQuery(hql).executeUpdate();

try {
csvReader = new CsvReader(tmpFileName);

String IDOT11D_INT_CODE, IDOT11D_CURR,IDOT11D_INT_DATE;
Float IDOT11D_INT_RATE;

int count = 0;// 存取大量資料時，給Hibernate用的計數器

while (csvReader.readRecord()) {
IDOT11D_INT_CODE = Util.TrimAll(csvReader.get(0));
IDOT11D_CURR = Util.TrimAll(csvReader.get(1));
IDOT11D_INT_DATE = Util.TrimAll(csvReader.get(2));
IDOT11D_INT_RATE = Float.valueOf(csvReader.get(3));

// 把資料存進DB
DWINTR dwintr = new DWINTR(IDOT11D_INT_CODE,
IDOT11D_CURR,IDOT11D_INT_DATE, IDOT11D_INT_RATE);

session.save(dwintr);

if (++count % 50 == 0) {
session.flush();
session.clear();
}
}

} catch (FileNotFoundException e) {
log.error(e.getMessage(), e);
} catch (IOException e) {
log.error(e.getMessage(), e);
}

tx.commit();
session.close();
}