Hi All,

I want to add more space to one of our tablespaces without having DB2 rebalance all the containers.

I know I can use the BEGIN NEW STRIPE option of ALTER TABLESPACE, but doesn't that add another container? How can I add more space without rebalancing and witout adding another container?

ENV: AIX, DB2 UDB v8.2

Thanks

Anks

Assuming all your current containers are of the same size, extend all the containers by the same amount. This will not cause rebalancing.

Before, BEGIN NEW STRIPE was introdcued, the above method was the only one available to avoid rebalancing

HTH

Sathyaram

There are actually three conatiners and they are not the same size.

Tablespace Containers for Tablespace 3

Container ID = 0
Name = /prod2/sms2/data/LADATA16K01.dbf
Type = File
Total pages = 173000
Useable pages = 172992
Accessible = Yes

Container ID = 1
Name = /prod2/sms2/data/LADATA16K02.dbf
Type = File
Total pages = 173000
Useable pages = 172992
Accessible = Yes

Container ID = 2
Name = /prod2/sms2/data/LADATA16K03.dbf
Type = File
Total pages = 110500
Useable pages = 110496
Accessible = Yes

Can I still extend all without rebalancing?

Thanks
Anks

Anks,

Your container output shows me that your tablespace is almost completely empty, so why are you worried about rebalancing? Rebalancing an empty db should take no time at all

Maybe your output is out of date, so it does not reflect the current state of the tablespace? Even if that is so, I would still make all containers the same size and take the hit for rebalancing. Do it off hours if you think it will be too intrusive - although I have never experienced a hugely significant slow down during a rebalance that cannot be lived with.

Another point: why are you creating different sized containers for the same tablespace in the first place? I guess that is why you wish to increase the last one - maybe to match the other 2?

As a general rule, containers should be of the same size at the start. Matter of fact a few versions of DB2 and AIX ago you had to make all containers exactly the same (for a single DMS tablespace). If you didn't, all containers would only fill to the size of the smallest container. This has since been fixed, but I still recommend making them all the same.

When you increase sizes in the future, make sure you increase all of them together and at the same amount. It just makes it easier to manage.


Steve
IBM Certified LUW DB2 DBA

Hi Steve,

I am worried about rebalancing because 2 weeks back this tablespace was hitting almost 90%, so I had to add more pages and right after I added these pages DB2 started rebalancing right away (stupid feature of DB2 that you can't let the rebalancing happen at some other time) and the system could not handle it and so the instance crashed. We had lot of trouble because the instance would just not start up. So finally we had to restore the database and rollforward just before the rabalancing happened because according to the IBM support, rebalancing caused data corruption.

My output is actually upto date and you are right I want to increase the third container to make them all same size. I understand its not a good idea to have different sizes of conatiners under one tablespace, but this has not been designed by me, all i can do is find ways to improve things and that is when i seek help from gurus at this forum as I have started working on DB2 few months back and I am still exploring lot of stuff about Db2.

whenever i want to add more space to a tablespace I always increase all the containers at one go. this is the command that I use:

alter tablespace LADATA16k extend (all 1000)

But i am sure this command does not get rid of the rebalancing.

Anks

Steve,

One more thing I would like to add. You said that the containers are almost completely empty after looking at the output that I sent.

How did you get to know this?
Please correct me if I am wrong the "list tablespace containers for 3 show detail" command only shows the total pages and Useable pages, the output does not talk about the used pages at all.

Anks

Sorry Anks, my mistake. I mistook the "useable" pages as free. My bad.

You can also run -

db2 list tablespaces show detail

and it will show the used and free pages for all tablspaces, but not for each individual container.


Here is something you may be able to do though: try a redirected restore. This method is usually used to restore a database into another instance (typically on another machine), but can be used to add or subtract containers.

Search through the recent threads from the last month or so. I did a quick write up on how to do a redirect if you have a full offline backup

How to Clone Database

There was another thread on how to do it with an online backup.

The main difference between a redirect and a normal restore is with a redirect, you change the files/paths for the containers and logs, and can even rename the db. It is very powerful and useful.

However, be warned that this will take some time to do. It will take longer than a regular restore, but not too much. Maybe 20% longer? I have never timed it, so I cannot give a definitive answer.


Hope this helps.

Steve

Anks,

You indicated in the thread above that IBM said that "rebalancing caused data corruption" - you may want to get more details - e.g. APAR #, etc.

-- Jayesh