In Netezza, it is crucial to comprehend the concept of transaction ids (xids) when dealing with data manipulation operations such as delete, update, or insert.
Understanding Transaction Ids
Every transaction, whether an insert, update, or delete, is assigned a sequential transaction id (xid).
The latest transaction for each row is indicated by the createxid.
Deleted rows are assigned a deletexid. If a row is readable (not deleted), its deletexid is set to 0. When a row gets deleted, this column gets populated with the transaction id assigned to the delete or update statement.
Updates are essentially deletes followed by inserts, where the transaction id from the old row becomes the deletexid and a new createxid is assigned to the new row.
Viewing Deleted Rows
By default, you will not see rows that have a non-zero deletexid. To change this behavior, set the following option:
set show_deleted_records = true
Restoring Netezza Records
Here is a simple select statement to view all data, including deleted rows:
select createxid,deletexid, * from table
Viewing Only Deleted Rows
To view only the deleted rows, use the following query:
SELECT * FROM table WHERE deletexid IS NOT NULL;
Reverting an UPDATE or INSERT Operation
Since Netezza doesn't support transactions in the traditional sense, there is no direct way to undo an UPDATE or INSERT. However, you can implement a workaround by using the following strategies:
Using a Temporary Table: Store the original data in a temporary table before performing an update or insert operation. Then, if needed, you can swap the tables to restore the previous state.
Using a Versioning Strategy: Use a versioning strategy for tracking changes in your data. This involves storing multiple versions of the table, which allows you to roll back to any previous state when needed.