RE: Bulk load on AS/400 DB2

["Xu, Weining" <Weining.Xu@xxxxxxx>]

Jim, thanks for your reply.

I believe that I am using positioned update.  I have the following sample
code (the program runs on AS/400 by using native JDBC driver):

.......//other code
rs = stmt.executeQuery("select co1, co2, co3, co4, co5 from dbname.tablename
for update of co4, co5");
while(rs.next()){  //400K rows
        ...... //a lot of calculations

        //some time trace code before update.
        rs.updateString(4, result4);
        rs.updateBigDecimal(5, result5);
        rs.updateRow();
        //some time trace code after update.

        ...... //other code
}
.......//other code

The SQL query and update are on the same table.  We run the process during
night without any other job running. The average time measured for each
updating was 7 milliseconds.  So the total time for these three lines of
updating was around 47 minutes (7*400,000/(60*1000)). This consumed about
60% of total process time for one of our batches.  

The updating is for the whole table, that is why I thought may be a bulk
load will help, instead of updating row by row.

Thanks for any suggestions.

Wayne Xu


-----Original Message-----
From: Jim Mason [mailto:jmason900@yahoo.com]
Sent: Thursday, February 13, 2003 11:00 AM
To: Java Programming on and around the iSeries / AS400
Subject: RE: Bulk load on AS/400 DB2


Hi Xu.

Performance depends on your iSeries server
characteristics and workload as well as db2.

IF the % rows in the table for update or insert are
HIGH (eg 75% or more), my experience says creating a
separate table which might not have concurrent access
via views etc and then doing a batch insert to the
first table MIGHT improve performance.  

Even without that, performance isn't too bad in my
examples IF I use JDBC with prepared statements
correctly with a declared cursor for a positioned
update (vs searched).  Inserts are adding records to
the end of the table anyway.  

A big issue is the level of concurrent access,
isolation level and commitment control you want
enforced during the updates.  If this process can
occur at a predetermined time when other activity on
the table doesn't exist, performance should be
reasonable.

Of course you can always write a host program to do
the update directly using the DB2400 file access which
is usually faster.

Jim Mason

--- "Xu, Weining" <Weining.Xu@AIG.com> wrote:
> David,
> 
> I have very large amount of rows (~400,000) need be
> either updated or
> inserted into DB2 tables after the data are
> processed by a Java program.
> JDBC rs.updateRow() method performs update row by
> row. It takes a lot of
> times.  In inserting case, to execute insert SQL
> statement row by row also
> takes a lot of time.  I am thinking that may be I
> could save the results
> from Java program into a file and then loading this
> file into DB table at
> one time.  Any suggestions?  Thanks.
> 
> Wayne
> 
> -----Original Message-----
> From: David Gibbs [mailto:david@midrange.com]
> Sent: Thursday, February 13, 2003 9:41 AM
> To: java400-l@midrange.com
> Subject: Re: Bulk load on AS/400 DB2
> 
> 
> "Xu, Weining"
> <Weining.Xu=3YG79awXLjU@public.gmane.org> wrote in
> message
>
2775CED022F4D4119CF800902754B5AE06F4C4CE@xwilmbx01.aig.com">news:2775CED022F4D4119CF800902754B5AE06F4C4CE@xwilmbx01.aig.com...
> > Does anyone know how to perform bulk load on
> AS/400 DB2 V5R1?  Any
> > documentations?  Thanks.
> 
> Can you be more specific on what you mean by "Bulk
> Load"?  Are you talking
> about loading tables from an external source?
> 
> david
> 
> 
> _______________________________________________
> This is the Java Programming on and around the
> iSeries / AS400 (JAVA400-L)
> mailing list
> To post a message email: JAVA400-L@midrange.com
> To subscribe, unsubscribe, or change list options,
> visit:
> http://lists.midrange.com/mailman/listinfo/java400-l
> or email: JAVA400-L-request@midrange.com
> Before posting, please take a moment to review the
> archives
> at http://archive.midrange.com/java400-l.
> _______________________________________________
> This is the Java Programming on and around the
> iSeries / AS400 (JAVA400-L) mailing list
> To post a message email: JAVA400-L@midrange.com
> To subscribe, unsubscribe, or change list options,
> visit:
> http://lists.midrange.com/mailman/listinfo/java400-l
> or email: JAVA400-L-request@midrange.com
> Before posting, please take a moment to review the
> archives
> at http://archive.midrange.com/java400-l.
> 


__________________________________________________
Do you Yahoo!?
Yahoo! Shopping - Send Flowers for Valentine's Day
http://shopping.yahoo.com
_______________________________________________
This is the Java Programming on and around the iSeries / AS400 (JAVA400-L)
mailing list
To post a message email: JAVA400-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/java400-l
or email: JAVA400-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/java400-l.