RE: SQL Insert -- JAVA400-L

Hi Suresh,
I'd recommend you to try different JDBC driver. Try one from HIT Software at
http://www.hitsw.com/products_services/sql400/jdbc400/jdbc400.html
They offer free of charge evaluation version and very good tech support. We 
tried one and we've got noticeable performance improvement.
We didn't buy one because we moved our app server to AS/400 and now use 
'native' JDBC for data access (which is even better)
Alex

-----Original Message-----
From: Suresh kumar BV [mailto:suresh@xxxxxxxxxxxxxxxxxx]
Sent: Monday, April 07, 2003 3:28 PM
To: java400-l@xxxxxxxxxxxx
Subject: Re: SQL Insert


Hi Dieter,
   In the last week this is what we have done to find the possible bottle
neck in the system:

   We tried to run the application in isolation when almost the entire
system was at the disposal (9406-720, 2GB memory, 50% DASD utilization,
DZDASOINIT runs in machine pool etc...). This is what we have seen:

1. DB Monitor shows that actual SQL time is negligible. It takes about 1.8
seconds to perform SQLs involved (about 25 SQLs). We noticed that OPEN and
DE (I don't know what is this -- description??) operations seem to take
most time out of this 1.8 seconds. Question is how do we make the opens
reuse ODPs? In our case every SQL seems to do full open. Note that we are
using JT400 to access AS400 data from servlets which are running on a PC
Webserver. Other question is what is this DE record and how do I reduce the
overhead there??

2. Overall response time is about 15-20 seconds. We feel that once the data
is obtained by servlet it should not take more than 1-2 seconds to create
the HTMl with images and render the page. Where else is the time lost?? Is
this thanx to JT400?? Note that the high speed PC server and AS400 are on
the LAN next to each other.

Any suggestion would be helpful
Suresh


----- Original Message -----
From:Dieter.Bender@xxxxxxxxxxx (Dieter Bender)
To:Java Programming on and around the iSeries / AS400
<java400-l@xxxxxxxxxxxx>
Subject:Re: SQL Insert

> Hi Suresh,
> 
> the test app runs on a pc as swing application, 10mb Ethernet, a little
> as400 
> 170, I'm using the toolbox driver.
> 
> On Monday 31 March 2003 14:53, you wrote:
> > HI Dieter,
> >   I am surprised to see the kind of performance you are getting out of
> your
> > AS400 box.  Are u running the app natively on AS400? In our case, we
> are
> > usng JT400 for DB access and the java servlets run on a PC server
under
> IBM
> > WAS 4.0 app server. Both PC server and AS400 are on the TCP/IP LAN
> network
> > wth the speed 10/100 MBPS. Also, the AS400 is pretty high end
> (9406-720)
> > runnng V4R4 and is about 45% utilized. Plenty of disk space (80GB),
> memory
> > (2GB) and processor speed available.
> >
> >  Do you think network config and/or JT400 is givign raise to this kind
> of
> > poor performance??
> 
> there must be a bottleneck, maybe main storage, maybe disk arms,
> maybe...
> the point to start - in my experience - is the database monitor and 
> afterwards having alook at the paging rates of the pools, the disk i/o
> and 
> the network traffic.
> starting the database monitor (it's part of the oops nav ...) with all 
> details, I would have alook at the detailed single operations (3rd card
> left 
> column first check box) they are sorted by time descending. second look
> at 
> the summary of operations, a single database access has to be in the
> region 
> of 1 millisecond in average.
> maybe somebody has done a little bit "optimization" on this box. I've
> seen 
> boxes with 200 mb mainstorage for the database server jobs, trying to
> scratch 
> holes in the 4 17 GB DASD.
> 
> Dieter
> 
> 
> 
> >
> > Suresh
> >
> > ----- Original Message -----
> > From: "Dieter Bender" <Dieter.Bender@xxxxxxxxxxx>
> > To: "Java Programming on and around the iSeries / AS400"
> > <java400-l@xxxxxxxxxxxx>
> > Sent: Thursday, March 27, 2003 2:46 PM
> > Subject: Re: SQL Insert
> >
> > > Hi Suresh,
> > >
> > > that seems quite a lot for me. On my little 170 it takes about 8
> seconds
> >
> > to
> >
> > > connect to the database and poll 10.000 records into a swing
> TableModel
> > > without any performance tuning, using plain JDBC or even SQLJ. Using
> a
> >
> > real
> >
> > > databasee Server, this should be finished in 1 or 2 seconds, or even
> > > less. you should not invest your time in finding work arounds
> (stored
> >
> > procedures,
> >
> > > record level access, ...) first thing should be to find the reason
> for
> > > the problem. what is the system doing all the time and wich
> component
> > > causes
> >
> > the
> >
> > > problem.
> > > Did you have a look with the Database Monitor to find out how long
> every
> > > request (Connect, open cursors, fetching records) to the database
> took???
> > > Did you have a look to the overall workload (CPU, memory, disk
> activity,
> > > network traffic) of the system with management central to find the
> > > bottleneck???
> > >
> > > Dieter
> > >
> > > On Thursday 27 March 2003 06:53, you wrote:
> > > > Hi Dieter,
> > > >   This is the statistics:
> > > > 1. We do about 25 SQL statements against 4 tables.
> > > >
> > > > 2. All the tables are small (about 100 records/table) except one
> which
> >
> > has
> >
> > > > about 10,000 records.
> > > >
> > > > 3. Ultimately we end up getting about 60-70 records from these
> tables
> >
> > which
> >
> > > > is needed for processing by the servlet (at the App server).
> > > >
> > > > 4. I have 2 questions in connection with this:
> > > >    a. In the stored procedure (written in RPGLE), we are basically
> > > > peforming various SQLs and returning the result set cursor back to
> the
> >
> > Java
> >
> > > > client program. This RPG program declares the cursors (SQLs), open
> the
> > > > cursors and return them: all together about 15 result sets. Other
> > > > option
> >
> > is
> >
> > > > to call the RPG program repeatedly so that it will send back
> proper
> >
> > cursor
> >
> > > > one at a time. Which will be faster??
> > > >
> > > >    b. Also, is it faster with array result sets as opposed to
> cursor
> >
> > result
> >
> > > > sets?? How can the client read an array result set in the Java
> servlet?
> > > >
> > > > Suresh
> > > > Antares Systems Ltd.
> > > > # 24, Sudha Complex, 3rd Stage, 4th Block,
> > > > Basaveshwaranagar,
> > > > Bangalore - 560 079
> > > > Telefax : 91-80-3284311, 3284313
> > > > _______________________________________________
> > > > 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.
> > Antares Systems Ltd.
> > # 24, Sudha Complex, 3rd Stage, 4th Block,
> > Basaveshwaranagar,
> > Bangalore - 560 079
> > Telefax : 91-80-3284311, 3284313
> > _______________________________________________
> > 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.
>