857 Rowids do not exist on table

Hello all, IDS 11.10.FC2W4 on solaris 10 select rowid from tab_name; # tab_name is 1 row table (db in buffered log)rowid 257 select * from tab_name into temp vikas; 1 row(s) retrieved into temp table. select rowid from vikas; 857: Rowids do not exist on table. This worked when we were on IDS 9.40 --When you create a temporary table, the database server uses the following criteria: 1.Rows in fragmented tables don't have ROWIDs 2.If the rows exceed 8 kilobytes, the database server creates multiple fragments and uses a round-robin fragmentation scheme to populate them unless you specify a fragmentation method and location for the table. select dbsname,tabname,ti_nextns, ti_nptotal, ti_npused, ti_npdata,ti_nrows,ti_rowsize from systabnames, systabinfo where partnum = ti_partnum and tabname='tab_name'; ti_nrows 1 ti_rowsize 88 what am i missing here? what more should i check for. Pls help... Regards vikas.

If you have more than one temp dbspace the engine automatically fragments the temp table round robin across all of those temp tables. It looks like in the 9.40 engine you only had a single temp dbspace. So, to get rowids in the temp table, you'll have to ALTER the temp table and ADD ROWID. Why do you want/need rowids in the temp table? Art On Fri, Sep 12, 2008 at 6:44 AM, VIKAS HIVARKAR <vikas.hivarkar@gmail.com>wrote: > Hello all, > > IDS 11.10.FC2W4 on solaris 10 > > select rowid from tab_name; # tab_name is 1 row table (db in buffered log)> rowid > 257 > > select * from tab_name into temp vikas;> > 1 row(s) retrieved into temp table. > > select rowid from vikas;> > 857: Rowids do not exist on table.> > This worked when we were on IDS 9.40 > > --When you create a temporary table, the database server uses the following > criteria: > 1.Rows in fragmented tables don't have ROWIDs > 2.If the rows exceed 8 kilobytes, the database server creates multiple > fragments and uses a round-robin fragmentation scheme to populate them > unless > you specify a fragmentation method and location for the table. > > select dbsname,tabname,ti_nextns, ti_nptotal, ti_npused, > ti_npdata,ti_nrows,> ti_rowsize from systabnames, systabinfo where partnum = ti_partnum and > tabname='tab_name'; > > ti_nrows 1 > ti_rowsize 88 > > what am i missing here? what more should i check for. > Pls help... > > Regards > vikas. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.

If you set DBSPACETEMP to just one dbspacetemp in you session environment do you get the same behavior? Regards, On Fri, Sep 12, 2008 at 11:44 AM, VIKAS HIVARKAR <vikas.hivarkar@gmail.com>wrote: > Hello all, > > IDS 11.10.FC2W4 on solaris 10 > > select rowid from tab_name; # tab_name is 1 row table (db in buffered log)> rowid > 257 > > select * from tab_name into temp vikas;> > 1 row(s) retrieved into temp table. > > select rowid from vikas;> > 857: Rowids do not exist on table.> > This worked when we were on IDS 9.40 > > --When you create a temporary table, the database server uses the following > criteria: > 1.Rows in fragmented tables don't have ROWIDs > 2.If the rows exceed 8 kilobytes, the database server creates multiple > fragments and uses a round-robin fragmentation scheme to populate them > unless > you specify a fragmentation method and location for the table. > > select dbsname,tabname,ti_nextns, ti_nptotal, ti_npused, > ti_npdata,ti_nrows,> ti_rowsize from systabnames, systabinfo where partnum = ti_partnum and > tabname='tab_name'; > > ti_nrows 1 > ti_rowsize 88 > > what am i missing here? what more should i check for. > Pls help... > > Regards > vikas. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...

