On 05-Aug-2014 07:58 -0500, Richard Schoen wrote:
I'm pretty sure the current method they use is DSPFD.
They were complaining about the process taking hours because they
have thousands of files with thousands of members which can take up
to 10hrs to list.
While indeed a slow interface [both TYPE(*MBR) and TYPE(*MBRLIST),
though the latter has potential for faster], being _that slow_ would
suggest the requests are likely being done serially for each file,
rather than concurrently; the same output file.mbr can be used
concurrently to /add/ the data from multiple DSPFD requests. FWiW, the
change timestamp of the database *FILE object should be a valid element
for determining staleness of cached results for added\removed members.
My technical curiosity was telling me there must be a better way,
but if these are flat tables and they won't show up in the SQL, it
probably wouldn't work for them.
Per my reference to a UDTF and the selection criteria DBXREL='Y', a
review of the Display File Description (DSPFD) of the SYSPSTAT VIEW
definition might behoove you with regard to the exclusion of flat files
by that VIEW. That is, there is nothing preventing use of the same UDTF
[in a SELECT optionally encapsulated in a VIEW] while omitting the
undesirable predicate from the WHERE clause.
And again, if the goal is to get a list, there is no reason to use
the SQL in any form; the /same/ work that is done by the UDTF in that
VIEW can be coded in a procedure that need never be registered to the
SQL as a UDF. The effect is basically what the QUSLMBR API can offer;
whether or not that effect would then additionally be manifest as rows
via a UDTF.