16.10 - DELETE - Teradata Database

Teradata Database Support Utilities

prodname
Teradata Database
vrm_release
16.10
created_date
June 2017
category
Configuration
featnum
B035-1180-161K

Purpose

The DELETE command deletes the currently selected object or range of objects, including all descendants and antecedents, meaning the delete cascades both up and down through the index structure.

The DELETE command can damage the File System B*Tree such that data can be removed or lost permanently. If used incorrectly, changes might be irreversible.

Syntax



/Y
Prevents Filer from prompting for confirmation before executing the command.

Usage Notes

The selected object or range is from the last operation and is saved in the context. Executing SHOWD displays the selected object or range. For more information, see SHOWDEFAULTS.

The delete context can be a row, table, DB, DBD, CI, CID, WAL log record, WDB, WDBD, WCI, WCID, or DEPOT, and ranges thereof. If a WAL object is selected, DELETE cascades throughout the WAL log index structure.

DELETE can apply to one, several or all AMPs, depending on the current scope. The DELETE confirmation scope can span AMPs but is condensed into a single range. For some ranges, DELETE is allowed only when the Teradata Database is down.

If you delete a single DEPOT cylinder, the scope can be on one AMP or all AMPs. If you delete the entire DEPOT, the scope must be on all AMPs. This command is allowed only when the Teradata Database is offline.

If there is a whereclause in the context, then only those rows or WAL log records that satisfy the whereclause are deleted. For more information on the whereclause, see Specifying a WHERE Clause.

Only tables and rows can be deleted if running from remote DBW console with multiple vprocs selected.

If the selected object is a CI, then the DELETE command effectively performs the same action as the INIT command. For more information, see INIT.

For example, deleting a CI would remove the CID from the MI and all DBs with DBDs in the CI.

Deleting Records from the WAL Log

When DELETE is used to delete records in the WAL log that have been selected by using the WAL or WREC command qualified with a WHERE clause, you can use the INQUIRE and ABORT commands to check the status or halt the DELETE operation.

The INQUIRE command has the following syntax:


INQUIRE displays the status of the fastest and slowest vprocs, and minimum WAL log sequence number (WLSN) of all WLSNs that are currently being processed across all vprocs:

> inquire

Slowest vproc      2  is   30% done
Fastest vproc      0  is   58% done
DELETE is currently checking WLSN 0x0000000002B7D84A
DELETE is about   43% done


The ABORT command has the following syntax:


After it is initiated, ABORT stops the DELETE process and reports the number of records deleted so far and the range of WAL log that has not been processed.

When the abort is successful, the following appears:

> abort
Abort request has been sent

Type ’ABORT’ to stop the command before completion
Type ’INQUIRE’ to check on progress of command

vproc  0 (0000)  response
 604018 (93772) record(s) deleted.
 WAL records from 0x00000000002D331E to 0x0000000000717807 are not processed
1 of 1 vprocs responded with no messages or errors.
WAL Record(s) DELETE Aborted
Filer ==>