SAP on IBM i – Update week 30, 2015: Data Reorganization with DB2 on IBM i (RGZPFM)
The database DB2 on IBM i is designed to make life easy for an administrator: You do not need to worry about the distribution of your tables in table spaces (IBM i storage management takes care of that), you do not need to kick off statistics collections (the SQL query engine takes care of that), and you should not need to reorganize your tables and indexes. If the latter is true, then why does the command “Reorganize Physical File Member” (RGZPFM) exist, and why did SAP publish an entire SAP Note about this topic (SAP Note 84081)?
When you delete rows from a table, the occupied space is not actually freed, but the rows are just marked as “deleted”. When an application later inserts a new row, the space of a deleted row can be reused for the new row. Reorganizing a file causes data from “not deleted” rows to be placed tightly together, leaving “free space” that is returned to the ASP. You might think reorganization can be helpful for two reasons: the first reason would be to reduce the table size, the second reason would be to help query performance, but this is not always the case.
For query performance, you should rather improve your indexing strategy than worrying about the number of deleted rows in a table. Most queries should be using an index for optimal performance rather than scanning through the table itself.
For reducing table size, if the deletion of many rows happened as part of an initial archiving project or when you have deleted client copies that you have no intention of replacing, then yes, it can be worthwhile to reorganize to reclaim space. However, if you are regularly reorganizing to reclaim rows that will just be replaced with a similar number of new ones over the next weeks or months – any storage savings are only going to be temporary and the administrative effort and computing resources spent reorganizing becomes rather pointless.
If you studied the IBM documentation for RGZPFM or SAP Note 84081, you know that there are two flavors of the reorganization: online and offline. The online reorganization allows you to reorganize tables while the SAP system stays up and running, but it has a few drawbacks. Probably the biggest shortcoming is the fact, that online reorganization is only freeing up space occupied by deleted fixed-length data, but the variable-length data space, used by VARGRAPHIC or LOB type columns, is left untouched. This takes away a big part of the size reduction. In addition, online reorganization bears a risk of affecting the outcome of application queries while the reorganization is running. With offline reorganization you avoid these shortcomings, but in general you can only execute an offline reorganization when your SAP system has been shut down. With today’s availability requirements, we believe that not very many customers do that. Given the limited capability of online RGZPFM and limited usefulness of frequent use of RGZPFM on the same file, we in the SAP on IBM i development team believe that you should only execute RGZPFM in very rare cases.
Once in a while we see incidents, where customers are doing reorganization – mostly online. So we would like to get an idea, how many of you do that, and why you are doing that. We have created a poll, and we would like you to vote there. If you are executing reorganizations on a more or less regular basis, we would appreciate your comments (here or under the poll), why you are doing it.