Note: TRUNCATING TABLE cannot be UNDONE
Note: Netezza “soft” deletes until the groom process runs. i.e. deleted data is really still there.
Note: Netezza Updates are just soft deletes and inserts. So, they can be recovered too
- Every insert, update, or delete transaction is assigned a sequential transaction id or xid.
- To indicate which transaction last updated the row, createxid indicates the latest transaction for each row
- To indicate deleted rows deletexid, assigned to each row is set to 0 if this is a readable (not a deleted row). When the row gets deleted, this column gets populated with the transaction id assigned to the delete or update statement
- Updates are essentially deletes that set the transaction id as deletexid to old row and createxid in the new row
- Groom would make the soft deletes final
Typically, when you run a select you will not see rows that have a deletexid not equal to zero. To change that, set the below option
set show_deleted_records = true
How to restore Netezza records
Here is a simple select statement:
select createxid,deletexid, * from table
If you want to see just the deleted rows , just select where deletexid is not zero
select createxid,deletexid, * from table where deletexid !=0
Once you can see your deleted data, and figure out which transaction you are trying to undo, you can simply re-insert the data
insert into table select * from table where deletexid=142233; –transaction id from delete.
To undo an update, just re-insert the deleted rows and delete the inserted rows.
insert into table select * from table where deletexid=146443 ;–transaction id from update delete from table where createxid=146443; –transaction id from update
To undo an insert, use the below SQL
delete from table where createxid=1434443; –transaction id from insert