Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

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

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
guruji
Warnings : 1

New User


Joined: 13 Apr 2007
Posts: 59
Location: Bangalore

PostPosted: Thu Jun 07, 2007 10:41 am    Post subject: How to handle logical delete of data from a DB2 table?
Reply with quote

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
Back to top
View user's profile Send private message

mkk157

Active User


Joined: 17 May 2006
Posts: 310

PostPosted: Thu Jun 07, 2007 2:09 pm    Post subject: Re: How to handle logical delete of data from a DB2 table?
Reply with quote

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.
Back to top
View user's profile Send private message
guruji
Warnings : 1

New User


Joined: 13 Apr 2007
Posts: 59
Location: Bangalore

PostPosted: Thu Jun 07, 2007 2:50 pm    Post subject: Can you provide logical delete with an example?
Reply with quote

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
Back to top
View user's profile Send private message
mkk157

Active User


Joined: 17 May 2006
Posts: 310

PostPosted: Thu Jun 07, 2007 2:55 pm    Post subject:
Reply with quote

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 http://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....
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Jun 07, 2007 3:16 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
mkk157

Active User


Joined: 17 May 2006
Posts: 310

PostPosted: Thu Jun 07, 2007 3:46 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Jun 07, 2007 5:54 pm    Post subject:
Reply with quote

Hello M K K,

Not to worry icon_smile.gif 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
Quote:
An application can support one of the following types of delete operations:

Physical delete--Data is physically deleted from the database.
Logical delete--A status column in a database entity is set to an inactive or invalid status, but the data is not deleted from the database.


Notice that the first sentence mentions that an application can support this (not the database system). In the logical delete, notice that a status column needs to be added and managed by the code rather than the dbms.

This does not mean that either is better than the other. Whether "this" must be sync'ed with "something else" may need consideration.

I'd recomend that the entire application and interactions with others be considered and then pick the strategfy that works for the particular case.
Back to top
View user's profile Send private message
guruji
Warnings : 1

New User


Joined: 13 Apr 2007
Posts: 59
Location: Bangalore

PostPosted: Thu Jun 07, 2007 5:57 pm    Post subject:
Reply with quote

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?
Back to top
View user's profile Send private message
Phrzby Phil

Active Member


Joined: 31 Oct 2006
Posts: 962
Location: Richmond, Virginia

PostPosted: Thu Jun 07, 2007 6:01 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
guruji
Warnings : 1

New User


Joined: 13 Apr 2007
Posts: 59
Location: Bangalore

PostPosted: Thu Jun 07, 2007 6:19 pm    Post subject: Re: How to handle logical delete of data from a DB2 table?
Reply with quote

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
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Jun 07, 2007 7:10 pm    Post subject:
Reply with quote

You're welcome icon_smile.gif

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.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Export flat file data into excel sheet murali.andaluri DFSORT/ICETOOL 2 Mon Mar 20, 2017 5:39 pm
No new posts Append data from two files into a sin... Praveen04 DFSORT/ICETOOL 5 Thu Mar 16, 2017 7:29 pm
No new posts SPUFI -- Joining 3 tables – data in... Sysaron DB2 2 Wed Mar 08, 2017 4:18 am
No new posts Overriding PS data in rexx Shaheen Shaik CLIST & REXX 8 Fri Mar 03, 2017 5:08 pm
No new posts how to send just 10 rows in a CICS sc... Megha Gupta CICS 5 Thu Feb 23, 2017 6:57 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us