This tip is one example of how information can be extracted from DB2/400 files and used to reorganize members only when deleted records exist. Note: My choice is to reorganize even if one record is deleted. One could calculate the percentage of deleted records and choose not to reorganize that member if it was less than that value.
How it works:
I schedule CL program REORG to run early Sunday morning -- see the code below. It contains DSPFD commands for each Library I want to analyze and each requests the member list option for physical files only. The first command issued overwrites the file SYPMBR in Library QGPL while subsequent commands append the data to that file. After all Libraries have been processed we call RPG program SYGMBR2.
SYGMBR2 tests the delete count field (MLNDTR), and if it's not equal to zero the member is reorganized. To reorganize the file, SYGMBR2 takes the left most two characters from the member name and sets the variable JOBQ to the appropriate value. Note: In our shop these two characters are consistent with the system to which they belong -- i.e. AR=Accounts Receivable; PE/PR/PY are all a part of Human Resources; etc. Therefore, it's fairly easy to ensure that only one member in a given system is reorganized before the next in the same system. All others default to the QBATCH job queue. Once the JOBQ has been set SYGMBR2 calls CL program SYCMBR2, passing variables MLLIB (library name), MLFILE (file name), MLNAME (member name), and the JOBQ. The process continues until all records have been read.
SYCMBR2 submits the RGZPFM command to the appropriate job queue. The "Job Name" is the "File Name" whose member is being reorganized.
The Benefits:
It's fully automatic. The only time changes need to be made is when a Library is added or deleted. In our shop we use Infinium's products, and when new releases are installed files come and go. Because all physical files are a
To continue reading for free, register below or login
To read more you must become a member of Search400.com
nalyzed library-by-library, we view whatever happens to be there at that time. Files with multiple members only have their members reorganized if the member has deleted records -- not every member whether it needs it or not. In our shop reorganization has gone from 12 hours to 3 hours or less and I know that whatever needed done was done!
The Programs:
1. CL Program REORG
2. RPG Program SYGMBR2
3. CL Program SYCMBR2
Reader Feedback
Steve W. writes: This is all well and good, and can be the basis for a more robust utility
that incorporates thresholds to control what files really need to be
reorganized. However the problem is that the reorganization job needs to
have an exclusive lock on the file for the length of the reorganization,
which is nearly impossible in today's 24x7 e-business environment.
Two other solutions come to mind:
1) The use of the reuse deleted records attribute to eliminate the need
for reorganizations. This can have detrimental impact on performance and
programs which foolishly assume that the relative record number is always
in FIFO order.
2) SoftLanding Systems Turnover PDQ. I have been using this product for
two years now and can heartily recommend its use and reliability. This
product creates a duplicate of the file, copies records from the original
into the duplicate (thus compressing out deleted records) while also
reading the journal receiver over the original file to ensure that all
add/change/delete transactions are applied. Finally, at a controlled
point in time, it will obtain a lock on the original file (okay, it will
only work in a 23.9 x 7 environment) then swap the duplicate with the
original. I would consider this a far more "Dynamic" solution that the
example.
