Re: SQL Insert -- JAVA400-L

Hi, 

I'm in hurry and going out of office for 10 days,
please send me another mail latter!
some comments in the text

On Monday 07 April 2003 22:27, you wrote:
> 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). 
looks quite good

We noticed that OPEN and
> DE 
I assume direct execution

(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. 

If an open of a cursor takes time, there might be an index missing.

start the db monitor before making connection for all jobs and you will see 
debug messages.


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??

use prepared statements, wherever possible and cash already prepared 
statements for further use.

>
> 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. 

normally milliseconds!!!

Where else is the time lost?? Is
> this thanx to JT400?? 

No, its not the driver (try more than 1 version and recent ones -> JTOPEN

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.
> 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