 | |
Could not open or create a temporary file
comp.databases.informix comp.databases.informix
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Could not open or create a temporary file -
01-25-2011
, 02:01 AM
#2
| |||
| |||
|
|
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. |
process or the servers onconfig file.
#3
| |||
| |||
|
|
-----Original Message----- From: informix-list-bounces (AT) iiug (DOT) org [mailto:informix-list-bounces (AT) iiug (DOT) org] On Behalf Of david (AT) smooth1 (DOT) co.uk Sent: Tuesday, January 25, 2011 8:42 PM To: informix-list (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. |
#4
| |||
| |||
|
|
-----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: DBSPACETEMP tempdbs:tempdbs2 |
temp space
(crea temp table with/without in clause, order by ,group,union,union
all,something else?).
#5
| |||
| |||
|
|
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: 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?). |
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.
#6
| |||
| |||
|
|
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? |
#7
| |||
| |||
|
|
-----Original Message----- From: Mark Jamison [mailto:majp51 (AT) mac (DOT) com] Sent: Friday, January 28, 2011 4:02 PM To: Habichtsberg, Reinhard Cc: informix-list (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-list (AT) iiug (DOT) org http://www.iiug.org/mailman/listinfo/informix-list |
#8
| |||
| |||
|
|
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 |
#9
| |||
| |||
|
|
-----Original Message----- From: informix-list-bounces (AT) iiug (DOT) org [mailto:informix-list-bounces (AT) iiug (DOT) org] On Behalf Of Superboer Sent: Monday, January 31, 2011 9:20 AM To: informix-list (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-list (AT) iiug (DOT) org http://www.iiug.org/mailman/listinfo/informix-list |
#10
| |||
| |||
|
|
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-bounces (AT) iiug (DOT) org [mailto:informix-list-bounces (AT) iiug (DOT) org] On Behalf Of Superboer Sent: Monday, January 31, 2011 9:20 AM To: informix-list (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-list (AT) iiug (DOT) org http://www.iiug.org/mailman/listinfo/informix-list |
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.
|
« 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.