|
Paul, if you have a look at the time, the inserts need, there must be another problem. Dieter On Wednesday 09 July 2003 16:16, you wrote: > 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 > _______________________________________________ > 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
As an Amazon Associate we earn from qualifying purchases.
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.