Re: online 7.10.UD2 problem figured

Jim Tibbets x143 (jtibb@rankin.com) wrote: : Hello all, : Just wanted to let you all know I found out what was going on : with my previous problem of: : >select * from (any EMPTY table) where rowid = 0 : >into temp my_tmp with no log : > : >and then... : > : >select rowid from my_tmp : > : >produces: : >857: Rowids do not exist in table. : > : >Yet if I run the select statement without the 'with no log' : >option the statement executes fine OR if there are : >rows in the select * from (table). : >According to informix this was a change from the uc to the ud : >verions that temp tables are created fragmented with the round robin : >option. But I still dont understand that if this is true why does : >the statement work if there are rowid in the select * from (table). : The DBSPACETEMP in the onconfig file is set to: : DBSPACETEMP=tmp1,tmp2 : so the temp table is indeed being created fragmented. : If DBSPACETEMP is set to just tmp1 the above statement : will simply return a notfound like would be expected. : This still seems like a bug to me. I would think that regardless of : if the table was created fragmented or not a select rowid from : the temp table should return a notfound if there aren't any rows : in the temp table and not a -857: Rowids do not exist in table. : If anyone else is having this problem the case # is 378620. : thanks : James Tibbets : jtibb@rankin.com James, Fragmented tables are not created with rowids. This is by design. I'll try to explain without getting too long winded... Rowid is a value comprised of two parts: logical page number and slot number. So, a row on logical page 1, slot 1 has a rowid of 0x101. (If you don't know what a logical page or slot number is, I believe it's all explained in the admin guides). Rowid gives the engine the ability to quickly locate a row without needing to use any indexes or doing any kind of searching. We can go directly to the row in question. A fragmented table is really multiple tablespaces at a lower layer, so if you have 2 fragments, you have (internally) 2 tablespaces, with 2 logical pages numbered 1, so rowid is no longer unique. This contradicts the definition of a rowid. Because of this contradiction, we can't use the above implementation of rowid to uniquely locate a row in a table. If you try to select rowid from a fragmented table, the engine will return the -857 error to let you know you've made an improper request. It's not a bug, it's the way the engine is designed. There is a way to create a fragmented table with rowids (via sql), but the addition of the rowid adds additional overhead that may not be a good idea for temp tables. I believe that rowid on a fragmented table is implemented via a unique index, so access by rowid is no longer a direct access of the row in question. The engine must now search an index to locate the rowid you are looking for. Here's a kludgy work-around: create temp table mytmp(...column definitions...) WITH ROWIDS fragment by round robin in tempdbspace1, tempdbspace2, ...etc.; insert into mytmp select * from emptytab where rowid=0; This probably isn't desirable, because you must tell the engine which dbspaces to use to locate your temp table, and does not make any use of the DBSPACETEMP variable, but I tested it and it does seem to work. Why are you so interested in the rowid of a row in a temporary table? Just curious. I guess I did get long-winded after all! :) ---------------------------------------------------------------------------- Bill Glidden Sr. Advanced Support Engineer E-mail: glidden@informix.com Advanced Support (415) 926-6904 Informix Software, Inc. ----------------------------------------------------------------------------