RE: Problem with GENERATED ALWAYS Identity column

Charles;

We use turnover and have the same problem with identity columns during table
promotions. We have also had problems with turnover/IBM applying the
constraints to the tables on rebuild also.
We promote an sql "script" (RUNSQLSTM on a source member with the same name
as the table) with each table for re-applying the constraints and added an
ALTER TABLE for updating any identity column(s). This has work well and
ensures the state of the table when the promotion has completed.

Duane Christen

 

-----Original Message-----
From: Wilt, Charles [mailto:CWilt@xxxxxxxxxxxx]
Sent: Thursday, August 04, 2005 7:27 AM
To: Midrange Systems Technical Discussion
Subject: RE: Problem with GENERATED ALWAYS Identity column


Brian,

We also use Aldon as our CMS.

I understand what you are saying about CPYF using the equivalent of the
"OVERRIDING SYSTEM VALUES" clause of the SQL INSERT statement.

That explains why my CPYF test I mentioned in other post failed.

So what you are saying is that its not the initial change to the file when
the identity column is added, its whenever the file is changed afterward
that CPYF causes a problem.

Interesting.


I can see how having CPYF behave this way is useful, but it doesn't seem
right that you have to come up with your own utility to reset the "next
identity value to use".  In addition, it seems like CPYF ought to offer a
way to control the behavior like the SQL insert does.


Did you open PMRs with IBM and/or Aldon when you hit this?


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:
>