Hmmm. We're a pretty small operation here. By "back up", I mean ,
essentially, a copy of the table as a file. So, I'll have to INSERT
those back-up records into a new table and need them to retain their
ID#s. Still sounds like this model will renumber them, even if I load
them as a non-ID and then ALTER starting at 1001.

Olsen, Richard N. wrote:
> No, the existing values (say 1 - 1000) that were saved when you ran your
> back-up will retain their value. You drop the table, recreate it with
> PARTICIPANTS_ID starting at 1001, then restore the tablespace using the
> back-up that you just made. What that will do is restore records 1 -
> 1000. The next record that is inserted (created) for the table will have
> a PARTICIPANTS_ID of 1001.
>
>
>
> -----Original Message-----
> From: Joanna Grossman [mailto:[Email address protected]
> Sent: Friday, May 16, 2008 8:29 AM
> To: Olsen, Richard N.; LazyDBA Discussion
> Subject: Re: unique IDs - Ack!
>
> That won't just renumber my table starting at 1000? I need to retain the
>
> existing values.
>
> Thanks!
>
> Olsen Richard N. wrote:
>
>> **A LazyDBA.com subscriber has responded to your lazydba.com post**
>> **LazyDBA.com mail shield has forwarded you this email,
>> **and removed any attachments, and kept your email address secret
>> **from this person, and any viruses/trojans.
>> **If you reply to this email, the person will see your email address
>>
> as normal
>
>> **Anything below this line is the original email text
>>
>>
>> You need to recreate the table with PARTICIPANTS_ID equal to the max
>> value + 1.
>>
>> For example, if the max value for PARTICIPANTS_ID = 1000, when you
>> recreate the table use:
>>
>> "PARTICIPANTS_ID" INTEGER NOT NULL GENERATED ALWAYS
>> AS IDENTITY (START WITH 1001, INCREMENT BY 1, NO CACHE,
>> NO MINVALUE, NO MAXVALUE, NO CYCLE, NO ORDER),
>>
>> Good luck.
>>
>> -----Original Message-----
>> From: Joanna Grossman
>> [Email address protected]
>> Sent: Thursday, May 15, 2008 5:26 PM
>> To: LazyDBA Discussion
>> Subject: unique IDs - Ack!
>>
>> Hi all -
>>
>> How can you retain the values in an existing ID field that's:
>> "PARTICIPANTS_ID" INTEGER NOT NULL GENERATED BY DEFAULT
>> AS IDENTITY (START WITH 0, INCREMENT BY 1, NO CACHE,
>> NO MINVALUE, NO MAXVALUE, NO CYCLE, NO ORDER),
>>
>> When you want to make the IDs for new values automatically generate? I
>>
>
>
>> backed this table up and then changed it to:
>>
>> "PARTICIPANTS_ID" INTEGER NOT NULL GENERATED ALWAYS
>> AS IDENTITY (START WITH 1, INCREMENT BY 1, NO CACHE,
>> NO MINVALUE, NO MAXVALUE, NO CYCLE, NO ORDER),
>>
>> and as you might've guessed, it renumbered the table. ouch! So, I can
>> restore, but I need to be able to insert new values w/o manually
>> assigning IDs. What am I missing here?
>>
>> Thanks.
>>
>>
>>
>> ---------------------------------------------------------------------
>> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
>> To post a dba job: http://jobs.lazydba.com
>> To Subscribe : http://www.LazyDBA.com
>> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>>
>>
>>
>>
>
>

--
joanna grossman
web & data manager
vermont monitoring coop
www.uvm.edu/vmc


DB2 & UDB email list listserv db2-l LazyDBA home page