RE: System Slowdowns w/ODBC jobs

Sounds like SQL processing requires significant amount of temporary storage
to satisfy query requests.  This is normal processing for SQL.  Especially
if you don't have good indexing strategy in place already.

DBMON is the best tool to use for SQL tuning so you're on the right track
there.  SQL performance tuning is hard but rewards can be phenomenal.  Check
out iSeries Navigator Performance Monitors, Indexing Strategy white paper,
SQL Performance Diagnosis redbook...

What I've seen in working with our customers is that often building indexes
is not the best alternative but rather figuring out who's running what, is
it necessary and is it possible to change the queries themselves so they
don't eat up all that temporary storage unnecessarily.  As a friend of my
always says, "Best I/O is one never done".

BTW, newer releases of the OS and DB fixpacks are much better in terms of
requiring less temporary storage for certain types of queries.  I recall
V5R2 as being particularly temp-store hungry release.

If you get into a purely reactive mode and have to put out fires
immediately, there is always a limit on the temporary storage you can put on
the class object.  That would solve the temp-store issue at the expense of
ending the jobs prematurely, so it's only appropriate for ad-hoc interactive
user query environments (STRSQL, ODBC with end-users on the other end etc.).

Hope that helps.

Elvis

-----Original Message-----
Subject: System Slowdowns w/ODBC jobs

We are seeing periodic system slowdowns with our ODBC jobs that connect to
our 570.  When I get alerted to the slowdowns the first thing I look at is
the WRKDSKSTS screen and our Internal drives on the system asp are running
at 90+ %.  Then when I look at WRKSYSSTS screen I see the following

Current unprotect used . : 61314 M

 This seems really high to me.  When the slowdowns stop that same thing
shows  18316 M.
 I have run the DBMON and produced some reports that show some indexes that
should be built.  What else should I be looking at.  We are getting into a
very busy time of year for us and these slowdowns cannot continue.


John Bresina Jr