How to handle logical delete of data from a DB2 table?

guruji · New User Joined: 13 Apr 2007 Posts: 59 Location: Chennai

Hi,

How can we logically delete data from a DB2 table?Could you please explain the same with an example?What is the difference between a logical delete & a physical delete? Could you suggest any links from where I can collect the same data?

Thanking you,
Reni

mkk157 · Active User Joined: 17 May 2006 Posts: 310

Physical Delete:

Physically removes the data from the DB2 Table. We can achieve this by DELETE statement.

We can delete the data from a DB2 table by specifying a predicate, then the rows that satisfies the predicate are physically removed from the DB2 table.

Logical Delete:

Here in this case, the data is not actually removed, instead, it marks it with the special delete status. i.e. logical deleiton. We can achieve this by UPDATE statement.

We can update data in the DB2 table by specisying the predicate in the UPDATE statement, then the rows that satisfies the predicate are logically removed and replaced by the new data.

guruji · New User Joined: 13 Apr 2007 Posts: 59 Location: Chennai

Hi,

Got an idea about the logical delete. But can you please provide an example on how to make it happen on a DB?

Also can you provide some links from where I can get a clear idea about the same?

Thanks,
Reni

mkk157 · Active User Joined: 17 May 2006 Posts: 310

Hi,

I didn't get any related links for this, But I got a link from Websphere Business Integration. Just I took it as the base and apply the same concept here.

For reference just have a look at the link publib.boulder.ibm.com/infocenter/wbihelp/v6rxmx/index.jsp?topic=/com.ibm.wbia_developer.doc/doc/connector_dev_cplusplus/cplus48.htm

Lets wait for the experts view and more links....

dick scherrer · Posted: Thu Jun 07, 2007 3:16 pm

Hello,

Database engines typically do not provide/support a logical delete (and IMHO do not need one) - when you specify DELETE, that entry is gone. When you specify UPDATE, that entry is changed. There is no "deleted" indicator at the databse level that can be checked to determine if a row has been logically deleted.

There are a few applications that work this way, but the function is application dependent.

mkk157 · Active User Joined: 17 May 2006 Posts: 310

hi dick,

U mean the concept of logical deletion applies only for applications. But not for Databases.

But I have a small doubt, From the follwoing link I found, shows that the concept of logical delete also applies to databases. please clarify it, whether my assumption is right or wrong. Give me some more explanation if possible.

http://publib.boulder.ibm.com/infocenter/wbihelp/v6rxmx/index.jsp?topic=/com.ibm.wbia_developer.doc/doc/connector_dev_java/java61.htm

I am not arguing with u, with enthusiasm i am asking.

dick scherrer · Posted: Thu Jun 07, 2007 5:54 pm

Hello M K K,

Not to worry

I didn't read your reply as an argument and even if so, sometimes arguments/discussions help make things more clear.

I believe an answer is in the first few lines at that link

guruji · New User Joined: 13 Apr 2007 Posts: 59 Location: Chennai

Hi,
Got to understand the handling of logical delete in applications. But still it is not clear that whether the same is applicable to Databases or not.

Regarding the use of UPDATE verb for logical delete, Dick stated that, "When you specify UPDATE, that entry is changed. There is no "deleted" indicator at the databse level that can be checked to determine if a row has been logically deleted.
"

Does it mean that logical delete is not at all possible in Databases?
Could you please make it clear?

Phrzby Phil · Posted: Thu Jun 07, 2007 6:01 pm

Note that logical delete must be implemented to ensure that all related tables, SQL, and other code check appropriately for your logical-delete column. This is the designer/programmer's responsibility.

E.g., if you logically delete a parent row, but do nothing about child rows (in another table), they will still be processed by any code that looks at that table. In this case, you would need to logically delete them, and modify their code to check for their logical-delete column as well, or always access them via code that looks at only child records of non-logically-deleted parents.

guruji · New User Joined: 13 Apr 2007 Posts: 59 Location: Chennai

Ok Dick,

So you tell that, if a status column is added& managed by the programmer, the logical delete can be made possible on Databases using UPDATE verb, right?

I hope I can follow this. Let me try this out.

Thank you all for sharing your knowledge in this regard.

If any one come to know anything more please share with me in the same link,

Thanks,
Reni

dick scherrer · Posted: Thu Jun 07, 2007 7:10 pm

You're welcome

As a caution, please keep in mind that if a deleted indicator is added to the tables, then every query or bit of procesing logic must check if the currently retrieved data is good or if it has been "deleted". Also, keep in mind that if this indicator is used, checking for a "not on file" takes on a new meaning - the sql return code is no longer sufficient to detect a "not on file".

Unless there is some very pressing reason to do so, i'd not recommend using a "deleted indicator" - FWIW.