How to “Undo” a Delete, Update, or Insert in Netezza

The great thing about Netezza is it does “soft” deletes until the groom process runs.  This means your deleted data is really still there.  This is of course very important if you just deleted something you didn’t mean too.  And since “updates” are really just a delete of the old values and an insert of the new behind the scenes, you can actually “undo” updates too.

So first a little about how Netezza handles transactions.  Every insert, update, or delete transaction is assigned a sequential transaction id or xid.   You can see this id by querying the column createxid .  Also there is a column to indicate deleted rows called deletexid.  This is set to 0 if this is a readable, not deleted row.  When the row gets deleted, this column gets populated with the transaction id assigned to the delete or update statement.  And as I mentioned above , updates are handled by doing a delete of the old record and inserting the entire new row with the new values.

Under normal circumstances when you run a select you will not see rows that have a deletexid not equal to zero.  But there is a simple session variable that can be set that allows you to see these deleted rows.  The simple command to run in your favorite query tool is …

set show_deleted_records = true

So to see what’s there you can then just select from your table and include the 2 transaction id columns, like ..

select createxid,deletexid, *

from your_table

If you want to see just the deleted rows , just select where deletexid is not zero

select createxid,deletexid, *

from your_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 your_table

select * from your_table

where deletexid=233443; –transaction id from delete.

To undo an update, just re-insert the deleted rows and delete the inserted rows.

insert into your_table

select * from your_table

where deletexid=233443 ;–transaction id from update

delete from your_table

where createxid=233443; –transaction id from update

And I’m stating the obvious, but to undo an insert, you don’t even need to show deleted rows, just delete by the transaction id

delete from your_table

where createxid=233443; –transaction id from insert

Do be warned, you cannot undo a truncate table!

 

This entry was posted in SQL Tricks and tagged , , , . Bookmark the permalink.

13 Responses to How to “Undo” a Delete, Update, or Insert in Netezza

  1. chinna says:

    Could you please give some on idea on how commit and rollback works in netezza
    i am an oracle guy and i m really new to netezza.

    • NZGuy says:

      Default behavior is autocommit where a commit happens at the completion of each sql command. If you desire transaction control you explicitly begin a transaction with BEGIN and end it with a COMMIT or ROLLBACK. Example

      BEGIN;
      UPDATE CUSTOMER SET FIRST_NAME = UPPER(FIRST_NAME);
      ROLLBACK;

      A lot of this behavior is inherited from Netezza’s Postgres underpinnings. I find the syntax for Postgres most often works with Netezza. So if in doubt, try searching for how it’s done in Postgres.

  2. russelr says:

    Good tips !! . But I have a different problem. I’m trying to drop a big table of 10 TB and its taking too long. Kept for almost a day but no results. Have you come across such an issue. Any tips will help .

    • NZGuy says:

      A drop should be fairly quick. I suspect something has some sort of lock on it. I know on the old 10xxx systems a reclaim that didn’t complete cleanly would do that. Perhaps a groom, maybe a backup. I’ll look into this some more and see if I can figure out how to tell what might have a lock.

  3. chinna says:

    When i am trying to follow the approach to delete the records based on the transaction id ( trasaction id is the id which deleted a record), it is throwing me an error:

    ERROR: 0x59947a : Concurrent update or delete of same row

    Could you tell me the reason behind that .. and please tell me how can i delete these deleted records completely from the table

    Below is the step by step scenario:

    CREATE TABLE DEPT_dummy (DEPTNO INTEGER, DNAME VARCHAR(10))

    INSERT INTO DEPT_dummy VALUES (10,’A’);
    INSERT INTO DEPT_dummy VALUES (20,’B’);

    set show_deleted_records = true

    SELECT createxid,deletexid,deptno,dname FROM DEPT_dummy

    delete from dept_dummy where deptno=20;

    output:
    1 row affected

    delete from dept_dummy where deletexid=5870770

    ERROR: 0x5994b2 : Concurrent update or delete of same row

    Could you please tell me what could be the reason for that, because i have an etl which does the deletes from a table and i want to prepare a process which cleans up these deleted stuff

    • NZGuy says:

      Doing a delete through sql will only do a soft delete. You are getting this error because you are trying to soft delete an already soft deleted row. If you want to make these rows go away, use the groom command if you are on the Twinfin platform or reclaim if you are on the older 10xxx platform.

      • chinna says:

        Thanks for your answers, but after doing a soft delete, i am trying to remove the deleted records even using the below command

        groom table dept_dummy;

        and after executing this command i still see that record with some deletexid0.

        What could be the reason for this, after running the groom command why the deleted record still exists?

        • NZGuy says:

          Default behavior for groom is for the system to synchronize your groom request with the most recent backup set to prevent reclaiming rows that have not yet been backedup by incremental backups. There is a “RECLAIM BACKUPSET NONE” phrase you can put on your groom command to override this, BUT WARNING THIS MAY FORCE YOUR NEXT BACKUP TO BE A FULL BACKUP. I would not recommend this.

  4. Gary Colbran says:

    Don’t play with this setting on a production system. It is not supported by IBM. Further, updating deleted rows (it is possible, I’ve tried it in a lab) can result in the crashing of NPS (it does, I did it in a lab) so you have been warned.
    This is a last resort option to recover deleted data and is not recommended.

    • NZGuy says:

      To clarify the original post NEVER recommended updating the deleted row. It was a method to reinsert the data. The setting , IF you are referring to the show_deleted_records = true, was only set at the session level. The is NO issue with this method.

  5. Niral Koradiya says:

    Hi,
    I have truncated table. Is there any way to recover those records ?
    I have tried above things. But my luck is not working. can anybody help me ?.

    • NZGuy says:

      Truncate releases the storage so there is no undelete from a truncate. If you have a backup, you can recover a single table, but without a backup, short of an NSA style disk based recovery, it’s gone.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>