DISCARD

DISCARD FROM TABLE tells BMC AMI Reorg to delete rows as part of the reorganization process based on a condition or set of conditions.

DISCARD FROM TABLE must appear last on the REORG statement. The following considerations apply to the DISCARD option:

• You cannot use this option when any of the following conditions exists:
  • You are reorganizing objects based on an object set.
  • You are rebalancing partitions.
  • The table space contains an archive table.
  • You are reorganizing system-maintained temporal table spaces when versioning is active.On such table spaces, deleted rows go to the history table.
  • You are using wildcards.
• BMC AMI Reorg invokes DSNUTILB when pending limit key changes exist on the table space.

• If the table space contains multiple tables, you can repeat the FROM TABLE keywords and table name, as in the following example:

  REORG TABLESPACE A.B DISCARDDN DELETES
  DISCARD FROM TABLE A.TABLE1 WHERE (COL01 = 'M')
          FROM TABLE A.TABLE2 WHERE (CUSTNO IN
              (SELECT CUSTOMER FROM A.TABLE3 WHERE INVOICE_DATE <
              CURRENT DATE - 30 DAYS))

• If you specify DISCARD and you are using BMC AMI Utility Manager, BMC AMI Reorg discards rows only from the partitions chosen by Utility Manager criteria for reorganization.

  creatorName.tableName

  Names the table whose rows are to be deleted

  WHERE

  This keyword (or its equivalent, WHEN) begins the specification of an SQL WHERE clause. The following requirement and restriction apply to this keyword: You cannot use wildcards. For more information about BMC AMI Utilities WHERE clause options, see BMC-AMI-Utilities-SQL-language. The most efficient WHERE clauses are the ones that BMC AMI Utilities can process natively without sending the SQL to Db2. BMC AMI Utilities WHERE also supports WHERE columnName IN FILE(ddname), which can be very efficient and useful when discarding the same keyed rows from multiple tables. The file specified in the WHERE clause is allocated to the Master job so this job must run with SVRMODE (INTERNAL). If a SYSPUNCH DD statement exists, the LOAD statement for the discarded rows will be written to the data set. In addition, you must specify the FORMAT keyword. However, if FORMAT UNLOAD is in effect, BMC AMI Reorg does not write generated LOAD statements to SYSPUNCH.

• (SPE2010) Tables defined with the DATA CAPTURE CHANGES attribute are commonly known as Change Data Capture (CDC) tables. Running BMC AMI Reorg on CDC tables can cause data being replicated to become inconsistent until a refresh is done. The Db2 V12 subsystem parameter UTILS_BLOCK_FOR_CDC specifies that certain Db2 Utilities will be blocked for CDC tables or tables spaces containing CDC tables.

  When processing REORG TABLESPACE with DISCARDS, BMC AMI Reorg checks whether any CDC tables are being processed. If BMC AMI Reorg is processing a CDC table and the UTILS_BLOCK_FOR_CDC subsystem parameter is set to YES, BMC AMI Reorg issues an error message and proceeds with +CLEANUP processing.