Re: I almost dropped the system due to SQL using large amounts for resources

[CRPence <CRPbottle@xxxxxxxxx>]

  The Class object [*CLS] for the Subsystem Description [*SBSD] 
determines the limit to the maximum temporary storage for the jobs 
running within that subsystem.  Take note that the outcome for exceeding 
the value is request termination [no inquiry], so take care in its 
setting, to avoid interrupting important work that actually has the 
justification for using that much temp storage.  If for instance no one 
job in that Subsystem should ever be able to take 5% of the total ASP in 
temporary storage, then changing the class [CHGCLS] to set the Maximum 
Temporary Storage limit [MAXTMPSTG] to represent that percentage as a 
limit, could prevent a poorly formed [e.g. join which is accidentally 
Cartesian product] or a poorly implemented query from taking extreme 
amounts of temporary storage.

  I am not sure if any QAQQINI option exists to influence the query 
against implementations that require large amounts of temporary storage, 
but that might be something to review.  But by the V5R4 of i5/OS there 
is a Query Storage Limit [QRYSTGLMT] setting on CHGQRYA.  Additionally 
the Query Time Limit [QRYTIMLMT] on CHGQRYA or CHGSYSVAL QQRYTIMLMT may 
be able to report a query that would also use large amounts of temporary 
storage [if not on V5R4]; i.e. a query which will take many minutes may 
reflect one which requires a lot of temporary storage.  Exceeding the 
time limit is inquired for a response, and there is a somewhat recent 
added function [specific to that feature vs the generic inquiry message 
exit] to respond by a programmed response versus user response via the 
Query Governor Exit Program; for search, QIBM_QQQ_QUERY_GOV exit point 
format QRYG0100, or more generically search just QQRYTIMLMT on any 
release doc.

  For permanent storage, for example for an INSERT INTO SELECT FROM bad 
join [too much data, not too much tempstg], the Maximum Storage [MAXSTG] 
on the User Profile [*USRPRF] object can be limited by CHGUSRPRF command.

Regards, Chuck

Tim Gornall wrote:
> We have a SQLRPGLE program that dynamically builds the SQL statement
> based on user input.  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 processor, but was surprised
> yesterday when I noticed the the system ASP went 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 interactively 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 resources the job can allocate?  Both processor
> and ASP. Basically put some type of governor on SQL.  Ideally it
> would be a global setting for all SQL run on the machine.