Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2013
    Posts
    31

    Unanswered: Simple question about adding Container file to Tablespace

    New DB2 DBA here ... and I have a very simple question, hopefully. Are there any caveats to adding a new container to a tablespace that's full on DB2 9.5 LUW running on Windows Server 2003? We have at tablespace with no free pages, and I'm about to add another container to create more space. Here are the specs now:

    Code:
    Tablespace ID                        = 154
    Name                                 = PTSNT054
    Type                                 = Database managed space
    Contents                             = All permanent data. Large table space.
    State                                = 0x0000
       Detailed explanation:
         Normal
    Total pages                          = 128000
    Useable pages                        = 127968
    Used pages                           = 127968
    Free pages                           = 0
    High water mark (pages)              = 127968
    Page size (bytes)                    = 4096
    Extent size (pages)                  = 32
    Prefetch size (pages)                = 16
    Number of containers                 = 1
    Minimum recovery time                = 2003-07-23-19.45.47.000000
    
                Tablespace Containers for Tablespace 154
    Container ID                         = 0
    Name                                 = D:\DB2_data\DMSFILES\PTSNT054.DBF
    Type                                 = File
    Total pages                          = 128000
    Useable pages                        = 127968
    Accessible                           = Yes
    And here's what I plan on running:

    alter tablespace PTSNT054 add (file 'D:\DB2_data\DMSFILES\PTSNT054A.DBF' 50000)

    There is plenty of space on D: for this new tablespace container, and in testing running this seemed to instantly increase my Free Pages count on the tablespace. I just wanted to make sure I'm not missing or overlooking anything.

    Thanks.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    In general, if you add a new container to the existing stripe set of the tablespace (which is the default), DB2 will try to rebalance the data across all the containers in the stripe set. So if the old and new containers are not the same size, there may be a problem, and you could end up wasting space. If you cannot make them equal size (make the new container equal to size of any existing containers) you may want to add the container to a new stripe set to avoid rebalancing. There may be a slight performance advantage of having the containers striped in the same strip set (and rebalanced), especially for disk intensive databases where bufferpool hit ratio is somewhat low due to the size of the data.

    The doc is a little unclear of exactly what will happen if DB2 cannot rebalance the tablespace containers. Here is what it says:

    "When new containers are added to a table space, or existing containers are extended, a rebalance of the table space might occur. The process of rebalancing involves moving table space extents from one location to another. During this process, an attempt is made to keep data striped within the table space. Rebalancing does not necessarily occur across all containers but depends on many factors, such as the existing container configuration, the size of the new containers, and how full the table space is.

    When containers are added to an existing table space, they might be added such that they do not start in stripe 0, as described in DMS table space maps. Where they start in the map is determined by the database manager and is based on the size of the containers being added. If the container being added is not large enough, it is positioned such that it ends in the last stripe of the map. If it is large enough, it is positioned to start in stripe 0.

    No rebalancing occurs if you are adding new containers and creating a new stripe set. A new stripe set is created using the BEGIN NEW STRIPE SET clause on the ALTER TABLESPACE statement. You can also add containers to existing stripe sets using the ADD TO STRIPE SET clause on the ALTER TABLESPACE statement."
    Bottom line is that you should either make the containers the same size as existing containers, or add the new containers to a new strip set (see alter tablespace command). Also, I would recommend you upgrade to at least 9.7, or at least make sure you have a later DB2 fixpack installed on your 9.5 DB2 instance.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Is there any reason why you are choosing to add a new container instead of extending the existing one?
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    Feb 2013
    Posts
    31
    Are there any caveats with extending the container? Sounds like if I added a container it'd need to be 128000 pages which would cause a rebalance and stripe the data across both pages, and my prior suggestion would create an imbalance and cause other issues. If I did expand it by say 50,000 pages is there anything else I would need to worry about or do?

    I assume this would be done by this command:

    ALTER TABLESPACE PTSNT054 EXTEND (FILE 'D:\DB2_data\DMSFILES\PTSNT054.DBF' 50000)

    Thanks in advance for any advice. As I said before I'm rather new to DB2, so I want to be sure i'm not missing anything. Thanks Marcus_A for the informative reply, from reading it I think i would be better off with n_i's suggestion with extending the existing the container.

    Thanks again for any advice or suggestions on anything i might be missing.

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by samalex01 View Post
    Thanks Marcus_A for the informative reply, from reading it I think i would be better off with n_i's suggestion with extending the existing the container.
    I just assumed (incorrectly) that the new space was on a different disk. Why don't you just alter the tablespace to AUTORESIZE YES? Take a look at the ALTER TABLESPACE command in the doc.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Feb 2013
    Posts
    31
    Quote Originally Posted by Marcus_A View Post
    I just assumed (incorrectly) that the new space was on a different disk. Why don't you just alter the tablespace to AUTORESIZE YES? Take a look at the ALTER TABLESPACE command in the doc.
    Thanks, but my same question goes as before, are there any caveats to doing this? Is it just run the command to set the size or make the change and that's it? Or are there other things we'd need to monitor or take into consideration. I'm about to start reading-up more on this, but most posts and articles, even in IBM Red Books or IBM's site say how to do these things but they don't say much about problems or considerations. Just curious from the experts which would be best given the info I posted in my first post on this thread. Expand Container, Add another container, or set Container to auto-grow? This seems to be one of those questions that can be answered with experience, and that's something I'm just starting to get with DB2

    Thanks

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by samalex01 View Post
    Thanks, but my same question goes as before, are there any caveats to doing this? Is it just run the command to set the size or make the change and that's it? Or are there other things we'd need to monitor or take into consideration. I'm about to start reading-up more on this, but most posts and articles, even in IBM Red Books or IBM's site say how to do these things but they don't say much about problems or considerations. Just curious from the experts which would be best given the info I posted in my first post on this thread. Expand Container, Add another container, or set Container to auto-grow? This seems to be one of those questions that can be answered with experience, and that's something I'm just starting to get with DB2

    Thanks
    The only concerns is that if the tablespace is set to AUTORESIZE, it may grow without you knowing about it and fill up the disk and impact other users of that same disk, and it is possible you would rather not let that happen. You should look at the syntax for ALTER TABLESPACE and look at all the options available in conjunction with AUTORESIZE that might satisfy an concerns you may have. If you carefully read the doc, you should be able to figure out what option is best for you.

    If you are really concerned about "best practices" I would recommend upgrading to 9.7 as soon as feasible.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    The only concern when extending a container is reaching the container or tablespace size limit. Look up SQL Limits in the manual to see what it might be for your page size. Other than that, there's nothing extra to do after extending the container.

    A good reason to use multiple containers is when you can place them on physically different devices, thus enabling parallel I/O operations. In all other cases a single container is sufficient.
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

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