DBSPACETEMP (<<< sigh >>>)

We are experiencing some strange behaviour regarding our use of the $ONCONFIG/UNIX Environment parameter DBSPACETEMP. I have followed earlier discussions with interest (thanks, Art, for your succinct "pecking order" clarification), but am still surprised at the results we are finding, as follows :- We have 3 temp dbspaces defined as tempdbs1, tempdbs2, and tempdbs3. There was some confusion as to which separator to use in the $ONCONFIG file, but I believe EITHER a comma or colon will suffice (as per Informix Unleashed) (experiments with both would appear to confirm this but I am open to an "official" line). We plumped for a colon as follows :- DBSPACETEMP tempdbs1:tempdbs2:tempdbs3 # Default temp dbspaces WITHOUT the UNIX environment parameter set, my understanding is that the engine will use the above dbspaces in round-robin mode (exactly what we are trying to achieve). However, when running one of our I4GL programs (that just happens to have a report section specifying "order external by"), we are finding that the sort is not operating correctly. If we set the UNIX environment variable as follows :- DBSPACETEMP=tempdbs1;export DBSPACETEMP then rerun the same program, all is sweetness and light. Experimentation has shown that this program only operates correctly if a single temp dbspace is defined in the UNIX environment variable, be it tempdbs1, tempdbs2 or tempdbs3. The bizarre thing about the above is that running "onstat -g iof" whilst running the above program shows that whichever dbspaces are defined in the UNIX environment variable DO have some activity. It's almost as though the engine is having problems gathering together the results spread across the 3 dbspaces, but I am merely guessing here. As ever, any thoughts, comments (even the Clown's !) much appreciated. Regards Mr Creosote Informix Dynamic Server 7.31.UC2 INFORMIX-4GL Version 7.20.UD7 INFORMIX-SQL Version 7.20.UE2 SCO OpenServer 5.0.4 -- "Just a waffer thin mint?" Sent via Deja.com http://www.deja.com/ Before you buy.

>If we set the UNIX environment variable as follows :- > >DBSPACETEMP=tempdbs1;export DBSPACETEMP > >then rerun the same program, all is sweetness and light. >Experimentation has shown that this program only operates correctly if >a single temp dbspace is defined in the UNIX environment variable, be >it tempdbs1, tempdbs2 or tempdbs3. > I had the same behavior once on AIX/7.22. It turned out that it - contrary to what one could assume - wanted "," as separator, ie. DBSPACETEMP=tempdbs1,tempdbs2,tempdbs3;export DBSPACETEMP (comma) Meanwhile in the config-file it is tempdbs1:tempdbs2:tempdbs3 (colon) At least one of these makes it work (looking at my onstat -D) ;) //F.E.Theodorsen Finn E. Theodorsen///theodor@inet.uni2.dk///AtCbM///Legend#60219605 J-M-B Type: INTP. Homepage: http://www.theodor.suite.dk/index.htm All advertisments sent to the above address will be treated as requests for computer support, and charged accordingly. Sending these kind of messages equals an acceptance of these terms. The minimum fee is $500.

