On 03-Mar-2015 11:29 -0600, Dan wrote:
<<SNIP>>
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.
That is the only supported authority\ownership, and even if the
authority [or owner] could be changed [should be generally not possible
per the file is /always/ open in the *DBXREF job(s)], the OS is supposed
to reset them [even at the cost of deleting and re-creating them if
problems in making that happen; the recovery for that is a reclaim of
the *DBXREF data].
In any case, I wonder if it's risky to try to build our own indexes
and/or views on QSYS / QSYS2 objects.
See the messages CPF32D# for the /risk/; effectively, any code must
assume the OS may have deleted their user-created [or SCHEMA catalog]
VIEW files or INDEX files and react as appropriate for the application.
Notably, CPF32D1 "System file &1 in library &2 was deleted." While an
INDEX [or keyed DDS LF] could be created on the physical QADB* files, no
UNIQUE keyed AccPth can be created; the /objects/ will be created, but a
diagnostic will warn that the UNIQUE attribute was ignored [and I am
unsure about EVI; those possibly may be restricted or intended to be
restricted]
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)
)
Of course the query result need not be a stored [temporary] copy of
the physical data; the SQL query can be a report designed on a VIEW or
that query itself. And if a VIEW is report-like enough with the detail
records [be they aggregations or otherwise] of the result-set being
returned, a simple request to RUNQRY *NONE (The_VIEW) should be possible
[barring any SQE restrictions due to the composition of the encapsulated
SQL query] to present a /report/ of the data instead of creating an
output file; of course the STRQMQRY with a variable set for the VIEW
name would not have any restrictions, but requires creating an actual
*QMQRY object [whereas the Query/400 run-time generates an
implicit\memory-only *QRYDFN to effect the Run Query to produce a report].
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.
Thus why I emphasized reduction in the total amount of row data to be
_generated_ by the UDTF; i.e. the fewest possible numbers of the tuple
(DBXLIB,DBXFIL) should be producing output, because that /work/ is
effectively what the Display File Description (DSPFD) for Member [List]
details will produce, whereby each request would be made successively
against each file rather than against a group[ed within a library] of files.
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.
This particular situation [almost surely] has nearly nothing to do
with the keyed [or sequential] Access Path. Instead, this is the effect
of the object headers for the files (*FILE) and members (*MEM) objects
having to be paged [sometimes faulted] into memory on the first request,
then on the second request many of those same objects remain available
in memory because they have not been paged-out. Over time, while
requirements of other work demand access to memory, those objects will
be paged-out such that the next invocation requires them to be paged or
faulted again into memory.
I'm wondering if there's a way to force the system to keep the access
path "alive" longer than it does.
For the physical data or access path(s), there is the Set Object
Access (SETOBJACC) command. For that and the object headers, the only
option is to get the memory from a non-shared pool which has sufficient
memory to keep the same data and object headers in memory. The UDTF is
/generated data/ rather than physically stored data, so the /work/ is
going to be the [re]generation of that data upon each new invocation;
whatever object references [in this case files and members] made by the
UDTF require memory, and memory is most shared and paged amongst the
jobs across the system.
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.
Again, mostly just the data\index\objects already paged. An
additional benefits that can be achieved and thus noticed are
pseudo-closed cursors and cached query access plans. For the latter,
multiple queries with nothing but a literal changed, the parameterized
query can be re-run with the different literal value [SQL calls them
constant values], but the identical [previously optimized] query.
midrange.com
