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
 

 

Automatic delete after n entries in the table for a Keyvalue

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

New User


Joined: 11 Oct 2004
Posts: 69
Location: chennai

PostPosted: Thu Sep 02, 2010 1:11 pm    Post subject: Automatic delete after n entries in the table for a Keyvalue
Reply with quote

Hi,

I have a requirement to create a History table.Whenever an update or delete is done in few tables through online screen, before update or delete an entry should be there in the history table with the current value in the table with time,user id etc.

But we need to maintain the last 20 entries in the table for a KEY value. Key value in our table is Program Name,status(update,delete),Table name. If we have entry for the 21st time the first entry needs to be deleted. So the history should be maintained only for the last 20 entries.

Please let me know how to achieve this.


Regards,
Rajan BOSE.
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Thu Sep 02, 2010 4:15 pm    Post subject:
Reply with quote

how are you determining that you need to INSERT to the History table?
Back to top
View user's profile Send private message
srajanbose
Warnings : 1

New User


Joined: 11 Oct 2004
Posts: 69
Location: chennai

PostPosted: Thu Sep 02, 2010 4:22 pm    Post subject:
Reply with quote

Hi Dick,

If we request a update for a row in table 'A' through online screen before updating the table 'A' we need to move the current value from table 'A' to the history table and then update the table 'A' with new values.

So based on the 'status' value from online screen (If its update then the value of status is 'U' and 'D' for delete) we need to insert the current value in the history table.


Regards,
Rajan BOSE.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Thu Sep 02, 2010 4:41 pm    Post subject:
Reply with quote

so, you already have code in any module that Updates or Deletes from active table.

I would use Trigger on INSERT/UPDATE to the History table, do a count and delete the earliest.
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 Loading data to table gives wrong for... Raghu navaikulam DB2 18 Thu Jul 13, 2017 2:11 pm
No new posts unload data from table with lob columns farhad_evan DB2 1 Sat Apr 22, 2017 1:32 pm
No new posts Data replication from multiple Db2 ta... kishpra DB2 9 Mon Mar 27, 2017 9:58 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
No new posts Join of more than one table dibyendumandal11111 DB2 6 Mon Feb 13, 2017 11:36 pm


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