SORT EXTERNAL BY tells the 4GL report NOT to sort the data as it is already sorted in the required order when it is passed to the report. If this is not the case then you will get strange behaviour in your 4GL report with before/after group sections. -- --------------------------------------- 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 . Mister Creosote wrote in message <889178$gq7$1@nnrp1.deja.com>... > We are experiencing some strange behaviour regarding our use of the >$ONCONFIG/UNIX Environment parameter DBSPACETEMP. I have followed >earlier discussions with interest (thanks, Art, for your >succinct "pecking order" clarification), but am still surprised at the >results we are finding, as follows :- We have 3 temp dbspaces defined >as tempdbs1, tempdbs2, and tempdbs3. There was some confusion as to >which separator to use in the $ONCONFIG file, but I believe EITHER a >comma or colon will suffice (as per Informix Unleashed) (experiments >with both would appear to confirm this but I am open to an "official" >line). We plumped for a colon as follows :- > >DBSPACETEMP tempdbs1:tempdbs2:tempdbs3 # Default temp dbspaces > >WITHOUT the UNIX environment parameter set, my understanding is that >the engine will use the above dbspaces in round-robin mode (exactly >what we are trying to achieve). However, when running one of our I4GL >programs (that just happens to have a report section specifying "order >external by"), we are finding that the sort is not operating correctly. >If we set the UNIX environment variable as follows :- > >DBSPACETEMP=tempdbs1;export DBSPACETEMP > >then rerun the same program, all is sweetness and light. >Experimentation has shown that this program only operates correctly if >a single temp dbspace is defined in the UNIX environment variable, be >it tempdbs1, tempdbs2 or tempdbs3. > >The bizarre thing about the above is that running "onstat -g iof" >whilst running the above program shows that whichever dbspaces are >defined in the UNIX environment variable DO have some activity. > >It's almost as though the engine is having problems gathering together >the results spread across the 3 dbspaces, but I am merely guessing here. > >As ever, any thoughts, comments (even the Clown's !) much appreciated. > >Regards > >Mr Creosote > >Informix Dynamic Server 7.31.UC2 >INFORMIX-4GL Version 7.20.UD7 >INFORMIX-SQL Version 7.20.UE2 >SCO OpenServer 5.0.4 > >-- >"Just a waffer thin mint?" > > >Sent via Deja.com http://www.deja.com/ >Before you buy.

Why have you created three different temp dbspaces ??? Any specific requirement???? In article <889178$gq7$1@nnrp1.deja.com>, Mister Creosote <glynb@my-deja.com> wrote: > We are experiencing some strange behaviour regarding our use of > the > $ONCONFIG/UNIX Environment parameter DBSPACETEMP. I have followed > earlier discussions with interest (thanks, Art, for your > succinct "pecking order" clarification), but am still surprised at > the > results we are finding, as follows :- We have 3 temp dbspaces > defined > as tempdbs1, tempdbs2, and tempdbs3. There was some confusion as to > which separator to use in the $ONCONFIG file, but I believe EITHER > a > comma or colon will suffice (as per Informix Unleashed) > (experiments > with both would appear to confirm this but I am open to an > "official" > line). We plumped for a colon as follows :- > DBSPACETEMP tempdbs1:tempdbs2:tempdbs3 # Default temp dbspaces > WITHOUT the UNIX environment parameter set, my understanding is > that > the engine will use the above dbspaces in round-robin mode (exactly > what we are trying to achieve). However, when running one of our > I4GL > programs (that just happens to have a report section specifying > "order > external by"), we are finding that the sort is not operating > correctly. > If we set the UNIX environment variable as follows :- > DBSPACETEMP=tempdbs1;export DBSPACETEMP > then rerun the same program, all is sweetness and light. > Experimentation has shown that this program only operates > correctly if > a single temp dbspace is defined in the UNIX environment variable, > be > it tempdbs1, tempdbs2 or tempdbs3. > The bizarre thing about the above is that running "onstat -g iof" > whilst running the above program shows that whichever dbspaces are > defined in the UNIX environment variable DO have some activity. > It's almost as though the engine is having problems gathering > together > the results spread across the 3 dbspaces, but I am merely guessing > here. > As ever, any thoughts, comments (even the Clown's !) much > appreciated. > Regards > Mr Creosote > Informix Dynamic Server 7.31.UC2 > INFORMIX-4GL Version 7.20.UD7 > INFORMIX-SQL Version 7.20.UE2 > SCO OpenServer 5.0.4 > -- > "Just a waffer thin mint?" > Sent via Deja.com http://www.deja.com/ > Before you buy. * Sent from RemarQ http://www.remarq.com The Internet's Discussion Network * The fastest and easiest way to search and participate in Usenet - Free!

