Syncronise Serial fields in two tables

"Matthias Meyerh'fer" <matthias.meyerhoefer@dpd.de> > Hello everybody, > > I've two table (t1 and t2) in the informix database with serial fields in. > There is a unique index on the serial fields. > First process inserts records in t1. The serial field is filled with the > value 0. > Second process copies records from table t1 to t2. The value of the serial > field is also copied. > Third process deletes records from table t1. > Problem is the first process inserts records in the serial field t1 with > values which are already exist in the table t2. So the second process can > not copy the records. > Now to my question is it some how possible to syncronise the serial fields > in t1 and t2. If t2 has got its own serial field, u will always run into this problem. why the second table t2 got to be a serial field. Why can't you declare it as an integer field with both PKY as well as FKY to t1.

Hello everybody, I've two table (t1 and t2) in the informix database with serial fields in. There is a unique index on the serial fields. First process inserts records in t1. The serial field is filled with the value 0. Second process copies records from table t1 to t2. The value of the serial field is also copied. Third process deletes records from table t1. Problem is the first process inserts records in the serial field t1 with values which are already exist in the table t2. So the second process can not copy the records. Now to my question is it some how possible to syncronise the serial fields in t1 and t2. best regards Matthias Meyerh'fer

The simple answer is that you can't. Ok... First, why do you have a serial column in t2? Are you ever going to insert in to t2 without first inserting in t1? And is all the data being inserted in to t2 going to first come from t1? And how is it that you have data in t2 that is not in t1? A serial field has a wrap around after 4 billion or so records. (Or something like that number if not larger, so that you wont get an automatic duplicate record.) The only way you can have an error is if you abend (abort/core dump) in the middle of a process. Or you're adding data from other sources other than t1. Since your problem does not indicate that you are adding records directly in to T2 without the data first being in T1, you don't need a serial column. Just an unsigned int and a unique index, which will prohibit duplicates. Are the copy to table T2 and delete from T1 supposed to be an atomic sequence? Meaning that you want them to occur within the same transaction? This would imply that you only want to store the row within one table. An example of this would be in a store's point of sale machine. You enter the daily transactions in to one table, then at end of day you reconcile the data, then after the reconcile process, you move the sales data to a historical table and delete from the day's total. (Not a great example, but it would explain the insert, then delete....) The simple solution is that when you attempt the insert in to t2, you trap the return code and upon failure, you delete from t2 and re-insert the record. Does that help? Matthias Meyerh'fer wrote: > Hello everybody, > > I've two table (t1 and t2) in the informix database with serial fields in. > There is a unique index on the serial fields. > First process inserts records in t1. The serial field is filled with the > value 0. > Second process copies records from table t1 to t2. The value of the serial > field is also copied. > Third process deletes records from table t1. > Problem is the first process inserts records in the serial field t1 with > values which are already exist in the table t2. So the second process can > not copy the records. > Now to my question is it some how possible to syncronise the serial fields > in t1 and t2. > > best regards > Matthias Meyerh'fer > >

Best way to do this is to not try. I'd suggest getting on 9.4 and use the sequence generator instead. M.P. "Matthias Meyerh'fer" <matthias.meyerhoefer@dpd.de> wrote in message news:2ucd8gF2262hjU1@uni-berlin.de... > Hello everybody, > > I've two table (t1 and t2) in the informix database with serial fields in. > There is a unique index on the serial fields. > First process inserts records in t1. The serial field is filled with the > value 0. > Second process copies records from table t1 to t2. The value of the serial > field is also copied. > Third process deletes records from table t1. > Problem is the first process inserts records in the serial field t1 with > values which are already exist in the table t2. So the second process can > not copy the records. > Now to my question is it some how possible to syncronise the serial fields > in t1 and t2. > > best regards > Matthias Meyerh'fer > >

Hello, thank you so much for your help. I see that there is no chance to solve the problem on database site. So the source code must be changed. thank you and best regards Matthias > Hello everybody, > > I've two table (t1 and t2) in the informix database with serial fields in. > There is a unique index on the serial fields. > First process inserts records in t1. The serial field is filled with the > value 0. > Second process copies records from table t1 to t2. The value of the serial > field is also copied. > Third process deletes records from table t1. > Problem is the first process inserts records in the serial field t1 with > values which are already exist in the table t2. So the second process can > not copy the records. > Now to my question is it some how possible to syncronise the serial fields > in t1 and t2. > > best regards > Matthias Meyerh'fer