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.
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.
- SYSPUNCH
- Used to reload the discarded data, if required, using the Load utility.
- SYSDISC
- Contains the records discarded by the utility.
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.