Rowid concept.

Hi, We are planning to fetch the data from set of tables by using rowids only (no other conditions in the 'where' clause). How Informix is generating rowids for each and every row inserted into Informix table? Is Informix using any sys tables to generate unique rowids for every inserted row? Thanks in advance. Regards, Ravi Shankar N.

Ravi, The rowid is a 4-byte code made up of the logical page number as the 3 first significant bytes and the slot number within that page as the least significant byte. So to answer your question, Informix doesn't need to "generate" the rowid because it represents the location of the actual row. rowids are not unique for fragmented tables, because the logical page is only unique to a fragment. For this reason, when you fragment a table and try to select from it by rowid, the engine will give you a rowid doesn't exist message. To get around the issue with older apps still using rowid, Informix implemented a create table with rowid syntax. Although Informix recommends using primary keys instead of rowids. rowid is not maintained if the row is deleted and re-inserted (some applications do this instead of updating). Alterations to the table can affect rowid. For example, change in fragment strategies, clustering, etc. Any operation that moves the row from it's logical location will change the rowid. That being said, Good Luck, Scott Kolaya -----Original Message----- From: N Ravishank.... [mailto:W2925C@motorola.com] Sent: Monday, September 08, 2003 1:30 AM To: ids@iiug.org Subject: Rowid concept. [1809] Hi, We are planning to fetch the data from set of tables by using rowids only (no other conditions in the 'where' clause). How Informix is generating rowids for each and every row inserted into Informix table? Is Informix using any sys tables to generate unique rowids for every inserted row? Thanks in advance. Regards, Ravi Shankar N.

Further to this; there's an ongoing problem with ISQL and forms on fragmented tables in that you can't run a form on a fragmented table without rowids. Another outstanding request from ages ago. (This is still present on IDS9.30 with ISQL 7.31) On Mon, 8 Sep 2003 14:20:55 -0400 (EDT), KOLAYA, SCOTT M wrote: >Ravi, > >The rowid is a 4-byte code made up of the logical page number as the 3 first >significant bytes and the slot number within that page as the least >significant byte. So to answer your question, Informix doesn't need to >"generate" the rowid because it represents the location of the actual row. >rowids are not unique for fragmented tables, because the logical page is >only unique to a fragment. For this reason, when you fragment a table and >try to select from it by rowid, the engine will give you a rowid doesn't >exist message. To get around the issue with older apps still using rowid, >Informix implemented a create table with rowid syntax. Although Informix >recommends using primary keys instead of rowids. rowid is not maintained if >the row is deleted and re-inserted (some applications do this instead of >updating). Alterations to the table can affect rowid. For example, change >in fragment strategies, clustering, etc. Any operation that moves the row >from it's logical location will change the rowid. > >That being said, Good Luck, > >Scott Kolaya > >-----Original Message----- >From: N Ravishank.... [mailto:W2925C@motorola.com] >Sent: Monday, September 08, 2003 1:30 AM >To: ids@iiug.org >Subject: Rowid concept. [1809] > > >Hi, > >We are planning to fetch the data from set of tables by using rowids only >(no other conditions in the 'where' clause). > >How Informix is generating rowids for each and every row inserted into >Informix table? >Is Informix using any sys tables to generate unique rowids for every >inserted row? > >Thanks in advance. > >Regards, >Ravi Shankar N. > > > -- Malc_p -- XS2Mail: Check your mail anywhere http://www.xs2mail.com/

