|
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 canmerge 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 recorderror. 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 nnnnQuery SYSCOLUMNS in the QSYS2 library to determine the next increment value.Paul
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2025 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.