aland@infmx.UUCP (Dr. Scump) (01/11/89)
In article <225@cmtl01.UUCP>, mdorion@cmtl01.UUCP (Mario Dorion) writes: > > Hi! > > One of my customers is playing with a big ( ~ 90000 records ) database managed > by Informix 2.10.00A on a Tower 32 mini. > > One of the fields is a 'serial#' type of field. When that database was set up > we assigned an initial value of 880000 to that field since we wanted the first > 2 digits to reflect the year the record was entered into the system. > > Now guess what? > > I'd like to set a new initial value of 890000. The Informix manuals aren't > helping a bit here. > > How should I do it? > > Thanking all in advance. > > -- > Mario Dorion | E-mail: mdorion@x02.UUCP > XIOS Systems | ...uunet!attcan!cmtl01!x02!mdorion The SERIAL column type retains a "assignment value" (unique id) at the C-ISAM level. When building an SQL table with a serial column, the value is set to the starting value you give (or 1, if you don't specify a starting point). Normally, you want the SQL engine to assign the serial value, so you use 0 as the column value in INSERT statements (if it's your own program; in PERFORM, you are prevented from entering a value in serial columns auto- matically). If you give it an explicit value *and* that value exceeds the current uniqueid value, the uniqueid is bumped up accordingly. (Just to be safe, copy your .dat and idx files somewhere in case you enter a typo in the following step; once the uniqueid is bumped up, you can't bring it back down...) So... to re-set your starting point for future adds to 890000, just use the explicit value 890000 for the serial column in your INSERT statement (instead of 0). That row will have 890000 for the serial value, and your next INSERT with a 0 will give it 890001, etc. If you normally only use PERFORM for inserting data, you can just bump the uniqueid with no other permanent effect by inserting a "phony row" with a value 1 less than the next desired uniqueid, then delete it immediately, e.g. INSERT INTO tbl VALUES (889999, ....); DELETE FROM tbl WHERE first_col = 889999; results in no net change to the table contents, but the next "zero insert" will be assigned the serial value 890000. Or, if you have C-ISAM, just open the file and run issetunique(fd, 890000L). Hope this helps. -- Alan S. Denney @ Informix Software, Inc. {pyramid|uunet}!infmx!aland "I want to live! -------------------------------------------- as an honest man, Disclaimer: These opinions are mine alone. to get all I deserve If I am caught or killed, the secretary and to give all I can." will disavow any knowledge of my actions. - S. Vega