looking at onstat -d doesn't always tell you if a DBSPACETEMP variable has been set. Onstat -d will only tell you if the dbspace that was created from the get go was set as temp. For example, if you create a dbspace called 'dbspace1' and don't set it as temp, you won't see the T flag when you do an onstat -d even if you set the DBSPACETEMP variable to reflect dbspace1 as being a temporary dbspace. Furthermore, I just spoke with Informix yesterday about the delimiters (comma vs. colon). The ONCONFIG file will accept either, while onmonitor will only accept a comma as delimiter. Try this: bounce your instance with your DBSPACETEMP separated by colons. Then access onmonitor, go to Parameters, then Shared Memory and then try to arrow down past the Dbspace Temp field. It won't let you until you change the delimiter to comma. BTW, I'm running IDS 7.30 on Solaris 2.5.1. "Finn E. Theodorsen" wrote: > >If we set the UNIX environment variable as follows :- > > > >DBSPACETEMP=tempdbs1;export DBSPACETEMP > > > >then rerun the same program, all is sweetness and light. > >Experimentation has shown that this program only operates correctly if > >a single temp dbspace is defined in the UNIX environment variable, be > >it tempdbs1, tempdbs2 or tempdbs3. > > > > I had the same behavior once on AIX/7.22. It turned out that > it - contrary to what one could assume - wanted "," as separator, ie. > > DBSPACETEMP=tempdbs1,tempdbs2,tempdbs3;export DBSPACETEMP > > (comma) > > Meanwhile in the config-file it is > > tempdbs1:tempdbs2:tempdbs3 > > (colon) > > At least one of these makes it work (looking at my onstat -D) ;) > > //F.E.Theodorsen > > Finn E. Theodorsen///theodor@inet.uni2.dk///AtCbM///Legend#60219605 > J-M-B Type: INTP. Homepage: http://www.theodor.suite.dk/index.htm > > All advertisments sent to the above address will be > treated as requests for computer support, and charged > accordingly. Sending these kind of messages equals an > acceptance of these terms. The minimum fee is $500. -- Phillip Tien Database Administrator Whole Foods Market, Inc. Sometimes I think you have to march right in and demand your rights, even if you don't know what your rights are, or who the person is you're talking to. Then on the way out, slam the door.

Savio Pereira wrote: > > Why have you created three different temp dbspaces ??? > Any specific requirement???? At least three temp dbspaces are recommended to speed sorting due to the way Informix merges sort-work temp tables/files this minimizes head movement on the chunks of a single dbspace. -- Art S. Kagel & Family kagel@erols.com

Mister Creosote wrote: > > We are experiencing some strange behaviour regarding our use of the > $ONCONFIG/UNIX Environment parameter DBSPACETEMP. I have followed > earlier discussions with interest (thanks, Art, for your > succinct "pecking order" clarification), but am still surprised at the > results we are finding, as follows :- We have 3 temp dbspaces defined > as tempdbs1, tempdbs2, and tempdbs3. There was some confusion as to > which separator to use in the $ONCONFIG file, but I believe EITHER a > comma or colon will suffice (as per Informix Unleashed) (experiments > with both would appear to confirm this but I am open to an "official" > line). We plumped for a colon as follows :- > > DBSPACETEMP tempdbs1:tempdbs2:tempdbs3 # Default temp dbspaces > > WITHOUT the UNIX environment parameter set, my understanding is that > the engine will use the above dbspaces in round-robin mode (exactly > what we are trying to achieve). However, when running one of our I4GL > programs (that just happens to have a report section specifying "order > external by"), we are finding that the sort is not operating correctly. > If we set the UNIX environment variable as follows :- If the query feeding the report does not have an ORDER BY clause then the EXTERNAL clause is the cause of the problem and your confusion. With only one tempdbspace the temp table you are apparently creating (explicitely or implicitely) as part of the process is accidentally sorted so everything looks OK. With multiple temp dbspaces in the environment (or ONCONFIG) the temp table is fragmented but read by a sequential scan which scans the fragments one at a time so the data is returned in three sorted blocks, one from each fragment. -- Art S. Kagel & Family kagel@erols.com