Dynamically reorganizing DB2 files

Learn how information can be extracted from DB2 files and used to reorganize members when deleted records exist.

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 analyzed 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

           /******************************************************************/
           /*  PROGRAM: REORG                                                */
           /*  PURPOSE: CREATE A LIST OF FILES/MEMBERS.  THEN CALL SYGMBR2   */
           /*           WHICH WILL LOOK FOR DELETED RECORDS.  IF DELETED     */
           /*           RECORDS EXIST FILE MEMBER WILL BE REORGANIZED.       */
           /*           FILE NAME: QGPL/SYPMBR CREATED BY DSPFD CMD          */
           /*                                                                */
           /*   NOTE -- THIS PROGRAM MUST BE COMPILED WITH OBJECT AUTH.      */
           /*           OF *OWNER.                                           */
           /*                                                                */
           /*       BY: PETE CODISPOTI   9/16/97                             */
           /*                                                                */
           /******************************************************************/
             PGM
             MONMSG     MSGID(CPF9999)

 BEGIN:      DSPFD      FILE(AM2000/*ALL) TYPE(*MBRLIST) +
                          OUTPUT(*OUTFILE) FILEATR(*PF) +
                          OUTFILE(QGPL/SYPMBR)

             DSPFD      FILE(QGPL/*ALL) TYPE(*MBRLIST) +
                          OUTPUT(*OUTFILE) FILEATR(*PF) +
                          OUTFILE(QGPL/SYPMBR) OUTMBR(*FIRST *ADD)

             DSPFD      FILE(ARDBFA/*ALL) TYPE(*MBRLIST) +
                          OUTPUT(*OUTFILE) FILEATR(*PF) +
                          OUTFILE(QGPL/SYPMBR) OUTMBR(*FIRST *ADD)

             DSPFD      FILE(CMDBFA/*ALL) TYPE(*MBRLIST) +
                          OUTPUT(*OUTFILE) FILEATR(*PF) +
                          OUTFILE(QGPL/SYPMBR) OUTMBR(*FIRST *ADD)

             DSPFD      FILE(EMDBFA/*ALL) TYPE(*MBRLIST) +
                          OUTPUT(*OUTFILE) FILEATR(*PF) +
                          OUTFILE(QGPL/SYPMBR) OUTMBR(*FIRST *ADD)

             DSPFD      FILE(GLDBFA/*ALL) TYPE(*MBRLIST) +
                          OUTPUT(*OUTFILE) FILEATR(*PF) +
                          OUTFILE(QGPL/SYPMBR) OUTMBR(*FIRST *ADD)

             DSPFD      FILE(GTDBFA/*ALL) TYPE(*MBRLIST) +
                          OUTPUT(*OUTFILE) FILEATR(*PF) +
                          OUTFILE(QGPL/SYPMBR) OUTMBR(*FIRST *ADD)

             DSPFD      FILE(HRDBFA/*ALL) TYPE(*MBRLIST) +
                          OUTPUT(*OUTFILE) FILEATR(*PF) +
                          OUTFILE(QGPL/SYPMBR) OUTMBR(*FIRST *ADD)

             DSPFD      FILE(IRDBFA/*ALL) TYPE(*MBRLIST) +
                          OUTPUT(*OUTFILE) FILEATR(*PF) +
                          OUTFILE(QGPL/SYPMBR) OUTMBR(*FIRST *ADD)

             DSPFD      FILE(PLDBFA/*ALL) TYPE(*MBRLIST) +
                          OUTPUT(*OUTFILE) FILEATR(*PF) +
                          OUTFILE(QGPL/SYPMBR) OUTMBR(*FIRST *ADD)

             DSPFD      FILE(PLDBFT/*ALL) TYPE(*MBRLIST) +
                          OUTPUT(*OUTFILE) FILEATR(*PF) +
                          OUTFILE(QGPL/SYPMBR) OUTMBR(*FIRST *ADD)

             DSPFD      FILE(ARCUST/*ALL) TYPE(*MBRLIST) +
                          OUTPUT(*OUTFILE) FILEATR(*PF) +
                          OUTFILE(QGPL/SYPMBR) OUTMBR(*FIRST *ADD)

             DSPFD      FILE(CMCUST/*ALL) TYPE(*MBRLIST) +
                          OUTPUT(*OUTFILE) FILEATR(*PF) +
                          OUTFILE(QGPL/SYPMBR) OUTMBR(*FIRST *ADD)

             DSPFD      FILE(EMCUST/*ALL) TYPE(*MBRLIST) +
                          OUTPUT(*OUTFILE) FILEATR(*PF) +
                          OUTFILE(QGPL/SYPMBR) OUTMBR(*FIRST *ADD)

             DSPFD      FILE(GLCUST/*ALL) TYPE(*MBRLIST) +
                          OUTPUT(*OUTFILE) FILEATR(*PF) +
                          OUTFILE(QGPL/SYPMBR) OUTMBR(*FIRST *ADD)

             DSPFD      FILE(HRCUST/*ALL) TYPE(*MBRLIST) +
                          OUTPUT(*OUTFILE) FILEATR(*PF) +
                          OUTFILE(QGPL/SYPMBR) OUTMBR(*FIRST *ADD)

             DSPFD      FILE(PLCUST/*ALL) TYPE(*MBRLIST) +
                          OUTPUT(*OUTFILE) FILEATR(*PF) +
                          OUTFILE(QGPL/SYPMBR) OUTMBR(*FIRST *ADD)

             CALL       PGM(QGPL/SYGMBR2)

             ENDPGM 

2. RPG Program SYGMBR2

H
     FSYPMBR    IF   E             DISK
      *
     C                   MOVEL     'QBATCH'      JOBQ             10
     C                   MOVEL     '  '          TA                2
     C     *IN99         DOWEQ     '0'
     C                   READ      SYPMBR                                 99
     C     *IN99         IFEQ      '0'
     C     MLNDTR        IFNE      0
     C                   MOVEL     MLFILE        TA
     C                   SELECT
     C                   WHEN      TA='AM'
     C                   MOVEL     'QBATCH'      JOBQ
     C                   WHEN      TA='AP'
     C                   MOVEL     'APJOBQ'      JOBQ
     C                   WHEN      TA='AR'
     C                   MOVEL     'ARJOBQ'      JOBQ
     C                   WHEN      TA='CM'
     C                   MOVEL     'GLJOBQ'      JOBQ
     C                   WHEN      TA='EM'
     C                   MOVEL     'PRJOBQ'      JOBQ
     C                   WHEN      TA='GL'
     C                   MOVEL     'GLJOBQ'      JOBQ
     C                   WHEN      TA='PE'
     C                   MOVEL     'PRJOBQ'      JOBQ
     C                   WHEN      TA='PR'
     C                   MOVEL     'PRJOBQ'      JOBQ
     C                   WHEN      TA='PY'
     C                   MOVEL     'PRJOBQ'      JOBQ
     C                   WHEN      TA='IR'
     C                   MOVEL     'QBATCH'      JOBQ
     C                   WHEN      TA='RW'
     C                   MOVEL     'QBATCH'      JOBQ
     C                   OTHER
     C                   MOVEL     'QBATCH'      JOBQ
     C                   ENDSL
     C                   CALL      'SYCMBR2'
     C                   PARM                    MLLIB
     C                   PARM                    MLFILE
     C                   PARM                    MLNAME
     C                   PARM                    JOBQ
     C                   ENDIF
     C                   ENDIF
     C                   ENDDO
     C                   SETON                                        LR
     C                   RETURN
      * *  * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

3. CL Program SYCMBR2

QSYS/PGM   PARM(&LIB &FILE &MBR &JOBQ)
             /************************************************/
             /* SYCMBR2                                      */
             /* CALLING PGM - SYGMBR2                        */
             /* LOADS JOBQ'S WITH REORGS                     */
             /* AUTHOR - PMC  BRODART  WMSPT, PA 09/19/97    */
             /************************************************/
             DCL        VAR(&LIB) TYPE(*CHAR) LEN(10)
             DCL        VAR(&FILE) TYPE(*CHAR) LEN(10)
             DCL        VAR(&MBR) TYPE(*CHAR) LEN(10)
             DCL        VAR(&JOBQ) TYPE(*CHAR) LEN(10)
             /*                                              */
             SBMJOB     CMD(RGZPFM FILE(&LIB/&FILE) MBR(&MBR)) +
                          JOB(&FILE) JOBD(MNHJOBD) JOBQ(&JOBQ)
             MONMSG     MSGID(CPF0000)
             RETURN
             ENDPGM

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.


This was first published in April 2001

Dig deeper on Integrated File System (IFS)

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchEnterpriseLinux

SearchDataCenter

Close