× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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
> 


As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 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.