Informix

Informix

Connect with Db2, Informix, Netezza, open source, and other data experts to gain value from your data, share insights, and solve problems.

 View Only

  • 1.  Tempdbs logged/non-logged

    Posted 04/20/20 12:21 PM

    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


  • 2.  RE: Tempdbs logged/non-logged

    Posted 04/20/20 12:39 PM
    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


    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.





    ------Original Message------

    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


  • 3.  RE: Tempdbs logged/non-logged

    Posted 04/21/20 03:57 AM
    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
    ------------------------------

    Original Message


  • 4.  RE: Tempdbs logged/non-logged

    Posted 04/20/20 01:06 PM
    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 8372208

    You create logged temp dbspace without the -t option using onspaces like:
    onspaces -c -d tempdbs04 -p $INFORMIXLINKS/tempdbs04 -o 0 -s 8372208

    Both have to be added to the onconfig file after they are created to be effective like this:
    onmode -wf DBSPACETEMP=tempdbs01:tempdbs02:tempdbs03:tempdbs04

    Thank 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
    ------------------------------



  • 5.  RE: Tempdbs logged/non-logged

    Posted 04/21/20 05:02 AM

    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
    ------------------------------

    Original Message


  • 6.  RE: Tempdbs logged/non-logged

    Posted 04/21/20 08:29 AM

    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

     



    ------Original Message------

    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
    ------------------------------

    #Informix


Related Content