Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2016
    Posts
    18

    Unanswered: Rebalance Did Not Occur after adding Container to DMS Tablespace

    Hello All,
    We had a Tablespace that ran out of space on the existing containers, so a new File/Container was added to the Tablespace using the Alter Tablespace command. The weird thing is, an automatic rebalance was not performed and now I have several containers that show almost full (99%), but the new container only about 7% used.

    I tried running an ALTER TABLESPACE <ts_name> REBALANCE command, but it returned a syntax error:
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0104N An unexpected token "rebalance" was found following "tablespace
    HARM_TS1". Expected tokens may include: "<stmt_or_dcl>". SQLSTATE=42601

    I issued a GET SNAPSHOT command and see the new container was added to the existing stripe set, but also noticed a section called "Rebalancer Mode" with a value of "No Rebalancing", so not sure if that means it is not in a rebalance state, or if rebalance is not allowed.
    "Rebalancer Mode = No Rebalancing"

    How do I get the data to be redistributed across all the containers in the tablespace? I read something about a REORG with Rebalance option, but only in a post and not in IBM's documentation...also, I see the rebalance option for alter tablespace in LUW9.7, but under 9.5 I dont see it...could it be it was done differently in v9.5?

    DB2 v9.5 LUW
    Tablespace in Question
    Tablespace ID = 4
    Tablespace Type = Database managed space
    Tablespace Content Type = All permanent data. Large table space.
    Tablespace Page size (bytes) = 16384
    Tablespace Extent size (pages) = 32
    Automatic Prefetch size enabled = No
    Tablespace Prefetch size (pages) = 32
    Buffer pool ID currently in use = 2
    Buffer pool ID next startup = 2
    Using automatic storage = No
    Auto-resize enabled = No
    File system caching = Yes
    Tablespace State = 0x'00000000'
    Detailed explanation:
    Normal
    Total number of pages = 28265472
    Number of usable pages = 28264864
    Number of used pages = 27834784
    Number of pending free pages = 0
    Number of free pages = 430080
    High water mark (pages) = 27834784
    Current tablespace size (bytes) = 463101493248
    Rebalancer Mode = No Rebalancing
    Minimum Recovery Time = 09/12/2016 20:44:51.000000
    Number of quiescers = 0
    Number of containers = 11

  2. #2
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    261
    Provided Answers: 39
    Hi,

    What's the result of query from SYSIBMADM.snapcontainer for this tablespace?
    http://www.ibm.com/support/knowledge...002.html?pos=2
    Regards,
    Mark.

  3. #3
    Join Date
    Oct 2016
    Posts
    18
    Quote Originally Posted by mark.bb View Post
    Hi,

    What's the result of query from SYSIBMADM.snapcontainer for this tablespace?
    http://www.ibm.com/support/knowledge...002.html?pos=2
    No code has to be inserted here.

  4. #4
    Join Date
    Oct 2016
    Posts
    18
    Quote Originally Posted by mark.bb View Post
    Hi,

    What's the result of query from SYSIBMADM.snapcontainer for this tablespace?
    http://www.ibm.com/support/knowledge...002.html?pos=2

    SNAPSHOT_TIMESTAMP TBSP_NAME TBSP_ID CONTAINER_NAME CONTAINER_ID CONTAINER_TYPE TOTAL_PAGES USABLE_PAGES ACCESSIBLE STRIPE_SET DBPARTITIONNUM FS_ID FS_TOTAL_SIZE FS_USED_SIZE
    2016-11-11 14:30:37.94408 HARM_TS1 4 /db2/mo_vol1/TS1_cs1 0 FILE_EXTENT_TAG 3585912 3585856 1 0 0 9223372298847780865 107105746944 87490162688
    2016-11-11 14:30:37.94408 HARM_TS1 4 /db2/mo_vol2/TS1_cs2 1 FILE_EXTENT_TAG 3717112 3717056 1 0 0 9223372247308173313 107105746944 81848418304
    2016-11-11 14:30:37.94408 HARM_TS1 4 /db2/mo_vol3/TS1_cs3 2 FILE_EXTENT_TAG 3717112 3717056 1 0 0 9223372243013206017 107105746944 87135199232
    2016-11-11 14:30:37.94408 HARM_TS1 4 /db2/mo_vol4/TS1_cs4 3 FILE_EXTENT_TAG 3717112 3717056 1 0 0 9223372234423271425 106971529216 87135043584
    2016-11-11 14:30:37.94408 HARM_TS1 4 /db2/mo_vol8/TS1_cs5 4 FILE_EXTENT_TAG 3717112 3717056 1 0 0 9223372264488042497 107105746944 87132258304
    2016-11-11 14:30:37.94408 HARM_TS1 4 /db2/mo_vol6/TS1_cs6 5 FILE_EXTENT_TAG 1651000 1650944 1 1 0 9223372294552813571 106300440576 106293923840
    2016-11-11 14:30:37.94408 HARM_TS1 4 /db2/mo_vol9/TS1_cs7 6 FILE_EXTENT_TAG 3717112 3717056 1 1 0 9223372337502486530 106568876032 99108888576
    2016-11-11 14:30:37.94408 HARM_TS1 4 /db2/mo_vol9/TS1_cs8 7 FILE_EXTENT_TAG 2331000 2330944 1 1 0 9223372337502486530 106568876032 99108888576
    2016-11-11 14:30:37.94408 HARM_TS1 4 /db2/mo_vol5/ts1_cs9 8 FILE_EXTENT_TAG 1371000 1370944 1 1 0 9223372251603140609 106971529216 101711982592
    2016-11-11 14:30:37.94408 HARM_TS1 4 /db2/mo_vol7/TS1_cs9 9 FILE_EXTENT_TAG 731000 730944 1 1 0 9223372294552813572 106568876032 91220316160
    2016-11-11 14:30:37.94408 HARM_TS1 4 /db2/mo_vol10/TS1_cs10 10 FILE_EXTENT_TAG 910000 909952 1 1 0 9223372354682355713 106300440576 14926012416

  5. #5
    Join Date
    Oct 2016
    Posts
    18
    Table space map:

    Range Stripe Stripe Max Max Start End Adj. Containers
    Number Set Offset Extent Page Stripe Stripe
    [ 0] [ 0] 0 560289 17929279 0 112057 0 5 (0,1,2,3,4)
    [ 1] [ 0] 0 576689 18454079 112058 116157 0 4 (1,2,3,4)
    [ 2] [ 1] 116158 663321 21226303 116158 159473 0 2 (5,6)
    [ 3] [ 1] 116158 688149 22020799 159474 167749 0 3 (5,6,7)
    [ 4] [ 1] 116158 731597 23411135 167750 189473 0 2 (6,7)
    [ 5] [ 1] 116158 791597 25331135 189474 209473 0 3 (6,7,8)
    [ 6] [ 1] 116158 881721 28215103 209474 232004 0 4 (6,7,8,9)
    [ 7] [ 1] 116158 883276 28264863 232005 232315 0 5 (6,7,8,9,10)

  6. #6
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    261
    Provided Answers: 39
    You have containers in the latest stripe set of quite a different sizes:

    6: 3717056
    7: 2330944
    8: 1370944
    9: 730944
    10: 909952

    db2 will add data to these containers only.
    If you want to distribute data evenly across these containers, then resize all of them to the some equal size = avg + some room to grow (if you have enough space in the corresponding file systems).
    Regards,
    Mark.

  7. #7
    Join Date
    Oct 2016
    Posts
    18
    Quote Originally Posted by mark.bb View Post
    You have containers in the latest stripe set of quite a different sizes:

    6: 3717056
    7: 2330944
    8: 1370944
    9: 730944
    10: 909952

    db2 will add data to these containers only.
    If you want to distribute data evenly across these containers, then resize all of them to the some equal size = avg + some room to grow (if you have enough space in the corresponding file systems).
    Thanks so much for your help! I see the values you referenced are from the USABLE_PAGES portion of the results. I assume I should find out what size (20GB, 20MB, etc...) the Files/Containers were added to the Tablespace as, then resize each individually to size of the largest + room to grow? Sorry if these are elementary questions...I'm not really a DB2 Admin and inherited the environment, but have learned quite a bit since taking ownership and I certainly like learning.

  8. #8
    Join Date
    Oct 2016
    Posts
    18
    Quote Originally Posted by ChrisBarnhart12 View Post
    Thanks so much for your help! I see the values you referenced are from the USABLE_PAGES portion of the results. I assume I should find out what size (20GB, 20MB, etc...) the Files/Containers were added to the Tablespace as, then resize each individually to size of the largest + room to grow? Sorry if these are elementary questions...I'm not really a DB2 Admin and inherited the environment, but have learned quite a bit since taking ownership and I certainly like learning.
    So, maybe as simply as running the following command with the correct new size? I assume an auto-rebalance will occur once the command is done...do I need to watch out for anything or take anything else into consideration?

    ALTER TABLESPACE <name>
    EXTEND
    (
    FILE '<filename>' <size>,
    FILE2 '<filename>' <size>,
    FILE3 '<filename>' <size>,
    FILE4 '<filename>' <size>,
    FILE5 '<filename>' <size>
    )

    I see the mount that the Vol6 falls under shows 100% used when issuing a DF -K, so I assume I won't be able to do this until I get our UNIX admin to increase the space on that LUN...unless I set all other containers to the same size as 6, since it appears to be the largest. I'm going to try and find out the size that each file was added as...

  9. #9
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    261
    Provided Answers: 39
    It would be great, if you could set all the containers (5-10) of this stripe set 1 to the same size - it's much easier to resize them manually in future.

    Actually, my 1-st suggestion about the containers 6-10 only is not good enough - they belong to the different ranges, and all they must have different sizes. Since you have your tablespace not auto resizable, you should do some math to understand when some of these containers reaches its limit at the moment. It might be not so trivial, especially if you are not DBA.

    If you can't set some equal size for containers 5-10, I'd suggest you the following:
    Make this tablespace auto resizable temporarily and wait until all these containers 6-10 start to grow simultaneously. You can extend these containers to the same size every time you need it afterwards. You can make the tablespace not auto resizable again after the first such an extending.
    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
  •