Re: Member Text of source member not in SYSPSTAT; where to find?

On 23-May-2016 17:27 -0500, Dan wrote:

So, last year, some of you helped me to cobble together the following
SQL, which produces a list of all source members on the system:

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
, b.CREATE_TIMESTAMP as Source_Create_Timestamp
, b.LAST_CHANGE_TIMESTAMP as Source_Type_Text_Change_Timestamp
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

Now, I am looking for the member text for the source member. I am
surprised it is not in SYSPSTAT. I looked in the IBM i catalog tables
and views
[http://www.ibm.com/support/knowledgecenter/ssw_ibm_i_72/db2/rbafzcatalogtbls.htm]
but came up empty. It seems to me that the member text must be
stored somewhere in the i catalog. Anyone know where to find this?

The member information is not /stored/ information; the information is generated dynamically from the system-defined\supplied User Defined Table Function (UDTF) named PARTITION_STATISTICS that is referenced in the above query. The information is generated for the named Lib/Fil passed as arguments DBXLIB as the library name and DBXFIL as the file name, values that are obtained via the data in QADBXREF PF [through the publicly-authorized QADBXATR LF].

What needs to be determined, is if that UDTF includes the Member Text information in a column of the RETURNS TABLE, and if so, then what is the column name assigned to that data. A couple ways to review the columns from that UDTF:

Query the SQL catalog tables for definitional attributes of the UDTF:

select p.*
from sysroutines r join sysparms p
using (specific_schema, specific_name)
WHERE routine_type ='FUNCTION'
and routine_schema='QSYS2'
and routine_name='PARTITION_STATISTICS'
and parameter_mode='OUT'
/* and PARAMETER_NAME like '%TEXT%' */
order by ordinal_position

Create a table from the UDTF, and then review the fields; e.g. with Display File Field (DSPFFD)

create table qtemp/mbr_inf as
( SELECT *
FROM TABLE( QSYS2.PARTITION_STATISTICS('QTEMP', 'MBR_INF') ) X
) WITH NO DATA
-- per QTEMP spec, conspicuously use DSPFFD vs catalogs

And given the information is generated dynamically, simple enough to create a scalar User Defined Function (UDF) to get just the member text, also dynamically. Then join that as an additional lateral table-reference generating the single-row result with a row values clause [e.g. VALUES(mbr_text(A.DBXLIB,A.DBXFIL,SYSTEM_TABLE_MEMBER)]. The simplest is probably using the CL command Retrieve Member Description (RTVMBRD) in a CLP used to define the MBR_TEXT as an External (scalar) Function.