Rowid on fragmented tables

[bgfarmer]

I have joined a company that has written all of their applications using rowid. Right now, all tables reside in single dbspaces. I want to fragment these tables by expression, but know that when this happens then rowid is thrown out. My question is, if tables are created in fragmented spaces and you use the "with rowid" statement in the create table, will rowid be kept? I have read that it will, but wanted to check others opinions in this. If I could get this to work, then apps would not have to be rewritten, which would be a good think. There are several hundred apps we are talking about and would save a lot of time. Any Ideas?

Regards

 

[vpshriyan]

Hi,

Yes. You may go ahead with the data fragmentation based on expression strategy with the use of "with rowids" clause of the create table statement for fragmented tables. By doing so you are forcing the database server to:

1 add the 4-byte unique value to each row in the table. (fragment ID address)
2 create an internal index that it uses to access the data in the table by rowid.
3 insert a row in the sysfragments catalog table for the internal index.

Your existing applications should be in a position to refer the rowid for the fragmented table in this way. However, you should note that rowids in fragmented tables are accessed by an index that is created when the rowid is introduced as discussed above; this access method is slow. Informix recommends that users creating new applications move toward using primary keys as a method of row
identification instead of rowid usage. The primary keys are defined in the ANSI standard of SQL, using them to access data makes your applications more portable.

Regards,
Shriyan

 

[bgfarmer]

Thanks Shriyan,
I appreciate your insight. I know all about going to primary keys instead of rowid, but right now we have several hundred apps that look at it this way. We will need a huge effort from apps to change this. It is going to be happening one of these days, but there is so much infighting in the group, I don't see it happeing for a while.

Regards