Could not open or create a temporary file
On IDS 11.50.FC7W3 with DBSPACETEMP set to two temp dbspaces (tempdbs:tempdbs2), one session consumed all of tempdbs while tempdbs2 stayed almost empty; other sessions needing temp space then failed with SQL -229 / ISAM -131 (no free disk space) instead of using the free second space. Replies confirmed nothing overrode DBSPACETEMP in the environment, identified the culprits as implicit temp usage (DISTINCT) and SELECT ... ORDER BY ... INTO TEMP WITH NO LOG, and suggested running oncheck -pe during the problem query, noting an explicit IN clause on temp tables can unbalance dbspace usage. No resolution is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi all
Version of IDS 11.50.FC7W3
We have configured two exclusive temp dbspaces (nothing new, they exist
for years).
Onstat -d:
277279488 4 0x42001 4 10 2048 N TB
informix tempdbs
27728a358 98 0x42001 480 9 2048 N TB
informix tempdbs2
277294028 4 4 5 1000000 999889
PO-B- /dev/vx/rdsk/db1/dbs5
277294218 5 4 5 1000000 999997
PO-B- /dev/vx/rdsk/db1/dbs6
2772a9028 148 4 5 1000000 999997
PO-B- /dev/vx/rdsk/db1/dbs149
27734f7e8 497 4 5 1000000 999997
PO-B- /dev/vx/rdsk/db1/dbs500
277374db8 548 4 5 1000000 999997
PO-B- /dev/vx/rdsk/db1/dbs511
277376028 549 4 5 1000000 999997
PO-B- /dev/vx/rdsk/db1/dbs513
277376218 550 4 5 1000000 999997
PO-B- /dev/vx/rdsk/db1/dbs523
2773765f8 552 4 5 1000000 999997
PO-B- /dev/vx/rdsk/db1/dbs521
27738b7e8 625 4 5 1000000 999997
PO-B- /dev/vx/rdsk/db1/dbs474
27738d5f8 632 4 5 1000000 999997
PO-B- /dev/vx/rdsk/db1/dbs476
2773465f8 464 98 5 1000000 999997
PO-B- /dev/vx/rdsk/db1/dbs477
27734a5f8 480 98 5 1000000 753941
PO-B- /dev/vx/rdsk/db1/dbs481
27734a7e8 481 98 5 1000000 999997
PO-B- /dev/vx/rdsk/db1/dbs482
27734fdb8 500 98 5 1000000 999997
PO-B- /dev/vx/rdsk/db1/dbs501
277378218 558 98 5 1000000 999997
PO-B- /dev/vx/rdsk/db1/dbs526
277378408 559 98 5 1000000 999997
PO-B- /dev/vx/rdsk/db1/dbs562
277366bc8 611 98 5 1000000 999997
PO-B- /dev/vx/rdsk/db1/dbs352
27738d408 631 98 5 1000000 999997
PO-B- /dev/vx/rdsk/db1/dbs475
277393408 639 98 5 1000000 999997
PO-B- /dev/vx/rdsk/db1/dbs506
Onconfig:
DBSPACETEMP tempdbs:tempdbs2
Today one session allocated all the space of tempdbs. No space of
tempdbs2 was used. The session seemed to be blocked.
Now we had two problems I do not understand:
Why didn't the session allocate space from tempdbs2?
The greater problem was: All other sessions which needed exclusive temp
dbspace received an error:
SQL statement error number -229
Could not open or create a temporary file
SYSTEM error number -131
ISAM error: no free disk space
Why couldn't the other session allocate exclusive temp dbspace from
tempdbs2 which was definitely free?
Reinhard.
On Jan 25, 12:01 am, "Habichtsberg, Reinhard" <RHabichtsb...@arz-
emmendingen.de> wrote:
> Hi all
>
> Version of IDS 11.50.FC7W3
>
> We have configured two exclusive temp dbspaces (nothing new, they exist
> for years).
>
> Onstat -d:
> 277279488 4 0x42001 4 10 2048 N TB
> informix tempdbs
> 27728a358 98 0x42001 480 9 2048 N TB
> informix tempdbs2
>
> 277294028 4 4 5 1000000 999889
> PO-B- /dev/vx/rdsk/db1/dbs5
> 277294218 5 4 5 1000000 999997
> PO-B- /dev/vx/rdsk/db1/dbs6
> 2772a9028 148 4 5 1000000 999997
> PO-B- /dev/vx/rdsk/db1/dbs149
> 27734f7e8 497 4 5 1000000 999997
> PO-B- /dev/vx/rdsk/db1/dbs500
> 277374db8 548 4 5 1000000 999997
> PO-B- /dev/vx/rdsk/db1/dbs511
> 277376028 549 4 5 1000000 999997
> PO-B- /dev/vx/rdsk/db1/dbs513
> 277376218 550 4 5 1000000 999997
> PO-B- /dev/vx/rdsk/db1/dbs523
> 2773765f8 552 4 5 1000000 999997
> PO-B- /dev/vx/rdsk/db1/dbs521
> 27738b7e8 625 4 5 1000000 999997
> PO-B- /dev/vx/rdsk/db1/dbs474
> 27738d5f8 632 4 5 1000000 999997
> PO-B- /dev/vx/rdsk/db1/dbs476
>
> 2773465f8 464 98 5 1000000 999997
> PO-B- /dev/vx/rdsk/db1/dbs477
> 27734a5f8 480 98 5 1000000 753941
> PO-B- /dev/vx/rdsk/db1/dbs481
> 27734a7e8 481 98 5 1000000 999997
> PO-B- /dev/vx/rdsk/db1/dbs482
> 27734fdb8 500 98 5 1000000 999997
> PO-B- /dev/vx/rdsk/db1/dbs501
> 277378218 558 98 5 1000000 999997
> PO-B- /dev/vx/rdsk/db1/dbs526
> 277378408 559 98 5 1000000 999997
> PO-B- /dev/vx/rdsk/db1/dbs562
> 277366bc8 611 98 5 1000000 999997
> PO-B- /dev/vx/rdsk/db1/dbs352
> 27738d408 631 98 5 1000000 999997
> PO-B- /dev/vx/rdsk/db1/dbs475
> 277393408 639 98 5 1000000 999997
> PO-B- /dev/vx/rdsk/db1/dbs506
>
> Onconfig:
> DBSPACETEMP tempdbs:tempdbs2>
> Today one session allocated all the space of tempdbs. No space of
> tempdbs2 was used. The session seemed to be blocked.
>
> Now we had two problems I do not understand:
>
> Why didn't the session allocate space from tempdbs2?
>
> The greater problem was: All other sessions which needed exclusive temp
> dbspace received an error:
> SQL statement error number -229
> Could not open or create a temporary file
> SYSTEM error number -131
> ISAM error: no free disk space>
> Why couldn't the other session allocate exclusive temp dbspace from
> tempdbs2 which was definitely free?
>
> Reinhard.
What is DBSPACETEMP set to in either the environment of the client
process or the servers onconfig file.
> -----Original Message-----
> From: informix-list-bounces@iiug.org [mailto:informix-list-bounces@iiug.org] On
> Behalf Of david@smooth1.co.uk
> Sent: Tuesday, January 25, 2011 8:42 PM
> To: informix-list@iiug.org
> Subject: Re: Could not open or create a temporary file
>
> On Jan 25, 12:01 am, "Habichtsberg, Reinhard" <RHabichtsb...@arz-
> emmendingen.de> wrote:
> > Hi all
> >
> > Version of IDS 11.50.FC7W3
> >
> > We have configured two exclusive temp dbspaces (nothing new, they exist
> > for years).
> >
> > Onstat -d:
> > 277279488 4 0x42001 4 10 2048 N TB
> > informix tempdbs
> > 27728a358 98 0x42001 480 9 2048 N TB
> > informix tempdbs2
> >
> > 277294028 4 4 5 1000000 999889
> > PO-B- /dev/vx/rdsk/db1/dbs5
> > 277294218 5 4 5 1000000 999997
> > PO-B- /dev/vx/rdsk/db1/dbs6
> > 2772a9028 148 4 5 1000000 999997
> > PO-B- /dev/vx/rdsk/db1/dbs149
> > 27734f7e8 497 4 5 1000000 999997
> > PO-B- /dev/vx/rdsk/db1/dbs500
> > 277374db8 548 4 5 1000000 999997
> > PO-B- /dev/vx/rdsk/db1/dbs511
> > 277376028 549 4 5 1000000 999997
> > PO-B- /dev/vx/rdsk/db1/dbs513
> > 277376218 550 4 5 1000000 999997
> > PO-B- /dev/vx/rdsk/db1/dbs523
> > 2773765f8 552 4 5 1000000 999997
> > PO-B- /dev/vx/rdsk/db1/dbs521
> > 27738b7e8 625 4 5 1000000 999997
> > PO-B- /dev/vx/rdsk/db1/dbs474
> > 27738d5f8 632 4 5 1000000 999997
> > PO-B- /dev/vx/rdsk/db1/dbs476
> >
> > 2773465f8 464 98 5 1000000 999997
> > PO-B- /dev/vx/rdsk/db1/dbs477
> > 27734a5f8 480 98 5 1000000 753941
> > PO-B- /dev/vx/rdsk/db1/dbs481
> > 27734a7e8 481 98 5 1000000 999997
> > PO-B- /dev/vx/rdsk/db1/dbs482
> > 27734fdb8 500 98 5 1000000 999997
> > PO-B- /dev/vx/rdsk/db1/dbs501
> > 277378218 558 98 5 1000000 999997
> > PO-B- /dev/vx/rdsk/db1/dbs526
> > 277378408 559 98 5 1000000 999997
> > PO-B- /dev/vx/rdsk/db1/dbs562
> > 277366bc8 611 98 5 1000000 999997
> > PO-B- /dev/vx/rdsk/db1/dbs352
> > 27738d408 631 98 5 1000000 999997
> > PO-B- /dev/vx/rdsk/db1/dbs475
> > 277393408 639 98 5 1000000 999997
> > PO-B- /dev/vx/rdsk/db1/dbs506
> >
> > Onconfig:
> > DBSPACETEMP tempdbs:tempdbs2> >
> > Today one session allocated all the space of tempdbs. No space of
> > tempdbs2 was used. The session seemed to be blocked.
> >
> > Now we had two problems I do not understand:
> >
> > Why didn't the session allocate space from tempdbs2?
> >
> > The greater problem was: All other sessions which needed exclusive temp
> > dbspace received an error:
> > SQL statement error number -229
> > Could not open or create a temporary file
> > SYSTEM error number -131
> > ISAM error: no free disk space> >
> > Why couldn't the other session allocate exclusive temp dbspace from
> > tempdbs2 which was definitely free?
> >
> > Reinhard.
>
> What is DBSPACETEMP set to in either the environment of the client
> process or the servers onconfig file.
There is nothing set in the environment. onconfig file: DBSPACETEMP tempdbs:tempdbs2
On Jan 26, 12:43 am, "Habichtsberg, Reinhard" <RHabichtsb...@arz-
emmendingen.de> wrote:
> > -----Original Message-----
> > From: informix-list-boun...@iiug.org [mailto:informix-list-boun...@iiug.org] On
> > Behalf Of da...@smooth1.co.uk
> > Sent: Tuesday, January 25, 2011 8:42 PM
> > To: informix-l...@iiug.org
> > Subject: Re: Could not open or create a temporary file
>
> > On Jan 25, 12:01 am, "Habichtsberg, Reinhard" <RHabichtsb...@arz-
> > emmendingen.de> wrote:
> > > Hi all
>
> > > Version of IDS 11.50.FC7W3
>
> > > We have configured two exclusive temp dbspaces (nothing new, they exist
> > > for years).
>
> > > Onstat -d:
> > > 277279488 4 0x42001 4 10 2048 N TB
> > > informix tempdbs
> > > 27728a358 98 0x42001 480 9 2048 N TB
> > > informix tempdbs2
>
> > > 277294028 4 4 5 1000000 999889
> > > PO-B- /dev/vx/rdsk/db1/dbs5
> > > 277294218 5 4 5 1000000 999997
> > > PO-B- /dev/vx/rdsk/db1/dbs6
> > > 2772a9028 148 4 5 1000000 999997
> > > PO-B- /dev/vx/rdsk/db1/dbs149
> > > 27734f7e8 497 4 5 1000000 999997
> > > PO-B- /dev/vx/rdsk/db1/dbs500
> > > 277374db8 548 4 5 1000000 999997
> > > PO-B- /dev/vx/rdsk/db1/dbs511
> > > 277376028 549 4 5 1000000 999997
> > > PO-B- /dev/vx/rdsk/db1/dbs513
> > > 277376218 550 4 5 1000000 999997
> > > PO-B- /dev/vx/rdsk/db1/dbs523
> > > 2773765f8 552 4 5 1000000 999997
> > > PO-B- /dev/vx/rdsk/db1/dbs521
> > > 27738b7e8 625 4 5 1000000 999997
> > > PO-B- /dev/vx/rdsk/db1/dbs474
> > > 27738d5f8 632 4 5 1000000 999997
> > > PO-B- /dev/vx/rdsk/db1/dbs476
>
> > > 2773465f8 464 98 5 1000000 999997
> > > PO-B- /dev/vx/rdsk/db1/dbs477
> > > 27734a5f8 480 98 5 1000000 753941
> > > PO-B- /dev/vx/rdsk/db1/dbs481
> > > 27734a7e8 481 98 5 1000000 999997
> > > PO-B- /dev/vx/rdsk/db1/dbs482
> > > 27734fdb8 500 98 5 1000000 999997
> > > PO-B- /dev/vx/rdsk/db1/dbs501
> > > 277378218 558 98 5 1000000 999997
> > > PO-B- /dev/vx/rdsk/db1/dbs526
> > > 277378408 559 98 5 1000000 999997
> > > PO-B- /dev/vx/rdsk/db1/dbs562
> > > 277366bc8 611 98 5 1000000 999997
> > > PO-B- /dev/vx/rdsk/db1/dbs352
> > > 27738d408 631 98 5 1000000 999997
> > > PO-B- /dev/vx/rdsk/db1/dbs475
> > > 277393408 639 98 5 1000000 999997
> > > PO-B- /dev/vx/rdsk/db1/dbs506
>
> > > Onconfig:
> > > DBSPACETEMP tempdbs:tempdbs2>
> > > Today one session allocated all the space of tempdbs. No space of
> > > tempdbs2 was used. The session seemed to be blocked.
>
> > > Now we had two problems I do not understand:
>
> > > Why didn't the session allocate space from tempdbs2?
>
> > > The greater problem was: All other sessions which needed exclusive temp
> > > dbspace received an error:
> > > SQL statement error number -229
> > > Could not open or create a temporary file
> > > SYSTEM error number -131
> > > ISAM error: no free disk space>
> > > Why couldn't the other session allocate exclusive temp dbspace from
> > > tempdbs2 which was definitely free?
>
> > > Reinhard.
>
> > What is DBSPACETEMP set to in either the environment of the client
> > process or the servers onconfig file.
>
> There is nothing set in the environment. onconfig file: DBSPACETEMP tempdbs:tempdbs2
What type sql command are the sessions executing, why do they need
temp space
(crea temp table with/without in clause, order by ,group,union,union
all,something else?).
Am 26.01.2011 21:15, schrieb david@smooth1.co.uk:
> On Jan 26, 12:43 am, "Habichtsberg, Reinhard" <RHabichtsb...@arz-
> emmendingen.de> wrote:
>>> -----Original Message-----
>>> From: informix-list-boun...@iiug.org [mailto:informix-list-boun...@iiug.org] On
>>> Behalf Of da...@smooth1.co.uk
>>> Sent: Tuesday, January 25, 2011 8:42 PM
>>> To: informix-l...@iiug.org
>>> Subject: Re: Could not open or create a temporary file
>>
>>> On Jan 25, 12:01 am, "Habichtsberg, Reinhard" <RHabichtsb...@arz-
>>> emmendingen.de> wrote:
>>>> Hi all
>>
>>>> Version of IDS 11.50.FC7W3
>>
>>>> We have configured two exclusive temp dbspaces (nothing new, they exist
>>>> for years).
>>
>>>> Onstat -d:
>>>> 277279488 4 0x42001 4 10 2048 N TB
>>>> informix tempdbs
>>>> 27728a358 98 0x42001 480 9 2048 N TB
>>>> informix tempdbs2
>>
>>>> 277294028 4 4 5 1000000 999889
>>>> PO-B- /dev/vx/rdsk/db1/dbs5
>>>> 277294218 5 4 5 1000000 999997
>>>> PO-B- /dev/vx/rdsk/db1/dbs6
>>>> 2772a9028 148 4 5 1000000 999997
>>>> PO-B- /dev/vx/rdsk/db1/dbs149
>>>> 27734f7e8 497 4 5 1000000 999997
>>>> PO-B- /dev/vx/rdsk/db1/dbs500
>>>> 277374db8 548 4 5 1000000 999997
>>>> PO-B- /dev/vx/rdsk/db1/dbs511
>>>> 277376028 549 4 5 1000000 999997
>>>> PO-B- /dev/vx/rdsk/db1/dbs513
>>>> 277376218 550 4 5 1000000 999997
>>>> PO-B- /dev/vx/rdsk/db1/dbs523
>>>> 2773765f8 552 4 5 1000000 999997
>>>> PO-B- /dev/vx/rdsk/db1/dbs521
>>>> 27738b7e8 625 4 5 1000000 999997
>>>> PO-B- /dev/vx/rdsk/db1/dbs474
>>>> 27738d5f8 632 4 5 1000000 999997
>>>> PO-B- /dev/vx/rdsk/db1/dbs476
>>
>>>> 2773465f8 464 98 5 1000000 999997
>>>> PO-B- /dev/vx/rdsk/db1/dbs477
>>>> 27734a5f8 480 98 5 1000000 753941
>>>> PO-B- /dev/vx/rdsk/db1/dbs481
>>>> 27734a7e8 481 98 5 1000000 999997
>>>> PO-B- /dev/vx/rdsk/db1/dbs482
>>>> 27734fdb8 500 98 5 1000000 999997
>>>> PO-B- /dev/vx/rdsk/db1/dbs501
>>>> 277378218 558 98 5 1000000 999997
>>>> PO-B- /dev/vx/rdsk/db1/dbs526
>>>> 277378408 559 98 5 1000000 999997
>>>> PO-B- /dev/vx/rdsk/db1/dbs562
>>>> 277366bc8 611 98 5 1000000 999997
>>>> PO-B- /dev/vx/rdsk/db1/dbs352
>>>> 27738d408 631 98 5 1000000 999997
>>>> PO-B- /dev/vx/rdsk/db1/dbs475
>>>> 277393408 639 98 5 1000000 999997
>>>> PO-B- /dev/vx/rdsk/db1/dbs506
>>
>>>> Onconfig:
>>>> DBSPACETEMP tempdbs:tempdbs2>>
>>>> Today one session allocated all the space of tempdbs. No space of
>>>> tempdbs2 was used. The session seemed to be blocked.
>>
>>>> Now we had two problems I do not understand:
>>
>>>> Why didn't the session allocate space from tempdbs2?
>>
>>>> The greater problem was: All other sessions which needed exclusive temp
>>>> dbspace received an error:
>>>> SQL statement error number -229
>>>> Could not open or create a temporary file
>>>> SYSTEM error number -131
>>>> ISAM error: no free disk space>>
>>>> Why couldn't the other session allocate exclusive temp dbspace from
>>>> tempdbs2 which was definitely free?
>>
>>>> Reinhard.
>>
>>> What is DBSPACETEMP set to in either the environment of the client
>>> process or the servers onconfig file.
>>
>> There is nothing set in the environment. onconfig file: DBSPACETEMP tempdbs:tempdbs2
>
> What type sql command are the sessions executing, why do they need
> temp space
> (crea temp table with/without in clause, order by ,group,union,union
> all,something else?).
The blocking query which filled tempsdbs implicitedly made use of
exclusive temp dbspace in behalf of distinct clause.
One of the sessions that received the error 229/131 made use of:
select ... order by 5 DESC, 1 into temp with no log. Hmmm, that's
explicit and implicit use of excl. temps.
> >>>> Version of IDS 11.50.FC7W3
> >>
> >>>> We have configured two exclusive temp dbspaces (nothing new, they
exist
> >>>> for years).
> >>>> Onconfig:
> >>>> DBSPACETEMP tempdbs:tempdbs2> >>
> >>>> Today one session allocated all the space of tempdbs. No space of
> >>>> tempdbs2 was used. The session seemed to be blocked.
> >>
> >>>> Now we had two problems I do not understand:
> >>
> >>>> Why didn't the session allocate space from tempdbs2?
> >>
> >>>> The greater problem was: All other sessions which needed
exclusive temp
> >>>> dbspace received an error:
> >>>> SQL statement error number -229
> >>>> Could not open or create a temporary file
> >>>> SYSTEM error number -131
> >>>> ISAM error: no free disk space> >>
> >>>> Why couldn't the other session allocate exclusive temp dbspace
from
> >>>> tempdbs2 which was definitely free?
> >>
> >>>> Reinhard.
> >>
> >>> What is DBSPACETEMP set to in either the environment of the client
> >>> process or the servers onconfig file.
> >>
> >> There is nothing set in the environment. onconfig file:
DBSPACETEMP> tempdbs:tempdbs2
> >
> > What type sql command are the sessions executing, why do they need
> > temp space
> > (crea temp table with/without in clause, order by ,group,union,union
> > all,something else?).
>
> The blocking query which filled tempsdbs implicitedly made use of
> exclusive temp dbspace in behalf of distinct clause.
>
> One of the sessions that received the error 229/131 made use of:
> select ... order by 5 DESC, 1 into temp with no log. Hmmm, that's
> explicit and implicit use of excl. temps.
>
No more ideas with my blocked exclusiv temp dbspaces? Could it be a bug?
On Jan 26, 9:30 pm, Wander_Reiter <wander_rei...@yahoo.de> wrote:
> Am 26.01.2011 21:15, schrieb da...@smooth1.co.uk:
>
>
>
> > On Jan 26, 12:43 am, "Habichtsberg, Reinhard" <RHabichtsb...@arz-
> > emmendingen.de> wrote:
> >>> -----Original Message-----
> >>> From: informix-list-boun...@iiug.org [mailto:informix-list-boun...@iiug.org] On
> >>> Behalf Of da...@smooth1.co.uk
> >>> Sent: Tuesday, January 25, 2011 8:42 PM
> >>> To: informix-l...@iiug.org
> >>> Subject: Re: Could not open or create a temporary file
>
> >>> On Jan 25, 12:01 am, "Habichtsberg, Reinhard" <RHabichtsb...@arz-
> >>> emmendingen.de> wrote:
> >>>> Hi all
>
> >>>> Version of IDS 11.50.FC7W3
>
> >>>> We have configured two exclusive temp dbspaces (nothing new, they exist
> >>>> for years).
>
> >>>> Onstat -d:
> >>>> 277279488 4 0x42001 4 10 2048 N TB
> >>>> informix tempdbs
> >>>> 27728a358 98 0x42001 480 9 2048 N TB
> >>>> informix tempdbs2
>
> >>>> 277294028 4 4 5 1000000 999889
> >>>> PO-B- /dev/vx/rdsk/db1/dbs5
> >>>> 277294218 5 4 5 1000000 999997
> >>>> PO-B- /dev/vx/rdsk/db1/dbs6
> >>>> 2772a9028 148 4 5 1000000 999997
> >>>> PO-B- /dev/vx/rdsk/db1/dbs149
> >>>> 27734f7e8 497 4 5 1000000 999997
> >>>> PO-B- /dev/vx/rdsk/db1/dbs500
> >>>> 277374db8 548 4 5 1000000 999997
> >>>> PO-B- /dev/vx/rdsk/db1/dbs511
> >>>> 277376028 549 4 5 1000000 999997
> >>>> PO-B- /dev/vx/rdsk/db1/dbs513
> >>>> 277376218 550 4 5 1000000 999997
> >>>> PO-B- /dev/vx/rdsk/db1/dbs523
> >>>> 2773765f8 552 4 5 1000000 999997
> >>>> PO-B- /dev/vx/rdsk/db1/dbs521
> >>>> 27738b7e8 625 4 5 1000000 999997
> >>>> PO-B- /dev/vx/rdsk/db1/dbs474
> >>>> 27738d5f8 632 4 5 1000000 999997
> >>>> PO-B- /dev/vx/rdsk/db1/dbs476
>
> >>>> 2773465f8 464 98 5 1000000 999997
> >>>> PO-B- /dev/vx/rdsk/db1/dbs477
> >>>> 27734a5f8 480 98 5 1000000 753941
> >>>> PO-B- /dev/vx/rdsk/db1/dbs481
> >>>> 27734a7e8 481 98 5 1000000 999997
> >>>> PO-B- /dev/vx/rdsk/db1/dbs482
> >>>> 27734fdb8 500 98 5 1000000 999997
> >>>> PO-B- /dev/vx/rdsk/db1/dbs501
> >>>> 277378218 558 98 5 1000000 999997
> >>>> PO-B- /dev/vx/rdsk/db1/dbs526
> >>>> 277378408 559 98 5 1000000 999997
> >>>> PO-B- /dev/vx/rdsk/db1/dbs562
> >>>> 277366bc8 611 98 5 1000000 999997
> >>>> PO-B- /dev/vx/rdsk/db1/dbs352
> >>>> 27738d408 631 98 5 1000000 999997
> >>>> PO-B- /dev/vx/rdsk/db1/dbs475
> >>>> 277393408 639 98 5 1000000 999997
> >>>> PO-B- /dev/vx/rdsk/db1/dbs506
>
> >>>> Onconfig:
> >>>> DBSPACETEMP tempdbs:tempdbs2>
> >>>> Today one session allocated all the space of tempdbs. No space of
> >>>> tempdbs2 was used. The session seemed to be blocked.
>
> >>>> Now we had two problems I do not understand:
>
> >>>> Why didn't the session allocate space from tempdbs2?
>
> >>>> The greater problem was: All other sessions which needed exclusive temp
> >>>> dbspace received an error:
> >>>> SQL statement error number -229
> >>>> Could not open or create a temporary file
> >>>> SYSTEM error number -131
> >>>> ISAM error: no free disk space>
> >>>> Why couldn't the other session allocate exclusive temp dbspace from
> >>>> tempdbs2 which was definitely free?
>
> >>>> Reinhard.
>
> >>> What is DBSPACETEMP set to in either the environment of the client
> >>> process or the servers onconfig file.
>
> >> There is nothing set in the environment. onconfig file: DBSPACETEMP tempdbs:tempdbs2
>
> > What type sql command are the sessions executing, why do they need
> > temp space
> > (crea temp table with/without in clause, order by ,group,union,union
> > all,something else?).
>
> The blocking query which filled tempsdbs implicitedly made use of
> exclusive temp dbspace in behalf of distinct clause.
>
> One of the sessions that received the error 229/131 made use of:
> select ... order by 5 DESC, 1 into temp with no log. Hmmm, that's
> explicit and implicit use of excl. temps.
What does oncheck -pe give whilst the 'bad' query is running?
create temp table with in clause could unbalance the usage of thedbspaces.