Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2015
    Posts
    2

    Unanswered: Question about DB2 and sizes on disk

    Hi there,

    I'm farely new to DB2, but trying to learn its greatness. Soon I will be attending some courses but for now I will have to do with this forum and some googling. However, the latter didnt yield any results, thus trying it here.

    Im running DB2 10.1.2 on RHEL 6.6 and I have a problem to solve:
    I have, for instance, 1 tablespace which has 3 containers on 3 different partitions on my RHEL machine. Data1/data2/data3.
    The containers are all the same size. However, on disk Data1 is nearly full, but data3 is only 25% used.

    What I'm trying to wrap my head around is this: How does DB2 determine in which container the data is stored. And how does it decide, when it has to extend the tablespace, where to extend it. Does it look on OS level on which disk the most space is available? Or does it do so randomly?

    Thanks for reading/replying!

  2. #2
    Join Date
    May 2012
    Location
    Canberra, Australia
    Posts
    176
    Provided Answers: 6
    Can you post the o/p of db2 "list tablespaces show detail" please. If there are truly three containers then they should be balancing. Another question - are the three filesystems completely on separate physical disk?
    Andy

    "All parts should go together without forcing. You must remember that the parts you are reassembling were disassembled by you. Therefore, if you can’t get them together again, there must be a reason. By all means, do not use hammer.” — IBM maintenance manual, 1975 "

  3. #3
    Join Date
    Oct 2015
    Posts
    2
    Yes. There are 3 containers. And the 3 partitions are physicly mounted. Database is DMS by the way.

    For example tablespace id 15:

    Tablespace Containers for Tablespace 15

    Container ID = 0
    Name = /data/db2inst1/NODE0000/PROD/DB_SP_4K
    Type = File
    Total pages = 104603040
    Useable pages = 104602800
    Accessible = Yes


    Container ID = 1
    Name = /data2/db2inst1/NODE0000/PROD/DB_SP_4K
    Type = File
    Total pages = 104603040
    Useable pages = 104602800
    Accessible = Yes

    Name = /data3/db2inst1/NODE0000/PROD/DB_SP_4K
    Type = File
    Total pages = 104603040
    Useable pages = 104602800
    Accessible = Yes
    Container ID = 2

    && your command.


    Tablespace ID = 0
    Name = SYSCATSPACE
    Type = Database managed space
    Contents = All permanent data. Regular table space.
    State = 0x0000
    Detailed explanation:
    Normal
    Total pages = 90112
    Useable pages = 90108
    Used pages = 82804
    Free pages = 7304
    High water mark (pages) = 82804
    Page size (bytes) = 4096
    Extent size (pages) = 4
    Prefetch size (pages) = 4
    Number of containers = 1

    Tablespace ID = 1
    Name = TEMPSPACE1
    Type = System managed space
    Contents = System Temporary data
    State = 0x0000
    Detailed explanation:
    Normal
    Total pages = 1
    Useable pages = 1
    Used pages = 1
    Free pages = Not applicable
    High water mark (pages) = Not applicable
    Page size (bytes) = 4096
    Extent size (pages) = 32
    Prefetch size (pages) = 32
    Number of containers = 1


    Tablespace ID = 15
    Name = DB_SP_4K
    Type = Database managed space
    Contents = All permanent data. Large table space.
    State = 0x0000
    Detailed explanation:
    Normal
    Total pages = 238928640
    Useable pages = 238927920
    Used pages = 171780240
    Free pages = 67147680
    High water mark (pages) = 171780240
    Page size (bytes) = 4096
    Extent size (pages) = 240
    Prefetch size (pages) = 240
    Number of containers = 3

    Tablespace ID = 16
    Name = DB_SP_32K
    Type = Database managed space
    Contents = All permanent data. Large table space.
    State = 0x0000
    Detailed explanation:
    Normal
    Total pages = 43536620
    Useable pages = 43536510
    Used pages = 32627280
    Free pages = 10909230
    High water mark (pages) = 32627280
    Page size (bytes) = 32768
    Extent size (pages) = 30
    Prefetch size (pages) = 30
    Number of containers = 3

    Tablespace ID = 17
    Name = DB_SP_TMP_32K
    Type = Database managed space
    Contents = System Temporary data
    State = 0x0000
    Detailed explanation:
    Normal
    Total pages = 1243800
    Useable pages = 1243710
    Used pages = 60
    Free pages = 1243650
    High water mark (pages) = 60
    Page size (bytes) = 32768
    Extent size (pages) = 30
    Prefetch size (pages) = 90
    Number of containers = 3
    Minimum recovery time = 2015-02-09-20.18.17.000000

  4. #4
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    start reading from here:
    Database managed space

    Useful citations:

    -- On how db2 places data
    The database manager uses striping to ensure an even distribution of data across all containers. This writes the data evenly across all containers in the table space, placing the extents for tables in round-robin fashion across all containers.
    --

    -- On what happens if some container is full due to various reasons
    If any container is full, DMS table spaces use available free space from other containers.
    --
    Regards,
    Mark.

  5. #5
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    -- On what happens if some container is full due to various reasons
    If any container is full, DMS table spaces use available free space from other containers.
    this is not what I can read
    if a tablespace keeps different containers the tablespace is limited to the size of the smallest container for each container
    eg cont1 1G cont2 2G cont3 3G : total size of tablespace is 3x1G=3G the other space is lost
    or you have to define a new stripe when adding container
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  6. #6
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Quote Originally Posted by przytula_guy View Post
    this is not what I can read
    if a tablespace keeps different containers the tablespace is limited to the size of the smallest container for each container
    eg cont1 1G cont2 2G cont3 3G : total size of tablespace is 3x1G=3G the other space is lost
    or you have to define a new stripe when adding container
    It can be true for a SMS tablespace considering a "container size" as a free space in the file system where this container resides.
    But this is not true for DMS tablespaces. There can't be lost space in the DMS container due to different size of other containers (if you don't add containers to a new stripe set, of course).
    Can you provide a link where you read it?
    Rebalancing DMS containers
    Example 1 shows the situation when you have different containers sizes. There is no lost space there...
    Last edited by mark.b; 10-16-15 at 11:01.
    Regards,
    Mark.

  7. #7
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    I will find the info : it is even a question in the certification exam
    rebalance is for the case where you added a container in the same stripe and want to re-distribute the data
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  8. #8
    Join Date
    Nov 2009
    Posts
    21
    Provided Answers: 1
    Hello guys... Just thought of some other possibility also.. in this scenario... since the database was created for.. DB2 10.1.2... it should be good idea to see.. if database is enabled for automatic storage by default...

    db2look -d <dbname> -createdb -e see if the database is automatic storage YES. If yes then its good to check the AUTOMATIC STORAGE YES
    ON clause.. and make sure.. the database is spread across 3 file systems....


    Thanks

    Surgeon

Posting Permissions

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