I would like to know if there is any value that I can change. How do I fine-tune my system for best performance? I there any book I can refer to? Is there any step-by-step procedure to fine-tune an iSeries? Is there any site on the Internet that I can refer to?
If your main problem is many interactive queries slowing the system to a crawl, you should consider only allowing queries to be run in batch. That will allow the higher priority system and interactive jobs to get the CPU they need for good response times. This can easily be done by doing a CHGCMD RUNQRY and removing *INTERACT from the ALLOW (Where allowed to run) parameter. Your users can then use Option 8 in WRKQRY to submit the query to batch.
If you are concerned only with a few long running queries, you might try changing the Query Attributes in your users' initial program. If a user's initial program has CHGQRYA QRYTIMLMT(30) in it, for example, this user will receive an error message if the estimated run time for this query exceeds 30 seconds. This message, CPA4259 - Estimated query processing time exceeds limit, allows valid replies of Cancel or Ignore. To keep users from ignoring this message, you can either change the message to only allow users to enter Cancel or you can add a system reply list entry to automatically cancel when this message is received. If the user then tries to run a long running query interactively, he will only see message QRY2293 - Query cannot be run. See lower level messages.
If you have only a few specific Query users who are slowing everyone else down, you could try changing their job class to run at priority 21. This should provide quick response times to the average interactive user without requiring the Query users to submit their queries to batch. To do this, first create a new class (or copy your existing QINTER class and change the priority), then set the Routing data in the user's Job Description and add a Routing Entry to the QINTER subsystem to pick up the new class. Use F11 on the WRKACTJOB display to verify that these users are using the new class.
Ron Turull has written more on changing the Query Attributes at https://search400.techtarget.com/tip/Use-the-CHGQRYA-command-to-help-lessen-interactive-workloads, and Paul Tuohy gives an example of a custom WRKQRY command that incorporates this at https://search400.techtarget.com/tip/Take-control-of-Query.
As far as performance tuning goes, the best advice I can give is to separate your batch work from the *BASE storage pool. See http://www-1.ibm.com/support/docview.wss?uid=nas19fa68bd7573e48af862565c2007d3d9b&rs=110 for more information on this. Then let QPFRADJ automatically tune your pools. There is a Performance Management Redbook at http://www.redbooks.ibm.com/redbooks/pdfs/sg244735.pdf. It's getting to be an older book, but the basic concepts still apply. The bottom line is that if your system CPU runs above 80% more than 80% of the time, tuning may not be enough. You may have to break down and consider an upgrade.
Dig Deeper on Performance
Related Q&A from Scott Ingvaldson
When encountering problems using the SNDDST command to send a *LMSG on the iSeries, specify a valid SMTP name in the directory entry of the sender to... Continue Reading
After extending storage capacity on an IBM 520, the system performance depends on the workload amount and should not be affected negatively as long ... Continue Reading
On the AS/400 system, close all sessions in the receiving envrionment when transferring files from one environment to another if the sessions hold ... Continue Reading