Re: Query Estimator (Was: Interactive Feature...)

Hi Chris

The answer I came up with is to front the WRKQRY command with a program
that will do something along the lines of:

CHGJOB     INQMSGRPY(*DFT)
CHGQRYA    QRYTIMLMT(300)
WRKQRY
CHGJOB     INQMSGRPY(&INQMSGRPY)
CHGQRYA    QRYTIMLMT(*SYSVAL)

Firstly I change the Inquiry message default for the job so that the
default reply is taken. I may have changed the message default regarding
time limit exceeded from query to "C" - I cannot remember what it was; if
the default action is not to cancel, the user will merely get an option to
Ignore the warning. I guess you may wish to take advantage of this feature,
but in any event will need to consider the needs of your site.

The CHGQRYA changes the optimizer time limit for the job (and because I
reset it later) effectively only limits it for the duration of the WRKQRY
session for that user. The Inquiry message default for the job is also
changed back to what it was before query was called. (Actually I've just
noticed that I could improve on that but the site I set this up for never
fooled with this parameter)

The program that runs this will need job control authority as I recall so
you may need to compile under a specific profile for this to work, again
depending on your site standards.

As someone else pointed out elsewhere, changing the global query time limit
will have drastic effects. If I remember rightly it will can SQL and
OPNQRYF statements :) Of course, I never tested this in production.

Hope this helps, its worked for me in a site where there were some real
query hogs - people were running queries that could take all day
*interactively*. Once I had their agreement in principle that interactive
queries were bad and only required in exceptional circumstances, they were
devastated at how well this system worked.

I also forced their queries to a jobqueue that didn't run during the day so
for those "urgent queries" they had to request that the job be specifically
released - this meant I was also able to check it before it ran and monitor
its progress. It was great from a system/response time management point of
view.

regards
Evan Harris

Evan, you stated "This is any easy one to fix based on how long the
estimator thinks the query will run. You can force everything over x
minutes to batch rather than letting your users decide."

That sounds great, but for the life of me I cannot find where that setting
is located. Does anyone have more information on that?

Chris Whisonant
Comporium
IBM Certified Specialist
(803) 326-7270
mailto:chris.whisonant@comporium.com