SYSPSTAT where source_type is not null is a poor performer

(Really apologize for not giving a subject to the OP.)

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,

TABLE_PARTITION FOR COLUMN TAB PART,

PARTITION_TYPE FOR COLUMN PARTTYPE,

PARTITION_NUMBER FOR COLUMN PARTNBR,

NUMBER_DISTRIBUTED_PARTITIONS FOR COLUMN DSTPARTS,

NUMBER_ROWS FOR COLUMN CARD,

NUMBER_ROW_PAGES FOR COLUMN NPAGES,

NUMBER_PAGES FOR COLUMN FPAGES,

OVERFLOW,

CLUSTERED,

ACTIVE_BLOCKS FOR COLUMN ACTBLOCKS,

AVGCOMPRESSEDROWSIZE FOR COLUMN ACR OWSIZE,

AVGROWCOMPRESSIONRATIO FOR COLUMN ACROWRATIO,

AVGROWSIZE,

PCTROWSCOMPRESSED FOR COLUMN PCTCROWS,

PCTPAGESSAVED FOR COLUMN PCTPGSAVED,

NUMBER_DELETED_ROWS FOR COLUMN DELETED,

DATA_SIZE FOR COLUMN SIZE,

VARIABLE_LENGTH_SIZE FOR COLUMN VLSIZE,

FIXED_LENG TH_EXTENTS FOR COLUMN FLEXTENTS,

VARIABLE_LENGTH_EXTENTS FOR COLUMN VLEXTENTS,

COLUMN_STATS_SIZE FOR COLUMN CSTATSSIZE,

MAINTAINED_TEMPO RARY_INDEX_SIZE FOR COLUMN MTISIZE,

NUMBER_DISTINCT_INDEXES FOR COLUMN DISTINCTIX,

OPEN_OPERATIONS FOR COLUMN OPENS,

CLOSE_OPERATIONS FOR COLUMN CLOSES,

INSERT_OPERATIONS FOR COLUMN INSERTS,

UPDATE_OPERATIONS FOR COLUMN UPDATES,

DELETE_OPERATIONS FOR COLUMN DELETES,

CLEAR_OPERATIONS FOR COLUMN DSCLEARS,

COPY_OPERATIONS FOR COLUMN DSCOPIES,

REORGANIZE_OPERATIONS FOR COLUMN DSREORGS,

INDEX_BUILDS FOR COLUMN DSINXBLDS,

LOGICAL_READS FOR COLUMN LGLREADS,

PHYSICAL_READS FOR COLUMN PHYREADS,

SEQUENTIAL_READS FOR COLUMN SEQREADS,

RANDOM_READS FOR COLUMN RANREADS,

CREATE_TIMESTAMP FOR COLUMN CREATED,

LAST_CHANGE_TIMESTAMP FOR COLUMN LASTCHG,

LAST_SAVE_TIMESTAMP FOR COLUMN LASTSAVE,

LAST_RESTORE_TIMESTAMP FOR COLUMN LASTRST,

LAST_USED_TIMESTAMP FOR COLUMN LASTUSED,

DAYS_USED_COUNT FOR COLUMN DAYSUSED,

LAST_RESET_TIMESTAMP FOR COLUMN LASTRESET,

NEXT_IDENTITY_VALUE FOR COLUMN NEXTVALUE,

LOWINCLUSIVE FOR COLUMN LOWINCL,

LOWVALUE,

HIGHINCLUSIVE FOR COLUMN HIGHINCL,

HIGHVALUE,

NUMBER_PARTITIONING_KEYS FOR COLUMN NBRPKEYS,

PARTITIONING_KEYS FOR COLUMN PARTKEYS,

KEEP_IN_MEMORY FOR COLUMN KEEPINMEM,

MEDIA_PREFERENCE FOR COLUMN MEDIAPREF,

LAST_SOURCE_UPDATE_TIMESTAMP FOR COLUMN LASTSRCUPD,

