Neoview SQL Reference Manual (R2.4 SP2)

PURGEDATA Utility
• “Syntax Description of PURGEDATA”
• “Considerations for PURGEDATA”
• “Example of PURGEDATA”
The PURGEDATA utility deletes all data from a Neoview SQL table and its related indexes.
The PURGEDATA utility is a syntax-based utility that can be executed in the Neoview Command
Interface.
PURGEDATA object [IGNORE_TRIGGER] [NOLOG]
Syntax Description of PURGEDATA
object
is the name of the table from which to purge the data. See “Database Object Names” (page 230).
IGNORE_TRIGGER
specifies that PURGEDATA should ignore DELETE triggers on the table. If they are not
ignored and a DELETE trigger exists, PURGEDATA fails.
NOLOG
specifies, for a table with active materialized views, that you want to perform PURGEDATA.
When there are materialized views on a table, every update on the table is stored in the IUD
log. Later, when a refresh is performed on the materialized view., the IUD log is read and
the updates applied. PURGEDATA is basically a delete, so given the rules of materialized
views, all rows deleted through the PURGEDATA operation should be stored in the IUD log
for later replay. However, for PURGEDATA this is not possible. The NOLOG option was
devised to indicate that the deletes occurring during a PURGEDATA operation are not logged.
If you have an active materialized view and you do not specify the NOLOG option, the
PURGEDATA fails. For a table with active materialized views, you need to specify NOLOG
in order to perform PURGEDATA. If you want the deletes to be stored in the IUD log and
replayed during a REFRESH, then you must use DELETE instead of PURGEDATA.
Considerations for PURGEDATA
• You must have SELECT/DELETE privileges on the table.
• table-name can be either a table name or a materialized view name.
• If table-name is a materialized view name, IGNORE_TRIGGER is not supported and will
be ignored if specified.
• If the NOLOG option is not specified, an error is returned if PURGEDATA tries to purge a
table that has a materialized view defined on it.
• If the NOLOG option is not specified, an error is returned if PURGEDATA tries to purge a
materialized view that has a materialized view defined on it.
• Errors are returned if table cannot be accessed or if a resource or file system problem causes
the delete to fail.
• PURGEDATA is not supported for volatile tables.
Availability and Accessibility
You must be the owner of the schema, owner of the object, or have SELECT/DELETE privileges
on the table or materialized view.
PURGEDATA marks the table or materialized view OFFLINE and sets the corrupt bit while
processing. If PURGEDATA fails before it completes, the table and its dependent indexes will
be unavailable and you must run PURGEDATA again to complete the operation and remove
220 SQL Utilities