Tempdbs logged/non-logged

Hi! At the weekend I watched the following webinar: Informix Tutorial - Managing Informix Disk Space - Advanced DataTools There´s one thing that I didn´t understand: Create at least 3 non-logged Temp DBspaces ... Create 1-3 Logged Temp DBspaces.... How can I create a logged/non-logged Temp-DBSpace? ------------------------------ Kind Regards Stefan ------------------------------ #Informix

Stefan: Any temp dbspace is unlogged. If you have three or more of them then sorting is most efficient and large temp tables will be created round robin across all of them. Sorts work best with from three to six temp dbspaces, but some sites create more either for better insert performance on temp tables or in order to isolate some groups of applications' temp tables from others by setting different lists of temp dbspaces in DBSPACETEMP in those sessions' environments. As to "logged temp dbspaces" those are just normal dbspaces that can be used for logged temp tables because they are listed in DBSPACETEMP. If you do not have any logged dbspaces listed in DBSPACETEMP then logged temp tables will be written to the root dbspace which is a bad thing. Logged temp tables happen when: TEMPTAB_NOLOG is not set or is set to zero (0), and a temp table is created explicitly with the CREATE TEMP TABLE statement that does not include the WITH NO LOG clause. Regardless of the setting for TEMPTAB_NOLOG a temp table is created with a storage clause that specifies storage in one or more logged dbspaces (ie non-temp). If you have TEMPTAB_NOLOG set to 1 then you can skip the recommendation to list normal dbspaces in DBSPACETEMP since the only way to create a logged temp table then would be to create it IN a logged dbspace so DBSPACETEMP is not referenced. If you do not have TEMPTAB_NOLOG set to 1 then this is an important recommendation. I usually recommend to include either a dbspace dedicated to the purpose or one that typically has very low IO activity other than these logged temp tables. Perhaps one used by history tables that are rarely accessed. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.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 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.

Hi Stefan, You create at non-logged temp dbspaces with the -t option using onspaces like: onspaces -c -d tempdbs01 -t -p $INFORMIXLINKS/tempdbs01 -o 0 -s 8372208 onspaces -c -d tempdbs02 -t -p $INFORMIXLINKS/tempdbs02 -o 0 -s 8372208 onspaces -c -d tempdbs03 -t -p $INFORMIXLINKS/tempdbs03 -o 0 -s 8372208You create logged temp dbspace withou t the -t option using onspaces like: onspaces -c -d tempdbs04 -p $INFORMIXLINKS/tempdbs04 -o 0 -s 8372208Both have to be added to the onconfig file after they are created to be effective like this: onmode -wf DBSPACETEMP=tempdbs01:tempdbs02:tempdbs03:tempdbs04Thank you for watching our tutorial. Regards - Lester ------------------------------ Lester Knutsen lester@advancedatatools.com Advanced DataTools Corporation Voice: 703-256-0267 x102 Visit our Web page: http://www.advancedatatools.com ------------------------------

Hi! Thanks for the fast response! It´s nice to see that here´s an active community at this forum. Conclusion for us: We always set TEMPTAB_NOLOG=1 and so we need no logged temp-dbspace. ------------------------------ Kind Regards Stefan ------------------------------

Hi! Also thanks for the fast response. Now I understand your solution: You create three temporary dbspaces an one "normal" dbspace and use this as temporary dbspace. One question else at this point: >Create at least 3 non-logged Temp DBspaces (min) – Informix will use them in round robin and in parallel How exactly does this work, when the dbspace is full? e.g. tempdbs01 has 8GB and 7GB are used. Now I start my heavy "INTO TEMP" - Statement and this Statement needs 2GB (at tempdbs01). Run I into an "No free diskspace" error or does Informix uses tempdbs02/tempdbs03? ------------------------------ Kind Regards Stefan ------------------------------

When it is full it is full, by default a temp table will not fragment across spaces. You could turn on auto extend ? Generally you want to size all your spaces the same size. There are exceptions but a good starting point Cheers Paul