Way temp dbspaces are filled

Good day to all. I have a question about how Informix fills and uses the temp dbspaces. Machine: Sun Ultra Enterprise 10000 OS: SunOS 5.6 Informix: 7.30.UC9 I think that this version of Informix uses the temp spaces in a round robin fashion both when you create a temp table with no log and when the engine needs to use the temp spaces during the sort portion of a query. Based on this statement, will the engine use the remaining temp spaces if one fills up? I recently had a situation where I was getting the error 229. This was caused by the root dbs being full, but in the investigation I noticed that 1 temp dbspace was completely full, the remaining 4 were empty. I started wondering if this would cause problems in trying to use the temp spaces. DBSpace Size Used tempdbs1 512000 512000 tempdbs2 512000 53 tempdbs3 512000 53 tempdbs4 512000 53 tempdbs5 512000 53 Would the engine use tempdbs2-5 for a sort on a query or would it try to use the spaces round robin, see that 1 is full and error out? I hope I have asked this question properly. Please let me know if additional clarification is needed. Lem Beason MIC, Inc. Sent via Deja.com http://www.deja.com/ Before you buy.

In article <81edv8$26h$1@nnrp1.deja.com>, Lem Beason <ltbeason@my-deja.com> wrote: > Good day to all. I have a question about how Informix fills and uses > the temp dbspaces. > > Machine: Sun Ultra Enterprise 10000 > OS: SunOS 5.6 > Informix: 7.30.UC9 > > I think that this version of Informix uses the temp spaces in a round > robin fashion both when you create a temp table with no log and when > the engine needs to use the temp spaces during the sort portion of a > query. Based on this statement, will the engine use the remaining > temp spaces if one fills up? > > I recently had a situation where I was getting the error 229. This was > caused by the root dbs being full, but in the investigation I noticed > that 1 temp dbspace was completely full, the remaining 4 were empty. I > started wondering if this would cause problems in trying to use the > temp spaces. > > DBSpace Size Used > tempdbs1 512000 512000 > tempdbs2 512000 53 > tempdbs3 512000 53 > tempdbs4 512000 53 > tempdbs5 512000 53 > > Would the engine use tempdbs2-5 for a sort on a query or would it try > to use the spaces round robin, see that 1 is full and error out? > > I hope I have asked this question properly. Please let me know if > additional clarification is needed. > > Lem Beason > MIC, Inc. > > Sent via Deja.com http://www.deja.com/ > Before you buy. > If I remember correctly from class, if one temp space is full it is excluded from the round robin strategy. -- # unrm / ksh: unrm: not found # man cpio Sent via Deja.com http://www.deja.com/ Before you buy.

In article <81edv8$26h$1@nnrp1.deja.com>, Lem Beason <ltbeason@my-deja.com> wrote: > Good day to all. I have a question about how Informix fills and uses > the temp dbspaces. > > Machine: Sun Ultra Enterprise 10000 > OS: SunOS 5.6 > Informix: 7.30.UC9 > > I think that this version of Informix uses the temp spaces in a round > robin fashion both when you create a temp table with no log and when > the engine needs to use the temp spaces during the sort portion of a > query. Based on this statement, will the engine use the remaining > temp spaces if one fills up? > > I recently had a situation where I was getting the error 229. This was > caused by the root dbs being full, but in the investigation I noticed > that 1 temp dbspace was completely full, the remaining 4 were empty. I > started wondering if this would cause problems in trying to use the > temp spaces. > > DBSpace Size Used > tempdbs1 512000 512000 > tempdbs2 512000 53 > tempdbs3 512000 53 > tempdbs4 512000 53 > tempdbs5 512000 53 > > Would the engine use tempdbs2-5 for a sort on a query or would it try > to use the spaces round robin, see that 1 is full and error out? > > I hope I have asked this question properly. Please let me know if > additional clarification is needed. In 7.3x all temp tables and sort work files should be created FRAGMENTED BY ROUND ROBIN across all of the tempdb spaces listed in the session's DBSPACETEMP environment variable or if that is invalid or not set then in those listed in the DBSPACETEMP environment variable when the engine was started or failing that in those listed in the DBSPACETEMP ONCONFIG parameter. If one of your temp dbspaces is filling up then some session(s) is(are) running with a bad or at least partial list of temp dbspaces in its environment. Art S. Kagel Sent via Deja.com http://www.deja.com/ Before you buy.

