iSeries Navigator has a tool in it called "Index Advisor" that may be helpful here as well. If someone is sorting or selecting data without a proper index, it can cause high CPU usage (especially if the table is large) since the system will build a temporary index.
You can also use Navigator to start a database performance monitor which will capture the queries (and the parameters used in prepared statements) along with execution statistics. It will also allow you to do a visual explain (same concept as Oracle's Explain Plan but unlike Explain Plan, it is generally easy to figure out what needs to be done to fix the issue).
Matt
------------------------------
message: 3
date: Fri, 7 May 2010 04:18:54 -0400
from: "Joe Sam Shirah" <joe_sam@xxxxxxxxxxxxx>
subject: Re: JDBC HIGH CPU and Restart Issue
Hi Paul,
The first thing I would look at is the actual queries running when you
see the problem. The QZDASOINT jobs don't have to do particularly with
Java; they are also used for ODBC database jobs, for example. In the days
before Java was even around, I saw a user manage to practically bring down
the machine and come dangerously close to filling the disk with a cartesian
join.
The only part of Pete's response (which seemed related to Tomcat running
*on* the AS/400) in your case that might help is looking at the run priority
and timeslice of the QZDASOINT jobs, but that would only be so they play
nice with other system users/jobs. I don't think it would address your base
problem.
I expect the jobs get restarted and continue because the connection pool
code detects that the connection failed (when you end the QZDASOINT jobs)
and just grabs another connection (which starts another QZDASOINT job) to
retry the query.
That's probably the best we can suggest without starting detailed
analysis, but let us know if the queries are the problem or you need to get
deper into it.
HTH,
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: <pholm@xxxxxxxxxxxxxxxxx>
To: <java400-l@xxxxxxxxxxxx>
Sent: Thursday, May 06, 2010 5:16 PM
Subject: JDBC HIGH CPU and Restart Issue
All,
I've found some related info on google but nothing definitive.
Environment: V5R4 iSeries acting as database with Tomcat and
JDBC/connection pools hosted on a Windows server.
Problem: Occasionally the QZDASOINT job servicing the JDBC will consume
most CPU. I've lowered the QZDASOINIT priority to 30 to lessen the
impact
to interactive users. However the problem and high cpu persist
occasionally. If we do a ENDJOB on these jobs they are restarted
automatically and resume using too much CPU. If we end Tomcat the jobs
go
away. Restarting Tomcat restarts the QZDASOINIT jobs. We have
connection
pool code but we do not have any logic to reissue JDBC queries to the
system. I suspect it to be an i5 issue. Has anyone experienced the
issue
or have insight?
Thanks, Paul Holm
--
midrange.com
