RE: Problem with GENERATED ALWAYS Identity column

Rob is correct, when using the CPY I was copying from a version of the file 
missing the identity column, so I used FMTOPT(*MAP).

However, as I understand it , the CPYF should work even if the source file had 
the identity column, as long as the target file's identity column was defined 
GENERATED ALWAYS.

GENERATED ALWAYS is supposed to mean that the identity value is ALWAYS 
GENERATED (imagine that ;-), even if you pass a value in for the column.

I just tried it, and it _DIDN'T_ work.

_BUT_ what is interesting is that the following SQL does work:
insert into meldbf/hpdocu    
select * from temprst/hpdocu 

even when the HPDOCU file in TEMPRST has the identity column.  The inserted 
records have an identity value in sequence with the values that were already in 
the meldbfq file, not the identity values the records had in the TEMPRST file.


Seems as if I'm going to have to open another PMR.



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 rob@xxxxxxxxx
> Sent: Wednesday, August 03, 2005 3:08 PM
> To: Midrange Systems Technical Discussion
> Subject: Re: Problem with GENERATED ALWAYS Identity column
> 
> 
> Why isn't the generator aware of the value is already taken?  
> He did not 
> CPYF over the identity column field, hence the *MAP.  It generated it.
> I did a 
> 
> CREATE TABLE ROB/CHUCKY (MYKEY INT NOT NULL WITH DEFAULT, MYDATA
> CHAR (10 ) NOT NULL WITH DEFAULT) 
> 
> INSERT INTO ROB/CHUCKY VALUES(1, 'A')
> INSERT INTO ROB/CHUCKY VALUES(2, 'B')
> 
> select * from chucky
> ....+....1....+....2..
>         MYKEY   MYDATA
>             1   A 
>             2   B 
> ********  End of data 
> 
> CREATE TABLE ROB/CHUCKY2 
>  (DHDOID INT GENERATED ALWAYS AS IDENTITY (start with 1),
>   MYKEY INT NOT NULL WITH DEFAULT, 
>   mydata CHAR (10 ) NOT NULL WITH DEFAULT) 
> 
> CPYF FROMFILE(CHUCKY) TOFILE(CHUCKY2) MBROPT(*ADD) FMTOPT(*MAP *DROP)
> 
> select * from chucky2
> ..1....+....2....+....3....+...
> DHDOID           MYKEY   MYDATA
>      1               1   A 
>      2               2   B 
> *  End of data  ******** 
> 
> INSERT INTO ROB/CHUCKY2 (MYKEY, MYDATA) VALUES(27, 'C')
> 
> select * from chucky2
> ..1....+....2....+....3....+...
> DHDOID           MYKEY   MYDATA
>      1               1   A 
>      2               2   B 
>      3              27   C 
> *  End of data  ******** 
> 
> Looks like the generator knew how to handle this perfectly.
> 
> Nice suggestion on QSYS2/SYSCOLUMNS, but I failed to find the field 
> containing the next increment.  I can see start, increment, 
> miniumum, etc 
> but not a 3 or a 4 value anywhere.
> 
> Rob Berendt
> -- 
> Group Dekko Services, LLC
> Dept 01.073
> PO Box 2000
> Dock 108
> 6928N 400E
> Kendallville, IN 46755
> http://www.dekko.com
>