Netezza CREATEXID DELETEXID

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 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

Meet Ananth Tirumanur. Hi there 👋

I work on projects in data science, big data, data engineering, data modeling, software engineering, and system design.

👨‍💻 All of my projects are available at https://github.com/akrish1982
💬 Ask me about Data engineering, SQL, Databases, Data pipelines, Data infrastructure
📄 My work history: https://www.linkedin.com/in/ananthtirumanur/
⚡ Fun fact: Marathoner & Casual Birding enthusiast

Connect with me:

Twitter: @akrish82
LinkedIn: https://linkedin.com/in/ananthtirumanur/

My Resources:

LinkedIn Newsletter: Data Engineering with AWS
Udemy Course: AWS Certified Data Engineer Associate Practice Test
Python Crash Course: Python Crash Course on Gumroad

Languages and Tools:

AWS, Bash, Docker, Elasticsearch, Git, Grafana, Hadoop, Hive, EMR, Glue, Athena, Lambda, Step Functions, Airflow/MWAA, DynamoDB, Kafka, Kubernetes, Linux, MariaDB, MySQL, Pandas, PostgreSQL, Python, Redis, Scala, SQLite