Multiple TEMP dbspace query (I should know this.......)
A poster whose query failed with error -564 / ISAM -179 (no free disk space for sort) asked if multiple DBSPACETEMP spaces would overflow into each other. Art S. Kagel said temp tables and sort files are fragmented round robin across them, so it should help. Jacques added that if any one temp dbspace fills to 100%, operations needing it still fail.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
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