 | |
Could not open or create a temporary file
comp.databases.informix comp.databases.informix
 |
| | Thread Tools | Display Modes |
#11
 
| |||
| |||
|
Re: Could not open or create a temporary file -
01-31-2011
, 05:26 PM
|
Am 26.01.2011 21:15, schrieb da... (AT) smooth1 (DOT) co.uk: On Jan 26, 12:43 am, "Habichtsberg, Reinhard" <RHabichtsb...@arz- emmendingen.de> wrote: -----Original Message----- From: informix-list-boun... (AT) iiug (DOT) org [mailto:informix-list-boun... (AT) iiug (DOT) org] On Behalf Of da... (AT) smooth1 (DOT) co.uk Sent: Tuesday, January 25, 2011 8:42 PM To: informix-l... (AT) iiug (DOT) 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: DBSPACETEMPtempdbs: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. |
create temp table with in clause could unbalance the usage of the
dbspaces.
#12
| |||
| |||
|
|
Superboer, 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-list-boun... (AT) iiug (DOT) org [mailto:informix-list-boun... (AT) iiug (DOT) .org] On Behalf Of Superboer Sent: Monday, January 31, 2011 9:20 AM To: informix-l... (AT) iiug (DOT) org Subject: Re: Could not open or create a temporary file Hello Reinhard, 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- emmendingen.de> wrote: Mark, indeed it has all been provided ;-): Version of IDS 11.50.FC7W3 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 Thank you, Reinhard. -----Original Message----- From: Mark Jamison [mailto:maj... (AT) mac (DOT) com] Sent: Friday, January 28, 2011 4:02 PM To: Habichtsberg, Reinhard Cc: informix-l... (AT) iiug (DOT) 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). 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 bea bug? _______________________________________________ Informix-list mailing list Informix-l... (AT) iiug (DOT) org http://www.iiug.org/mailman/listinfo/informix-list _______________________________________________ Informix-list mailing list Informix-l... (AT) iiug (DOT) org http://www.iiug.org/mailman/listinfo/informix-list |
#13
| |||
| |||
|
|
Hello Reinhard, 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- emmendingen.de> wrote: Superboer, 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 morechunks 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-list-boun... (AT) iiug (DOT) org [mailto:informix-list-boun... (AT) iiug (DOT) org] On Behalf Of Superboer Sent: Monday, January 31, 2011 9:20 AM To: informix-l... (AT) iiug (DOT) org Subject: Re: Could not open or create a temporary file Hello Reinhard, 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- emmendingen.de> wrote: Mark, indeed it has all been provided ;-): Version of IDS 11.50.FC7W3 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 Thank you, Reinhard. -----Original Message----- From: Mark Jamison [mailto:maj... (AT) mac (DOT) com] Sent: Friday, January 28, 2011 4:02 PM To: Habichtsberg, Reinhard Cc: informix-l... (AT) iiug (DOT) 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). 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 theyneed 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 useof 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? _______________________________________________ Informix-list mailing list Informix-l... (AT) iiug (DOT) org http://www.iiug.org/mailman/listinfo/informix-list _______________________________________________ Informix-list mailing list Informix-l... (AT) iiug (DOT) org http://www.iiug.org/mailman/listinfo/informix-list |
#14
| |||
| |||
|
|
-----Original Message----- From: informix-list-bounces (AT) iiug (DOT) org [mailto:informix-list-bounces (AT) iiug (DOT) org] On Behalf Of Superboer Sent: Thursday, February 03, 2011 9:42 AM To: informix-list (AT) iiug (DOT) org Subject: Re: Could not open or create a temporary file Hello Reinhard, 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. On 1 feb, 09:22, Superboer <superbo... (AT) t-online (DOT) de> wrote: Hello Reinhard, 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- emmendingen.de> wrote: Superboer, 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-list-boun... (AT) iiug (DOT) org [mailto:informix-list-boun... (AT) iiug (DOT) org] On Behalf Of Superboer Sent: Monday, January 31, 2011 9:20 AM To: informix-l... (AT) iiug (DOT) org Subject: Re: Could not open or create a temporary file Hello Reinhard, 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- emmendingen.de> wrote: Mark, indeed it has all been provided ;-): Version of IDS 11.50.FC7W3 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 Thank you, Reinhard. -----Original Message----- From: Mark Jamison [mailto:maj... (AT) mac (DOT) com] Sent: Friday, January 28, 2011 4:02 PM To: Habichtsberg, Reinhard Cc: informix-l... (AT) iiug (DOT) 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). 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? _______________________________________________ Informix-list mailing list Informix-l... (AT) iiug (DOT) org http://www.iiug.org/mailman/listinfo/informix-list _______________________________________________ Informix-list mailing list Informix-l... (AT) iiug (DOT) org http://www.iiug.org/mailman/listinfo/informix-list _______________________________________________ Informix-list mailing list Informix-list (AT) iiug (DOT) org http://www.iiug.org/mailman/listinfo/informix-list |
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.