Informix temp spaces

Hello everyone I have Informix 11.7 with UNBUFFER databases. I'm playing with informix temp dbspaces. I have create 8 temp dbspaces 4 are createt with "T" flag and 4 without, like regular dbspace. All 8 dbspaces are added in DBSPACETEMP on onconfig file. While monitoring temp dbspaces activities I see only read/write activities on the 4 chunks created with "T" flag. From Informix Q/A "How Informix Dynamic Server uses the temporary dbspace" I found this example Assuming the databases has logging: 1. If we have created a dbspace named tmpdbs, but we could not see it was marked as 'T' in the result of onstat -d. We set DBSPACETEMP configuration parameter to tmpdbs. On this condition, tmpdbs will be used for logged temporary tables. That means if a temp table is created with 'WITH NO LOG' option, the server will not use it. Any ideas what I'm doing wrong?

Hi Vladimir, You are using Informix 11.7 with UNBUFFER databases. First - check the value of TEMPTAB_NOLOG variable in your configuration file: 1. If you have TEMPTAB_NOLOG 0, than you have two possibilities: 1.1. When you create temp table with "create temp table tmptbl ... WITH NO LOG;", tmptbl will be created in temporary dbspaces(4 dbspaces with "T" flag). 1.2. When you execute "select ... into temp tmptbl WITH NO LOG;", tmptbl will be created in temporary dbspaces(4 dbspaces with "T" flag). 1.3 When you create temp table with "create temp table tmptbl ... ;", tmptbl will be created in "normal" temporary dbspaces(4 dbspaces without "T" flag). 1.4 When you execute "select ... into temp tmptbl;", tmptbl will be created in "normal" temporary dbspaces(4 dbspaces without "T" flag). 2. If you have TEMPTAB_NOLOG 1 : 2.1 All of the above SQL commands will create temp table tmptbl in temporary dbspaces(4 dbspaces with "T" flag). Ypu can check the locations of the temporary objects with: "oncheck -pe <name_of_temporary_dbspace>". Regards,

Hi Boycho, I have TEMPTAB_NOLOG 1; Do you think it will be good practice to set it on "0"? I have configured and two RSS servers.

You're seeing activity in the temp spaces for unlogged temp tables and not in the temp space for logged temp tables. That is expected and for most of us desirable. I only create logged temp space just in case a temp table is created without the no log option, so as to not fill up the rootdbs with temp tables. --EEM > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > VLADIMIR RISTOVSKI > Sent: Tuesday, August 25, 2015 08:00 AM > To: ids@iiug.org > Subject: Re: Informix temp spaces [35653] > > Hi Boycho, > > I have TEMPTAB_NOLOG 1; > > Do you think it will be good practice to set it on "0"? > I have configured and two RSS servers. > > > *********************************************************************** > ******** > Forum Note: Use "Reply" to post a response in the discussion forum.

Hi Vladimir, If you have RSS servers: For HDR, RSS, and SDS secondary servers in a high-availability cluster, logical logging on temporary tables should always be disabled by setting the TEMPTAB_NOLOG configuration parameter to 1. So, you can remove last 4 temporary dbspaces without "T" flag and recreate them with the same names and "T" flag. Be sure to make archive (fake or normal) after deleting these dbspaces and before recreating them. If you want to use only 4 "real" temporary dbspaces, just change the value of DBSPACETEMP parameter.But if you want to use 8 "real" temporary dbspaces - see above. Regards,

Hi Thanks for the answers, then I will leave configuration as it is and I will play with adding more tempdbs with "T" flags