Results 1 to 11 of 11

Thread: Tablespace size

  1. #1
    Join Date
    May 2012
    Posts
    155

    Unanswered: Tablespace size

    Hallo everyone,

    I have a question regarding these columns from the table sysibmadm.TBSP_UTILIZATION:
    TBSP_TOTAL_SIZE_KB
    TBSP_USABLE_SIZE_KB
    TBSP_USED_SIZE_KB
    TBSP_FREE_SIZE_KB

    I have a snapshot example for some table spaces:


    Code:
    TBSP_NAME       TOTAL(GB) USABLE(GB) USED(GB) FREE(GB) HIGHWM(GB) 
          
    syscatspace	,28	,28	    ,26	    ,02	    ,26
    systoolstmpspace	,00	,00	    ,00	    ,00	    ,00
    tempspace1	,00	,00	    ,00	    ,00	    ,00
    dim_index	1,00	1,00	    ,93	    ,07	    ,99
    tablspace1	1,33	1,33	    1,27	    ,06	    1,31
    tablspace2	3,78	3,78	    3,35	    ,43	    3,69
    tablspace3	8,49	8,49	    6,29	    2,20	    7,20
    tablspace4	12,88	12,88	    12,86    ,02     12,88
    tablspace5	20,09	20,09	    19,36    ,74  19,69
    tablspace6	88,70	88,70	    74,62	14,08  74,62
    tablspace7	6,92	6,92	     6,92	,00  6,92
    tablspace8	,00	,00	     ,00	    ,00	   ,00
    systoolspace	,06	,06	     ,03	    ,03	   ,03
    userspace1	,05	,05	     ,05	    ,00	   ,05
    I have recognized that the value from the columns above varies from time to time. I assume, this is because of the dynamic tablespace resizing option?

    For all the table spaces, I use 4 containers, each 100 GB:
    1. cont_01
    2. cont_02
    3. cont_03
    4. cont_04

    If I look at the disk usage with df :
    Code:
    100G    36G    63G    37%    /../ratna_01
    100G    36G    63G    37%    /../ratna_02
    100G    36G    63G    37%    /../ratna_03
    100G    36G    63G    37%    /../ratna_04
    Is it always like that, that everything will be equally distributed in all 4 containers?

    Thank you for the help.

    Regards,

    Ratna

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    May 2012
    Posts
    155
    Hallo n_i,

    could you please at least give me an answer for this simple question:
    Code:
    I have recognized that the value from the columns above varies from time to time. I assume, this is because of the dynamic tablespace resizing option?
    I assume, you just have to say yes or no.

    I am so dumb, so that I could not find the answer for the question, even from the documentation you suggested.

    Thank you.

    Regards,
    Ratna

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Sorry, I don't really understand what you mean by "varies from time to time". What value of the five in your table varies? Could you please demonstrate?

    The link I provided was in response to your second question about multiple containers.
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    May 2012
    Posts
    155
    Hallo n_i,

    thanks for the quick reply. I have recognized, that the sizes change from time to time, for example:
    Snapshot for 1 table space

    on 04.07.2012
    Code:
    TBSP_NAME       TOTAL(GB) USABLE(GB) USED(GB) FREE(GB) HIGHWM(GB)
    tablspace6	88,70	88,70	    74,62	 14,08    74,62
    on 06.07.2012
    Code:
    TBSP_NAME       TOTAL(GB) USABLE(GB) USED(GB) FREE(GB) HIGHWM(GB)
    tablspace6	20,09	20,09	    19,36    ,74  19,69
    Who decide, how much TOTAL size a table space receives?

    Thank you.

    Regards,

    Ratna

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I'm assuming you are talking about a DMS tablespace (or automatic storage). DMS tablespaces will grow automatically as needed, if autoresize is enabled, however, they will not shrink automatically. Someone might have issued an ALTER TABLESPACE ... RESIZE statement or simply dropped and re-created the tablespace.
    ---
    "It does not work" is not a valid problem statement.

  7. #7
    Join Date
    May 2012
    Posts
    155
    Hallo n_i,

    but in case of DMS, the size of a container can not be changed? In my case, all 4 containers will be just in size of 100GB each?

    Code:
    100G    36G    63G    37%    /../ratna_01
    100G    36G    63G    37%    /../ratna_02
    100G    36G    63G    37%    /../ratna_03
    100G    36G    63G    37%    /../ratna_04
    Thank you.

    Regards,
    Ratna

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    You define the container sizes.

    Altering DMS table spaces
    ---
    "It does not work" is not a valid problem statement.

  9. #9
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    How are the tablespaces defined? Please post the output from "db2pd -d <db name> -sto -tab"

  10. #10
    Join Date
    May 2012
    Posts
    155
    Hallo n_i and db2girl,

    thanks for the helps.

    For all the table spaces, I use 4 containers, each 100 GB:
    1. cont_01
    2. cont_02
    3. cont_03
    4. cont_04

    Obviously, each container size was configured to 100GB. Actually I am wondering if this container size will grow dynamically if needed?

    With the command suggested by db2girl, I got the following output fragment:

    Code:
    Tablespace Configuration:
    Address            Id    Type Content PageSz ExtentSz Auto Prefetch BufID BufIDDisk FSC NumCntrs MaxStripe  LastConsecPg Name
    0**************C60 0     DMS  Regular 16384  4        Yes  96       1     1         Off 4        0          3            SYSCATSPACE
    0**************A00 1     SMS  UsrTmp  16384  4        Yes  96       1     1         On  4        0          3            SYSTOOLSTMPSPACE
    0**************760 2     SMS  SysTmp  16384  4        Yes  96       1     1         Off 4        0          3            TEMPSPACE1
    0**************4C0 3     DMS  Large   16384  4        Yes  96       2     2         Off 4        0          3            TABLSPACE1
    0**************260 4     DMS  Large   16384  4        Yes  96       2     2         Off 4        0          3            TABLSPACE2
    0**************000 5     DMS  Large   16384  4        Yes  96       2     2         Off 4        0          3            TABLSPACE3
    0**************DA0 6     DMS  Large   16384  4        Yes  96       3     3         Off 4        0          3            TABLSPACE4
    0**************080 7     DMS  Large   16384  4        Yes  96       3     3         Off 4        0          3            TABLSPACE5
    0**************DC0 8     DMS  Large   16384  4        Yes  96       4     4         Off 4        0          3            TABLSPACE6
    0**************B00 9     DMS  Large   16384  4        Yes  96       1     1         Off 4        0          3            TABLSPACE7
    0**************840 10    DMS  Large   16384  4        Yes  96       1     1         Off 4        0          3            TABLSPACE8
    0**************580 11    SMS  UsrTmp  16384  4        Yes  96       1     1         On  4        0          3            TS_USER_TEMP16K
    0**************2E0 12    DMS  Large   16384  4        Yes  96       1     1         Off 4        0          3            SYSTOOLSPACE
    0**************020 13    DMS  Large   16384  4        Yes  96       1     1         Off 4        0          3            USERSPACE1
    
    
    Containers:
    Address            TspId ContainNum Type    TotalPgs   UseablePgs PathID     StripeSet  Container
    0*************1A0 0     0          File    4608       4604       0          0          /db2/.../cont_01/inste/.../T0000000/C0000000.CAT
    0*************3B0 0     1          File    4608       4604       1          0          /db2/.../cont_02/inste/.../T0000000/C0000001.CAT
    0*************5C0 0     2          File    4608       4604       2          0          /db2/.../cont_03/inste/.../T0000000/C0000002.CAT
    0*************7D0 0     3          File    4608       4604       3          0          /db2/.../cont_04/inste/.../T0000000/C0000003.CAT
    0*************EE0 1     0          Path    1          1          0          0          /db2/.../cont_01/inste/.../T0000001/C0000000.UTM
    0*************0F0 1     1          Path    1          1          1          0          /db2/.../cont_02/inste/.../T0000001/C0000001.UTM
    0*************300 1     2          Path    1          1          2          0          /db2/.../cont_03/inste/.../T0000001/C0000002.UTM
    0*************510 1     3          Path    1          1          3          0          /db2/.../cont_04/inste/.../T0000001/C0000003.UTM
    0*************C40 2     0          Path    1          1          0          0          /db2/.../cont_01/inste/.../T0000002/C0000000.TMP
    0*************E50 2     1          Path    1          1          1          0          /db2/.../cont_02/inste/.../T0000002/C0000001.TMP
    0*************060 2     2          Path    1          1          2          0          /db2/.../cont_03/inste/.../T0000002/C0000002.TMP
    0*************270 2     3          Path    1          1          3          0          /db2/.../cont_04/inste/.../T0000002/C0000003.TMP
    ...
    ...
    ...
    Obviously, each table space is distributed equally to all 4 containers, so that the Database Manager tries to distribute all tables spaces equally to all 4 containers

    Thank you.

    Regards,

    Ratna
    Last edited by ratnalein; 01-03-13 at 18:33.

  11. #11
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    What happened to the rest of the info displayed by db2pd?

    Anyhow, if a DMS tablespace is set to auto-resize (AR column under 'Tablespace Autoresize Statistics' heading contains Y), then "database manager extends only those containers that exist within the last range of the table space map (the map describes the storage layout for the table space) and extends them by an equal amount."


    For example, a tablespace has 4 containers and the tablespace map shows (captured with tablespace snapshot):

    Code:
       Range  Stripe Stripe  Max         Max  Start  End    Adj.   Containers
       Number Set    Offset  Extent      Page Stripe Stripe
       [   0] [   0]      0  450555  14417791      0 112638   0    4 (0,1,2,3)
    All 4 containers are part of range #0, so when db2 needs to auto-grow the tablespace, it will equally extend all 4 containers.

Posting Permissions

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