Infor XA IBM i SQL Services Health Query Generator

-- Largest physical files in the XA library by data space size
SELECT TABLE_NAME,
       NUMBER_ROWS,
       NUMBER_DELETED_ROWS,
       DEC(DATA_SIZE / 1048576.0, 15, 1) AS DATA_MB,
       LAST_CHANGE_TIMESTAMP
  FROM QSYS2.SYSTABLESTAT
 WHERE TABLE_SCHEMA = 'AMFLIB'
 ORDER BY DATA_SIZE DESC
 FETCH FIRST 25 ROWS ONLY;

-- Files where deleted records exceed the threshold, with the CL command to run
-- Run RGZPFM only when the file is not in use (XA users and batch stopped, backup taken)
SELECT TABLE_NAME,
       NUMBER_ROWS,
       NUMBER_DELETED_ROWS,
       DEC(NUMBER_DELETED_ROWS * 100.0
           / NULLIF(NUMBER_ROWS + NUMBER_DELETED_ROWS, 0), 7, 2) AS DELETED_PCT,
       DEC(DATA_SIZE / 1048576.0, 15, 1) AS DATA_MB,
       'RGZPFM FILE(' CONCAT TRIM(TABLE_SCHEMA) CONCAT '/'
         CONCAT TRIM(SYSTEM_TABLE_NAME) CONCAT ')' AS RGZ_COMMAND
  FROM QSYS2.SYSTABLESTAT
 WHERE TABLE_SCHEMA = 'AMFLIB'
   AND NUMBER_DELETED_ROWS * 100.0
       / NULLIF(NUMBER_ROWS + NUMBER_DELETED_ROWS, 0) > 20
 ORDER BY NUMBER_DELETED_ROWS DESC;

-- Example CL for one file:
-- RGZPFM FILE(AMFLIB/IMHIST)

-- IBM i profiles (prefix '[User profile prefix filter]') with no sign-on in the last 90 days
SELECT AUTHORIZATION_NAME,
       STATUS,
       PREVIOUS_SIGNON,
       TEXT_DESCRIPTION,
       GROUP_PROFILE_NAME
  FROM QSYS2.USER_INFO
 WHERE AUTHORIZATION_NAME LIKE '[User profile prefix filter]%'
   AND (PREVIOUS_SIGNON IS NULL
        OR PREVIOUS_SIGNON < CURRENT TIMESTAMP - 90 DAYS)
 ORDER BY PREVIOUS_SIGNON;

-- Active jobs, highest CPU first (CPU_TIME is in milliseconds)
-- An empty SUBSYSTEM_LIST_FILTER returns jobs in all subsystems
SELECT JOB_NAME,
       SUBSYSTEM,
       AUTHORIZATION_NAME,
       JOB_STATUS,
       CPU_TIME,
       ELAPSED_CPU_PERCENTAGE
  FROM TABLE(QSYS2.ACTIVE_JOB_INFO(
         SUBSYSTEM_LIST_FILTER => '[XA subsystem name]')) X
 ORDER BY CPU_TIME DESC;

-- Row counts and size of the main XA history files
SELECT TABLE_NAME,
       NUMBER_ROWS,
       NUMBER_DELETED_ROWS,
       DEC(DATA_SIZE / 1048576.0, 15, 1) AS DATA_MB,
       LAST_CHANGE_TIMESTAMP
  FROM QSYS2.SYSTABLESTAT
 WHERE TABLE_SCHEMA = 'AMFLIB'
   AND TABLE_NAME IN ('IMHIST', 'MOHMST', 'MOHDTA', 'MOHRTG', 'MOHTRN',
                      'POHSTM', 'POHISTI', 'MBFBREP', 'MBDRREP')
 ORDER BY DATA_SIZE DESC;