× 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.



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>>


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

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.