Problem with GENERATED ALWAYS Identity column

All,

I'm wondering if anybody else as seen this problem:

I have a table defined as follows:                                      
                                                                        
CREATE TABLE MELDBFQ.HPDOCU (                                           
  DHDOID INTEGER GENERATED ALWAYS AS IDENTITY (                          
  START WITH 1 INCREMENT BY 1                                            
  NO MINVALUE NO MAXVALUE                                                
  NO CYCLE NO ORDER                                                      
  CACHE 20 ),                                                            
  DHCSCD CHAR(3) CCSID 37 NOT NULL DEFAULT '' ,                          
  DHBRCH CHAR(13) CCSID 37 NOT NULL DEFAULT ''                           
        --other fields removed                                          
  CONSTRAINT MELDBFQ.QSYS_HRDOCU_00001 PRIMARY KEY( DHDOID ) ) ;         
                                                                        
Now, the primary key is the only constraint defined in addition there    
are no logical files defined with UNIQUE keys.                          
                                                                        
My question is, why would I get a Duplicate Key message when trying     
to insert records into this file using either SQL INSERT or CPYF        
MBROPT(*ADD) FMTOPT(*MAP)?                                              
                                                                        
After issuing the following:                                            
alter table meldbfq/hpdocu                                              
 alter column dhdoid restart with 20000                                 
                                                                        
I was able to insert my record.                                        
                                                                        
It seems as if the "next identity value to use" was somehow off.        
                                                                        
How is this possible?                                                   
Is it a bug?                                                            
Is there any way to find out what the "next identity value to use"      
value is currently for a table?                                         


I've opened a PMR with IBM (#09361,082).  But don't seem to be getting anywhere 
since I can't tell IBM support how to recreate the problem; the table that had 
the problem is in a test data library whose files are controlled by our CMS.  
The same table in other test data libraries or in the production library don't 
have the same problem.


In particular, I'm wondering if anybody knows of a way to find out a table's 
"next identity value to use".  Before you mention the IDENTITY_VAL_LOCAL 
function, note that the manual says the following:  "The function returns an 
unpredictable result when it is invoked after the unsuccessful execution of an  
      
INSERT statement for a table with an identity column."

Perhaps I should post that question to the MI list...


Thanks,

Charles Wilt
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121