RE: System Slowdowns w/ODBC jobs

This kind of performance tuning is an iterative process.  I recommend:

1. Building the recommended indexes.  At least one or two of them,
anyways, to gauge their impact.
2. Review the SQL being run via ODBC.  Look for things like TRIMs and
other data manipulations that would be CPU/disk intense.
3. Tune the ODBC settings (cache, etc.) on the PC.
4. Tune the QZDASONINT and QAQQINI settings.

For 3 & 4, there are some good docs in InfoCenter and the RedBooks.

We recently got some Crystal Reports jobs down from 3.5-4 minutes down
to about 3 seconds by mostly just reworking the SQL and creating a
couple of indexes.  Our issue was mostly CPU and not disk, but the same
concepts should apply.  We worked with IBM/Rochester on the tuning, but
it's really just running the performance tools, DBMon, and the SQL
Analyzer in iNav and interpreting the results.

If all else fails, i.e. you've iteratively done the above until you've
reached the point of diminishing returns, since your issue seems to be
manifesting itself with high DASD % busy, consider the SPENDMONEY
command and add memory and/or more disk arms to the ASP.

John A. Jones, CISSP
Americas Information Security Officer
Jones Lang LaSalle, Inc.
V: +1-630-455-2787 F: +1-312-601-1782
john.jones@xxxxxxxxxx

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
John_Bresina@xxxxxxxxxxxxxxx
Sent: Wednesday, June 14, 2006 8:39 AM
To: Midrange Systems Technical Discussion
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
Sr Server Engineer - Midrange Team
Allianz Life of North America
5701 Golden Hills Drive
Minneapolis, Mn 55416
763 582 6761


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

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



This email is for the use of the intended recipient(s) only.  If you have 
received this email in error, please notify the sender immediately and then 
delete it.  If you are not the intended recipient, you must not keep, use, 
disclose, copy or distribute this email without the author's prior permission.  
We have taken precautions to minimize the risk of transmitting software 
viruses, but we advise you to carry out your own virus checks on any attachment 
to this message.  We cannot accept liability for any loss or damage caused by 
software viruses.  The information contained in this communication may be 
confidential and may be subject to the attorney-client privilege. If you are 
the intended recipient and you do not wish to receive similar electronic 
messages from us in the future then please respond to the sender to this effect.