SOURCE_TYPE FOR COLUMN SRCTYPE,

SYSTEM_TABLE_SCHEMA FOR COLUMN SYS_DNAME,

SYSTEM_TABLE_NAME FOR COLUMN SYS_TNAME,

SYSTEM_TABLE_MEMBER FOR COLUMN SYS_MNAME )



AS SELECT

A.DBXLB2,

A.DBXLFI,

COALESCE(B.TABLE_PARTITION, ''),

CHAR(COALESCE(B.PARTITION_TYPE, ' '), 1),

B.PARTITION_NUMBER,

B.NUMBER_DISTRIBUTED_PARTITIONS,

COALESCE(B.NUMBER_ROWS, 0),

COALESCE(B.NUMBER_ROW_PAGES, 0),

COALESCE(B.NUMBER_PAGES, 0),

COALESCE(B.OVERFLOW, 0),

B.CLUSTERED,

COALESCE(B.ACTIVE_BLOCKS, -1),

CAST(-1 AS BIGINT),

CAST(-1 AS REAL),

COALESCE(B.AVGROWSIZE, -1),

CAST(-1 AS REAL),

CAST(-1 AS SMALLINT),

COALESCE(B.NUMBER_DELETED_ROWS, 0),

COALESCE(B.DATA_SIZE, 0),

COALESCE(B.VARIABLE_LENGTH_SIZE, 0),

COALESCE(B.FIXED_LENGTH_EXTENTS, 0),

COALESCE(B.VARIABLE_LENGTH_EXTENTS, 0),

COALESCE(B.COLUMN_STATS_SIZE, 0),

COALESCE(B.MAINTAINED_TEMPORARY_INDEX_SIZE, 0),

COALESCE(B.NUMBER _DISTINCT_INDEXES, 0),

COALESCE(B.OPEN_OPERATIONS, 0),

COALESCE(B.CLOSE_OPERATIONS, 0),

COALESCE(B.INSERT_OPERATIONS, 0),

COALESCE(B.UPDATE_OPERATIONS, 0),

COALESCE(B.DELETE_OPERATIONS, 0),

COALESCE(B.CLEAR_OPERATIONS, 0),

COALESCE(B.COPY_OPERATIONS, 0),

COALESCE(B.REORGANIZE_OPERATIONS, 0),

COALESCE(B.INDEX_BUILDS, 0),

COALESCE(B.LOGICAL_READS, 0),

COALESCE(B.PHYSICAL_READS, 0),

COALESCE(B.SEQUENTIAL_READS, 0),

COALESCE(B.RANDOM_READS, 0),

COALESCE(B.CREATE_TIMESTAMP, '0001-01-01-00.00.00.000000'),

COALESCE(B.LAST_CHANGE_TIMESTAMP, '0001-01- 01-00.00.00.000000'),

B.LAST_SAVE_TIMESTAMP,

B.LAST_RESTORE_TIMESTAMP,

B.LAST_USED_TIMESTAMP,

COALESCE(B.DAYS_USED_COUNT, 0),

B.LAST_RESET_TIMESTAMP,

B.NEXT_IDENTITY_VALUE,

B.LOW_INCLUSIVE,

B.LOW_VALUE,

B.HIGH_INCLUSIVE,

B.HIGH_VALUE,

B.NUMBER_PARTITIONING_KEYS,

B.PARTITIONING_KEYS,

COALESCE(B.KEEP_IN_MEMORY, CHAR(' ',1)),

COALESCE(B.MEDIA_PREFERENCE, SMALLINT(0)),

B.LAST_SOURCE_UPDATE_TIMESTAMP,

B.SOURCE_TYPE,

A.DBXLIB AS SYSTEM_TABLE_SCHEMA,

A.DBXFIL AS SYSTEM_TABLE_NAME,

CHAR(COALESCE(B.SYSTEM_TABLE_MEMBER, ''), 10)



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'