ROWID question

Aristotle

ROWID question
Greetings list members,
When I create a table with ROWID as a column with
GENERATED ALWAYS, without any index I am able to
insert few rows, it worked. When I create another
table with ROWID as column with GENERATED BY DEFAULT
and try to insert some rows it is telling it needs an
index and giving a message like table definition is
in-complete. SQLCODE = -540, SQLSTATE = 57001. Can
anyone
explain this?

CREATE TABLE owner.TABLE1
(ROW_ID ROWID GENERATED BY DEFAULT
,PROJ_ID SMALLINT NOT NULL
,PROJ_NAME CHARACTER(10) NOT NULL
)
CCSID
EBCDIC
IN database.tablespace1 ;

Thanking you in advance!

Regards,
AA.


__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com



Agus Kwee

Re: ROWID question
(in response to Aristotle)
Alex,

According to the DB2 SQL Reference manual:
Following is the requirements for a ROWID column with
GENERATED BY DEFAULT:
1. The value inserted into the column must be a valid
row ID value previously generated by DB2. It should
only be used if you are using data propagation.
2. Before inserting into or loading the table, a unique
index must be created on this ROWID column.

Agus Kwee
Themis Training



Syed (TATA) Fazal

Re: ROWID question
(in response to Agus Kwee)
Alex,

GENERATED BY DEFAULT will need a unique index defined on it where as in
case of GENERATED ALWAYS DB2 takes care of the uniqueness of the column
defined. The option DEFAULT is where DB2 and a user or application can
supply with a value but where as in case of ALWAYS it is DB2 which will take
care of the value so it will make sure that the column is unique.


Regards,
Syed

-----Original Message-----
From: Alex, Aristotle [mailto:[login to unmask email]
Sent: Saturday, December 14, 2002 12:20 AM
To: [login to unmask email]
Subject: ROWID question


Greetings list members,
When I create a table with ROWID as a column with
GENERATED ALWAYS, without any index I am able to
insert few rows, it worked. When I create another
table with ROWID as column with GENERATED BY DEFAULT
and try to insert some rows it is telling it needs an
index and giving a message like table definition is
in-complete. SQLCODE = -540, SQLSTATE = 57001. Can
anyone
explain this?

CREATE TABLE owner.TABLE1
(ROW_ID ROWID GENERATED BY DEFAULT
,PROJ_ID SMALLINT NOT NULL
,PROJ_NAME CHARACTER(10) NOT NULL
)
CCSID
EBCDIC
IN database.tablespace1 ;

Thanking you in advance!

Regards,
AA.


__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com