Re: How about source PFs? (was: Is there an easy way to identify all the save files on a system?)

Chuck sensed what I was looking for. I would, in fact, prefer a view for
which I can get a list of every source member on the system. (I don't care
about anything in QTEMP.) If such a view also provides a last modification
timestamp, all the better.

qsys2.syspartitionstat and qsys2.syspstat appear to be mirrors of each
other. Neither have the SOURCE_TYPE column defined as Rob suggested. We
are on v7r1. If there is a specific TR and/or PTF involved, please let me
know how to identify such.

Chuck wrote: "If /member/ information is desired, the SYSPSTAT [partition
status] [or perhaps the table function used to implement that] VIEW may be
preferable." Can you explain the "table function used to implement that"
part of the statement?

Thanks,
Dan

On Tue, Feb 17, 2015 at 4:41 PM, <rob@xxxxxxxxx> wrote:

I'm rather fond of this simple query:
SELECT SYSTEM_TABLE_SCHEMA, SYSTEM_TABLE_NAME,
SYSTEM_TABLE_MEMBER,SOURCE_TYPE
FROM qsys2.syspartitionstat
WHERE source_type is not null
and system_table_schema='ROB'


Rob Berendt

...

From: CRPence <CRPbottle@xxxxxxxxx>
On 17-Feb-2015 14:41 -0600, Dan wrote:

I've never noticed the object_statistics function before. I can't
remember how long I've been looking for a one-step method to get a
list of all of the source physical files on a system. (I.e.,
PF-SRC*)

Currently, I use DSPFD *MBR to an outfile, and select MBDTAT = 'S'.

That Display File Description request produces a row for every
database file member; seems potentially massive overkill if only a list
of all PF-SRC _files_ are of interest versus a list of all PF-SRC
_members_ is of interest.

The following DSPFD can significantly limit the output, yet the
likely large number of non-source physical files are included so further
selection is PHDTAT='S', but an extra record for every additional member
beyond a first is not generated [which as a list of /files/ would all be
ignored]:

DSPFD TYPE(*ATR) FILEATR(*PF) /* model file QAFDPHY in QSYS */

Any better options?

select * from QSYS/QADBXLFI where DBXTYP='S'

Note: Database [source] physical files in QTEMP libraries were never
tracked [and presumably still are not] by the System Database
Cross-Reference (DBXREF) feature that is responsible for populating
[most of] the QADB* files in QSYS. I named the logical file QADBXLFI in
the table-reference for the above SELECT query because that file is
publicly authorized. Either the creator of the logical VIEW over the
physical file QADBXREF in QSYS must have *ALLOBJ authority or the
program that accesses the data directly from that PF named as
table-reference must adopt that authority because there is no support to
modify the authorities of [or really to make _any_ changes to] the files
QADB* in QSYS.

The above query instead could use the SQL catalog VIEW SYSTABLES
[that is also publicly authorized], for which the DBXTYP field of
QADBXREF is exposed as FILE_TYPE or FILETYPE:

select * from QSYS2/SYSTABLES where FILE_TYPE='S' /* FILETYPE='S' */

If /member/ information is desired, the SYSPSTAT [partition status]
[or perhaps the table function used to implement that] VIEW may be
preferable.

--
Regards, Chuck