Reorg/Discard utility

The Reorg/Discard utility enables you to delete the data included in the tables using the Purge condition included in the DRLPURGECOND table. This table is provided in IBM Z Decision Support. At the same time, the Reorg/Discard utility automatically reorganizes the table space where data has been deleted.

The records deleted by the Discard function are automatically saved in a specific data set, SYSPUNCH. SYSPUNCH can be used at a later time to reload discarded data in the table, if required.

During the Discard step, the Reorg function reorganizes the table space to improve access performance and reclaim fragmented space. Also, the keyword STATISTICS is automatically selected for the Reorg/Discard, enabling you to collect online statistics during database reorganization.

See the Db2 for z/OS: Utility Guide and Reference, for more information about Reorg/Discard utility.

There are two ways to run the Reorg/Discard utility from the Administration window of IBM Z Decision Support:

From the Tables window, select option 12 from the Utilities pull-down menu.

Figure 1. Tables window - Option 12

  Table  Maintenance  Utilities  Edit  View  Other  Help
------------------------------------------------------------------------------
                    | 12 1. Display...               F11 |  Row 1 to 21 of 129
                    |    2. Show size...                 |
Select one or more  |    3. *mport...                    | definition.
                    |    4. *xport...                    |
/   Tables          |    5. Grant...                     |
_   CICS_DICTIONARY |    6. Revoke...                    |
_   CICS_FIELD      |    7. Document...                  |
_   DAY_OF_WEEK     |    8. Recalculate...               |
_   EXCEPTION_T     |    9. Purge...                     |
_   IMS_APPLICATION |   10. Unload...                    |
_   IMS_APPLICATION |   11. Load...                      |
_   IMS_APPLICATION |   12. Reorg/Discard...             |
_   IMS_CHKPT_IOSAM |   13. DB2HP Unload...              |
_   IMS_CHKPT_POOLS |____________________________________|

In this way, the data contained in the table or tables selected from the table list is discarded, and a space reorganization is automatically performed in the table space where the selected tables reside. The Discard operation is only performed on the selected tables, while the Reorg operation is performed on all the tables contained in the table space. You cannot run the Discard utility on Views or Tables that have any discard condition specified in the DRLPURGECOND table.

As an alternative, use option 1 from the Maintenance pull-down menu of the Tables window to open the Tablespace window, then select option 3 from the Utilities pull-down menu.

In this second scenario, from the Tablespace window, you select the table spaces for the Reorg operation. The Discard operation is automatically run on all the tables contained in the selected table spaces, according to the conditions specified in the DRLPURGECOND table.

All the tables that have the Discard operation specified in the DRLPURGECOND table are included in the processing. All the tables that do not have the Discard operation specified in the DRLPURGECOND table are ignored.

When you run Reorg/Discard, whichever procedure you use, a JCL is created and saved in your library, so that it can be used at a later time, if required. When the JCL is launched, the following data sets are created:

SYSPUNCH: Used to reload the discarded data, if required, using the Load utility.
SYSDISC: Contains the records discarded by the utility.

In addition, SYSREC data set is available. It contains all the records in the table, and you can specify whether you want it to be Temporary or Permanent. If you specify Temporary, the data set is automatically erased at the end of the reorganization job. If you specify Permanent, it is permanently allocated on your disk.

When using the Reorg/Discard utility, you can select one or more tables and table spaces at a time. However, in the data sets SYSPUNCH and SYSDISC, data is overwritten, therefore each data set maintains only the information contained in the last table you processed.

The following is an example of how the Reorg/Discard utility works on a table space that contains several tables:

//REODIS JOB  (ACCOUNT),'NAME'
//*
//***************************************************************
//*    Run Db2 Utility
//*
//* WARNING (REORG/DISCARD):
//* If you want, you can specify the SORTKEYES option:
//* a subtask sorts the index keys. For this optional
//* operation you have need of enough space in your
//* default Storage Diskfor this SORT operation.
//*
//***************************************************************
//DB2UTIL EXEC DSNUPROC,
//  SYSTEM=DSN6,UID=MYUID
//*
//DSNUPROC.STEPLIB  DD  DISP=SHR,DSN='db2loadlibrary'
//DSNUPROC.SYSREC   DD DSN=MYUID.DRLUNLD,UNIT=SYSDA,
//         SPACE=(4096,(1,1)),DISP=(MOD,DELETE,CATLG)
//DSNUPROC.SYSUT1   DD DSN=MYUID.DRLWORK,UNIT=SYSDA,
//         SPACE=(4096,(1,1)),DISP=(MOD,DELETE,CATLG)
//DSNUPROC.SORTOUT  DD DSN=MYUID.DRLSROUT,UNIT=SYSDA,
//         SPACE=(4096,(1,1)),DISP=(MOD,DELETE,CATLG)
//DSNUPROC.WORK     DD DSN=MYUID.WORK1,UNIT=SYSDA,
//         SPACE=(4096,(1,1)),DISP=(MOD,DELETE,CATLG)
//DSNUPROC.SYSPUNCH DD DISP=(MOD,CATLG),
//         DSN=MYUID.TAB.SYSPUNCH,
//         SPACE=(4096,(1,1)),UNIT=SYSDA
//DSNUPROC.SYSDISC DD DISP=(MOD,CATLG),
//         DSN=MYUID.TAB.DISCARDS,
//         SPACE=(4096,(5040,504)),UNIT=SYSDA,
//         DCB=(RECFM=FB,LRECL=410,BLKSIZE=27880)
//DSNUPROC.SYSIN    DD  *
REORG TABLESPACE MYDB.DRLSCOM  LOG YES
 STATISTICS INDEX(ALL) DISCARD
  FROM TABLE MYDB.AVAILABILITY_D
  WHEN (
        DATE < CURRENT DATE - 90 DAYS
        )
FROM TABLE MYDB.AVAILABILITY_T
  WHEN (
        DATE < CURRENT DATE - 14 DAYS
        )
FROM TABLE MYDB.AVAILABILITY_M
  WHEN (
        DATE < CURRENT DATE - 104 DAYS
        )
/*

In this example, the Reorg/Discard utility reorganizes the MYUID.DRLSCOM table space and discards data from the MYDB.AVAILABILITY_D, MYDB.AVAILABILITY_M, and MYDB.AVAILABILITY_T tables. This example shows that the DDNAME for the SYSPUNCH data set is SYSPUNCH, the DDNAME for the discard results data set is SYSDISC, and the DDNAME for the sort output data set is defaulted to SORTOUT. The SYSDISC and SYSPUNCH data sets are reused every time the utility is run for all tables.