JDBC Insert Performance -- JAVA400-L

Allen,

In terms of SQL insert performance, could you code your data load application so it did something like a "Insert into lib.file (a, b, c) (select a, b , c from lib2.file2)" ????

From my days as a DB2/400 developer, I know this would reduce the Open Data Paths and much of the work could be done below the MI.

You can obviously tweek your SQL code to do transformations of data values, etc using the full power of SQL....

Just a thought. -Paul

At 02:50 AM 7/9/2003 -0500, you wrote:

Send JAVA400-L mailing list submissions to
        java400-l@xxxxxxxxxxxx

To subscribe or unsubscribe via the World Wide Web, visit
        http://lists.midrange.com/mailman/listinfo/java400-l
or, via email, send a message with subject or body 'help' to
        java400-l-request@xxxxxxxxxxxx

You can reach the person managing the list at
        java400-l-owner@xxxxxxxxxxxx

When replying, please edit your Subject line so it is more specific
than "Re: Contents of JAVA400-L digest..."

Today's Topics:

   1. RE: AS/400 JDBC Insert performance (Hall, Philip)
   2. RE: AS/400 JDBC Insert performance (Joe Pluta)
   3. RE: AS/400 JDBC Insert performance (Allen)
   4. RE: AS/400 JDBC Insert performance (Allen)
   5. JDBC Connection to V4R1 (Mahmood, Anwar (Cognizant))
   6. Re: AS/400 JDBC Insert performance (Joe Sam Shirah)
   7. Re: AS/400 JDBC Insert performance (Allen)
   8. Re: JDBC Connection to V4R1 (Dieter Bender)
   9. Re: AS/400 JDBC Insert performance (Dieter Bender)
  10. RE: JDBC Connection to V4R1 (Mahmood, Anwar (Cognizant))

----------------------------------------------------------------------

message: 1
date: Tue, 8 Jul 2003 22:07:28 -0500
from: "Hall, Philip" <phall@xxxxxxxx>
subject: RE: AS/400 JDBC Insert performance


> I am using the JT/Open toolkit JDBC driver.  I'm using
> Prepared Statements >\

Have you compared using non-prepared statements ? I know for SQL server it was actually slower using prepared statements, than dynamically building the SQL in a string buffer for each row.

Also, bulk loading is the fastest, but only SQLServer offers a SQL statement to do it.

In DB2 you use the db2 command line and the LOAD sub-command, Oracle uses the sqlldr command.

--phil

------------------------------

message: 2
date: Tue, 8 Jul 2003 22:19:40 -0500
from: "Joe Pluta" <joepluta@xxxxxxxxxxxxxxxxx>
subject: RE: AS/400 JDBC Insert performance

> From: Allen
>
> I need to insert 180,000 rows into an AS/400 table
> within a 4 hour period.  Currently the best I can do
> will come in at 8 hours.  I am pretty sure that I
> should be able to at least double this speed.
>
> I am using the JT/Open toolkit JDBC driver.

Have you tried using the RLA classes rather than JDBC?  RLA tends to perform
better in certain circumstances, and inserts are one of those areas in
particular where RLA excels.

Joe

------------------------------

message: 3
date: Tue, 8 Jul 2003 20:17:52 -0700 (PDT)
from: Allen <tayal01@xxxxxxxxx>
subject: RE: AS/400 JDBC Insert performance

I am using a single connection for all the work.

Cheers,
Allen.

--- "Bartell, Aaron L. (TC)"
<ALBartell@xxxxxxxxxxxxxx> wrote:
> Are you pooling your connections?
>
> -----Original Message-----
> From: Allen [mailto:tayal01@xxxxxxxxx]
> Sent: Tuesday, July 08, 2003 9:59 PM
> To: j400-l
> Subject: AS/400 JDBC Insert performance
>
>
>
> I need to insert 180,000 rows into an AS/400 table
> within a 4 hour period.  Currently the best I can do
> will come in at 8 hours.  I am pretty sure that I
> should be able to at least double this speed.
>
> I am using the JT/Open toolkit JDBC driver.  I'm
> using
> Prepared Statements and I'm commiting after each
> batch
> of 250 rows.  The odd thing is that batching up the
> commits at 250 rows doesn't appear to be any faster
> than single row commits.
>
> Is there anything I need to do on the AS/400 to
> improve performance?  Will changing the isolation
> level of my JDBC connection make any difference?
>
> Thanks,
> Allen.
>
> =====
>
>
> __________________________________
> Do you Yahoo!?
> SBC Yahoo! DSL - Now only $29.95 per month!
> http://sbc.yahoo.com
> _______________________________________________
> This is the Java Programming on and around the
> iSeries / AS400 (JAVA400-L)
> mailing list
> To post a message email: JAVA400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit:
> http://lists.midrange.com/mailman/listinfo/java400-l
> or email: JAVA400-L-request@xxxxxxxxxxxx
> 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@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit:
> http://lists.midrange.com/mailman/listinfo/java400-l
> or email: JAVA400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the
> archives
> at http://archive.midrange.com/java400-l.
>

