Designing a Backout Strategy for DB2 Database Changes

You may also like...

4 Responses

  1. Michael Davies says:


    I listened to a recording of your DB2 Night Show on this topic recently and enjoyed it very much.

    Firstly, I have a question:-

    When preserving the current data before an insert, update or delete statement, why do you export the data rather than copying it to a backup copy of the table within the database?

    Secondly, I’d like to share my thoughts on exporting data for the purpose of backing out a DML change:-

    I have experienced problems in the past when importing delimited data back into a table. Issues such as the delimiter character and newline characters appearing in the data can cause problems. For that reason I always prefer to export in IXF format.

    When using one statement to export the data and another to delete or update it, there is a small risk that the WHERE clause on the two statements may not be identical. One technique I particularly like is combining the export, select and delete/update statements as shown in the example below.

    export to backup.ixf of ixf
    select *
    from old table
    delete from
    where …

    This provides a fool-proof way of exporting exactly the rows that have been deleted or updated.



    • Ember Crooks says:

      First, so sorry for the late response. My spam filter and I seem to be having some relationship issues that I’ve just become aware of.

      I generally prefer .del because it is easily human readable, and some backouts may require me to actually go through the data to pull out a subset of it. Yes, I could also create a table to do that, I suppose, but I have had developers ask for a csv of the data before and it’s nice to just have it. I have never had issues with the data in del format, but I admit to exporting in both formats for some changes, just because I like options to be available on backout.

      The main reason I don’t create “shadow” tables is that some of my clients object to tables being created or require change management around creating a table – so I avoid it pretty strongly. But it’s certainly a valid approach if it works for your environment.

      The idea of selecting from the delete is intriguing to me. If I can get past my desire to have my backout data in place before actually performing the change, that might work nicely. I’ll have to play with it a bit and see how I like it.

      Thanks for the comment – interesting stuff here.

  2. Sathy says:

    Mike/Ember, Thanks both for sharing techniques. Quiet useful and interesting to know what people out there do.

    I prefer Mike’s idea of exporting to ixf – and if needed the data into a table (in the same database or a test database, depending on the need) . But in the context of Commerce, I believe csv is good too.

    ixf gives platform portability and is much easier for a quick import with CREATE_REPLACE option, IMHO .

    Mike, I have used your technique of exporting from OLD TABLE occasionally , but I am not very comfortable with it yet. My concern is – What happens if the export fails , say, filesystem is full in the middle of the export ? Does the delete carry on nevertheless or does it roll back, given that EXPORT cannot be called within the context of UOW (it does internal commits as far as I understand), but correct me if I am wrong . If the export fails during initialization (eg. you are writing to a read only filesystem) , the delete statement does not happen

    Going back to Mike’s point of copying data into another table within the same database, you cannot use INSERT INTO BACKUP_TABLE SELECT * FROM OLD TABLE(DELETE FROM …) . A work around is to load with cursor

    Again, the problem is what happens if this fails.

    Note to Ember: Feel free to moderate. if my response is not relevant to your main topic. apologies.

    • Ember Crooks says:

      I only moderate comments because I get a lot of spam comments. I approve every comment that’s legitimate except for the few where the poster just wants to get in contact with me rather than actually comment on the topic. I love every real comment!

Leave a Reply

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