Chuck;
I have this running in at least 75 - 100 production programs, and they are working correctly. I started using identity columns, using generated always, in V5R3, and have used this specification since then.
There are two forms, overriding user value, and overriding system value. Overriding user value replaces the user supplied value with the system value and vice-versa.
From the 6.1 Infocenter SQL reference:
OVERRIDING SYSTEM VALUE or OVERRIDING USER VALUE
Specifies whether system generated values or user-specified values for a ROWID, identity, or row change timestamp column are used. If OVERRIDING SYSTEM VALUE is specified, the implicit or explicit list of columns for the INSERT statement must contain a column defined as GENERATED ALWAYS. If OVERRIDING USER VALUE is specified, the implicit or explicit list of columns for the INSERT statement must contain a column defined as either GENERATED ALWAYS or GENERATED BY DEFAULT.
OVERRIDING SYSTEM VALUE
Specifies that the value specified in the VALUES clause or produced by a fullselect for a column that is defined as GENERATED ALWAYS is used. A system-generated value is not inserted.
OVERRIDING USER VALUE
Specifies that the value specified in the VALUES clause or produced by a fullselect for a column that is defined as either GENERATED ALWAYS or GENERATED BY DEFAULT is ignored. Instead, a system-generated value is inserted, overriding the user-specified value.
If neither OVERRIDING SYSTEM VALUE nor OVERRIDING USER VALUE is specified:
A value cannot be specified for a ROWID, identity, or row change timestamp column that is defined as GENERATED ALWAYS.
A value can be specified for a ROWID, identity, or row change timestamp column that is defined as GENERATED BY DEFAULT. If a value is specified that value is assigned to the column. However, a value can be inserted into a ROWID column defined BY DEFAULT only if the specified value is a valid row ID value that was previously generated by DB2(r) for z/OS(r) or DB2 for i. When a value is inserted into an identity or row change timestamp column defined BY DEFAULT, the database manager does not verify that the specified value is a unique value for the column unless the identity or row change timestamp column is the sole key in a unique constraint or unique index. Without a unique constraint or unique index, the database manager can guarantee unique values only among the set of system-generated values as long as NO CYCLE is in effect.
If a value is not specified the database manager generates a new value.
Duane Christen
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Tuesday, February 15, 2011 1:33 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: Re: embedded SQL - how to insert with a GENERATED ALWAYS AS IDENTITY column
Actually that variation on the SQL INSERT is not a valid option for a
GENERATED ALWAYS IDENTITY column, at least not with the OVERRIDING USER
VALUE clause, but with an override, the identity value would not be
generated, and that is not likely the desired effect. The literal zero
versus the word DEFAULT asks\attempts to insert the numeric value zero
as the identity value for column TKTTKTDID, which would require an
override to the GENERATED value. The statement with the numeric literal
would fail with SQL0798 "Value cannot be specified for GENERATED ALWAYS
column TKTTKTDID."
Regards, Chuck
On 2/15/11 10:32 AM, Christen, Duane wrote:
You can also do:
Exec SQL
Insert into pdatalib/TKT001DPF overriding user value
Values(:P1_SysID , :P2_AEmail , :P3_REmail,
:P4_TEmail , :P5_PrbName , :P6_Priority,
:P7_Status , :P8_TktType, :P9_Subject,
:PA_Body , :TICKET# , :TIMSTAMP,
0);
Although I will look into DEFAULT, as Birgitta suggested.
Birgitta Hauser on Tuesday, February 15, 2011 12:05 AM wrote:
Try:
Exec SQL
Insert into pdatalib/TKT001DPF VALUES
( :P1_SysID , :P2_AEmail , :P3_REmail,
:P4_TEmail , :P5_PrbName , :P6_Priority,
:P7_Status , :P8_TktType, :P9_Subject,
:PA_Body , :TICKET# , :TIMSTAMP,
DEFAULT) ;
<<SNIP>>
midrange.com
