RE: I almost dropped the system due to SQL using large amounts forresources

I'm guessing the problem is temporary storage used by the query engine.

From:
http://forums.systeminetwork.com/isnetforums/showthread.php?t=49102


"New in V5R4, there is a new STORAGE_LIMIT parameter of QAQQINI control file that controls how much
temp disk space a job can allocate when using CQE or SQE. This is to help avoid the incident above.
Pre-V5R4, you can use the QRYTIMLMT parameter of CHGQRYA command or QUERY_TIME_LIMIT parameter of
QAQQINI to contol it in an indirect manner (jobs that tend to allocate lots of temp disk during its
run-time is quite likely to take long to run)."

Not a bad idea to set this for your protection, but IMHO it's not a fix.

The fix is to change the query and/or create indexes so that the query engine won't need so much temp
space in the first place.


Charles Wilt
--
Software Engineer
CINTAS Corporation - IT 92B
513.701.1307

wiltc@xxxxxxxxxx

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-
bounces@xxxxxxxxxxxx] On Behalf Of Tim Gornall
Sent: Friday, May 30, 2008 10:47 AM
To: midrange-l@xxxxxxxxxxxx
Subject: I almost dropped the system due to SQL using large amounts
forresources


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


--
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 e-mail transmission contains information that is intended to be confidential and privileged. If you receive this e-mail and you are not a named addressee you are hereby notified that you are not authorized to read, print, retain, copy or disseminate this communication without the consent of the sender and that doing so is prohibited and may be unlawful. Please reply to the message immediately by informing the sender that the message was misdirected. After replying, please delete and otherwise erase it and any attachments from your computer system. Your assistance in correcting this error is appreciated.