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

Thanks Chuck!

Can you or anyone else explain what the following does? I've never seen
the LATERAL (aka TABLE) keyword and, even after reading up a bit in the SQL
Reference, I'm still not sure what this is doing:

FROM QSYS.QADBXATR A
, LATERAL
( SELECT *
FROM TABLE
( QSYS2.PARTITION_STATISTICS(A.DBXLIB, A.DBXFIL)
) AS X
) AS B

- Dan

On Thu, Feb 26, 2015 at 11:35 AM, CRPence <CRPbottle@xxxxxxxxx> wrote:

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

--
Regards, Chuck