| View previous topic :: View next topic |
| Author |
Message |
sumithar
Beginner
Joined: 22 Sep 2006
Posts: 84
Topics: 29
|
 Posted: Wed Apr 09, 2014 10:13 am Post subject: History data |
 |
|
currently we store history data on the same table. when a table row is updated we don't actually update it, we end date existing row and insert a new row with the changed data.
I know that log trigger tables are an alternative to this, so the "main" table has only one row per "instance" so to speak.
Are these temporal tables an alternative to this too? how do they do that?
Thanks! |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
|
| Posted: Wed Apr 09, 2014 1:29 pm Post subject: |
|
|
sumithar,
How about writing the deleted record to a GDG ? You would have a daily generation with the deleted records and the at end of the month you run another job to create a monthly generation which deletes all the daily generations once merged. You would have similar process for the year end where it takes all the monthly generations and then creates 1 yearly generation.
Since you are writing to a file you can have all the identifiers like current timestamp to identify when the record is written to the file
Kolusu |
|
| Back to top |
|
|
sumithar
Beginner
Joined: 22 Sep 2006
Posts: 84
Topics: 29
|
| Posted: Thu Apr 10, 2014 2:35 pm Post subject: |
|
|
| kolusu wrote: | sumithar,
How about writing the deleted record to a GDG ? You would have a daily generation with the deleted records and the at end of the month you run another job to create a monthly generation which deletes all the daily generations once merged. You would have similar process for the year end where it takes all the monthly generations and then creates 1 yearly generation.
Since you are writing to a file you can have all the identifiers like current timestamp to identify when the record is written to the file
Kolusu |
Thanks Kolusu. Not sure what you mean by the "deleted" row? Are you talking about the row before the update happens? Instead of writing it out into a log trigger table, write it out into a file? |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
|
| Posted: Thu Apr 10, 2014 3:05 pm Post subject: |
|
|
| sumithar wrote: | | Are you talking about the row before the update happens? Instead of writing it out into a log trigger table, write it out into a file? |
Yes.
Kolusu |
|
| Back to top |
|
|
sumithar
Beginner
Joined: 22 Sep 2006
Posts: 84
Topics: 29
|
| Posted: Mon Apr 14, 2014 6:41 pm Post subject: |
|
|
| kolusu wrote: | | sumithar wrote: | | Are you talking about the row before the update happens? Instead of writing it out into a log trigger table, write it out into a file? |
Yes.
Kolusu |
That's viable too, but with the log trigger, that process is automated for me, isn't it? If push comes to shove I can always unload the history table to a file and load replace it periodically, especially if it's partitioned, as part of a periodic purge/archive.
I have done some reading about these temporal tables, they are somewhat similar in approach too. |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
