There are a number of ways to limit temporary storage use. I would be
cautious about setting the limit system wide, as you can break other
applications that are using SQL (once temp stg is exceeded, job is killed
abruptly, and some applications have a hard time recovering from this).
As for CPU, you can control if a job uses SMP or not, as well do some
resource throttling on a particular job (priority, time slice etc.), but if
you have a single CPU machine, you could still see a 'query from hell'
consume significant CPU resources.
That said, rather than govern, most of the time the right answer is to get
current on temporary storage leak PTFs (i.e. for V5R4:
http://www-912.ibm.com/s_dir/slkbase.nsf/ibmscdirect/064EED642D6D78A58625714
E0076157B) and follow some basic Indexing Strategy guidelines
(
http://www-03.ibm.com/servers/enable/site/bi/strategy/index.html).
HTH, Elvis
Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
OS/400
www.centerfieldtechnology.com
-----Original Message-----
Subject: I almost dropped the system due to SQL using large amounts for
resources
We have a SQLRPGLE program that dynamically builds the SQL statement based
on user imput. Sometimes the statement can fairly large and when it is it
uses very large amounts of both processing and storage resources. I have
seen it gobble up processer, but was surprised yesterday when I noticed the
the system ASP when through the roof. The ASP was up to 93 percent and
climbing when I got word. As soon as I ended the job the ASP came back down
almost immediately. I backed out the process completely until I can be
assured this won't happen again.
The job is run interactivly within an order entry process. We implemented
it last week and instantly noticed slow system response when large queries
are run. As a quick fix, I change the job priority within the program
before the sql runs and then change it back afterwards. This makes it run a
bit slower for the user but the system does not take the hit.
But now with the ASP issue, I need a real fix. Is there a way to to limit
the amount of resourses the job can alocate? Both processer and ASP.
Basically put some type of govenor on SQL. Idealy it would be a global
setting for all SQL run on the machine.
Thanks, Tim
midrange.com
