RE: Problem with GENERATED ALWAYS Identity column

Brian,

Did you talk to Aldon about this?


In any event, I'd be interested in your utility if you are willing to share.

My email is 'cwilt at meaa.mea.com'


Thanks,


Charles Wilt
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
 

> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Brian Johnson
> Sent: Wednesday, August 03, 2005 11:11 PM
> To: Midrange Systems Technical Discussion
> Subject: Re: Problem with GENERATED ALWAYS Identity column
> 
> 
> Charles,
> 
> I've had similar exprience with our CMS (Aldon CMS) where the 
> table is 
> recreated, then distributed to the test library.  The 
> identity column in 
> the new table starts the column at 1, then CPYF command (run 
> by CMS to 
> copy the data from the old table into the new table) inserts 
> the rows, 
> but retains the values of the identity column.  Now the data 
> in the new 
> table is out of sync with the identity column.  Apparently 
> CPYF command 
> is using the equivalent of the OVERRIDING SYSTEM VALUE clause 
> of the SQL 
> INSERT statement.
> 
> We had to write a little utility program that resets the 
> identity column 
> in a table.
> 
> Brian Johnson
> 
> Wilt, Charles wrote:
> 
> >Paul,
> >
> >What do you mean "This is a flaw in your design.  You have 
> two copies of
> >the file using the same identity."
> >
> >As I understand it, an identity is unique to a particular 
> table object.  You're not thinking about a SEQUENCE object 
> are you?  That can indeed be shared between objects.
> >
> >
> >Don't understand what you are saying about the INSERT and the CPYF.
> >
> >With a GENERATED ALWAYS AS IDENTITY, every time a row is 
> inserted no matter what the method, a new IDENTITY value is 
> assigned.  The "generator" is always involved and thus should 
> always know what the next value is.
> >
> >Charles Wilt
> >--
> >iSeries Systems Administrator / Developer
> >Mitsubishi Electric Automotive America
> >ph: 513-573-4343
> >fax: 513-398-1121
> > 
> >  
> >
> >>-----Original Message-----
> >>From: midrange-l-bounces@xxxxxxxxxxxx
> >>[mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Paul Morgan
> >>Sent: Wednesday, August 03, 2005 2:38 PM
> >>To: midrange-l@xxxxxxxxxxxx
> >>Subject: Re: Problem with GENERATED ALWAYS Identity column
> >>
> >>
> >>Charles,
> >>
> >>This is not a bug.  This is a flaw in your design.  You have 
> >>two copies of
> >>the file using the same identity.  When you add records to 
> >>both files they
> >>can end up with the same identity value because the files 
> >>aren't aware of
> >>each other.  As you mentioned you are doing CPYFs between 
> >>tables which can
> >>merge these records into the same file.
> >>
> >>The INSERT is having a problem because you used a CPYF to 
> >>insert a record
> >>with the next available index value.  The generator isn't 
> >>aware that value
> >>is already taken and it just attempts to increment for the 
> >>next value on the
> >>INSERT.  But that value is already in the file so you get a 
> >>duplicate record
> >>error.
> >>
> >>You can reset the next value for an identity column (above any value
> >>currently in the file) with:
> >>
> >>ALTER TABLE MELDBFQ.HPDOCU ALTER DHDOID RESTART WITH nnnn
> >>
> >>Query SYSCOLUMNS in the QSYS2 library to determine the next 
> >>increment value.
> >>
> >>Paul
> >>
> >>    
> >>
> >
> >  
> >
> 
> -- 
> This is the Midrange Systems Technical Discussion 
> (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
> 
>