Cleanup Deleted Rows

deletedRowCleanup.sql

--
-- Find database tables that contain deleted rows, return the largest potential storage savings first.
--
WITH DELETED_ROWS_DATA AS (
        SELECT TABLE_SCHEMA,
               TABLE_NAME,
               NUMBER_DELETED_ROWS,
               NUMBER_ROWS,
               DECIMAL(NUMBER_DELETED_ROWS, 18, 3) * 100 / (NUMBER_DELETED_ROWS + NUMBER_ROWS) AS PERCENT_DELETED,
               CASE
                   WHEN AVGROWSIZE != -1 THEN AVGROWSIZE
                   ELSE ((DATA_SIZE - VARIABLE_LENGTH_SIZE) / MAX((NUMBER_ROWS + NUMBER_DELETED_ROWS),
                               1))
               END AS ESTIMATED_ROW_SIZE
            FROM QSYS2.SYSTABLESTAT X
            WHERE NUMBER_DELETED_ROWS > 0
                  AND TABLE_SCHEMA NOT LIKE 'Q%'
    )
    SELECT TABLE_SCHEMA,
           TABLE_NAME,
           NUMBER_DELETED_ROWS,
           PERCENT_DELETED,
           DECIMAL((NUMBER_DELETED_ROWS * ESTIMATED_ROW_SIZE / DECIMAL(1000000)), 20, 2) AS ESTIMATED_SPACE_SAVED_MB
        FROM DELETED_ROWS_DATA DELROWS
        ORDER BY ESTIMATED_SPACE_SAVED_MB DESC;