Writer instructions
Page title	For most spaces, this page must be titled Space announcements. For spaces with localized content, this page must be titled Space announcements l10n.
Purpose	Provide an announcement banner on every page of your space.
Location	Move this page outside of your home branch.
Guidelines	Space-announcements-banners

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:

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.

Warning

If you are deleting rows from a table with a primary key, BMC AMI Reorg does not:

Guarantee or maintain any referential integrity of the primary keys
Set the CHECK pending status

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

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

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

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.

DELETE

On this page