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 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 ============== 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. In the onconfig file, you need to set DBSPACETEMP to tempdbs1 (the dbspace you've marked with T), like so: DBSPACETEMP tempdbs1This 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