Could not open or create a temporary file
Habichtsberg, Reinhard
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.
da...@smooth1.co.uk
emmendingen.de> wrote:
> Hi all
>
> Version of IDS 11.50.FC7W3
>
> We have configured two exclusive temp dbspaces (nothing new, they exist
> for years).
>
What is DBSPACETEMP set to in either the environment of the client
process or the servers onconfig file.
Habichtsberg, Reinhard
There is nothing set in the environment. onconfig file: DBSPACETEMP tempdbs:tempdbs2
da...@smooth1.co.uk
emmendingen.de> wrote:
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?).
Wander_Reiter
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.
Habichtsberg, Reinhard
> >>
> >>>> We have configured two exclusive temp dbspaces (nothing new, they
exist
> >>>> for years).
Habichtsberg, Reinhard
indeed it has all been provided ;-):
Version of IDS 11.50.FC7W3
Thank you, Reinhard.
> -----Original Message-----
> From: Mark Jamison [mailto:maj...@mac.com]
> Sent: Friday, January 28, 2011 4:02 PM
> To: Habichtsberg, Reinhard
> Cc: inform...@iiug.org
> Subject: Re: Could not open or create a temporary file
>
> So a couple of questions , one what version are you using? Two,
could you show
> me the onstat -d output for these spaces? Sorry of this has already
been provided.
>
>
>
> Sent from my iPhone
>
> On Jan 28, 2011, at 1:31 AM, "Habichtsberg, Reinhard"
<RHabichtsberg@arz-
> emmendingen.de> wrote:
>
> >>>>>> Version of IDS 11.50.FC7W3
> >>>>
> >>>>>> We have configured two exclusive temp dbspaces (nothing new,
they
> > exist
> >>>>>> for years).
> > No more ideas with my blocked exclusiv temp dbspaces? Could it be a
bug?
> >
> >
> > _______________________________________________
> > Informix-list mailing list
> > Inform...@iiug.org
> > http://www.iiug.org/mailman/listinfo/informix-list
Superboer
not much sorry, however what does:
dbaccess sysmaster <<!
select * from sysconfig
where cf_name matches "DBSPACETEMP*"
!
returns???
also do you get any i/o on your tempdbs2??
eq what does onstat -D show...??
Superboer.
On 30 jan, 10:13, "Habichtsberg, Reinhard" <RHabichtsb...@arz-
> > > Informix-l...@iiug.org
> > >http://www.iiug.org/mailman/listinfo/informix-list
Habichtsberg, Reinhard
thank you for asking, I try to provide the missing informations:
* from sysconfig:
cf_id 88
cf_name DBSPACETEMP
cf_flags 0
cf_original tempdbs:tempdbs2
cf_effective tempdbs:tempdbs2
cf_default
onstat -D
277276488 4 0x42001 4 10 2048 N TB informix tempdbs
277285358 98 0x42001 480 9 2048 N TB informix tempdbs2
page Rd page Wr
27728f218 4 4 5 83147 237371 /dev/vx/rdsk/db1/dbs5 (tempdbs)
27737d7e8 480 98 5 116795 247380 /dev/vx/rdsk/db1/dbs481 (tempdbs2)
I have only listed the chunks with read and writes. There are some more chunks all with page Rd 1 page Wr 4.
We see: Both spaces or used.
onstat -g iof:
gfd pathname bytes read page reads bytes write page writes io/s
7 dbs5 173002752 84474 839948288 410099 200.7
op type count avg. time
seeks 0 N/A
reads 0 N/A
writes 0 N/A
kaio_reads 14910 0.0021
kaio_writes 69194 0.0056
482 dbs481 239353856 116872 595398656 290722 211.1
op type count avg. time
seeks 0 N/A
reads 0 N/A
writes 0 N/A
kaio_reads 15950 0.0019
kaio_writes 51011 0.0056
Does these informations help?
TIA, Reinhard.
> -----Original Message-----
> From: informix-l...@iiug.org [mailto:informix-l...@iiug.org] On
Jonn...@usenet-news.net
1. Post the sqexplain :
You can do "set explain on avoid execute".
Sounds like a single contiguous extent was needed somewhere.
2. Was a backup running at the time???
i.e. Level 0 archives will need a "before images" extent.
3. As a slight tangent :
Check the output from onstat -g iof and see if your 2 temp dbspaces are
getting a lot more activity than the others when comparing I/Os per
second. The selective output for the two temp dbspaces showed 200 I/Os
per second, so easy performance improvement may be there just by adding
another 2 temporary dbspaces.
da...@smooth1.co.uk
What does oncheck -pe give whilst the 'bad' query is running?
create temp table with in clause could unbalance the usage of the
dbspaces.
Superboer
Yes it helps; we can definitly conclude that your temp spaces are
both used.
i did a quick test on my test box fedora RH 13 engine 11.50.
i have a bigger tempdbs then tempdbs2.(only one chunk per
tempspace.)
i filled up tempdbs2 and then started selecting stuff into temp -->>
that was stored in tempdbs.
no error so that sounds good.
i really need to try filling up tempdbs which is also listed first in
my engine.
when time allows i'll give that a go.
meanwhile i think you should contat support, this sounds like a bug;
manual is also not really clear about this. it says when a temp table
is created, it will
be created round robin... if one space is full the create table should
get an error...???
however my above test completed successfully???
also when i cleared tempdbs2 and reran the select... into temp .. with
no log
it did store stuff in both temp spaces???
Unless i had something undefined in my eyes....
Superboer.
On 31 jan, 12:20, "Habichtsberg, Reinhard" <RHabichtsb...@arz-
> > Informix-l...@iiug.org
> >http://www.iiug.org/mailman/listinfo/informix-list
Superboer
i just reran this reversed; the first tempspace listed was filled up;
other sessions selecting into temp with no log simply used the free
space in the other tempdbspace....
so i guess you look at a bug???; 11.7 behaves different then 11.5 .
Superboer.
Habichtsberg, Reinhard
thank you for your help (and all others too).
I have difficulties to reproduce the situation. I'll follow your suggestion and open a call.
Kind regards,
Reinhard.