Hi

We have a Java application that inserts a certain amount of data into a set of tables.

After the insert the data is processed one row a a time and inserts are made into the main tables...

Our problem is that on a mySQL DB this takes about 1 1/2 hours and
on our DB2 about 14 hours! It seems that the problem is that the indexes
on the first set of tables are not updated efter the insert therefore the
updates takes too long to perform.

We guess that this could be fixed by running a reorg or update statistics but we cant figure out how to do it between the two jobs!

The JDBC-driver does'nt accept the reorg commands so we cant do it through
the application.

Does anone have any ideas???

Sincerely
Stefan Sidholm
Sweden

Hi!

Inserting and updating data can be extremely demanding... Especially, if you have lot of indexes and/or triggers involved. But also JDBC-trafic can be the bottleneck.

Indexes ARE updated, but they can be really mixed up by lot of new update. And having REORG between two jobs is no option. What to do?

1) Have material in right order. Sort input material (if you can), so that insert are done only in same order as tables cluster-index is. This will minimize i/o operations.
2) Can you use TEMP-tables? Do first insert into TEMP-table and then do further inserting by "INSERT INTO xxxx SELECT ... FROM TEMP" -SQL-sentence. This way indexes are not hurting initial insert and secondary insert are done INSIDE the DB2 (no data traveling trough JDBC-interface).

There are more tricks, but first you must find out, which element is causing slowness. For instance, take indexes away and try then to do insert. Is it faster? And keep it in mind, sequential i/o is x100 faster than random i/o.

Cheers, Bill

What tablespace are you using ? Using DMS will improve performance ... If you wish to use SMS only, then enable multipage file allocation using the db2empfa command ...

There is a recent article on Developer Domain on insert performance ... You can search in www7b.boulder.ibm.com/dmdd

Cheers
Sathyaram

JDBC - set autocommit off, then do commits ever N records.

Better still - use the batch capabilities:

PreparedStatement pstmt =
con.prepareStatement("INSERT INTO XXX values (?, ?)");
for (int i=0; i<ITER; i++) {
pstmt.setString(1, "STRING" + i);
pstmt.setString(2, "another string");
pstmt.addBatch();
}
int[] res = pstmt.executeBatch();
con.commit();

Keep the batch sizes reasonable, 1000 rows for small ones, less if the rows are big... but I've found a big performance gain, especially if the app is running remotely.