DML Insurance
In a chat today, we discussed how to protect data when formulating DML, particularly those SQL update and delete statements that can destroy perfectly good data if done incorrectly. You may find yourself having to run such SQLs against large data sets (and, yes, even production data), so no amount of care is too much.
One option is to hold a transaction open, run the DML, select the (uncommitted) results, and then commit if good or rollback if not. That’s clever, but it can hold a lot of locks for a long time, is error-prone, and doesn’t work for all cases. Another is to keep full backups handy and restore the table(s) if something goes wrong. This can be time consuming and can harm availability during restore if the table(s) are large.
A better approach is to create exports of exactly the data you will be changing or deleting. That is, run an export command whose where clause is exactly the same as that in your update or delete statement. That way, you can restore (via import) just the data you changed. For DB2, I use IXF exports since they’re the richest. So, for example, before you:
update company.employees set salary=100000000 where name=’Derek’
You should:
export to empderek.ixf of ixf select * from company.employees where name=’Derek’
If you realized maybe you shouldn’t have done that, you can put it back with:
import from empderek.ixf of ixf insert_update into company.employees
This can be used to recover from deletes as well (hence insert_update). For example, before you:
delete from wikipedia.articles where title=’Malamanteau’
You should:
export to articles.ixf of ixf select * from from wikipedia.articles where title=’Malamanteau’
MySQL has fewer export/import options, but mysqldump can definitely help. For example, export with:
mysqldump wikipedia articles –compact –where=”title=’Malamanteau’” > articles.sql
And, after the above delete, you can put it back with:
mysql wikipedia < articles.sql
For ease of reference, I often give these “backup” IXF and SQL files unique, descriptive names. It’s helpful to include the date and some tag that identifies what the data is. In the above example, I could have used articles-Malamanteau-12-May-2010.ixf, although Malamanteau is hard to spell and probably isn’t even a real word.
Perhaps this simple technique of “export first, DML second” may rescue you from the occasional “oops moment.”
