identify the recent add record using rowid
A user wanted a nightly cron job to copy only newly added rows from a production database into an EIS database without re-pulling duplicates, and asked whether ROWID could identify new rows. Respondents said ROWID is unreliable (it's page/slot based, reused after deletes, not unique with fragmentation, and misses updates/deletes). Suggested alternatives: Enterprise Replication (deemed the best fit for this job), insert triggers writing to a change table, a maintained timestamp column, or adding CRCOLS (hidden replication timestamp, available in his IDS 9.40) and tracking the last copied timestamp; VERCOLS needs 11.50. It was also noted 9.40 was about to go out of support. No final choice by the poster is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi I'm preparing the new database for EIS solution. There will be a cronjob to pull the records from production database and insert to EIS database. My question how can i make sure that I'm only pull the recent add records on the day?. How to make sure the following day I won't get the duplicates records? Appreciate any advice from iiug friends. Thanks and Regards
The best solution would be to use Enterprise Replication (ER) - it will ensure that only required rows are transferred and you can run sync processes to confirm that the tables are in line. The rest assumes you don't want ER. I would hope that your database does not have too many tables - managing them individually would be a nightmare. From a script point of view, you may be able to find any new rows using ROWID (assuming you have NO fragmentation as they will then not be unique or sequential). A better solution would be to have a datetime field in the table that is always set to the timestamp of the record being added / modified. ROWID won't find any update or deletes... Just additions. Another option would be to use a INSERT trigger to write a copy of the row to another table when it is inserted. This table could then be processed by the script and the rows transferred over to EIS. You could also write SPL for the updates and deletes. Jarrod Teale Fonterra NZ -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of SYED AHMAD NAJMI SYED MD NASIR Sent: Tuesday, 24 March 2009 5:35 p.m. To: ids@iiug.org Subject: identify the recent add record using rowid [15302] Hi I'm preparing the new database for EIS solution. There will be a cronjob to pull the records from production database and insert to EIS database. My question how can i make sure that I'm only pull the recent add records on the day?. How to make sure the following day I won't get the duplicates records? Appreciate any advice from iiug friends. Thanks and Regards ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. DISCLAIMER: This email contains confidential information and may be legally privileged. If you are not the intended recipient or have received this email in error, please notify the sender immediately and destroy this email. You may not use, disclose or copy this email or its attachments in any way. Any opinions expressed in this email are those of the author and are not necessarily those of the Fonterra Co-operative Group. http://www.fonterra.com/
Thanks Jarrord I agreed with your recommendations including the ER. The EIS database got few tables , which also included huge tables. It is fine for small tables but for huge big tables I should think to have the trigger and sp as your suggestion. I thaught to just used the shell script and SQL to pull the recent add records using rowid but rowid as u mentioned wasn't help if update/delete been to the record. THanks
Version information would help us give you a useful answer, it usually is important. If you are running IDS 11.50 you can ADD CRCOLS to the table. That will include a hidden timestamp column in the table that is automatically maintained by the engine. You can filter for rows that have a more recent timestamp than the latest row in the EIS copy of the table if you add crcols to that table as well or if you record the newest timestamp that you last fetched in some other way with each run. Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Mar 24, 2009 at 12:34 AM, SYED AHMAD NAJMI SYED MD NASIR < najmi@centurysoftware.com.my> wrote: > Hi > > I'm preparing the new database for EIS solution. There will be a cronjob to > pull the records from production database and insert to EIS database. > > My question how can i make sure that I'm only pull the recent add records > on > the day?. How to make sure the following day I won't get the duplicates > records? > > Appreciate any advice from iiug friends. > > Thanks and Regards > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001636163eb5fe7eef0465db2e22
The OP may not be able to use rowid as rowid is not neccessarily an increasing value. It is made up of the relative page # and slot # holding the record. If rows are deleted old slots and therefore old rowids are reused. Oh, in my reply I said the OP needs v11.50, that's not right. I was thinking of VERCOLS. CRCOLS are available since v7.10 so they can be used in any IDS version. Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Mar 24, 2009 at 12:44 AM, Jarrod Teale <Jarrod.Teale@fonterra.com>wrote: > The best solution would be to use Enterprise Replication (ER) - it will > ensure that only required rows are transferred and you can run sync > processes to confirm that the tables are in line. > > The rest assumes you don't want ER. > > I would hope that your database does not have too many tables - managing > them individually would be a nightmare. > >From a script point of view, you may be able to find any new rows using > ROWID (assuming you have NO fragmentation as they will then not be > unique or sequential). A better solution would be to have a datetime > field in the table that is always set to the timestamp of the record > being added / modified. > > ROWID won't find any update or deletes... Just additions. > > Another option would be to use a INSERT trigger to write a copy of the > row to another table when it is inserted. This table could then be > processed by the script and the rows transferred over to EIS. You could > also write SPL for the updates and deletes. > > Jarrod Teale > Fonterra NZ > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > SYED AHMAD NAJMI SYED MD NASIR > Sent: Tuesday, 24 March 2009 5:35 p.m. > To: ids@iiug.org > Subject: identify the recent add record using rowid [15302] > > Hi > > I'm preparing the new database for EIS solution. There will be a cronjob > to pull the records from production database and insert to EIS database. > > My question how can i make sure that I'm only pull the recent add > records on the day?. How to make sure the following day I won't get the > duplicates records? > > Appreciate any advice from iiug friends. > > Thanks and Regards > > ************************************************************************ > ******* > Forum Note: Use "Reply" to post a response in the discussion forum. > > DISCLAIMER: > This email contains confidential information and may be legally privileged. > If > you are not the intended recipient or have received this email in error, > please notify the sender immediately and destroy this email. > You may not use, disclose or copy this email or its attachments in any way. > Any opinions expressed in this email are those of the author and are not > necessarily those of the Fonterra Co-operative Group. > http://www.fonterra.com/ > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0016364edc32c2bf320465db3d55
OK, you didn't say that you want to capture updates also. To catch updates you will need CRCOLS and VERCOLS (vercols are available in IDS 11.50+) and you will have to copy the vercols column values to the EIS server and compare them to know if rows were updated. Triggers are probably better. Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Mar 24, 2009 at 2:09 AM, SYED AHMAD NAJMI SYED MD NASIR < najmi@centurysoftware.com.my> wrote: > Thanks Jarrord > > I agreed with your recommendations including the ER. > > The EIS database got few tables , which also included huge tables. It is > fine > for small tables but for huge big tables I should think to have the trigger > and sp as your suggestion. I thaught to just used the shell script and SQL > to > pull the recent add records using rowid but rowid as u mentioned wasn't > help > if update/delete been to the record. > > THanks > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --00163646c20093132a0465db44d3
Thanks for feedback. Sorry for not given the version detail. Actually we are working at IBM IDS 9.4 platform on IBM AIX 5.3. Few of the tables which we want to pull from production database into EIS database having million of records. I'm trying to get a best approach on the above platform. We don't have any plan yet to move up to latest IDS version at this point of time. Thanks
Note that IDS 9.40 goes out of support completely in August 2009! In 9.40 you can use CRCOLS but not VERCOLS, so you can capture inserts and updates that way by keeping track of the latest CRCOLS timestamp value you last copied. BTW I agree with the poster that said that ER may be the best solution for you. This is EXACTLY the job that it was designed to do best. Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Mar 24, 2009 at 9:27 PM, SYED AHMAD NAJMI SYED MD NASIR < najmi@centurysoftware.com.my> wrote: > Thanks for feedback. > > Sorry for not given the version detail. > > Actually we are working at IBM IDS 9.4 platform on IBM AIX 5.3. > Few of the tables which we want to pull from production database into EIS > database having million of records. I'm trying to get a best approach on > the > above platform. We don't have any plan yet to move up to latest IDS version > at > this point of time. > > Thanks > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001636426e258862d90465e86428
http://www-01.ibm.com/software/data/support/lifecycle/ Actually, 9.4 goes out of support April 30, 2009 Thanks! Kate Tomchik [ kate@iiug.org ] www.iiug.org International Informix Users Group Board of Directors A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratliffe ---------- Original Message ----------- From: "Art Kagel" <art.kagel@gmail.com> To: ids@iiug.org Sent: Tue, 24 Mar 2009 22:38:22 -0400 (EDT) Subject: Re: identify the recent add record using rowid [15326] > Note that IDS 9.40 goes out of support completely in August 2009! > > In 9.40 you can use CRCOLS but not VERCOLS, so you can capture > inserts and updates that way by keeping track of the latest CRCOLS > timestamp value you last copied. > > BTW I agree with the poster that said that ER may be the best > solution for you. This is EXACTLY the job that it was designed to do > best. > > Art S. Kagel > Oninit (www.oninit.com) > IIUG Board of Directors (art@iiug.org) > > Disclaimer: Please keep in mind that my own opinions are my own > opinions and do not reflect on my employer, Oninit, the IIUG, nor > any other organization with which I am associated either explicitly > or implicitly. Neither do those opinions reflect those of other > individuals affiliated with any entity with which I am affiliated > nor those of the entities themselves. > > On Tue, Mar 24, 2009 at 9:27 PM, SYED AHMAD NAJMI SYED MD NASIR < > najmi@centurysoftware.com.my> wrote: > > > Thanks for feedback. > > > > Sorry for not given the version detail. > > > > Actually we are working at IBM IDS 9.4 platform on IBM AIX 5.3. > > Few of the tables which we want to pull from production database into EIS > > database having million of records. I'm trying to get a best approach on > > the > > above platform. We don't have any plan yet to move up to latest IDS version > > at > > this point of time. > > > > Thanks > > > > > > > > > ***************************************************************************** ** > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --001636426e258862d90465e86428 > > ***************************************************************************** ** Forum Note: Use "Reply" to post a response in the discussion forum. ------- End of Original Message -------