Does that problem exist even if you create the fragmented table with rowids, or just when you fragment it without specifying with rowids? Scott -----Original Message----- From: Malc_p [mailto:malc_p@btinternet.com] Sent: Tuesday, September 09, 2003 3:45 AM To: KOLAYA, SCOTT M Cc: ids@iiug.org Subject: Re: Rowid concept. [1810] Further to this; there's an ongoing problem with ISQL and forms on fragmented tables in that you can't run a form on a fragmented table without rowids. Another outstanding request from ages ago. (This is still present on IDS9.30 with ISQL 7.31) On Mon, 8 Sep 2003 14:20:55 -0400 (EDT), KOLAYA, SCOTT M wrote: >Ravi, > >The rowid is a 4-byte code made up of the logical page number as the 3 first >significant bytes and the slot number within that page as the least >significant byte. So to answer your question, Informix doesn't need to >"generate" the rowid because it represents the location of the actual row. >rowids are not unique for fragmented tables, because the logical page is >only unique to a fragment. For this reason, when you fragment a table and >try to select from it by rowid, the engine will give you a rowid doesn't >exist message. To get around the issue with older apps still using rowid, >Informix implemented a create table with rowid syntax. Although Informix >recommends using primary keys instead of rowids. rowid is not maintained if >the row is deleted and re-inserted (some applications do this instead of >updating). Alterations to the table can affect rowid. For example, change >in fragment strategies, clustering, etc. Any operation that moves the row >from it's logical location will change the rowid. > >That being said, Good Luck, > >Scott Kolaya > >-----Original Message----- >From: N Ravishank.... [mailto:W2925C@motorola.com] >Sent: Monday, September 08, 2003 1:30 AM >To: ids@iiug.org >Subject: Rowid concept. [1809] > > >Hi, > >We are planning to fetch the data from set of tables by using rowids only >(no other conditions in the 'where' clause). > >How Informix is generating rowids for each and every row inserted into >Informix table? >Is Informix using any sys tables to generate unique rowids for every >inserted row? > >Thanks in advance. > >Regards, >Ravi Shankar N. > > > -- Malc_p -- XS2Mail: Check your mail anywhere http://www.xs2mail.com/

Only if the table is created without specifying rowids. It works if you do an ALTER TABLE ... ADD ROWIDS. On Tue, 9 Sep 2003 13:44:59 -0400 , KOLAYA, SCOTT M wrote: >Does that problem exist even if you create the fragmented table with rowids, >or just when you fragment it without specifying with rowids? > >Scott > >-----Original Message----- >From: Malc_p [mailto:malc_p@btinternet.com] >Sent: Tuesday, September 09, 2003 3:45 AM >To: KOLAYA, SCOTT M >Cc: ids@iiug.org >Subject: Re: Rowid concept. [1810] > > >Further to this; there's an ongoing problem with ISQL and forms on >fragmented >tables in that you can't run a form on a fragmented table without rowids. >Another outstanding request from ages ago. >(This is still present on IDS9.30 with ISQL 7.31) > > >On Mon, 8 Sep 2003 14:20:55 -0400 (EDT), KOLAYA, SCOTT M wrote: >>Ravi, >> >>The rowid is a 4-byte code made up of the logical page number as the 3 >first >>significant bytes and the slot number within that page as the least >>significant byte. So to answer your question, Informix doesn't need to >>"generate" the rowid because it represents the location of the actual row. >>rowids are not unique for fragmented tables, because the logical page is >>only unique to a fragment. For this reason, when you fragment a table and >>try to select from it by rowid, the engine will give you a rowid doesn't >>exist message. To get around the issue with older apps still using rowid, >>Informix implemented a create table with rowid syntax. Although Informix >>recommends using primary keys instead of rowids. rowid is not maintained >if >>the row is deleted and re-inserted (some applications do this instead of >>updating). Alterations to the table can affect rowid. For example, change >>in fragment strategies, clustering, etc. Any operation that moves the row >>from it's logical location will change the rowid. >> >>That being said, Good Luck, >> >>Scott Kolaya >> >>-----Original Message----- >>From: N Ravishank.... [mailto:W2925C@motorola.com] >>Sent: Monday, September 08, 2003 1:30 AM >>To: ids@iiug.org >>Subject: Rowid concept. [1809] >> >> >>Hi, >> >>We are planning to fetch the data from set of tables by using rowids only >>(no other conditions in the 'where' clause). >> >>How Informix is generating rowids for each and every row inserted into >>Informix table? >>Is Informix using any sys tables to generate unique rowids for every >>inserted row? >> >>Thanks in advance. >> >>Regards, >>Ravi Shankar N. >> >> >> > >-- >Malc_p > >-- >XS2Mail: Check your mail anywhere >http://www.xs2mail.com/ > -- Malc_p -- XS2Mail: Check your mail anywhere http://www.xs2mail.com/