Netezza data warehouse appliance does soft delete until the groom process is performed. Netezza logically deletes the rows by flagging deletexid. The data will be there in the system and you can recover the same whenever you accidentally delete the rows from the table. In this article, We will check how to perform Netezza recover deleted rows.
Netezza Recover Deleted Rows
Every table has the system columns such as createxid, deletexid and rowid. You can check the column by querying these columns from the table.
Whenever you delete the record from the Netezza table, deletexid column will have non-zero value. For newly inserted records, deletexid will have zero value.
Under normal circumstances when you run a select statement you will not see rows that have a deletexid not equal to zero. You can run below command to see delete records:
set show_deleted_records = true;
Netezza Recover Deleted Rows- Query
Below is the query to recover the deleted records from the able:
select createxid,deletexid, * from your_tablewhere deletexid !=0;
Once you can see your deleted data, and figure out which transaction you are trying to recover, you can simply re-insert the data into the final table form which you have deleted or you can copy them to Netezza temp tables, validates the rows and then re-insert them to final table.
insert into your_tableselect * from your_tablewhere deletexid=12345; –transaction id from delete.
Note: You cannot recover the deleted rows if you run GROOM or truncate table. You can only recover rows which are deleted.
Read:
- Identify and Remove Netezza Duplicate Records in Table
- Netezza Internal DataTypes: rowid, createxid, deletexid and datasliceid
- IBM Netezza Rollup Group Aggregates using Grouping sets
- Netezza Cumulative Sum, Average and Example
- IBM Netezza Extract Numbers from String Examples
- Netezza Update Join Syntax and Examples
- Netezza Split Delimited Fields into Table Records and Examples
thank you 🙂