Re: Misunderstood DBSPACETEMP ?

Stefan Weideneder <stefan@www.weideneder.de> wrote: >Hi folks, >has anyone some experience with DBSPACETEMP ? Starting with version >7.0 ( an inofficial version ) until 7.12UC1 I've made a few tests >with temporary tables. Now I wonder, if I was wrong. >My problem: >I create two dbspaces, dbs1 ( a regular one ) and tempdbs1 ( a dbspace >that is marked as a temporary dbspace (T) ). >I set my DBSPACETEMP environment variable to "dbs1,tempdbs1" and start >dbaccess ( of course I've exported DBSPACETEMP ). >Now I create a temporary table with no log. >CREATE TEMP TABLE t1 ( f1 char(2016) ) WITH NO LOG; >I thought, that this table should be placed in "tempdbs1" now. But >no, it's obviously placed in dbs1. I don't know if the system has been >restarted again, after the dbspaces (dbs1 and tempdbs1) >had been created. >The system where I've made this strange experience was HP-UX 10.0 >with 7.12UC1 OnLine DS. >( A last hint, my database was created in dbs1 ). >Is this a bug or do I have to restart the OnLine System after adding >a dbspace that I want to use as a temporary dbspace ? Stefan, Here's my understanding of how temp tables relate to the DBSPACETEMP environment variable or as a parameter in the onconfig file. In your example, you have DBSPACETEMP set to dbs1 and tempdbs1. When you create temp table t1, it is going to go into the dbs1 dbspace if thisis the first temp table that was created. In other words, Informix does a round robin approach when there is more than one dbspace for DBSPACETEMP. If you created another temp table, it would go in tempdbs1 and the next one would go in dbs1 again. One way that you could force your t1 temp table to tempdbs1 would be to use the 'in tempdbs1' clause of the create table statement. Howver, I don't understand why you specify your regular dbspace dbs1 that contains your database as a dbspace that can be used for temporary tables and sort files. Also, understand that there is a difference between dbs1 and tempdbs1. You created dbs1 as a regular dbspace and tempdbs1 as a temporary dbspace. A temporary dbspace (-t) will not be logged(physical and logical) or backed up. If your database uses logging, in this case temp table t1 will not be logged because of you using the 'no log' clause. If you did not use the 'no log' option and your database used logging, then t1 would have been logged because it was created in dbs1, a regular dbspace. So, this is not a bug...working as design. You do not need to restart the online after adding a temporary dbspace if you use the DBSPACETEMP as an environment variable, but if you use it as a config parameter, the online would need to be restarted if the temporary dbspace was added after the online was up. Hope this helps and I didn't confuse you more. -- Melvin Mariney, Informix DBA e-mail: melvin.mariney2@bridge.bellsouth.com