SQL - PF with overgrown increments

By kimot, 19 October, 2023

If you want to know files with very big increments

SELECT PART.TABLE_SCHEMA, PART.TABLE_NAME, NUMBER_ROWS, NUMBER_DELETED_ROWS, 
       CHAR(IFNULL(TO_CHAR(INITIAL_RECORDS),'*NOMAX'),10) AS "INIT_RECORDS",
       INCREMENT_RECORDS, MAXIMUM_INCREMENTS, REUSE_DELETED_RECORDS
FROM QSYS2.SYSPARTITIONSTAT AS PART 
  INNER JOIN QSYS2.SYSFILES AS FILE
  ON PART.TABLE_SCHEMA = FILE.SYSTEM_TABLE_SCHEMA AND PART.TABLE_NAME = FILE.TABLE_NAME 
WHERE PART.TABLE_SCHEMA = 'LIBRARY_NAME' 
      AND INITIAL_RECORDS <> 0 AND (NUMBER_ROWS > INITIAL_RECORDS) 
      AND MAXIMUM_INCREMENTS < 5  
ORDER BY NUMBER_ROWS DESC;