Why Temp Table created by View using DATA dbspace
Hi, I am using IDS 11.70 FC4. I have a query which is joininig two views to pull more than 100 million data rows and sorting them. I have noticed that whenever I execute it, my datadbs free space gets consumed, instead of tempdbs. I have added sufficient tempdbs engine is not using it, why? I have seen the execution plan of the query and it is showing usage of temp tables for view. I think these temp tables are consuming the space from datadbs. It there any onconfig parameter which I need to change? And sometimes, it gives following warninig in the online log file as well: Warning: The storage pool is out of space. To enable automatic chunk creation use the OpenAdmin Tool to add space to the pool. I will be looking for support. thanks.
Just checking... You have set up DBSPACETEMP in onconfig? Your temporary dbsapces are really temp spaces? On Wed, Jun 12, 2013 at 9:34 PM, O KHAN <theultimateboy@hotmail.com> wrote: > Hi, > > I am using IDS 11.70 FC4. > > I have a query which is joininig two views to pull more than 100 million > data > rows and sorting them. > > I have noticed that whenever I execute it, my datadbs free space gets > consumed, instead of tempdbs. > > I have added sufficient tempdbs engine is not using it, why? > > I have seen the execution plan of the query and it is showing usage of temp > tables for view. I think these temp tables are consuming the space from > datadbs. > > It there any onconfig parameter which I need to change? > > And sometimes, it gives following warninig in the online log file as well: > Warning: The storage pool is out of space. To enable automatic chunk > > creation use the OpenAdmin Tool to add space to the pool. > > I will be looking for support. thanks. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --047d7b3a8fa474396e04defb5049
What is DBSPACETEMP set to in your user environment and in the server's environment? Your's takes precedence if it is set! If set to an empty string that will override the server's setting and put temp tables in the database's home dbspace. Also, you should set TEMPTAB_NOLOG in your ONCONFIG file. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Jun 12, 2013 at 4:34 PM, O KHAN <theultimateboy@hotmail.com> wrote: > Hi, > > I am using IDS 11.70 FC4. > > I have a query which is joininig two views to pull more than 100 million > data > rows and sorting them. > > I have noticed that whenever I execute it, my datadbs free space gets > consumed, instead of tempdbs. > > I have added sufficient tempdbs engine is not using it, why? > > I have seen the execution plan of the query and it is showing usage of temp > tables for view. I think these temp tables are consuming the space from > datadbs. > > It there any onconfig parameter which I need to change? > > And sometimes, it gives following warninig in the online log file as well: > Warning: The storage pool is out of space. To enable automatic chunk > > creation use the OpenAdmin Tool to add space to the pool. > > I will be looking for support. thanks. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e0158ba1274b9b804defc858e