Results 1 to 14 of 14
  1. #1
    Join Date
    Oct 2013
    Posts
    18

    Unanswered: SYSTOOLSTMPSPACE recommended sizings

    Hi,

    Am on 10.1.

    My SYSTOOLSTMPSPACE is currently SMS & is sized at 64MB. I have just aquired the db.

    It is vastly undersized though does anyone have any good sizing tips, calculations that I should follow to size this temp ts correctly.

    There appears to be little available on ibm info centre so thought I'd ask to see if people would share experiences.

  2. #2
    Join Date
    Apr 2012
    Posts
    1,034
    Provided Answers: 18
    Depends entirely on the nature of the application+database and its size, and which admin routines/ auto-utilities are being used on the database (if any).

  3. #3
    Join Date
    Oct 2013
    Posts
    18
    ok.

    We use PSAPTEMP for all our SAP ERP related sorts/reorgs etc etc & am following sap guidelines for that ts.

    Though for SYSTOOLSTMPSPACE i am unsure at all. All I can say is that it is approx 500GB db with approx 500 users.

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by AT14 View Post

    My SYSTOOLSTMPSPACE is currently SMS & is sized at 64MB. ...

    It is vastly undersized
    How do you know it is "vastly undersized"? SMS tablespaces grow and shrink as needed, so what matters is the filesystem size, no?
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Oct 2013
    Posts
    18
    Quote Originally Posted by n_i View Post
    How do you know it is "vastly undersized"? SMS tablespaces grow and shrink as needed, so what matters is the filesystem size, no?
    So its my understanding that SMS tablespaces are of fixed size & cannot be resized under my conditions:

    DB2 Version 10.1 for Linux, UNIX, and Windows
    Altering SMS table spaces

    You cannot add containers to or change the size of containers for SMS table spaces once they have been created, with one exception; when you add new data partitions, you can add new containers to an SMS table space for those partitions.



    I think 64MB is vastly undersized for this TEMP tablespace - though this is based on instinct alone with no real world knowledge or production-level experience of this temp tablespace type.

  6. #6
    Join Date
    Apr 2012
    Posts
    1,034
    Provided Answers: 18
    Any competent sysadmin can dynamically resize a filesystem (being used for an SMS tablespace ) on modern Unix type operating systems...even on Windows it is possible depending on the setup.

  7. #7
    Join Date
    Oct 2013
    Posts
    18
    fine. i'm just going on the docs state.

    so if anyone can help with my original question that would be nice.

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by AT14 View Post
    fine. i'm just going on the docs state.

    so if anyone can help with my original question that would be nice.
    Your question has already been answered. SMS tablespaces automatically resize themselves up to the maximum size of the disk they reside one.

    Andy

  9. #9
    Join Date
    Oct 2013
    Posts
    18
    Hi Andy.

    No it hasn't...so if sizing tips, calculations are shared that would be great.

    SMS tablespaces don't auto resize - they are fixed in size. If they auto sized themselves they could fill the file system (and as all my other tablespaces sit on the same ASM managed file system this could cause me quite a few problems

    Either I don't fully understand how SMS's work or the docs are misleading...

  10. #10
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by AT14 View Post
    Hi Andy.

    No it hasn't...so if sizing tips, calculations are shared that would be great.

    SMS tablespaces don't auto resize - they are fixed in size. If they auto sized themselves they could fill the file system (and as all my other tablespaces sit on the same ASM managed file system this could cause me quite a few problems

    Either I don't fully understand how SMS's work or the docs are misleading...
    Can you please post the documentation that state that SMS tablespaces are fixed in size?

    Andy

  11. #11
    Join Date
    Oct 2013
    Posts
    18
    Altering SMS table spaces

    so once created they are fixed in size, as per my conditions. i posted this info earlier. I am using file containers also.

  12. #12
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by AT14 View Post
    Altering SMS table spaces

    so once created they are fixed in size, as per my conditions. i posted this info earlier. I am using file containers also.
    Yes, you cannot add a container to SMS, but that does not mean that it does not change in size. Look at the CREATE TABLESPACE command. When you specify a SMS tablespace your container is NOT a file, but a directory. That is what resizes.

    Andy

  13. #13
    Join Date
    Apr 2012
    Posts
    1,034
    Provided Answers: 18
    Sometimes you have to interpret the DB2 docs, as distinct from taking them literally. But IBM does have a feedback system for docs via the infocenter. I use it often and get corrections and changes (though it is a slow process due to the need for translations into so many languages).

  14. #14
    Join Date
    Oct 2013
    Posts
    18
    I raised this with SAP support & they said the same:

    Their response based on DMS tablespaces:

    You cannot resize the PSAPTEMP16. As it is an SMS managed tablspace
    you have to drop an recreate it as per the output of the error above.
    To add more containers to a system managed table space, drop and
    recreate the table space with more containers, ensuring that each
    container is of the same size and less than the container size limit.


    So, for the final time i put this question to the forum:

    Does anyone have any value-based sizing tips, calculations, or formulas that I can perhaps use?


    ps. I wont be responding to this thread again as i am consuming far too much time on this, so if anyone is able to answer my above Q many thanks in advance

    pps, thanks to everyone that has contributed so far, appreciate your comments

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •