Re: Need to get quick database member list for multimember files

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.