Re: Need help with jdbcr4

On Wed, May 6, 2009 at 1:45 PM, Scott Klement <rpg400-l@xxxxxxxxxxxxxxxx> wrote:

But, as I said in the COMMON session, it might be faster to export the
data from DB2 (CPYTOIMPF) and import it into SQL Server.  Folks have
told me that in the past, anyway -- JDBC isn't the most efficient method
for a "mass update".

Note that, at least for mass updates of the i from SQL Server Data,
JDBC does a pretty good job if you do the following:

1) Use commitment control if the destination file is journaled (commit
periodically)
2) Use Batch mode inserts

Here's some code I wrote as a test, I was impressed by the
performance.  Note that this is all done in Java, no RPG at all.  I
don't know if you can follow the rules above if you're trying to use
JDBC from RPG.  But IMHO, it's cleaner just to have it all in Java.

       public void TransferData() {

               System.out.println("Starting Transfer...");
               long startTimeMS = System.currentTimeMillis( );
               try {
                       Statement stmt = sqlConn.createStatement();
                       ResultSet rs = stmt.executeQuery("select distinct c.location_cd,
c.customer_no, ue.unit_no             " +
                                                                " from CINTAS.cadmin.unit_Extension ue
                                   " +
                                                                "       inner join
CINTAS.cadmin.customer c                                  " +
                                                                "            on ( ue.cust_id = c.cust_id
)                            " +
                                                                "       left outer join
CINTAS.cadmin.unit u                                 " +
                                                                "            on ( ue.cust_id = u.cust_id
AND ue.unit_no = u.unit_no ) " +
                                                                " where u.cust_id IS NULL  ");
                       System.out.println("---ResultSet in: " +
Long.toString(System.currentTimeMillis( ) - startTimeMS));

                       i5Conn.setAutoCommit(false);
                       //i5Conn.setTransactionIsolation(JDBC_TRANSACTION_READ_COMMITTED);
                       startTimeMS = System.currentTimeMillis( );
                       PreparedStatement ps = i5Conn.prepareStatement("INSERT INTO
CINTASDTA.INACTCAB VALUES(?,?,?)");
                       System.out.println("---StmtPrepared in: " +
Long.toString(System.currentTimeMillis( ) - startTimeMS));


                       startTimeMS = System.currentTimeMillis( );
                       int nbrRecs = 0;
                       while (rs.next()) {
                         ps.setString(1,rs.getString(1));
                         ps.setInt(2,rs.getInt(2));
                         ps.setString(3,rs.getString(3));
                         //ps.executeUpdate();
                         ps.addBatch();
                         nbrRecs += 1;
                         if (nbrRecs >= 5000) {
                                 ps.executeBatch();
                                 i5Conn.commit();
                                 nbrRecs = 0;
                         }

                       }

                       ps.executeBatch();
                       i5Conn.commit();

                       System.out.println("---Data inserted in: " +
Long.toString(System.currentTimeMillis( ) - startTimeMS));

       } catch (SQLException e) {
           System.out.println(e.getMessage());
               }
       }


HTH,
Charles