=====


__________________________________
Do you Yahoo!?
SBC Yahoo! DSL - Now only $29.95 per month!
http://sbc.yahoo.com

------------------------------

message: 4
date: Tue, 8 Jul 2003 20:21:26 -0700 (PDT)
from: Allen <tayal01@xxxxxxxxx>
subject: RE: AS/400 JDBC Insert performance


I don't have a choice with the PreparedStatement type.
 I'm surprised to hear that a prepared statement with
bind variables might be less efficient on SQL Server
than a dynamic statement which would have to be fully
parsed and qualified before execution.

Cheers,
Allen.

--- "Hall, Philip" <phall@xxxxxxxx> wrote:
>
> > I am using the JT/Open toolkit JDBC driver.  I'm
> using
> > Prepared Statements >\
>
> Have you compared using non-prepared statements ? I
> know for SQL server it was actually slower using
> prepared statements, than dynamically building the
> SQL in a string buffer for each row.
>
> Also, bulk loading is the fastest, but only
> SQLServer offers a SQL statement to do it.
>
> In DB2 you use the db2 command line and the LOAD
> sub-command, Oracle uses the sqlldr command.
>
> --phil
>
> _______________________________________________
> This is the Java Programming on and around the
> iSeries / AS400 (JAVA400-L) mailing list
> To post a message email: JAVA400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit:
> http://lists.midrange.com/mailman/listinfo/java400-l
> or email: JAVA400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the
> archives
> at http://archive.midrange.com/java400-l.
>

=====


__________________________________
Do you Yahoo!?
SBC Yahoo! DSL - Now only $29.95 per month!
http://sbc.yahoo.com

------------------------------

message: 5
date: Wed, 9 Jul 2003 10:01:07 +0530
from: "Mahmood, Anwar (Cognizant)" <MANWAR@xxxxxxxxxxxxxxxxx>
subject: JDBC Connection to V4R1

Hi, I want to make a JDBC connection from a unix m/c to an AS/400 V4R1. I'll be grateful if someone can tell me the pre requisites for that. I am trying with jt400 and all the PTF's installed in AS/400. But still i am not able to make the connection.

Thanks in advance.

regards,
Anwar

-------------- next part -------------- This e-mail and any files transmitted with it are for the sole use of the intended recipient(s) and may contain confidential and privileged information. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message. Any unauthorised review, use, disclosure, dissemination, forwarding, printing or copying of this email or any action taken in reliance on this e-mail is strictly prohibited and may be unlawful.

Visit us at http://www.cognizant.com

------------------------------

message: 6
date: Wed, 9 Jul 2003 02:43:42 -0400
from: "Joe Sam Shirah" <jshirah@xxxxxxxxxxxxx>
subject: Re: AS/400 JDBC Insert performance

Hi Allen,

    There's probably quite a lot that can be done, but from the information
given, the only thing that can be said with certainty is that the native
driver should be faster IF, of course, the application is running on the
'400.  Any particular basis for being "pretty sure" you can double the
speed?

> Is there anything I need to do on the AS/400 to
> improve performance?

Probably, but we have no information about the data or your application.

> Will changing the isolation
> level of my JDBC connection make any difference?

Yes.

> Do you Yahoo!?

No.

Joe Sam

Joe Sam Shirah -        http://www.conceptgo.com
conceptGO         -        Consulting/Development/Outsourcing
Java Filter Forum:       http://www.ibm.com/developerworks/java/
Just the JDBC FAQs: http://www.jguru.com/faq/JDBC
Going International?    http://www.jguru.com/faq/I18N
Que Java400?             http://www.jguru.com/faq/Java400

----- Original Message -----
From: "Allen" <tayal01@xxxxxxxxx>
To: "j400-l" <java400-l@xxxxxxxxxxxx>
Sent: Tuesday, July 08, 2003 10:59 PM
Subject: AS/400 JDBC Insert performance


>
> I need to insert 180,000 rows into an AS/400 table
> within a 4 hour period.  Currently the best I can do
> will come in at 8 hours.  I am pretty sure that I
> should be able to at least double this speed.
>
> I am using the JT/Open toolkit JDBC driver.  I'm using
> Prepared Statements and I'm commiting after each batch
> of 250 rows.  The odd thing is that batching up the
> commits at 250 rows doesn't appear to be any faster
> than single row commits.
>
> Is there anything I need to do on the AS/400 to
> improve performance?  Will changing the isolation
> level of my JDBC connection make any difference?
>
> Thanks,
> Allen.
>
> =====
>
>
> __________________________________
> Do you Yahoo!?

