Multiple TEMP dbspace query (I should know this.......)

I really should but I can't find the reference to it in TFM. We have a query falling over with "SQL statement error number -564. Cannot sort rows. SYSTEM error number -179. ISAM error: no free disk space for sort" So I'm going to increase the size of our temp space, but thought I'd consider going from our current single temp space to multiple ones (as in DBSPACETEMP=tempspace1,tempspace2 etc etc). If the above query plays it's usual trick on us, will it overflow from one temp space to the next, or will it still fall over? I have a nasty memory of reading somewhere that it'll still fall over - can anyone corroborrate? Ta

iiug@perrior.net wrote: > I really should but I can't find the reference to it in TFM. > > We have a query falling over with > > "SQL statement error number -564. > Cannot sort rows. > SYSTEM error number -179. > ISAM error: no free disk space for sort"> > So I'm going to increase the size of our temp space, but thought I'd > consider going from our current single temp space to multiple ones (as > in DBSPACETEMP=tempspace1,tempspace2 etc etc). > > If the above query plays it's usual trick on us, will it overflow from > one temp space to the next, or will it still fall over? > > I have a nasty memory of reading somewhere that it'll still fall over > - can anyone corroborrate? > Actually, if you have multiple temp spaces, it will fragment temp tables across them all and use them to hold sort-work files round robin. So it's best to place multiple temp spaces on separate structures, but it will definitely help solve your problem and will likely be faster than expanding the single temp space. Art S. Kagel Oninit > Ta > =========================================================================================== Please access the attached hyperlink for an important electronic communications disclaimer: http://www.oninit.com/home/disclaimer.php ===========================================================================================

On 17 Jan, 17:11, "Art S. Kagel (Oninit LLC)" <a...@oninit.com> wrote: > i...@perrior.net wrote: > > I really should but I can't find the reference to it in TFM. > > > We have a query falling over with > > > "SQL statement error number -564. > > Cannot sort rows. > > SYSTEM error number -179. > > ISAM error: no free disk space for sort"> > > So I'm going to increase the size of our temp space, but thought I'd > > consider going from our current single temp space to multiple ones (as > > in DBSPACETEMP=tempspace1,tempspace2 etc etc). > > > If the above query plays it's usual trick on us, will it overflow from > > one temp space to the next, or will it still fall over? > > > I have a nasty memory of reading somewhere that it'll still fall over > > - can anyone corroborrate? > > Actually, if you have multiple temp spaces, it will fragment temp tables > across them all and use them to hold sort-work files round robin. So > it's best to place multiple temp spaces on separate structures, but it > will definitely help solve your problem and will likely be faster than > expanding the single temp space. > > Art S. Kagel > Oninit > Aha! even better than I had hoped for, thanks Art. Just to keep me happy, this applies to 9.30 IDS too? (I can't get funding here for the R&D needed to prove our app on 9.40, 10 or 11 so we're stuck). Malc Oh, and before Captain P mentions it - I do know how to spell "corroborate"!

On Jan 17, 11:04 am, i...@perrior.net wrote: > If the above query plays it's usual trick on us, will it overflow from > one temp space to the next, or will it still fall over? > > I have a nasty memory of reading somewhere that it'll still fall over > - can anyone corroborrate? > As Art mentioned if you have multiple temp spaces it should fragment the temp tables, however, to address your concern, the answer is yes, if for some reason 1 of your temp dbspaces gets full and an object in that temp dbspace needs to grow, it will still fail. It won't be able to just spill over into your other temp dbspaces. So while adding 1 or more temp dbspaces is probably a good idea, if any one of them fills to 100%, it could still cause operations that require temp space to fail. Jacques