Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Securely delete data from a table

Securely delete data from a table

Securely delete data from a table


I don't even know what I am about ask exists or if DB2 does it already.  I am being asked to securely delete data from a table. Its a two fold issue; one is to delete only select records from a table, the second is to delete data from a field.

So for example, table Customers


CustID     Name
1234       Bob Smith
1235       John Dole
Now normally if I want to delete a customer a simple delete script;


delete from Customers where CustID = '1234';
If The customer's name has to be deleted but the record has to stay a simple update command;


update customers
set Name = ''
where CustID = '1234';

Although now I am being asked to "securely" delete the data.  The problem is the person that is asking me this is familiar  with how just deleting a file from a hard drive is not secure.

Trying to google this topic doesn't come up with a lot of data one way or the other.  Any help would be great.


RE: Securely delete data from a table


The problem is the person that is asking me this is familiar  with how just deleting a file from a hard drive is not secure
Which really has nothing to do with "deleting" data content from a column or deleting a row from a table. . .
When values are updated/deleted from a database it is nothing at all like dealing with some "deleted" file.

RE: Securely delete data from a table

Yeah I know that, but is it secure?  It it cached someplace (auto commit turned on)?  Is it in memory for a given time?  FYI I am ignoring user generate backups.

RE: Securely delete data from a table

omacron, I believe that the data is still available if you were issuing a 'dirty read' until it is committed. Once that has happened, the row on the table is physically re-written with the new data.

As far as I am aware, that data is not stored or cached anywhere and to all intents and purposes has been deleted from the system. The only way that I know of of retrieving that data is from an indepth analysis of the DB2 log using software written specifically for that purpose.


RE: Securely delete data from a table

thanks for information.

RE: Securely delete data from a table

Keeping in mind that you're not making periodic backups ...


RE: Securely delete data from a table

As Marc notes, updating a column will physically rewrite the data for the row, wiping out the value. Deleting a whole row will remove the index entries and update the free space in the page, but in the interests of performance and CPU usage this is unlikely to set the resulting free space to low values. So a determined hacker *may* be able to recover the 'deleted' data by looking at the raw data files, at least until the space is reused. There may be an option you can set at the server/database/tablespace/table level to force the write of low values, but I don't know.

But how far do we want to take this? Modern SANs are RAID devices that will smear datasets across a number of disks, and in many cases will write new blocks on different stripes in preference to updating in place to support resilience and failover (which again leaves the old data lying around on the disk). Is your security wonk worried about that too?

I'd be more worried about the log file, as there are plenty of utilities that can read it, and deletion is one of those activities that has to log the complete row to support backout and recovery, otherwise it wouldn't be much use as a log. These don't require a huge knowledge of DB2 internals to use, and most will generate SQL scripts to put the data back or load it to another table right out of the box.


"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object::PerlDesignPatterns)

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close