2008/9/12 Art Kagel <art.kagel@gmail.com>: > If you have more than one temp dbspace the engine automatically fragments > the temp table round robin across all of those temp tables. It looks like > in the 9.40 engine you only had a single temp dbspace. So, to get rowids in > the temp table, you'll have to ALTER the temp table and ADD ROWID. > > Why do you want/need rowids in the temp table? 'cos he doesn't understand primary keys !!! > > Art > > On Fri, Sep 12, 2008 at 6:44 AM, VIKAS HIVARKAR > <vikas.hivarkar@gmail.com>wrote: > >> Hello all, >> >> IDS 11.10.FC2W4 on solaris 10 >> >> select rowid from tab_name; # tab_name is 1 row table (db in buffered log)>> rowid >> 257 >> >> select * from tab_name into temp vikas;>> >> 1 row(s) retrieved into temp table. >> >> select rowid from vikas;>> >> 857: Rowids do not exist on table.>> >> This worked when we were on IDS 9.40 >> >> --When you create a temporary table, the database server uses the following >> criteria: >> 1.Rows in fragmented tables don't have ROWIDs >> 2.If the rows exceed 8 kilobytes, the database server creates multiple >> fragments and uses a round-robin fragmentation scheme to populate them >> unless >> you specify a fragmentation method and location for the table. >> >> select dbsname,tabname,ti_nextns, ti_nptotal, ti_npused, >> ti_npdata,ti_nrows,>> ti_rowsize from systabnames, systabinfo where partnum = ti_partnum and >> tabname='tab_name'; >> >> ti_nrows 1 >> ti_rowsize 88 >> >> what am i missing here? what more should i check for. >> Pls help... >> >> Regards >> vikas. >> >> >> >> > ******************************************************************************* >> Forum Note: Use "Reply" to post a response in the discussion forum. >> >> > > -- > Art S. Kagel > Oninit (www.oninit.com) > IIUG Board of Directors (art@iiug.org) > > Disclaimer: Please keep in mind that my own opinions are my own opinions and > do not reflect on my employer, Oninit, the IIUG, nor any other organization > with which I am associated either explicitly or implicitly. Neither do those > opinions reflect those of other individuals affiliated with any entity with > which I am affiliated nor those of the entities themselves. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >

@Mr Kagel >If you have more than one temp dbspace the engine automatically fragments >the temp table round robin across all of those temp tables. It looks like >in the 9.40 engine you only had a single temp dbspace. So, to get rowids in >the temp table, you'll have to ALTER the temp table and ADD ROWID. I tried with single temp dbs against DBSPACETEMP and every thing works as it should for us.Thank you very much... >Why do you want/need rowids in the temp table? yes if today i have to write a code then i wont use rowids (rowid column is unique for each row, but it is not necessarily sequential. It is recommended, however, that you use primary keys as an access method rather than exploiting the rowid column - nicely documented). but we have code that no one would think of changing and the application owners are using these rowids from the temp table for their code. and i am not a developer my self so.. @Fernando >If you set DBSPACETEMP to just one dbspacetemp in you session environment do >you get the same behavior? >Regards, PERFECT ! we had done exactly what you have mentioned..as we want change the onconfig with atleast 2 temp db spaces against DBSPACETEMP and also avoid fragmentation for temp tables. Thank you.. @Keith >'cos he doesn't understand primary keys !!! i understand primary keys but then.. anyway thank you for your response.

Pls i need some help with keys also.. DBSPACETEMP tempdbs1,tempdbs2 I have created a table with column key. primary key (key) constraint "informix".tab_01 select key from tab_name; 0 1 2 3 4 ... and then do select * from tab_name order by key desc into temp vk; key 612 606 604 ... 6 4 2 0 what should i do in order to get the desired result as key 612 611 606 605 604 603 602 601 which i am getting by making sure that there is only one temp dbspcae againts DBSPACETEMP. Regards,

Very confusing... You have two tempspaces... You select into a temp table with an order by, whihc doesn't make too much sense... (the order of inserts should be irrelevant to whatever you try to do with the temp table after). You're printing an output from a query which does not give you any output (because you use an INTO clause). So, can you expalin the problem again? Are you then selecting from the temp table? If that's the problem, then forget the order by in the "into temp" select, and put it on the SELECT from temp_table Regards, On Fri, Sep 12, 2008 at 2:47 PM, VIKAS HIVARKAR <vikas.hivarkar@gmail.com>wrote: > Pls i need some help with keys also.. > > DBSPACETEMP tempdbs1,tempdbs2> > I have created a table with column key. > primary key (key) constraint "informix".tab_01 > > select key from tab_name;> > 0 > > 1 > > 2 > > 3 > > 4 > > .... > > and then do > select * from tab_name order by key desc into temp vk;> > key > > 612 > > 606 > > 604 > > .... > > 6 > > 4 > > 2 > > 0 > > what should i do in order to get the desired result as > > key > > 612 > > 611 > > 606 > > 605 > > 604 > > 603 > > 602 > > 601 > which i am getting by making sure that there is only one temp dbspcae > againts > DBSPACETEMP. > > Regards, > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...

