Analyzing column names in tables to be copied

[TimJowers]

Is it normal to see: "Analyzing column names in tables to be copied" for 5 minutes or so? i'd guess the next step is to actually copy. Maybe i should have used a smaller test first. Copying from Oracle to Postgresql.

[TimJowers]

Ran with 1 table and it copied fine. When doing all tables it seems to hang there…. I see the dbcopytest table created in the destination db but nothing is in it. nope. This time it started copying data. So, i guess the "hang" is not normal. This time it only "hung" there for about 1 minute or a little less.

[TimJowers]

I didn't look at the source code but doesn't look like its using "batch" mode for JDBC. I'm sure they are but it sure doesn't look like it by the counter and speed. E.g. copying 700,000 rows should be done in batches of like 1000 rows at a time (depends on memory)… maybe I need to see if there is anywhere to configure this. i'll read the fine manual now. :-)

[TimJowers]

Hmmm. methinks needs to use batch prepared statements. executeBatch
CopyExecutor.java

576: InsertStmt.executeUpdate();

[TimJowers]

This ought to do it. Add 1 file to a new java project:  /my_dbcopy/src/net/sourceforge/squirrel_sql/plugins/dbcopy/CopyExecutor.java
9src is from squairrel install/plugins/dbcopy/src.jar
Add the jars: dbcopy.jar, hibernate.jar, adn squirrel-sql.jar from the normal places within the squirrel app tree.
Ok, changes made to CopyExecutory.java:
651:            destConn.setAutoCommit(false);
            int rowsToAdd=0;
606:                     rowsToAdd++;
                    insertStmt.addBatch();
624:            // insertStmt.executeUpdate();
                rowsToAdd = batchAdd(insertStmt, insertSQL, bindVarVals,rowsToAdd);
635:            rowsToAdd = batchAdd(insertStmt, insertSQL, bindVarVals,rowsToAdd);

And a new method:
private int batchAdd(PreparedStatement insertStmt, String insertSQL,
String bindVarVals, int rowsToAdd) throws SQLException {
if( rowsToAdd>1000 ) {
sendStatementEvent("Batch ending with: " +insertSQL, bindVarVals);
int rowsAdded = insertStmt.executeBatch();
if( rowsAdded.length != rowsToAdd ) { // not correct way to check but you get the idea
sendStatementEvent("Some error with batch insert ("+rowsAdded.length+"): " +insertSQL, bindVarVals);
// die/exit/return needed here of course
}
rowsToAdd=0;
}
return rowsToAdd;
}
Rebuild and then unjar/rejar dbcopy.jar with the fix.
Trying it now.

[TimJowers]

Changed the code some and tried it. Maybe the "autocommit" off is the way it was meant to run  but that does not work when I try to copy from Oracle to Postgresql. So, maybe the batching is needed.

[Rob Manning]

Hi Tim,

This project is intended to provide a version of DBCopy that supports older versions of SQuirreL.  It is included in the SQuirreL installer as an "optional" plugin for years now.  When you say that "autocommit" off … doesn't work, what exactly do you mean ? (still slow or you are getting some error ?)  The "Global Preferences" -> DBCopy tab allows you to set the number of records at which a commit will be issued.

Rob

Rob