Re: temp space question

2009/9/29 Floyd Wellershaus <floyd@fwellers.com>: > So I was under the impression if you run a query that requires implicit > sorting and needs temp space, and you have nothing set in the environment > like PSORT_DBTEMP, it would use the DBSPACETEMP parameter from the onconfig > file. > Mine is: DBSPACETEMP wto_tmp1:wto_tmp2:wto_tmp3 > > Yet, when I run a large query ( unload to somefile select * from someview ), > what happens is only wto_tmp1 dbspace gets used, and then when it runs out > of space the query dies with a disk full error. > > That is not normal behavior is it ? It should use all three spaces right ? > > Thanks, > floyd > > > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > > Floyd Temp DBSPACES have the same limitations as others in that a table fragment must only exist entirely within a dbspace therefore if you are selecting more data than can be contained in the free space of the dbspace that is selected (on a random basis by the engine) then you will encounter the above error. Once the data is fully selected then the engine will use other temp spaces to sort into. Either select fewer rows or specific columns (rather than *). Another thing to watch out for is that temp dbspaces can be logged or unlogged. If they are unlogged then they will not be used for sort/count work files or for tables that are implicitly or explicitly unlogged. Keith