The latest Technology Refresh (TR) for IBM i 7.4 and 7.5 has something new within them for reorganizing a file. If you have lost count of the TR levels IBM i 7.4 is at TR7 and IBM i 7.5 is at TR1. Every time a new TR is announced it can be overwhelming with the amount of enhancements and by the time you have it installed on your IBM i Server, it is hard to remember all the good stuff coming along with it.

So please see this as a kind reminder and for me the opportunity to put one specific announcement in the spotlight, falling under the category “Db2 for i – Database Engineer Enhancements”, called “Online reorganize physical file member (RGZPFM) enhancement”. The good news is that this enhancement was made available for both IBM i 7.4 and 7.5, implemented in the same way. When you open up the link it will show you two screenshots:

And…

Apparently, the difference lies with the fact that with the latest TR reorganizing with Allow Cancel , ALWCANCEL(*YES) now moves the record instead of deleting then reinserting it.  

Before going into more details about what is happening now when you reorganize a file with the RGZPFM command, allow me to give you some background information about the Reorganize While Active.

This link RGZPFM – Basics on Reorganize While Active will help you in getting to know what is needed to perform a Reorganize while active. Today it is hard to determine in which release this option was made available as documentation for IBM i older than 7.1 is hard to find (please correct me if I’m wrong) but I think it was in IBM i 6.1.

As DDS is still around and used when creating a physical file so is the default of the Create Physical File (CRTPF) command. The default for the parameter “Reuse deleted records . . . . . REUSEDLT” is still set to *NO. When creating a table, the default is the other way around and the Reuse deleted records option is always set to *YES. This is for sure a disk space saver. When thinking about SQL performance you might have a look here A screen a story – SQL Performance – a Table Scan.

As you may know or not know a Table scan touches all the records in a file. If a file contains deleted records a scan will take longer because the process touches every record, including deleted records. The fact that the command RGZPFM requires down time, time when the file is not in use, prevents many shops from reorganizing files with deleted records on a regular basis.

The Reorganize while active requires journaling and even that is still not implemented everywhere because of the DDS heritage IBM i has. With the right settings and the 5770SS1 option 42 installed called  “HA Journal Performance ” the disk impact and performance impact of journaling is limited. As mentioned in my article A screen a story –  Db2 Symmetric Multiprocessing the HA journal Performance now also comes for free with your IBM i core license.

With the reuse deleted records option and the journaling in place you are ready to unleash the power of this new option to reorganize your files which contain deleted records. When doing so you will find for every record being moved it causes two entries to be written in the journal, one record for the delete action and another for the record being inserted. You now may think hang on a minute, but with this new TR the record was moved. The screens below will show you what is happening.

An example of a file with deleted records:

Starting the Reorganize while active:

Check the command used, by pressing the button “Show Command”:

Tip: Please do not run the command in an ACS SQL script because this requires you to leave RSS active. Run it by pressing the OK button in the background.

Follow it’s progress (please notice text in green):

When it’s done:

Check the journal to see what is being done under the covers:

With this new TR, now the records are moved resulting in pairs of DL and PX journal type entries. DL stands for “Record deleted”, PX for “Record added directly”. Also notice that each pair has exactly the same timestamp, the indication that the record was moved. Before this TR, there was a small time gap between every DL and PX entry, meaning that for a very short time the record was not there. This is now fixed which completely removes the need to plan down time when doing this.

When contacting IBM support to they explained to me that when using the option Allow Cancel (ALWCANCEL(*YES) this is the way it was implemented. If a new journal entry type “move” would have been added all software replication vendors would have had a problem. IBM i was designed to run your business and with the growing needs for 24/7 uptime, this is a huge step forward.