Rowids: How do younow when the associated record is actually available???

Hello, I don't know if this is the correct newgroup for this. If not, please disregard. I've written a temporary (expected daemon life - 3wks) replication daemon, in Informix 4GL, using Tools 7.20, to read a table from x to end and insert each row into a "like" table on a remote server. Example: (Note:assume prepared statements and declared cursors) Table "tablename" is a corporate table that is inserted (many times simultaneously) into by a daemon at 80 stores To migrate this data from this corporate machine to a replacement corporate machine, my temporary daemon will: Select last_row_migrated into last_row_migrated from controller-table Select rowid, * from tablename where rowid > last_row_migrated order by rowid As each row is read, it is inserted into a remote database and last_row_migrated is updated in the controller-table Problem: Informix is retrieving rowids that have been assigned for records from the 80 stores, but in some cases the stores are inserting at the same time my temporary daemon is reading (and migrating) the data. Informix finds the rowids, but if the associated records haven't been "completely inserted" into the local corporate table, Informix doesn't include them in the select list. Nor does it report an error - the reocrds simply don't yet exist, even though the rowid has already been assigned. This results in those records being skipped during that iteration and potentially skipped in subsequent iterations. Does anyone know of a way to determine just when a record actually exists or a method for causing Informix to return an error under these circumstances? Thanks, Danny Staggs

Uh, Why don't you just use ER? Or are you using SE? In the year of Our Lord Mon, 12 Feb 2001 13:17:36 -0600, "Danny Staggs" <dstaggs@cox-internet.com> spake, saying: >Hello, > >I don't know if this is the correct newgroup for this. If not, please >disregard. > >I've written a temporary (expected daemon life - 3wks) replication daemon, >in Informix 4GL, using Tools 7.20, to read a table from x to end and insert >each row into a "like" table on a remote server. > >Example: (Note:assume prepared statements and declared cursors) > >Table "tablename" is a corporate table that is inserted (many times >simultaneously) into by a daemon at 80 stores > >To migrate this data from this corporate machine to a replacement corporate >machine, my temporary daemon will: > >Select last_row_migrated into last_row_migrated from controller-table >Select rowid, * > from tablename > where rowid > last_row_migrated > order by rowid> >As each row is read, it is inserted into a remote database and >last_row_migrated is updated in the controller-table >Problem: > >Informix is retrieving rowids that have been assigned for records from the >80 stores, but in some cases the stores are inserting at the same time my >temporary daemon is reading (and migrating) the data. > >Informix finds the rowids, but if the associated records haven't been >"completely inserted" into the local corporate table, Informix doesn't >include them in the select list. Nor does it report an error - the reocrds >simply don't yet exist, even though the rowid has already been assigned. > >This results in those records being skipped during that iteration and >potentially skipped in subsequent iterations. > >Does anyone know of a way to determine just when a record actually exists or >a method for causing Informix to return an error under these circumstances? > >Thanks, > >Danny Staggs > >

Danny Staggs wrote in message ... > >I've written a temporary (expected daemon life - 3wks) replication daemon, >in Informix 4GL, using Tools 7.20, to read a table from x to end and insert >each row into a "like" table on a remote server. > Heh heh. Expect to be supporting it 3 years from now... I'm a fan of rowids, in fact I often spread them on my morning toast, but this scares me. Try adding a posted flag to the corporate table. If your daemon shifts the row, assign true or "Y" to the row. OR - setup a trigger which fires each time a row is inserted into the corporate table. This trigger can shove the PK into a "please post me" table, and a separate process can collect, post and remove the posting requests. Get your isolation levels and wait modes correct, make sense of your promotable locks and Codd's your uncle. Rest of the message was: >Table "tablename" is a corporate table that is inserted (many times >simultaneously) into by a daemon at 80 stores > >To migrate this data from this corporate machine to a replacement corporate >machine, my temporary daemon will: > >Select last_row_migrated into last_row_migrated from controller-table >Select rowid, * > from tablename > where rowid > last_row_migrated > order by rowid> >As each row is read, it is inserted into a remote database and >last_row_migrated is updated in the controller-table >Problem: > >Informix is retrieving rowids that have been assigned for records from the >80 stores, but in some cases the stores are inserting at the same time my >temporary daemon is reading (and migrating) the data. > >Informix finds the rowids, but if the associated records haven't been >"completely inserted" into the local corporate table, Informix doesn't >include them in the select list. Nor does it report an error - the reocrds >simply don't yet exist, even though the rowid has already been assigned. > >This results in those records being skipped during that iteration and >potentially skipped in subsequent iterations. > >Does anyone know of a way to determine just when a record actually exists or >a method for causing Informix to return an error under these circumstances?