temp table environment variables

We are testing an application. This is an esqlc app. When running, it uses some of our temp dbspaces ( I assume during the sort for the order by in a select sql ), and also creates sort files in /tmp. This is normal behaviour, the way I understand it. I have 3 temp dbspaces in the onconfig for DBSPACETEMP. The parameter is setup like this : DBSPACETEMP tmpdbs1:tmpdbs2:tmpdbs3. Our developer doesn't believe me the way it works, and wanted to set up the DBSPACETEMP as an evironment variable.I told her to set it up the exact same way as in the onconfig. When we do the 'env' command, it looks like this : DBSPACETEMP tmpdbs1:tmpdbs2:tmpdbs3. That seems right to me, but when testing the app yesterday, there was no usage of the temp dbspaces, and also, no sort files in /tmp. Is there any possible explanation for this behaviour ? 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 ========================

Smaller sorts are performed in memory. You would have to make a test result set that is big enough to warrant the creation of sort-work files. Art S. Kagel ----- Original Message ----- From: Floyd Welle.... <fwellers@yahoo.com> At: 2/ 3 6:54 > We are testing an application. This is an esqlc app. When running, it uses some > of our temp dbspaces ( I assume during the sort for the order by in a select sql > ), and also creates sort files in /tmp. > > This is normal behaviour, the way I understand it. I have 3 temp dbspaces in the > onconfig for DBSPACETEMP. The parameter is setup like this : DBSPACETEMP > tmpdbs1:tmpdbs2:tmpdbs3. > > Our developer doesn't believe me the way it works, and wanted to set up the > DBSPACETEMP as an evironment variable.> I told her to set it up the exact same way as in the onconfig. When we do the > 'env' command, it looks like this : DBSPACETEMP tmpdbs1:tmpdbs2:tmpdbs3. > > That seems right to me, but when testing the app yesterday, there was no usage > of the temp dbspaces, and also, no sort files in /tmp. > > Is there any possible explanation for this behaviour ? > > 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 > ========================

thanks. I think I figured it out. Doing another test now. It seems that somebody set the PSORT_DBTEMP variable in the enviroment, thus moving the location of the sort files to a place I wasn't looking. The assumption is that the sort files will go to /tmp by default, which is where I was looking for them. Thanks, Floyd Martin Fuerderer <MARTINFU@de.ibm.com> wrote: Any other changes happened since ? E.g. have indexes been created that make sorting for ORDER BY superfluous ? Again you can try "SET EXPLAIN ON;" to find out more about how queries are executed ... :) Regards, Martin -- Martin Fuerderer IBM Informix Development Munich, Germany Information Management forum.subscriber@iiug.org wrote on 03.02.2005 12:49:23: > We are testing an application. This is an esqlc app. When running, it uses some of our temp dbspaces ( I assume > during the sort for the order by in a select sql ), and also creates sort files in /tmp. > > This is normal behaviour, the way I understand it. I have 3 temp dbspaces in the onconfig for DBSPACETEMP. The > parameter is setup like this : DBSPACETEMP tmpdbs1:tmpdbs2:tmpdbs3. > > Our developer doesn't believe me the way it works, and wanted to set up the DBSPACETEMP as an evironment variable. > I told her to set it up the exact same way as in the onconfig. When we do the 'env' command, it looks like this : > DBSPACETEMP tmpdbs1:tmpdbs2:tmpdbs3. > > That seems right to me, but when testing the app yesterday, there was no usage of the temp dbspaces, and also, no > sort files in /tmp. > > Is there any possible explanation for this behaviour ? > > Thanks, > Floyd > > ======================== > -<>- > Database Administrator > Unix Administrator > > email: fwellers@yahoo.com ======================== -<<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 ========================

Thanks, but this app is not creating any explicit temp tables. Khaled Bentebal <khaled.bentebal@consult-ix.fr> wrote:Hi, If your dbspaces are of type TEMPORARY (created as temporary: check the flags N T using onstat -d ), the temp tables created without the clause WITH NO LOG will go to either the rootdbs or the dbspace of the database depending on the case. If you use the WITH NO LOG clause when creating the temp tables, the temp tables will go to the dbspaces specified in the DBSPACETEMP environment variable. Are you using the WITH NO LOG clause when creating the temp table? If not, we advice to use it for performance reasons on one hand and to use the temporary dbspaces on the other hand. If the temp tables go the dbspaces that are not temporary, this could generate fragmentation for the other tables if these tables were to grow (more extents) at that time. Sorry I didn't go into much detail. I hope that this helps you. If not, I can explain more. Khaled Bentebal ConsultiX Tél: 33 (0) 1 39 72 17 00 Fax: 33 (0) 1 39 72 17 01 Mobile: 33 (0) 6 07 78 41 97 Email: khaled.bentebal@consult-ix.fr Site Web: http://www.consult-ix.fr ----- Original Message ----- From: "Floyd Welle...." To: Sent: Thursday, February 03, 2005 12:49 PM Subject: temp table environment variables [4151] > We are testing an application. This is an esqlc app. When running, it uses some of our temp dbspaces ( I assume during the sort for the order by in a select sql ), and also creates sort files in /tmp. > > This is normal behaviour, the way I understand it. I have 3 temp dbspaces in the onconfig for DBSPACETEMP. The parameter is setup like this : DBSPACETEMP tmpdbs1:tmpdbs2:tmpdbs3. > > Our developer doesn't believe me the way it works, and wanted to set up the DBSPACETEMP as an evironment variable. > I told her to set it up the exact same way as in the onconfig. When we do the 'env' command, it looks like this : DBSPACETEMP tmpdbs1:tmpdbs2:tmpdbs3. > > That seems right to me, but when testing the app yesterday, there was no usage of the temp dbspaces, and also, no sort files in /tmp. > > Is there any possible explanation for this behaviour ? > > 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 ========================