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