Reading Time: 2 minutes


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
Netezza Createxid and Deletexid

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