Results 1 to 4 of 4
  1. #1
    Join Date
    May 2015
    Location
    Korea
    Posts
    50

    Unanswered: How to change tabelspace to AUTOMATIC STORAGE ?

    We have a 8k DMS SYSTEM TEMPORARY TABLESPACE in IBMTEMPGROUP on 3 partitions DPF instance (with non-automatic storage db).

    USING (FILE '/home/db2inst1/fs1/proddb/temp8k' 8650752, FILE '/rsvcfs/db2inst1/NODE0000/proddb/temp8k' 9175040) ON DBPARTITIONNUM (0)
    USING (FILE '/home/db2inst1/fs2/proddb/temp8k' 8650752, FILE '/rsvcfs/db2inst1/NODE0000/proddb/temp8k' 9175040) ON DBPARTITIONNUM (1)
    USING (FILE '/home/db2inst1/fs3/proddb/temp8k' 8650752, FILE '/rsvcfs/db2inst1/NODE0000/proddb/temp8k' 9175040) ON DBPARTITIONNUM (2)

    But due to changed workload we are randomly seeing "Unable to allocate new pages in table space" error on app side.
    We would like to convert this to AUTOMATIC STORAGE.

    We saw command : ALTER TABLESPACE <tbspName> MANAGED BY AUTOMATIC STORAGE
    But wondering if we have to consider anything else? Can someone please share details steps to achieve this?

    Stella

  2. #2
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    68
    Provided Answers: 7
    Stella,

    It's not clear what you want to achieve.
    If you create your system temp as auto storage, it will be created as SMS which is not generally recommended for the DSS systems at the moment.
    It's better to understand why your system requires too large system temporary tablespace.
    May be it's worth to use DB2_OPT_MAX_TEMP_SIZE registry variable to make the optimizer not to choose access plans which require too much space...

    Regards,
    Mark.

  3. #3
    Join Date
    May 2015
    Location
    Korea
    Posts
    50
    Quote Originally Posted by mark.bb View Post
    Stella,

    It's not clear what you want to achieve.
    If you create your system temp as auto storage, it will be created as SMS which is not generally recommended for the DSS systems at the moment.
    It's better to understand why your system requires too large system temporary tablespace.
    May be it's worth to use DB2_OPT_MAX_TEMP_SIZE registry variable to make the optimizer not to choose access plans which require too much space...

    Regards,
    Mark.
    Thanks Mark.
    For my understanding please share bit more insights
    (1) .... is not generally recommended for the DSS systems at the moment.
    Why is that? Can you please share official reference as well?

    (2) It's better to understand why your system requires too large system temporary tablespace.
    Right, its just because of increase in our daily workload we need to make change.

    (3) Is there any guideline on what DB2_OPT_MAX_TEMP_SIZE value should be used?

    Stella

  4. #4
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    68
    Provided Answers: 7
    Quote Originally Posted by StellaZhang View Post
    For my understanding please share bit more insights
    (1) .... is not generally recommended for the DSS systems at the moment.
    Why is that? Can you please share official reference as well?
    Due to the performance reasons.
    PureData System for Operational Analytics, Temporary table spaces

    Quote Originally Posted by StellaZhang View Post
    (2) It's better to understand why your system requires too large system temporary tablespace.
    Right, its just because of increase in our daily workload we need to make change.

    (3) Is there any guideline on what DB2_OPT_MAX_TEMP_SIZE value should be used?
    Some vendors like SAP may have some guidelines for their well-known workload.
    If you know that your queries shouldn't use a huge amount of temp space, you can set this variable to some reasonable for your system value.
    Regards,
    Mark.

Posting Permissions

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