× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



I posted the code the code.midrange.com also:

http://code.midrange.com/dfc66e6500.html

Charles

On Wed, May 6, 2009 at 4:11 PM, Charles Wilt <charles.wilt@xxxxxxxxx> wrote:
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


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.