Re: Database monitor (V7R2) 5005 entries: meaning and QQ1000L -- MIDRANGE-L

Hi Mandy

I didn't mean to say there is no information in DBMON - much simpler - this isn't even really and IBM thing - I Googled for "order of results of SQL SELECT without ORDER BY" and got an interesting hit at

https://stackoverflow.com/questions/10064532/the-order-of-a-sql-select-statement-without-order-by-clause

that includes this info -

from the relational database theory, a|select|statement without an|order by|clause should be considered to have no particular order

The rest of that article is interesting in regard to this topic, especially regarding the concept that RDBMS'

have the mandate to give you/exactly/what you asked for, as efficiently as possible

If you don't specify an order by, you'll get whatever results in the most efficient manner, and that "manner" is decided by the optimizer, which can give different results based on many things at different times.

Now maybe if the DBMON output gives you the same optimizer (the referred article uses the word "planner", maybe that's SQL Server, but it's the same purpose) results, maybe the order will be the same. But even if the chosen access plan is "table scan", the results could be different if your table can reuse deleted records.

HTH
Vern

On 5/9/2021 12:28 PM, mandy.shaw@xxxxxxxxxxxxxxxx wrote:

Many thanks for your input Vern and Timothy.

Timothy, I can't use the nice ACS screens, unfortunately, because the name
of the Query being run is not logged anywhere in the Database Monitor, so I
am having to use various bits of locally meaningful SQL to match monitor
output against specific Queries. I don't suppose there is any way of easily
identifying the SQL for a specific panel (like you can in the Performance
navigation screens in iNav)? That would be really useful.
(I have given up on the 5005 entries for now!)

Anyway I've now got a bit further.
Am I right Vern (I think this was your point?) that Query Sort processing
has no specific information logged in the DBMON output, and is effectively
entirely unpredictable in its results?
This is absolutely what I am finding - everything else seems predictable.
In particular, if there are no sort fields at all and the optimisation
matches across SQE and CQE, then the output seems to match every time. The
only exception to this relates to the handling of Report Breaks where there
are no sort fields (it is clear that SQL GROUP type processing is not being
used, either in SQE or in CQE).

Finally, another question (sorry): what is the 'proper' way of identifying
SQE vs CQE in the DBMON output? I am using QQSMINTF/Plan_Iteration_Number,
treating null as CQE and everything else as SQE - this seems to work, but am
I missing a more formal/accurate method? Not all the record types include
the query options library name, or I'd use that.

Many thanks
Mandy