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

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.