Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2004
    Posts
    15

    Question Answered: Tablespaces Balancing

    Hi DB2 Gurus,

    I am using db2 v9.7.6 on an AIX box. There are a few tablespaces that are currently using a single container in a single file system on the AIX server.

    We have just been given an additional file system of the same size as the original one.

    What I want to do is to distribute the data in the two file systems equally. I have added a new container to the tablespace to point to the new file system, how do I achieve the equal distribution of data?

    The tablespace is currently on DMS.

    I tried to use "alter tablespace MyTBSpace rebalance" but it does not work, I guess its because I am on DMS! Kindly advise on the options that I have to achieve this

    Many thanks.
    Faisal.

  2. Best Answer
    Posted by db2dp

    "Hi faisalee,

    here is a quick and dirty for you :

    - add a 2.5 GB container on the new file system
    - reduce the size off the old 5 GB container to 2.5 GB

    if you have a big difference between used and allocated space (DMS) you might want to vary the sizes a bit

    Good luck
    db2dp"


  3. #2
    Join Date
    Sep 2004
    Posts
    15
    I am thinking of exporting the data out to ixf files, delete the data from the tables, resize the tablespace containers to same size and then load the data back. I think this will work but is that a correct approach? or is there a better option.

  4. #3
    Join Date
    Sep 2011
    Location
    Delhi
    Posts
    24
    Hi,
    I think if you add a container in the tablespace even if its DMS then the rebalance (forward rebalancing) will get trigger automatically if the added container size space is lower than the high watermark size of the tablespace, but if the added size is more than the HWM size then need to run the command for rebalancing.

  5. #4
    Join Date
    Sep 2004
    Posts
    15
    Quote Originally Posted by Sandeep_Sharma View Post
    Hi,
    I think if you add a container in the tablespace even if its DMS then the rebalance (forward rebalancing) will get trigger automatically if the added container size space is lower than the high watermark size of the tablespace, but if the added size is more than the HWM size then need to run the command for rebalancing.
    Hi Sandeep,

    Thank you for your answer. The new containers that I added were only 1MB in size while the existing container is way over 5GB, it did not trigger any rebalancing on its own.

    When I try to do: "alter tablespace MyTBSpace rebalance" it says SQL0109N The "REBALANCE" clause is not allowed!

    I am lost currently and don't know how to get it to work

    Cheers for your help

  6. #5
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    110
    Provided Answers: 13
    Hi,

    As for container operations:
    http://www.ibm.com/support/knowledge.../c0008087.html

    Alter tablespace ... Rebalance works for automatic DMS tablespaces only.
    When you add container to non-automatic storage tablespace, rebalance occurs automatically. You can check if it occurred comparing tablespace map before and after, for example, using a tablespace snapshot as described at the link above.
    Regards,
    Mark.

  7. #6
    Join Date
    Jul 2016
    Location
    Germany
    Posts
    32
    Provided Answers: 2

    quick and dirty

    Hi faisalee,

    here is a quick and dirty for you :

    - add a 2.5 GB container on the new file system
    - reduce the size off the old 5 GB container to 2.5 GB

    if you have a big difference between used and allocated space (DMS) you might want to vary the sizes a bit

    Good luck
    db2dp

  8. #7
    Join Date
    Sep 2004
    Posts
    15
    Quote Originally Posted by db2dp View Post
    Hi faisalee,

    here is a quick and dirty for you :

    - add a 2.5 GB container on the new file system
    - reduce the size off the old 5 GB container to 2.5 GB

    if you have a big difference between used and allocated space (DMS) you might want to vary the sizes a bit

    Good luck
    db2dp
    Thank you for your solution... I actually did a similar thing and got it working

    1. I exported the tables out to IXF files
    2. loaded the tables with /dev/null
    3. lower the high water mark
    4. resized the containers to 1 MB
    5. created new containers in a different file system with the same size (1 MB)
    6. loaded the data back from the IXF exports and bang!!! The data got distributed equally

    Basically what I understand now is that the tablespaces have to be of equal size in order for the db2 to distribute data equally in a DMS setup.

    Thanks a lot guys for all the help!

    Cheers,
    Faisal.

  9. #8
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    110
    Provided Answers: 13
    Quote Originally Posted by faisalee View Post
    Basically what I understand now is that the tablespaces have to be of equal size in order for the db2 to distribute data equally in a DMS setup.
    This is not a correct statement.
    It was said that you could add another 2.5 GB container and reduce the old one to 2.5 GB after the automatic rebalance operation.
    When you add a very small container, the rebalance operation does occur, but it's very quick since db2 will not try to increase the size of new container automatically.
    Instead of this in creates a new very small stripe over all containers and place there the data of a few higher extents leaving other ones untouched.
    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
  •