mars1972@my-deja.com wrote: : In article <81edv8$26h$1@nnrp1.deja.com>, : Lem Beason <ltbeason@my-deja.com> wrote: : > Good day to all. I have a question about how Informix fills and uses : > the temp dbspaces. : > : > Machine: Sun Ultra Enterprise 10000 : > OS: SunOS 5.6 : > Informix: 7.30.UC9 : > : > I think that this version of Informix uses the temp spaces in a round : > robin fashion both when you create a temp table with no log and when : > the engine needs to use the temp spaces during the sort portion of a : > query. Based on this statement, will the engine use the remaining : > temp spaces if one fills up? : > : > I recently had a situation where I was getting the error 229. This : was : > caused by the root dbs being full, but in the investigation I noticed : > that 1 temp dbspace was completely full, the remaining 4 were empty. : I : > started wondering if this would cause problems in trying to use the : > temp spaces. : > : > DBSpace Size Used : > tempdbs1 512000 512000 : > tempdbs2 512000 53 : > tempdbs3 512000 53 : > tempdbs4 512000 53 : > tempdbs5 512000 53 : > My guess from this would be that you haven't defined TEMPDBS with all these spaces or the temp spaces weren't all added with -t flag. Informix will round robin the spaces, when one fills up it stops and reports there is no temp space. It gets a list of temp spaces from TEMPDBS, however. : > Would the engine use tempdbs2-5 for a sort on a query or would it try : > to use the spaces round robin, see that 1 is full and error out? : > : > I hope I have asked this question properly. Please let me know if : > additional clarification is needed. : > : > Lem Beason : > MIC, Inc. : > : > Sent via Deja.com http://www.deja.com/ : > Before you buy. : > : If I remember correctly from class, if one temp space is full it is : excluded from the round robin strategy. : -- : # unrm / : ksh: unrm: not found : # man cpio : Sent via Deja.com http://www.deja.com/ : Before you buy. -- Rob Wilson rwilson@ntsource.com

In article <rTz_3.1055$qy2.6178@newsfeed.slurp.net>, rwilson@ntsource.com (Rob Wilson) wrote: > mars1972@my-deja.com wrote: > : In article <81edv8$26h$1@nnrp1.deja.com>, > : Lem Beason <ltbeason@my-deja.com> wrote: > : > Good day to all. I have a question about how Informix > : > fills and uses the temp dbspaces. > : > > : > Machine: Sun Ultra Enterprise 10000 > : > OS: SunOS 5.6 > : > Informix: 7.30.UC9 > : > > : > I think that this version of Informix uses the temp spaces in a > : > round robin fashion both when you create a temp table with no log > : > and when the engine needs to use the temp spaces during the sort > : > portion of a query. Based on this statement, will the engine use > : > the remaining temp spaces if one fills up? > : > > : > I recently had a situation where I was getting the error 229. > : > This was caused by the root dbs being full, but in the > : > investigation I noticed that 1 temp dbspace was completely full, > : > the remaining 4 were empty. I started wondering if this would > : > cause problems in trying to use the temp spaces. > : > > : > DBSpace Size Used > : > tempdbs1 512000 512000 > : > tempdbs2 512000 53 > : > tempdbs3 512000 53 > : > tempdbs4 512000 53 > : > tempdbs5 512000 53 > : > > > My guess from this would be that you haven't defined TEMPDBS with all > these spaces or the temp spaces weren't all added with -t flag. From the onconfig file. DBSPACETEMP tempdbs1, tempdbs2, tempdbs3, tempdbs4, tempdbs5 None of the user profiles set a DBSPACETEMP variable or a TEMPDBS variable. Looking at onstat -d, all of the temp spaces have the T status flag. The only thing I can think of is that someone had purposly created something in that particular temp space. > Informix will round robin the spaces, when one fills up it stops and > reports there is no temp space. It gets a list of temp spaces from > TEMPDBS, however. That is what I thought, but after arguing with the in-house DBA for a little while, I thought it best to check my facts. TEMPDBS is used on NT, isn't it? > > -- > Rob Wilson > rwilson@ntsource.com Thanks for your reply. Lem Beason MIC, Inc. lbeason@micinc.com Sent via Deja.com http://www.deja.com/ Before you buy.

> > DBSpace Size Used > > tempdbs1 512000 512000 > > tempdbs2 512000 53 > > tempdbs3 512000 53 > > tempdbs4 512000 53 > > tempdbs5 512000 53 > > > > Would the engine use tempdbs2-5 for a sort on a query or would it > > try to use the spaces round robin, see that 1 is full and error > > out? > > > > I hope I have asked this question properly. Please let me know if > > additional clarification is needed. > > In 7.3x all temp tables and sort work files should be created > FRAGMENTED BY ROUND ROBIN across all of the tempdb spaces listed in > the session's DBSPACETEMP environment variable or if that is invalid > or not set then in those listed in the DBSPACETEMP environment > variable when the engine was started or failing that in those listed > in the DBSPACETEMP ONCONFIG parameter. If one of your temp dbspaces > is filling up then some session(s) is(are) running with a bad or at > least partial list of temp dbspaces in its environment. > > Art S. Kagel The only place that I have found so far that DBSPACETEMP is defined is in the onconfig file. None of the profiles that I have found (so far) set it as an environment variable. From the onconfig: DBSPACETEMP tempdbs1, tempdbs2, tempdbs3, tempdbs4, tempdbs5 The only thing that I can figure out is that someone intentionally created something in that temp space, however, no one will own up to that so it's somewhat of a mystery right now. Thanks for your reply. Lem Beason MIC, Inc. lbeason@micinc.com Sent via Deja.com http://www.deja.com/ Before you buy.