need to get sorts to use temp dbspaces

We are having sort files being created in the OS, when doing large order by's. According to the performance manual, if DBSPACETEMP is created correctly, these implicit sort files should be created in the temp dbspaces, unless PSORT_DBTEMP is set. I noticed one thing in the performance manual where it says that the temp dbspaces in the list for DBSPACETEMP must be made of chunks that are allocated as "unbuffered" devices. I have never heard that term when referring to devices. Do they just mean "raw" devices ? What does it mean ? Any other ideas why we can't get the sorts to go to the dbspaces listed in DBSPACETEMP ?We have tried creating an index on the ordered by column, our tempdbspaces are plenty large enough for the query. Thanks, floyd ======================== -<<Floyd Wellershaus>>- Database Administrator Unix Administrator email: fwellers@yahoo.com Work: 703-733-4126 Pager: 703-705-9241 Email Pager: 7037059241@my2way.com Home: 703-430-0805 Cell: 703-477-6045 ========================

Which version of Informix are you using? A chunk is a physical disk space, entire disk, part of a disk or cooked file. Regards Diane Lavoie -----Original Message----- From: Floyd Welle.... [mailto:fwellers@yahoo.com] Sent: Tuesday, February 08, 2005 3:11 PM To: ids@iiug.org Subject: need to get sorts to use temp dbspaces [4206] We are having sort files being created in the OS, when doing large order by's. According to the performance manual, if DBSPACETEMP is created correctly, these implicit sort files should be created in the temp dbspaces, unless PSORT_DBTEMP is set. I noticed one thing in the performance manual where it says that the temp dbspaces in the list for DBSPACETEMP must be made of chunks that are allocated as "unbuffered" devices. I have never heard that term when referring to devices. Do they just mean "raw" devices ? What does it mean ? Any other ideas why we can't get the sorts to go to the dbspaces listed in DBSPACETEMP ?We have tried creating an index on the ordered by column, our tempdbspaces are plenty large enough for the query. Thanks, floyd ======================== -<<Floyd Wellershaus>>- Database Administrator Unix Administrator email: fwellers@yahoo.com Work: 703-733-4126 Pager: 703-705-9241 Email Pager: 7037059241@my2way.com Home: 703-430-0805 Cell: 703-477-6045 ========================

Hi, I got the same behavior this morning while upgrading to 9.30FC4XK. 15 files 'root_needed.tmp_xxxx were created in the $INFORMIXDIR while I was doing the upgrade. I never saw that in the past using 7.31, usually when all temp space defined in the onconfig file by DBSPACETEMP is used, the job aborted with an error 'no disk space for tmp available' and while monitoring the temp space I can see that it reached 100%, but never saw any files created on the server for temp. I read some notes saying that PSORT_DBTEMP should be defined with a directory with sufficient space. This space on disk will be used if there is unsufficient space in DBSPACETEMP. But on my systems, PSORT_DBTEMP is not defined neither in 9.30 or 7.31 systems. Notes are also saying that the DBSPACETEMP should be the same size as the logdbs to avoid creation of files on the server. Still looking for information to understand the used of temp. Let me know if you find anything else. Regards Diane Lavoie -----Original Message----- From: Lavoie, Diane Sent: Wednesday, February 09, 2005 11:14 AM To: 'Floyd Welle....'; ids@iiug.org Subject: RE: need to get sorts to use temp dbspaces [4206] Which version of Informix are you using? A chunk is a physical disk space, entire disk, part of a disk or cooked file. Regards Diane Lavoie -----Original Message----- From: Floyd Welle.... [mailto:fwellers@yahoo.com] Sent: Tuesday, February 08, 2005 3:11 PM To: ids@iiug.org Subject: need to get sorts to use temp dbspaces [4206] We are having sort files being created in the OS, when doing large order by's. According to the performance manual, if DBSPACETEMP is created correctly, these implicit sort files should be created in the temp dbspaces, unless PSORT_DBTEMP is set. I noticed one thing in the performance manual where it says that the temp dbspaces in the list for DBSPACETEMP must be made of chunks that are allocated as "unbuffered" devices. I have never heard that term when referring to devices. Do they just mean "raw" devices ? What does it mean ? Any other ideas why we can't get the sorts to go to the dbspaces listed in DBSPACETEMP ?We have tried creating an index on the ordered by column, our tempdbspaces are plenty large enough for the query. Thanks, floyd ======================== -<<Floyd Wellershaus>>- Database Administrator Unix Administrator email: fwellers@yahoo.com Work: 703-733-4126 Pager: 703-705-9241 Email Pager: 7037059241@my2way.com Home: 703-430-0805 Cell: 703-477-6045 ========================

>>>Notes are also saying that the DBSPACETEMP should be the same size as the logdbs to avoid creation of files on the server. <<<< Can you show me where that is ? I haven't run accross that requirement yet. Thanks. Floyd "Lavoie, Diane" <diane.lavoie@domtar.com> wrote: Hi, I got the same behavior this morning while upgrading to 9.30FC4XK. 15 files 'root_needed.tmp_xxxx were created in the $INFORMIXDIR while I was doing the upgrade. I never saw that in the past using 7.31, usually when all temp space defined in the onconfig file by DBSPACETEMP is used, the job aborted with an error 'no disk space for tmp available' and while monitoring the temp space I can see that it reached 100%, but never saw any files created on the server for temp. I read some notes saying that PSORT_DBTEMP should be defined with a directory with sufficient space. This space on disk will be used if there is unsufficient space in DBSPACETEMP. But on my systems, PSORT_DBTEMP is not defined neither in 9.30 or 7.31 systems. Notes are also saying that the DBSPACETEMP should be the same size as the logdbs to avoid creation of files on the server. Still looking for information to understand the used of temp. Let me know if you find anything else. Regards Diane Lavoie -----Original Message----- From: Lavoie, Diane Sent: Wednesday, February 09, 2005 11:14 AM To: 'Floyd Welle....'; ids@iiug.org Subject: RE: need to get sorts to use temp dbspaces [4206] Which version of Informix are you using? A chunk is a physical disk space, entire disk, part of a disk or cooked file. Regards Diane Lavoie -----Original Message----- From: Floyd Welle.... [mailto:fwellers@yahoo.com] Sent: Tuesday, February 08, 2005 3:11 PM To: ids@iiug.org Subject: need to get sorts to use temp dbspaces [4206] We are having sort files being created in the OS, when doing large order by's. According to the performance manual, if DBSPACETEMP is created correctly, these implicit sort files should be created in the temp dbspaces, unless PSORT_DBTEMP is set. I noticed one thing in the performance manual where it says that the temp dbspaces in the list for DBSPACETEMP must be made of chunks that are allocated as "unbuffered" devices. I have never heard that term when referring to devices. Do they just mean "raw" devices ? What does it mean ? Any other ideas why we can't get the sorts to go to the dbspaces listed in DBSPACETEMP ?We have tried creating an index on the ordered by column, our tempdbspaces are plenty large enough for the query. Thanks, floyd ======================== -<>- Database Administrator Unix Administrator email: fwellers@yahoo.com Work: 703-733-4126 Pager: 703-705-9241 Email Pager: 7037059241@my2way.com Home: 703-430-0805 Cell: 703-477-6045 ======================== ======================== -<<Floyd Wellershaus>>- Database Administrator Unix Administrator email: fwellers@yahoo.com Work: 703-733-4126 Pager: 703-705-9241 Email Pager: 7037059241@my2way.com Home: 703-430-0805 Cell: 703-477-6045 ========================