Compare Data in Two table
A shop nightly compares 90 days of OLTP data against an ODS copy (both IDS 9.4 on HP-UX) and wants to identify only changed/new rows, since the Update_DtTime columns aren't reliably maintained. Replies suggested INSERT/UPDATE/DELETE triggers writing changed keys to a log table, or using replication. The poster objected that triggers across 35-40 large tables could slow a busy live web site. Madison Pruet recommended Enterprise Replication with timestamp conflict resolution (replicating just the primary keys, with upserts on the target so no initial sync is needed) as lower impact than triggers. No confirmation of what was finally implemented is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
We have a OLTP and ODS databases and every night we migrate data from OLTP to ODS database. Both OLTP and ODS use IDS dyamic 9.4 on HP-UX. We purge records in OLTP database after 90 days. Currently every night we compare entire data in OLTP database with ODS and add new rows to ODS and update those changed. Is there a way to know only what changed in source database? This will allow only delta's or new rows to be migrated to ODS instead of comparing 90days of OLTP data every night with ODS? Most of the tables do have Update_DtTime column but there is no gurantee that all process(including some back ground) process correctly update that column. ANy help you provide will be appreciated.
Just create triggers on INSERT, UPDATE and DELETE and write the key to a new table. Then you only have to transfer datarecords where you find the key in this protocol table. gerd "TUKOBA INGALE" <tukoba@yahoo.com> schrieb am 15.05.05 07:32:32: > > We have a OLTP and ODS databases and every night we migrate data from OLTP to ODS database. Both OLTP and ODS use IDS dyamic 9.4 on HP-UX. > We purge records in OLTP database after 90 days. Currently every night we compare entire data in OLTP database with ODS and add new rows to ODS and update those changed. > Is there a way to know only what changed in source database? This will allow only delta's or new rows to be migrated to ODS instead of comparing 90days of OLTP data every night with ODS? Most of the tables do have Update_DtTime column but there is no gurantee that all process(including some back ground) process correctly update that column. > ANy help you provide will be appreciated. > __________________________________________________________ Mit WEB.DE FreePhone mit hoechster Qualitaet ab 0 Ct./Min. weltweit telefonieren! http://freephone.web.de/?mc=021201
Sounds like what you want is replication. j. ----- Original Message ----- From: "TUKOBA INGALE" <tukoba@yahoo.com> To: <ids@iiug.org> Sent: Sunday, May 15, 2005 1:52 AM Subject: Compare Data in Two table [4954] > We have a OLTP and ODS databases and every night we migrate data from OLTP to ODS database. Both OLTP and ODS use IDS dyamic 9.4 on HP-UX. > We purge records in OLTP database after 90 days. Currently every night we compare entire data in OLTP database with ODS and add new rows to ODS and update those changed. > Is there a way to know only what changed in source database? This will allow only delta's or new rows to be migrated to ODS instead of comparing 90days of OLTP data every night with ODS? Most of the tables do have Update_DtTime column but there is no gurantee that all process(including some back ground) process correctly update that column. > ANy help you provide will be appreciated. >
--0__=09BBFA91DFCA97678f9e8a93df938690918c09BBFA91DFCA9767 Content-type: multipart/alternative; Boundary="1__=09BBFA91DFCA97678f9e8a93df938690918c09BBFA91DFCA9767" --1__=09BBFA91DFCA97678f9e8a93df938690918c09BBFA91DFCA9767 Content-type: text/plain; charset=US-ASCII Content-transfer-encoding: quoted-printable What is a OLTP and ODS database? If both of these are IDS, then you co= uld consider using ER. = "TUKOBA INGALE" = <tukoba@yahoo.com = > = To Sent by: ids@iiug.org = forum.subscriber@ = cc iiug.org = Subj= ect Compare Data in Two table [4954= ] 05/14/2005 11:52 = PM = = = = = We have a OLTP and ODS databases and every night we migrate data from O= LTP to ODS database. Both OLTP and ODS use IDS dyamic 9.4 on HP-UX. We purge records in OLTP database after 90 days. Currently every night = we compare entire data in OLTP database with ODS and add new rows to ODS a= nd update those changed. Is there a way to know only what changed in source database? This will allow only delta's or new rows to be migrated to ODS instead of compari= ng 90days of OLTP data every night with ODS? Most of the tables do have Update_DtTime column but there is no gurantee that all process(includin= g some back ground) process correctly update that column. ANy help you provide will be appreciated. = --1__=09BBFA91DFCA97678f9e8a93df938690918c09BBFA91DFCA9767 Content-type: text/html; charset=US-ASCII Content-Disposition: inline Content-transfer-encoding: quoted-printable <html><body> <p>What is a OLTP and ODS database? If both of these are IDS, then you= could consider using ER.<br> <br> <img src=3D"cid:10__=3D09BBFA91DFCA97678f9e8a93df938@us.ibm.com" width=3D= "16" height=3D"16" alt=3D"Inactive hide details for "TUKOBA INGALE= " <tukoba@yahoo.com>">"TUKOBA INGALE" <tukoba@y= ahoo.com><br> <br> <br> <table width=3D"100%" border=3D"0" cellspacing=3D"0" cellpadding=3D"0">= <tr valign=3D"top"><td style=3D"background-image:url(cid:20__=3D09BBFA9= 1DFCA97678f9e8a93df938@us.ibm.com); background-repeat: no-repeat; " wid= th=3D"40%"> <ul> <ul> <ul> <ul><b><font size=3D"2">"TUKOBA INGALE" <tukoba@yahoo.com&= gt;</font></b><font size=3D"2"> </font><br> <font size=3D"2">Sent by: forum.subscriber@iiug.org</font> <p><font size=3D"2">05/14/2005 11:52 PM</font></ul> </ul> </ul> </ul> </td><td width=3D"60%"> <table width=3D"100%" border=3D"0" cellspacing=3D"0" cellpadding=3D"0">= <tr valign=3D"top"><td width=3D"1%" valign=3D"middle"><img src=3D"cid:3= 0__=3D09BBFA91DFCA97678f9e8a93df938@us.ibm.com" border=3D"0" height=3D"= 1" width=3D"58" alt=3D""><br> <div align=3D"right"><font size=3D"2">To</font></div></td><td width=3D"= 100%"><img src=3D"cid:30__=3D09BBFA91DFCA97678f9e8a93df938@us.ibm.com" = border=3D"0" height=3D"1" width=3D"1" alt=3D""><br> <font size=3D"2">ids@iiug.org</font></td></tr> <tr valign=3D"top"><td width=3D"1%" valign=3D"middle"><img src=3D"cid:3= 0__=3D09BBFA91DFCA97678f9e8a93df938@us.ibm.com" border=3D"0" height=3D"= 1" width=3D"58" alt=3D""><br> <div align=3D"right"><font size=3D"2">cc</font></div></td><td width=3D"= 100%"><img src=3D"cid:30__=3D09BBFA91DFCA97678f9e8a93df938@us.ibm.com" = border=3D"0" height=3D"1" width=3D"1" alt=3D""><br> </td></tr> <tr valign=3D"top"><td width=3D"1%" valign=3D"middle"><img src=3D"cid:3= 0__=3D09BBFA91DFCA97678f9e8a93df938@us.ibm.com" border=3D"0" height=3D"= 1" width=3D"58" alt=3D""><br> <div align=3D"right"><font size=3D"2">Subject</font></div></td><td widt= h=3D"100%"><img src=3D"cid:30__=3D09BBFA91DFCA97678f9e8a93df938@us.ibm.= com" border=3D"0" height=3D"1" width=3D"1" alt=3D""><br> <font size=3D"2">Compare Data in Two table [4954]</font></td></tr> </table> <table border=3D"0" cellspacing=3D"0" cellpadding=3D"0"> <tr valign=3D"top"><td width=3D"58"><img src=3D"cid:30__=3D09BBFA91DFCA= 97678f9e8a93df938@us.ibm.com" border=3D"0" height=3D"1" width=3D"1" alt= =3D""></td><td width=3D"336"><img src=3D"cid:30__=3D09BBFA91DFCA97678f9= e8a93df938@us.ibm.com" border=3D"0" height=3D"1" width=3D"1" alt=3D""><= /td></tr> </table> </td></tr> </table> <br> <tt>We have a OLTP and ODS databases and every night we migrate data fr= om OLTP to ODS database. Both OLTP and ODS use IDS dyamic 9.4 on HP-UX.= <br> We purge records in OLTP database after 90 days. Currently every night = we compare entire data in OLTP database with ODS and add new rows to OD= S and update those changed. <br> Is there a way to know only what changed in source database? This will = allow only delta's or new rows to be migrated to ODS instead of compari= ng 90days of OLTP data every night with ODS? Most of the tables do have= Update_DtTime column but there is no gurantee that all process(includi= ng some back ground) process correctly update that column.<br> ANy help you provide will be appreciated.<br> <br> </tt><br> </body></html>= --1__=09BBFA91DFCA97678f9e8a93df938690918c09BBFA91DFCA9767-- --0__=09BBFA91DFCA97678f9e8a93df938690918c09BBFA91DFCA9767 Content-type: image/gif; name="graycol.gif" Content-Disposition: inline; filename="graycol.gif" Content-ID: <10__=09BBFA91DFCA97678f9e8a93df938@us.ibm.com> Content-transfer-encoding: base64 R0lGODlhEAAQAKECAMzMzAAAAP///wAAACH5BAEAAAIALAAAAAAQABAAAAIXlI+py+0PopwxUbpu ZRfKZ2zgSJbmSRYAIf4fT3B0aW1pemVkIGJ5IFVsZWFkIFNtYXJ0U2F2ZXIhAAA7 --0__=09BBFA91DFCA97678f9e8a93df938690918c09BBFA91DFCA9767 Content-type: image/gif; name="pic31004.gif" Content-Disposition: inline; filename="pic31004.gif" Content-ID: <20__=09BBFA91DFCA97678f9e8a93df938@us.ibm.com> Content-transfer-encoding: base64 R0lGODlhWABDALP/AAAAAK04Qf79/o+Gm7WuwlNObwoJFCsoSMDAwGFsmIuezf///wAAAAAAAAAA AAAAACH5BAEAAAgALAAAAABYAEMAQAT/EMlJq704682770RiFMRinqggEUNSHIchG0BCfHhOjAuh EDeUqTASLCbBhQrhG7xis2j0lssNDopE4jfIJhDaggI8YB1sZeZgLVA9YVCpnGagVjV171aRVrYR RghXcAGFhoUETwYxcXNyADJ3GlcSKGAwLwllVC1vjIUHBWsFilKQdI8GA5IcpApeJQt8L09lmgkH LZikoU5wjqcyAMMFrJIDPAKvCFletKSev1HBw8KrxtjZ2tvc3d5VyKtCKW3jfz4uMKmq3xu4N0nK BVoJQmx2LGVOmrqNjjJf2hHAQo/eDwJGTKhQMcgQEEAnEjFS98+RnW3smGkZU6ncCWav/4wYOnAI TihRL/4FEwbp28BXMMcoscQCVxlepL4IGDSCyJyVQOu0o7CjmLN50OZlqWmyFy5/6yBBuji0AxFR M00oQAqNIs
Thanks to all of you who replied to my message. Most of you suggested using triggers. Triggers will definately get the job done but here are my concerns. 1. We are talking about migrating at least 35 to 40 large tables. 2. These tables are updated live by company's web-site and few background process. We don't want our customers to experience shopping delay becuase of additional trigger overhead. We are number 3 consumer electronics retailer and web site traffic is very high. How can I find out impact of triggers (in terms of slowing down the web-site) without actually coding them and simulating load test. ANy suggestion or commets are most welcome Thanks
--0__=09BBFA96DFF5AB498f9e8a93df938690918c09BBFA96DFF5AB49 Content-type: multipart/alternative; Boundary="1__=09BBFA96DFF5AB498f9e8a93df938690918c09BBFA96DFF5AB49" --1__=09BBFA96DFF5AB498f9e8a93df938690918c09BBFA96DFF5AB49 Content-type: text/plain; charset=US-ASCII Content-transfer-encoding: quoted-printable If you want to minimize the activity on the machines that the customers= are using, I'd really suggest using ER with timestamp conflict resolution. = If you replicate only the primary keys to an different system, then you ge= t the keys that have changed and the time of the change. That should be enough to know easily what has changed and what has not. I'd define it as using timestamp CR because we perform upserts on the target server, which means that you don't have to worry about getting a= n initial sync. Triggers will impact the user application's performance. = "TUKOBA Ingale" = <tukoba@yahoo.com = > = To Sent by: ids@iiug.org = forum.subscriber@ = cc iiug.org = Subj= ect Re: Compare Data in Two table = 05/18/2005 10:40 (Updated) [4993] = AM = = = = = = Thanks to all of you who replied to my message. Most of you suggested u= sing triggers. Triggers will definately get the job done but here are my concerns. 1. We are talking about migrating at least 35 to 40 large tables. 2. These tables are updated live by company's web-site and few backgrou= nd process. We don't want our customers to experience shopping delay becua= se of additional trigger overhead. We are number 3 consumer electronics retailer and web site traffic is very high. How can I find out impact of triggers (in terms of slowing down the web-site) without actually coding them and simulating load test. ANy suggestion or commets are most welcome Thanks = --1__=09BBFA96DFF5AB498f9e8a93df938690918c09BBFA96DFF5AB49 Content-type: text/html; charset=US-ASCII Content-Disposition: inline Content-transfer-encoding: quoted-printable <html><body> <p>If you want to minimize the activity on the machines that the custom= ers are using, I'd really suggest using ER with timestamp conflict reso= lution. If you replicate only the primary keys to an different system,= then you get the keys that have changed and the time of the change. T= hat should be enough to know easily what has changed and what has not. = <br> <br> I'd define it as using timestamp CR because we perform upserts on the t= arget server, which means that you don't have to worry about getting an= initial sync.<br> <br> <br> Triggers will impact the user application's performance.<br> <br> <img src=3D"cid:10__=3D09BBFA96DFF5AB498f9e8a93df938@us.ibm.com" width=3D= "16" height=3D"16" alt=3D"Inactive hide details for "TUKOBA Ingale= " <tukoba@yahoo.com>">"TUKOBA Ingale" <tukoba@y= ahoo.com><br> <br> <br> <table width=3D"100%" border=3D"0" cellspacing=3D"0" cellpadding=3D"0">= <tr valign=3D"top"><td style=3D"background-image:url(cid:20__=3D09BBFA9= 6DFF5AB498f9e8a93df938@us.ibm.com); background-repeat: no-repeat; " wid= th=3D"40%"> <ul> <ul> <ul> <ul><b><font size=3D"2">"TUKOBA Ingale" <tukoba@yahoo.com&= gt;</font></b><font size=3D"2"> </font><br> <font size=3D"2">Sent by: forum.subscriber@iiug.org</font> <p><font size=3D"2">05/18/2005 10:40 AM</font></ul> </ul> </ul> </ul> </td><td width=3D"60%"> <table width=3D"100%" border=3D"0" cellspacing=3D"0" cellpadding=3D"0">= <tr valign=3D"top"><td width=3D"1%" valign=3D"middle"><img src=3D"cid:3= 0__=3D09BBFA96DFF5AB498f9e8a93df938@us.ibm.com" border=3D"0" height=3D"= 1" width=3D"58" alt=3D""><br> <div align=3D"right"><font size=3D"2">To</font></div></td><td width=3D"= 100%"><img src=3D"cid:30__=3D09BBFA96DFF5AB498f9e8a93df938@us.ibm.com" = border=3D"0" height=3D"1" width=3D"1" alt=3D""><br> <font size=3D"2">ids@iiug.org</font></td></tr> <tr valign=3D"top"><td width=3D"1%" valign=3D"middle"><img src=3D"cid:3= 0__=3D09BBFA96DFF5AB498f9e8a93df938@us.ibm.com" border=3D"0" height=3D"= 1" width=3D"58" alt=3D""><br> <div align=3D"right"><font size=3D"2">cc</font></div></td><td width=3D"= 100%"><img src=3D"cid:30__=3D09BBFA96DFF5AB498f9e8a93df938@us.ibm.com" = border=3D"0" height=3D"1" width=3D"1" alt=3D""><br> </td></tr> <tr valign=3D"top"><td width=3D"1%" valign=3D"middle"><img src=3D"cid:3= 0__=3D09BBFA96DFF5AB498f9e8a93df938@us.ibm.com" border=3D"0" height=3D"= 1" width=3D"58" alt=3D""><br> <div align=3D"right"><font size=3D"2">Subject</font></div></td><td widt= h=3D"100%"><img src=3D"cid:30__=3D09BBFA96DFF5AB498f9e8a93df938@us.ibm.= com" border=3D"0" height=3D"1" width=3D"1" alt=3D""><br> <font size=3D"2">Re: Compare Data in Two table (Updated) [4993]</font>= </td></tr> </table> <table border=3D"0" cellspacing=3D"0" cellpadding=3D"0"> <tr valign=3D"top"><td width=3D"58"><img src=3D"cid:30__=3D09BBFA96DFF5= AB498f9e8a93df938@us.ibm.com" border=3D"0" height=3D"1" width=3D"1" alt= =3D""></td><td width=3D"336"><img src=3D"cid:30__=3D09BBFA96DFF5AB498f9= e8a93df938@us.ibm.com" border=3D"0" height=3D"1" width=3D"1" alt=3D""><= /td></tr> </table> </td></tr> </table> <br> <tt>Thanks to all of you who replied to my message. Most of you suggest= ed using triggers. Triggers will definately get the job done but here a= re my concerns.<br> 1. We are talking about migrating at least 35 to 40 large tables.<br> 2. These tables are updated live by company's web-site and few backgrou= nd process. We don't want our customers to experience shopping delay be= cuase of additional trigger overhead. We are number 3 consumer electron= ics retailer and web site traffic is very high.<br> <br> How can I find out impact of triggers (in terms of slowing down the web= -site) without actually coding them and simulating load test.<br> <br> ANy suggestion or commets are most welcome<br> Thanks<br> <br> <br> </tt><br> </body></html>= --1__=09BBFA96DFF5AB498f9e8a93df938690918c09BBFA96DFF5AB49-- --0__=09BBFA96DFF5AB498f9e8a93df938690918c09BBFA96DFF5AB49 Content-type: image/gif; name="graycol.gif" Content-Disposition: inline; filename="graycol.gif" Content-ID: <10__=09BBFA96DFF5AB498