RE: System Slowdowns w/ODBC jobs

Some more information:  We have 12 internal drives on ASP1  4 70g and 8 35g
drives.  Our load source drives are the 4 70g drives and they are mirrored
and we also do bus level mirroring.  When we see the disk % busy go up to
90+ it is only the load source drives.  The other drives in the ASP are at
less then 15%.
When it is creating the temp indexes why would it only be driving the load
source drives so hard?

John Bresina Jr
Sr Server Engineer - Midrange Team
Allianz Life of North America
5701 Golden Hills Drive
Minneapolis, Mn 55416
763 582 6761


                                                                           
             "Elvis Budimlic"                                              
             <ebudimlic@center                                             
             fieldtechnology.c                                          To 
             om>                       "'Midrange Systems Technical        
             Sent by:                  Discussion'"                        
             midrange-l-bounce         <midrange-l@xxxxxxxxxxxx>           
             s@xxxxxxxxxxxx                                             cc 
                                                                           
                                                                   Subject 
             06/14/2006 11:42          RE: System Slowdowns w/ODBC jobs    
             AM                                                            
                                                                           
                                                                           
             Please respond to                                             
             Midrange Systems                                              
                 Technical                                                 
                Discussion                                                 
             <midrange-l@midra                                             
                 nge.com>                                                  
                                                                           
                                                                           




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



--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.




-----------------------------------------
CONFIDENTIALITY NOTICE:  The information in this message, and any
files transmitted with it, is confidential, may be legally
privileged, and intended only for the use of the individual(s)
named above.  Be aware that the use of any confidential or personal
information may be restricted by state and federal privacy laws.
If you are not the intended recipient, do not further disseminate
this message.  If this message was received in error, please notify
the sender and delete it.