ROWID vs Identity Column

Denise M Gantz

ROWID vs Identity Column
I am building a new DB2 table that requires a row_count column which will be incremented for each new record put into the table. It is also included as part of the table primary key.
I'm trying to determine which is the better choice, ROWID or an identity column.
I have some questions on both.
From what I have read, neither of these column types can be included in a LOAD statement; is that correct?
What happens to the value in that column when a row is deleted? Is the value that was assigned to the deleted row reused?
What happens when either ROWID or the identity column data type reaches the end of the number sequence?

Suggestions/advice are welcome.

Thank you

_________________________________________________________________

Register NOW for the IDUG DB2 Tech Conference in Anaheim, May 2-6, 2011!
_________________________________________________________________
International DB2 User Group (IDUG) - Independent, not-for-profit, User Run
Your only source for independent, unbiased, and trusted DB2 information

Chris Hoelscher

Re: ROWID vs Identity Column
(in response to Denise M Gantz)
-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Denise Gantz
Sent: Friday, May 06, 2011 4:16 PM
To: [login to unmask email]
Subject: [DB2-L] ROWID vs Identity Column

I am building a new DB2 table that requires a row_count column which will be incremented for each new record put into the table. It is also included as part of the table primary key.
I'm trying to determine which is the better choice, ROWID or an identity column.
I have some questions on both.
From what I have read, neither of these column types can be included in a LOAD statement; is that correct?
What happens to the value in that column when a row is deleted? Is the value that was assigned to the deleted row reused?
What happens when either ROWID or the identity column data type reaches the end of the number sequence?

Denise, if you are on V8 or higher, have you considered a Sequence ????? it *might* be a better option for you ...

Chris Hoelscher
IDMS & DB2 Database Administrator
502-476-2538

I refuse to repeat gossip - so listen closely the first time



The information transmitted is intended only for the person or entity to which it is addressed and may contain CONFIDENTIAL material. If you receive this material/information in error, please contact the sender and delete or destroy the material/information.


_________________________________________________________________

Register NOW for the IDUG DB2 Tech Conference in Anaheim, May 2-6, 2011!
_________________________________________________________________
International DB2 User Group (IDUG) - Independent, not-for-profit, User Run
Your only source for independent, unbiased, and trusted DB2 information