In version 6 of DB2 IBM introduced the DISCARD option for DB2 REORG. With DISCARD it is possible to delete rows during a REORG. This is a simple and yet genious concept. It is a clever option, too, because the deleted rows can be unloaded to a flat file together with a DB2 LOAD statement that can be used to load the deleted rows back into the tablespace again.

I almost jumped out of my chair and shouted HURRAY and I jumped to the keyboard and started using this little miracle right away. After a while my enthusiasm dropped to zero, because REORG now ran extremely slow and abended several times with B37 abends on SYSREC. What happened? My first observation was that the tablespace was defined as compressed. Therefore DB2 has to uncompress every single row in order to validate the rows against the DISCARD criteria. Then I discovered that the unloaded rows on SYSREC all were at full length despite the fact that there was a LONG VARCHAR column at the end of every row.

In the Utility Guide for DB2 I found the description of REORG and found option NOPAD. This option ensures that VARCHAR columns aren't padded with blanks when they are unloaded. Of course NOPAD is not the default, so I had to try this option in my desperation. Now suddenly my B37 abends disappeared and the REORG ran significantly faster but still rather slow compared to a REORG without DISCARD.

My conclusion is that DISCARD must be used with care although it is a very promising option. I will recommend that you always use NOPAD in conjunction with DISCARD and if you have a compressed tablespace you can expect signifcantly longer elapse time. Then you have to consider that it may be faster to remove the rows using a traditional SQL DELETE. I haven't tried DISCARD with version 7 so I don't know if any improvements has been implemented, but my first thought was, that the rows could be unloaded to SYSREC in compressed format when KEEPDICTIONARY is used. This would save a lot of time when the rows are loaded back into the tablespace.