Re: SYSPSTAT where source_type is not null is a poor performer

John, Chuck,

Thanks for the information. This is still a bit over my head, and I just
need to play with this a bit more to see for myself how this differentiates
from other joins. Searching for a brief primer on joins.

Back to the OP re the poor performance of this query:
Per the index adviser,I attempted to create an index on QSYS/QADBXREF, but
was denied as "not authorized". The object owner is QSYS and the only user
authority is for *PUBLIC Read. I dunno if that is how the object ships, or
if the authority was changed after installation. In any case, I wonder if
it's risky to try to build our own indexes and/or views on QSYS / QSYS2
objects.

So, what I'm left to do is to submit the following:
SBMJOB CMD(RUNSQL SQL('create table testlib/srcmbrlist as (
SELECT A.DBXLIB as SRCPF_LIBR
, A.DBXFIL as SRCPF_NAME
, B.SYSTEM_TABLE_MEMBER as SrcMbrName
, B.LAST_SOURCE_UPDATE_TIMESTAMP as SRCPF_UDAT
, B.SOURCE_TYPE as SRCPF_SRCTYPE
FROM QSYS.QADBXATR A
, LATERAL
( SELECT * FROM TABLE
( QSYS2.PARTITION_STATISTICS(A.DBXLIB, A.DBXFIL)
) AS X
) AS B
WHERE A.DBXATR = ''PF'' and A.DBXREL = ''Y'' and A.DBXTYP = ''S''
AND B.SOURCE_TYPE IS NOT NULL ) with data') COMMIT(*NONE))

What is curious to me is that the first time this runs in a given day, it
takes approx 20 minutes to complete. If I run it again immediately after
the first run completes, it takes approx 3 minutes. Subsequent runs in
close proximity take only 3 seconds. Wait an hour, and it takes 20 minutes
again. I vaguely recall this behavior from a few jobs ago (ten years +),
recalling that the system creates its own access path, but eventually
discards it after an unspecified amount of time. I'm wondering if there's
a way to force the system to keep the access path "alive" longer than it
does.

Also interesting to me is that I can run a query similar to the one above,
but that adds selection on the member name, and it appears that this query
"piggybacks" on the index built on the other query (presuming it is run in
close proximity) completing within just a few seconds as well.

- Dan