------------------------------

message: 7
date: Wed, 9 Jul 2003 00:02:09 -0700 (PDT)
from: Allen <tayal01@xxxxxxxxx>
subject: Re: AS/400 JDBC Insert performance

Joe,

--- Joe Sam Shirah <jshirah@xxxxxxxxxxxxx> wrote:
>
>     Hi Allen,
>
>     There's probably quite a lot that can be done,
> but from the information
> given, the only thing that can be said with
> certainty is that the native
> driver should be faster IF, of course, the
> application is running on the
> '400.  Any particular basis for being "pretty sure"
> you can double the
> speed?

I truly believe that I should be seeing a noticeable
performance benefit from batching my commits.

>
> > Is there anything I need to do on the AS/400 to
> > improve performance?
>
>     Probably, but we have no information about the
> data or your application.
>

What sort of information would you like?

> > Will changing the isolation
> > level of my JDBC connection make any difference?
>
>     Yes.

So what is the best for an INSERT scenario?

> > Do you Yahoo!?
>
>     No.
>

Sorry no control over this. :-)

>
>
>     Joe Sam
>
> Joe Sam Shirah -        http://www.conceptgo.com
> conceptGO         -
> Consulting/Development/Outsourcing
> Java Filter Forum:
> http://www.ibm.com/developerworks/java/
> Just the JDBC FAQs: http://www.jguru.com/faq/JDBC
> Going International?
> http://www.jguru.com/faq/I18N
> Que Java400?
> http://www.jguru.com/faq/Java400
>
>
>
> ----- Original Message -----
> From: "Allen" <tayal01@xxxxxxxxx>
> To: "j400-l" <java400-l@xxxxxxxxxxxx>
> Sent: Tuesday, July 08, 2003 10:59 PM
> Subject: AS/400 JDBC Insert performance
>
>
> >
> > I need to insert 180,000 rows into an AS/400 table
> > within a 4 hour period.  Currently the best I can
> do
> > will come in at 8 hours.  I am pretty sure that I
> > should be able to at least double this speed.
> >
> > I am using the JT/Open toolkit JDBC driver.  I'm
> using
> > Prepared Statements and I'm commiting after each
> batch
> > of 250 rows.  The odd thing is that batching up
> the
> > commits at 250 rows doesn't appear to be any
> faster
> > than single row commits.
> >
> > Is there anything I need to do on the AS/400 to
> > improve performance?  Will changing the isolation
> > level of my JDBC connection make any difference?
> >
> > Thanks,
> > Allen.
> >
> > =====
> >
> >
> > __________________________________
> > Do you Yahoo!?
>
>
> _______________________________________________
> This is the Java Programming on and around the
> iSeries / AS400 (JAVA400-L) mailing list
> To post a message email: JAVA400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit:
> http://lists.midrange.com/mailman/listinfo/java400-l
> or email: JAVA400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the
> archives
> at http://archive.midrange.com/java400-l.
>

=====


__________________________________
Do you Yahoo!?
SBC Yahoo! DSL - Now only $29.95 per month!
http://sbc.yahoo.com

------------------------------

message: 8
date: Wed, 9 Jul 2003 09:24:56 +0200
from: Dieter.Bender@xxxxxxxxxxx (Dieter Bender)
subject: Re: JDBC Connection to V4R1

Anwar,

please give us more information: what error do you get at wich step of your
program.

just a guess: maybe the database serves on the as400 are not started, or a
configuration entry for the database is missing.

Dieter

On Wednesday 09 July 2003 06:31, you wrote:
> Hi,
> I want to make a JDBC connection from a unix m/c to an AS/400 V4R1. I'll be
> grateful if someone can tell me the pre requisites for that. I am trying
> with jt400 and all the PTF's installed in AS/400. But still i am not able
> to make the connection.
>
> Thanks in advance.
>
> regards,
> Anwar
> _______________________________________________
> This is the Java Programming on and around the iSeries / AS400 (JAVA400-L)
> mailing list To post a message email: JAVA400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/java400-l
> or email: JAVA400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/java400-l.

--
mfG

Dieter Bender


DV-Beratung Dieter Bender
Wetzlarerstr. 25
35435 Wettenberg
Tel. +49 641 9805855
Fax +49 641 9805856
www.bender-dv.de

------------------------------

message: 9
date: Wed, 9 Jul 2003 09:21:46 +0200
from: Dieter.Bender@xxxxxxxxxxx (Dieter Bender)
subject: Re: AS/400 JDBC Insert performance

Allen,

