RE: Limiting (but not stopping) interactive query by user -- MIDRANGE-L

Al Barsa's explanation and cautions about the query time estimates are
accurate.  I've set up two queries off of the same 18 million record file.
One just lists all fields, all records with no selects or sorts.  The second
is a copy of the first with a complex selection not supported by any key.

The first query comes up with it's completed "query" instantaneously -- 
Query running.  18445342 records selected.  Selection complete.
...then proceeds to read the data and dump it to spool for over 15 minutes.

The second query spends 3-4 minutes cycling through its selection --
Query running.  xxxxx records selected, xxxxxxx processed.
...and produces its results in less than six minutes total.

With a 30 second limit CHGQRYA in effect for the interactive job both
queries come up with the same estimate.  The CPA4359 message is the same:
Estimated query processing time 226 exceeds limit 30 (C I) 

I've run the same queries over and over today, and the estimates have not
changed by more than a second.

It doesn't seem very scientific.  If you wanted to limit users to five
minutes of interactive execution this wouldn't stop the six minute or
fifteen minute processes.  The estimate is under four minutes.

I'm sure actual long term results will vary, but it doesn't look like a
practical solution.

-Jim



-----Original Message-----
From: Jim Damato [mailto:jdamato@xxxxxxxxxxxxxxxxx]
Sent: Friday, December 05, 2003 11:47 AM
To: 'Midrange Systems Technical Discussion'
Subject: RE: Limiting (but not stopping) interactive query by user


I know about Extended Help.  From my last message the paragraph "The Change
Query Attributes (CHGQRYA) command specifies..."  was cut directly from the
Extended Help.

To me that's the source of the ambiguity.  I'm not saying you're wrong, but
the extended help suggests that there's a difference between a query and a
query.  I suppose I could find a nice clean WRKQRY query performing trivial
i/o on a monster table and see if CHGQRYA throttles it.  I don't know
whether the QRYTIMLMT parameter will affect only the opening of the view, as
the text suggests, or also affect the subsequent i/o.

If I find time for a test I'll let you know.

-Jim

-----Original Message-----
From: Mark S. Waterbury [mailto:mark.s.waterbury@xxxxxxx]
Sent: Friday, December 05, 2003 10:57 AM
To: Midrange Systems Technical Discussion
Subject: Re: Limiting (but not stopping) interactive query by user


Hi, Jim:

All query processing in OS/400 eventually invokes a query "engine",
the API *PGM in QSYS named QQQQRY. This is true for SQL,
whether running interactive SQL (STRSQL) or running a compiled
program that uses "embedded" SQL, and for Query Manager/400
queries, and Query/400 *QRYDFNs (RUNQRY), and when using
the OPNQRYF command in CL programs. Most third-party ISV
query tools (like ASC's SEQUEL) also invoke QQQQRY, either
directly, or indirectly (e.g. by using SQL/400 or QM/400, etc.).

QQQQRY is documented under OS/400 Programming, APIs in
the InfoCenter and in the API manuals.

Prompt the CHGQRYA command and press F1=Help, then press
F2=Extended Help to see help text for the entire command. This
text describes affecting the "query optimizer" and other "stuff" that
is all "below" (or "inside") the QQQQRY API.

So, as far as I can tell, values I set using CHGQRYA will apply
to ALL "queries".

Does that help?

Mark S. Waterbury