Re: SYSPSTAT where source_type is not null is a poor performer

On 25-Feb-2015 16:58 -0600, Dan wrote:

For those who are curious, I took the CREATE VIEW DDL from the DSPFD
and made it readable:

CREATE VIEW SYSPARTITIONSTAT (
TABLE_SCHEMA FOR COLUMN TABSCHEMA,
TABLE_NAME FOR COLUMN TABNAME,
<<SNIP>>
AS SELECT
A.DBXLB2,
A.DBXLFI,
<<SNIP>>
FROM QSYS.QADBXREF A
, LATERAL
( SELECT *
FROM TABLE
( QSYS2.PARTITION_STATISTICS(A.DBXLIB, A.DBXFIL)
) AS X
) AS B
WHERE A.DBXATR IN ('TB','PF','MQ' ) AND A.DBXREL = 'Y'

So create your own query [optionally encapsulated in a VIEW] that will limit the amount of data generated; results generated by the UDTF that are then just discarded by the implied or explicit selection to include only "source physical files". For example:

SELECT
A.DBXLIB as SRCPF_LIBR
, A.DBXFIL as SRCPF_NAME
, 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' /* minimize data to only PFs */
AND A.DBXREL = 'Y' /* probably moot */
AND A.DBXTYP = 'S' /* the "PF" is a PF-SRC */
AND B.SOURCE_TYPE IS NOT NULL