Performing Deleted Records Analysis (iSeries)

When rows (or records) are deleted from a database table, the occupied space on disk is not actually freed but only marked as deleted. This space is then reused when adding rows later to the table. However, if many rows are deleted and few rows are inserted, the amount of space that is occupied by these deleted records can be significant.

To display information on deleted records, call transaction DB4COCKPIT.

  1. In the navigation frame choose Space ® State on disk.
  2. Choose Deleted row analysis.

The result is a list of tables that contain deleted records ordered by the number of deleted rows in descending order. The list also shows the total number of rows in the table, the row length, the total amount and the percentage of the space that is occupied by deleted rows.

You should consider reorganizing tables that show a high number of deleted rows. Reorganizing the tables removes the deleted rows and saves disk space.

To reorganize a table you can use the operating system command RGZPFM (Reorganize Physical File Member).

This command requires an exclusive lock on the table. You may not be able to reorganize a table while it is in use by an SAP system, and you may not be able to use a table from within an SAP system while it is being reorganized. To avoid locking conflicts, it is recommended to stop your SAP system prior to reorganizing a table.