[no subject]

In another thread, I asked how to query system tables to get a list of all
of the source members on the system. (That thread was primarily a
discussion of the PTF groups required to get this new functionality.) The
query to do this is:

select * from qsys2.syspartitionstat
where source_type is not null

I am now able to run this query. The performance is very disappointing.
The above query returns results within several seconds, but do any row
selection or ordering, and you may as well go take a lunch break.

Since they’ve disabled the Database part of System Navigator in our shop,
I’m limited to using STRDBG and then run the query with the selection and
ordering to get advice on how to optimize it. (Does Data Studio have this
functionality?) The advisor suggested building an access path on QADBXREF
using fields DBXREL & DBXATR. Curious, I did a DSPFD on qsys2/syspstat and
found the CREATE VIEW statement used to create the file. It was here where
I realized just how little I know about SQL. The FROM clause really threw
me; I’ve never seen the “LATERAL” (synonymous with “TABLE”) keyword before,
and after reviewing the SQL Reference, I’m no closer to understanding it.
Can anyone explain to me what the following is doing?

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'

Since there seems to be a lot of extraneous stuff in syspstat, I’m thinking
of using its DDL as a template for a new view, and paring down the columns
I need and also adding the “source_type is not null” selection to it.

Thoughts?

- Dan