Re: Identity Columns

[Dave Murvin <davem@xxxxxxxx>]

How do you define the table?  I have an assets table that uses an asset ID 
that gets created when the record is written in my RPGLE program with a 
regular write statement.  Field is defined as follows

create table Assets.Assets
       (Asset_ID               for column ASAssetID    integer not null
            generated always as identity
            (start with 1 increment by 1), ...

When I write the new record with RPGLE program, the asset_ID field value is 
automatically created and incremented by 1 from the last Asset_ID field 
that was previously added to the file.   I believe that I initially tested 
this with Ezview, but don't remember for sure as it was a couple of years 
ago.

At 04:29 AM 4/29/2005, you wrote:
> I can't find anything in the IBM manuals to explain what is 'supposed'
> to happen when using an identity column with the identity value
> generated by default in RPG.
>
> An article by Kent Milligan on the iSeries network site at:
> http://www.iseriesnetwork.com/artarchive/index.cfm?fuseaction=viewarticl
> e&CO_ContentID=15329&channel=art&subart=issue&issueid=677
>
> states that :
>
> "If a row is added via a non-SQL application interface (e.g., RPG
> Write), DB2 UDB will always generate the identity column value even if
> BY DEFAULT has been specified."
>
> However our (VR53) box doesn't behave in this manner - if an RPG program
> writes a record or it is inserted by another means (eg. DFU without
> specifying a value for the identity column) it is written as zero.
>
> As I can't find IBM documentation saying how this is supposed to work, I
> don't know if this is a bug, or it is working as designed... Is there a
> way (compile option?) to make the database generate the value for the
> identity column without specifying generated always (which doesn't work
> with our HA mirroring software)?

Dave Murvin
DRM Enterprises, Inc.