Interesting table

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Arun Srinivasan - 07 Jan 2008 20:56 GMT

create table test (a integer not null generated always as identity
(start with 1 increment by 1))

I cannot insert data since a is defined with "generated always", and
there aint no other columns. This is a hole, just found this when I
was taking prescription for everyday boredom.

Reply to this Message

Karl Hanson - 07 Jan 2008 21:31 GMT

> create table test (a integer not null generated always as identity
> (start with 1 increment by 1))
>
> I cannot insert data since a is defined with "generated always", and
> there aint no other columns. This is a hole, just found this when I
> was taking prescription for everyday boredom.

Which platform and DB2 version? This works on System i :

=> insert into test values(default)

From description of DEFAULT in VALUES clause in the reference:

DEFAULT
Specifies that the default value is assigned to a column. The value that
is inserted depends on how the column was defined, as follows:
<snip>
* If the column is a ROWID or identity column, the database manager
will generate a new value.

DEFAULT must be specified for a ROWID or an identity column that was
defined as GENERATED ALWAYS unless OVERRIDING USER VALUE is specified to
indicate that any user-specified value will be ignored and a unique
system-generated value will be inserted.

http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=/db2/rbafz
mstbackup.htm

--
Karl Hanson

Reply to this Message

Arun Srinivasan - 07 Jan 2008 22:18 GMT

> > create table test (a integer not null generated always as identity
> > (start with 1 increment by 1))
[quoted text clipped - 25 lines]
> --
> Karl Hanson

Thanks Karl. It works, and I learnt something new today about use of
default.

Reply to this Message

Serge Rielau - 08 Jan 2008 01:38 GMT

>>> create table test (a integer not null generated always as identity
>>> (start with 1 increment by 1))
[quoted text clipped - 26 lines]
> Thanks Karl. It works, and I learnt something new today about use of
> default.

Also works with UPDATE. In that case it generates a new value,
overwriting the existing one.

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

Sanjuro - 07 Jan 2008 22:07 GMT

Why do you consider this to be a hole?

Cheers,
Sanjuro

Reply to this Message

--CELKO-- - 11 Jan 2008 20:03 GMT

>> I cannot insert data since a is defined with "generated always", and there ain't no other columns. <<

Think about what that table models in RM. It is a **physical**
counter for the order the rows are inserted into the *physical* table
and has nothing to do with a data model at all!

Reply to this Message