Important We are integrating BMC AMI LOBMaster technology into the BMC AMI Reorg product and discontinuing BMC AMI LOBMaster as a separate product. For the latest integrated documentation, see BMC AMI Reorg for Db2 13.1.

DELETE

The DELETE FROM option tells BMC AMI Reorg to delete rows from a specified table. You can specify that BMC AMI Reorg should delete rows only when the conditions specified in a WHERE clause are satisfied.

The table name specifies the table to which the delete criterion applies. If you do not specify the creator name, BMC AMI Reorg uses the authorization ID of the user running the reorganization.

The following considerations apply to the DELETE FROM option:

Related topic

DISCARD-or-DELETE-specification-option-descriptions

BMC-AMI-Utilities-SQL-language

NOCHECKPEND

BMC-AMI-Reorg-SETCHECKPEND

  • You cannot use this option when any of the following conditions exists:
    • You are performing an index-only reorganization.
    • 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.
  • DELETE FROM must appear last on the REORG statement.
  • BMC AMI Reorg invokes DSNUTILB when pending limit key changes exist on the table space.
  • You cannot use wildcards with the DELETE FROM option.
  • If you specify DELETE FROM and reorganize objects that are in RREPL status, BMC AMI Reorg invokes DSNUTILB.

  • 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
    DELETE FROM A.TABLE1 WHERE COL01 = 'M'
    DELETE FROM A.TABLE2 WHERE CUSTNO IN
                (SELECT CUSTOMER FROM A.TABLE3 WHERE INVOICE_DATE <
                CURRENT DATE - 30 DAYS))
  • If you specify DELETE FROM and you are using BMC AMI Utility Manager, BMC AMI Reorg discards rows only from the partitions chosen by RTS for reorganization.
  • You can specify only one DELETE clause per table.
  • BMC AMI Reorg optionally writes the deleted rows to the SYSARC archive data set.

creatorName.tableName

Specifies 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 products 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 Primary 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.

When you use WHERE on a partial reorganization, BMC AMI Reorg processes only the specified partitions. If the WHERE condition matches rows in any of the partitions that are not being reorganized, those rows are unaffected. You do not need to specify predicates to limit the WHERE condition to only partitions that you are reorganizing.

  • 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 12 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 DELETE, 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.

Setting CHECK pending status

BMC AMI Reorg sets CHECK pending status

  • If you specify +SETCHECKPEND(YES) and BMC AMI Reorg invokes the IBM DSNUTILB utility, then BMC AMI Reorg sets the CHECK pending status of the dependent table spaces.
  • If you specify the +SETCHECKPEND(YES) option and BMC AMI Reorg runs natively, then BMC AMI Reorg sets the CHECK pending status of the dependent table spaces. 

BMC AMI Reorg does not set the CHECK pending status

  • When the BMC AMI Reorg is run with the default value of +SETCHECKPEND(NO) and rows are discarded from a table with a primary key, BMC AMI Reorg does not set the CHECK pending status of the dependent table spaces.
  • When the BMC AMI Reorg is run with the default value of +SETCHECKPEND(NO) and the IBM DSNUTILB utility is invoked, then NOCHECKPEND option is passed to DSNUTILB. CHECK pending status is not set for the dependent table spaces.
  • If you specify the +SETCHECKPEND(YES) option and the BMC AMI Reorg NOCHECKPEND keyword, and BMC AMI Reorg runs natively, then NOCHECKPEND overrides the +SETCHECKPEND(YES) option and CHECK pending status is not set for dependent table spaces
  • If you specify the +SETCHECKPEND(YES) option and the BMC AMI Reorg NOCHECKPEND keyword, and the IBM DSNUTILB utility is invoked, then NOCHECKPEND overrides the +SETCHECKPEND(YES) option and is passed to DSNUTILB. The CHECK pending status is not set for dependent table spaces.

You might need to perform one of the following steps on the dependent tables:

  • Run the IBM Db2 CHECK DATA utility to restore referential integrity.
  • Run BMC AMI Reorg to delete the dependent rows.

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*