RE: Misunderstood DBSPACETEMP ?

} } >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 this } is 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 } } This is absolutely correct. Michael Strother INFORMIX } } ============== } } } } Stefan } } As I understand it, DBSPACETEMP is an onconfig configuration parameter. } Setting it from the environment should have no effect. The application } will create the temp table in the default dbspace or where the database } was created. This is absolutely wrong The environment variable will override any previous settings including the system default Michael Strother Informix } } In the onconfig file, you need to set DBSPACETEMP to tempdbs1 (the } dbspace you've marked with T), like so: } DBSPACETEMP tempdbs1 } This will create the temp table in DBSPACETEMP. If you set it to dbs1, } tempdbs1 then it will attempt to first create in dbs1 and if it fails } then in tempdbs1. } } Hope this helps } } Sujit Pal } DBA, Computer Sciences Corp. } } ---------- } From: Stefan Weideneder[SMTP:stefan@sweidi.weideneder.de] } Sent: Tuesday, October 08, 1996 11:46 AM } To: informix-list@rmy.emory.edu } Subject: Misunderstood DBSPACETEMP ? } } 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 ? } } Thanks for your reply, } } Stefan } } stefan@weideneder.de } } }