midrange.com code scratchpad
Name:
Java JDBC procedure that loads iSeries table from an SQL server
Scriptlanguage:
Plain Text
Tabwidth:
4
Date:
05/06/2009 08:16:37 pm
IP:
Logged
Description:
This procedure uses commitment control and batch inserts on the iSeries side to improve the performance of a JDBC data transfer from an SQL server to an iSeries.
Code:
  1.     public void TransferData() {
  2.         
  3.         System.out.println("Starting Transfer...");
  4.         long startTimeMS = System.currentTimeMillis( );
  5.         try {
  6.             Statement stmt = sqlConn.createStatement();
  7.             ResultSet rs = stmt.executeQuery("select distinct c.location_cd, c.customer_no, ue.unit_no             " + 
  8.                                              " from CINTAS.cadmin.unit_Extension ue                                       " + 
  9.                                              "       inner join CINTAS.cadmin.customer c                                  " + 
  10.                                              "            on ( ue.cust_id = c.cust_id )                            " + 
  11.                                              "       left outer join CINTAS.cadmin.unit u                                 " + 
  12.                                              "            on ( ue.cust_id = u.cust_id AND ue.unit_no = u.unit_no ) " + 
  13.                                              " where u.cust_id IS NULL  ");
  14.             System.out.println("---ResultSet in: " + Long.toString(System.currentTimeMillis( ) - startTimeMS));
  15.             
  16.             i5Conn.setAutoCommit(false);
  17.             //i5Conn.setTransactionIsolation(JDBC_TRANSACTION_READ_COMMITTED);
  18.             startTimeMS = System.currentTimeMillis( );
  19.             PreparedStatement ps = i5Conn.prepareStatement("INSERT INTO CINTASDTA.INACTCAB VALUES(?,?,?)");
  20.             System.out.println("---StmtPrepared in: " + Long.toString(System.currentTimeMillis( ) - startTimeMS));
  21.             
  22.             
  23.             startTimeMS = System.currentTimeMillis( );
  24.             int nbrRecs = 0;
  25.             while (rs.next()) {
  26.               ps.setString(1,rs.getString(1));
  27.               ps.setInt(2,rs.getInt(2));
  28.               ps.setString(3,rs.getString(3));
  29.               //ps.executeUpdate();
  30.               ps.addBatch();
  31.               nbrRecs += 1;
  32.               if (nbrRecs >= 5000) {
  33.                   ps.executeBatch();
  34.                   i5Conn.commit();
  35.                   nbrRecs = 0;
  36.               }
  37.               
  38.             }
  39.             
  40.             ps.executeBatch();
  41.             i5Conn.commit();
  42.  
  43.             System.out.println("---Data inserted in: " + Long.toString(System.currentTimeMillis( ) - startTimeMS));
  44.             
  45.         } catch (SQLException e) {
  46.             System.out.println(e.getMessage());
  47.         }
  48.     }
  49.  
  50.  
© 2004-2019 by midrange.com generated in 0.007s valid xhtml & css