>Very confusing... >You have two tempspaces... >You select into a temp table with an order by, whihc doesn't make too much >sense... (the order of inserts should be irrelevant to whatever you try to >do with the temp table after). i was just over reacting by using the order clause. >You're printing an output from a query which does not give you any output >(because you use an INTO clause). >So, can you expalin the problem again? >Are you then selecting from the temp table? If that's the problem, then >forget the order by in the "into temp" select, and put it on the SELECT from >temp_table >Regards, oops my mistake as i forgot to write the select * from vk; # vk is the temp table. Ok i try again 1)select key from tab_name; # column key has a primery key on it key 0 1 2 3 ... 2)select key from tab_name into temp vk; 249 row(s) retrieved into temp table 3)select key from vk; key 0 2 4 6 8 10 12 ...... 604 606 612 1 3 5 7 9 11 13 Can i have get the result in manner that i got from 1st query ie select key from tab_name; Or is it again as Mr kagel correctly mentioned that due to 2 dbspaces against DBSPACETEMP the the engine is automatically fragmenting the temp table round robin . Regards vikas

Yes, as Art said. Using two DBSPACETEMP it fragments the table in two pieces using round robin. Remember that you can NEVER trust the order of data retieval UNLESS you use an order by clause. Regards. On Fri, Sep 12, 2008 at 3:19 PM, VIKAS HIVARKAR <vikas.hivarkar@gmail.com>wrote: > >Very confusing... > >You have two tempspaces... > >You select into a temp table with an order by, whihc doesn't make too much > >sense... (the order of inserts should be irrelevant to whatever you try to > >do with the temp table after). > i was just over reacting by using the order clause. > >You're printing an output from a query which does not give you any output > >(because you use an INTO clause). > >So, can you expalin the problem again? > >Are you then selecting from the temp table? If that's the problem, then > >forget the order by in the "into temp" select, and put it on the SELECT > from > >temp_table > > >Regards, > > oops my mistake as i forgot to write the > select * from vk; # vk is the temp table.> > Ok i try again > > 1)select key from tab_name; # column key has a primery key on it > > key > > 0 > > 1 > > 2 > > 3 > > .... > > 2)select key from tab_name into temp vk; > > 249 row(s) retrieved into temp table > > 3)select key from vk; > > key > > 0 > > 2 > > 4 > > 6 > > 8 > > 10 > > 12 > ....... > > 604 > > 606 > > 612 > > 1 > > 3 > > 5 > > 7 > > 9 > > 11 > > 13 > > Can i have get the result in manner that i got from 1st query ie select key > from tab_name; > > Or is it again as Mr kagel correctly mentioned that due to 2 dbspaces > against > DBSPACETEMP the the engine is automatically fragmenting the temp table > round > robin . > > Regards > vikas > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...

Am I missing something? How about: select key from vk order by key desc; Art On Fri, Sep 12, 2008 at 9:47 AM, VIKAS HIVARKAR <vikas.hivarkar@gmail.com>wrote: > Pls i need some help with keys also.. > > DBSPACETEMP tempdbs1,tempdbs2> > I have created a table with column key. > primary key (key) constraint "informix".tab_01 > > select key from tab_name;> > 0 > > 1 > > 2 > > 3 > > 4 > > .... > > and then do > select * from tab_name order by key desc into temp vk;> > key > > 612 > > 606 > > 604 > > .... > > 6 > > 4 > > 2 > > 0 > > what should i do in order to get the desired result as > > key > > 612 > > 611 > > 606 > > 605 > > 604 > > 603 > > 602 > > 601 > which i am getting by making sure that there is only one temp dbspcae > againts > DBSPACETEMP. > > Regards, > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.