Re: Data Migration
>From: crszczub@jupiter.cse.utoledo.edu (craig szczublewski) >Subject: Data Migration >Date: Thu, 28 Jul 1994 14:46:30 GMT >X-Informix-List-Id: <news.7876> > >Hi all, I'm running Informix On-Line 5 under AIX 3.2.5 Thanks for the version info, even though it isn't needed this time. >Situation: I want to unload information out of a table in one database > and reload it into another databse for testing. There are > two tables I am unloading. One header and the corresponding > detail table. The header table has a serial value for a > doc_no which provides a unique key. The detail table is linked > to the header via that serial doc_no which is contained in > an integer column. > >Problem..: When I reload into the test database, informix will want to > reassign the serial numner in the table for each row I insert. > If that happens, I will loose my link between my header and > detail. Misconception: SERIAL columns always assign new values. Actuality: SERIAL columns are INTEGER columns with the special property that if you try to insert the value 0 (zero), you get a positive value greater than zero that is one larger than the largest value ever stored in the column in this table. This means, in particular, that unloaded data can be reloaded, and there will not be aany mismatch between the data values in the header and detail tables. The only time funny things start happening is if you get to the state where the largest values is 2**31 - 2, or thereabouts. Left to its own devices, the serial numbers wrap around to 1 again -- but cannot be guaranteed to be unique any more. If you go trying to force it, it gets upset -- see the attached example. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h> Kerry: potential FAQ material... ************************************************************** ** TO DEMONSTRATE THE BEHAVIOUR OF SERIALS AND RESEQUENCING ** ************************************************************** + create database junk; + create table junk (col00 serial(1000) not null, col01 char(10) not null); + create unique index pk_junk on junk(col00); + insert into junk values (0, "A" ); + select max(col00), min(col00) from junk; 1000 1000 + select * from junk; 1000 A + insert into junk values (2147483647, "a" ); + select max(col00), min(col00) from junk; 2147483647 1000 + select * from junk; 1000 A 2147483647 a + insert into junk values (0, "A" ); + select max(col00), min(col00) from junk; 2147483647 1000 + select * from junk; 1000 A 2147483647 a 1001 A + drop table junk; + create table junk (col00 serial(1000) not null, col01 char(10) not null); + create unique index pk_junk on junk(col00); + insert into junk values (0, "A" ); + select max(col00), min(col00) from junk; 1000 1000 + select * from junk; 1000 A + insert into junk values (2147483646, "a" ); + select max(col00), min(col00) from junk; 2147483646 1000 + select * from junk; 1000 A 2147483646 a + insert into junk values (0, "A" ); + select max(col00), min(col00) from junk; 2147483647 1000 + select * from junk; 1000 A 2147483646 a 2147483647 A + insert into junk values (0, "A" ); + select max(col00), min(col00) from junk; 2147483647 1 + select * from junk; 1000 A 2147483646 a 2147483647 A 1 A + insert into junk values (0, "A" ); + select max(col00), min(col00) from junk; 2147483647 1 + select * from junk; 1000 A 2147483646 a 2147483647 A 1 A 2 A + close database; + drop database junk;