the easiest way to speed up such bulk inserts is to do it multithreaded with
multiple connections. this way the database workload is splitted to multiple
jobs, one for each connection.
8 hours for 180.000 rows = 166 Milliseconds per simple transaction is very
slow. I would have a look to the database monitor, what the job is doing all
the time. The summary by operations shows up the average for each sql
operation. normally an insert should be in the range of  < 2 milliseconds .

Is the table loaded from zero records?
does the job start fast and is getting slower?

Dieter

On Wednesday 09 July 2003 04:59, you wrote:
> I need to insert 180,000 rows into an AS/400 table
> within a 4 hour period.  Currently the best I can do
> will come in at 8 hours.  I am pretty sure that I
> should be able to at least double this speed.
>
> I am using the JT/Open toolkit JDBC driver.  I'm using
> Prepared Statements and I'm commiting after each batch
> of 250 rows.  The odd thing is that batching up the
> commits at 250 rows doesn't appear to be any faster
> than single row commits.
>
> Is there anything I need to do on the AS/400 to
> improve performance?  Will changing the isolation
> level of my JDBC connection make any difference?
>
> Thanks,
> Allen.
>
> =====
>
>
> __________________________________
> Do you Yahoo!?
> SBC Yahoo! DSL - Now only $29.95 per month!
> http://sbc.yahoo.com
> _______________________________________________
> This is the Java Programming on and around the iSeries / AS400 (JAVA400-L)
> mailing list To post a message email: JAVA400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/java400-l
> or email: JAVA400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/java400-l.

--
mfG

Dieter Bender


DV-Beratung Dieter Bender
Wetzlarerstr. 25
35435 Wettenberg
Tel. +49 641 9805855
Fax +49 641 9805856
www.bender-dv.de

------------------------------

message: 10
date: Wed, 9 Jul 2003 13:22:31 +0530
from: "Mahmood, Anwar (Cognizant)" <MANWAR@xxxxxxxxxxxxxxxxx>
subject: RE: JDBC Connection to V4R1

Hi, Thanx for replying. I am just trying to make a connection. One simple class. I load the driver and use ConnectionManager to get a connection. There it self it fails. I have the host servers have been started. Can u plz give some more detail abt the Database configuration entry u mentioned in ur reply??

Regards,
Anwar

-----Original Message-----
From: Dieter Bender [mailto:Dieter.Bender@xxxxxxxxxxx]
Sent: Wednesday, July 09, 2003 12:55 PM
To: Java Programming on and around the iSeries / AS400
Subject: Re: JDBC Connection to V4R1

Anwar,

please give us more information: what error do you get at wich step of your
program.

just a guess: maybe the database serves on the as400 are not started, or a
configuration entry for the database is missing.

Dieter

On Wednesday 09 July 2003 06:31, you wrote:
> Hi,
> I want to make a JDBC connection from a unix m/c to an AS/400 V4R1. I'll be
> grateful if someone can tell me the pre requisites for that. I am trying
> with jt400 and all the PTF's installed in AS/400. But still i am not able
> to make the connection.
>
> Thanks in advance.
>
> regards,
> Anwar
> _______________________________________________
> This is the Java Programming on and around the iSeries / AS400 (JAVA400-L)
> mailing list To post a message email: JAVA400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/java400-l
> or email: JAVA400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/java400-l.

--
mfG

Dieter Bender

DV-Beratung Dieter Bender Wetzlarerstr. 25 35435 Wettenberg Tel. +49 641 9805855 Fax +49 641 9805856 www.bender-dv.de _______________________________________________ This is the Java Programming on and around the iSeries / AS400 (JAVA400-L) mailing list To post a message email: JAVA400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options, visit: http://lists.midrange.com/mailman/listinfo/java400-l or email: JAVA400-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/java400-l.

-------------- next part -------------- This e-mail and any files transmitted with it are for the sole use of the intended recipient(s) and may contain confidential and privileged information. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message. Any unauthorised review, use, disclosure, dissemination, forwarding, printing or copying of this email or any action taken in reliance on this e-mail is strictly prohibited and may be unlawful.

Visit us at http://www.cognizant.com

------------------------------

_______________________________________________ This is the Java Programming on and around the iSeries / AS400 (JAVA400-L) digest list To post a message email: JAVA400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options, visit: http://lists.midrange.com/mailman/listinfo/java400-l or email: JAVA400-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/java400-l.

End of JAVA400-L Digest, Vol 1, Issue 737
*****************************************


Paul Holm
Java and WebSphere Senior Consultant
PlanetJ Corporation
pholm@xxxxxxxxxxxxxxxxx
Work: (760)-432-0600
Fax: (760)-432-0600
Web site:  www.planetjavainc.com