Informix TEMP space query

Hi all, Can someone please tell me the following regarding how INFORMIX utilises TEMP space, in particular: 1) When a table is modified with the ALTER TABLE statement, is it true the table is copied into the same DBSpace where it exists while the alteration takes place? Or does it get copied to somewhere else. Differences between Online 5.* and Online7.31? 2) What about when an index is added to a table? Differences between Online 5.* and Online7.31? 3) Can someone please clear up for me where both implicit and explicit temp tables are created? Differences between Online 5.* and Online7.31? 4) What role do the following play: i) $DBTEMP (Online 5.)? ii) $DBSPACETEMP (Online7.31)? iii) The root DBSpace ......in terms of temporary table creation? 5) I know that in Online7.31 I can create DBSpaces and flag them as being for "T"emporary tables - can I do the same in Online5.*? Regards, Adam

see comments below -- --------------------------------------- Tony Flaherty aef@mfs.misys.co.uk Analyst Programmer Misys Financial Systems All statements and opinions are my own, Misys don't pay me enough to have opinions on their behalf . Adam Bradley wrote in message <01bf8d71$2e68bc60$150510ac@adamb.ahmg.com.au>... >Hi all, > >Can someone please tell me the following regarding how INFORMIX utilises >TEMP space, in particular: > >1) When a table is modified with the ALTER TABLE statement, is it true the >table is copied into the same DBSpace where it exists while the alteration >takes place? Or >does it get copied to somewhere else. Differences between Online 5.* and >Online7.31? > The new table is created in the same dbspace as the original, had a problem only the other day where I couldnt reduce the size of a table by creating a cluster index after deleting lots of row because the dbspace was too full. :o( > >2) What about when an index is added to a table? Differences between Online >5.* and Online7.31? > you can create the index as detached by specifing the dbspace to create it in. I'm not sure of the syntax. > >3) Can someone please clear up for me where both implicit and explicit temp >tables are created? Differences between Online 5.* and Online7.31? > depends on the logging mode of the database and the types of dbspaces you have in DBSPACETEMP, check out the previous posts on this subject, there's lots of em! >4) What role do the following play: > > i) $DBTEMP (Online 5.)? > ii) $DBSPACETEMP (Online7.31)? >iii) The root DBSpace > >......in terms of temporary table creation? > >5) I know that in Online7.31 I can create DBSpaces and flag them as being >for "T"emporary tables - can I do the same in Online5.*? > Don't know much about online 5 but there are lots of posts on this subject, check the archives. >Regards, >Adam >

In article <01bf8d71$2e68bc60$150510ac@adamb.ahmg.com.au>, Adam Bradley <Adam.Bradley@ahmg.com.au> writes >Hi all, > >Can someone please tell me the following regarding how INFORMIX utilises >TEMP space, in particular: > >1) When a table is modified with the ALTER TABLE statement, is it true the >table is copied into the same DBSpace where it exists while the alteration >takes place? Or >does it get copied to somewhere else. Differences between Online 5.* and >Online7.31? > Online 5 yes, Online 7.31 depends which subversion and what you are doing. Under 7.31.UC5 most of the time it is not copied. An in-place alter table is done and the alter table takes only a few seconds. As rows are read they are converted to the new table schema and when rows are written they are written with the new schema. >2) What about when an index is added to a table? Differences between Online >5.* and Online7.31? > The table is not copied. >3) Can someone please clear up for me where both implicit and explicit temp >tables are created? Differences between Online 5.* and Online7.31? > >4) What role do the following play: > > i) $DBTEMP (Online 5.)? > ii) $DBSPACETEMP (Online7.31)? >iii) The root DBSpace > >......in terms of temporary table creation? > >5) I know that in Online7.31 I can create DBSpaces and flag them as being >for "T"emporary tables - can I do the same in Online5.*? > >Regards, >Adam > -- David Williams