Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2011
    Posts
    87

    Unanswered: DB2 Tablespace page szie

    Hi,
    we have two version of databases running on windows.
    db2 v8.2 and db2 v9.7 fp 3a.

    in v8.2 there is a dms tablespace with 8K page. Only one table in this TBS.
    the average row length is 165 bytes. how do I calculate that we are wasting any space or not?

    In v9.7 same tablespace with 4K page. again the row length is 165 bytes.
    Again I need to see this will fit correctly into the page.

    Please advise.

    Thanks.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You need the cardinality of the table and the number of pages used by the table. This will give you the rows per page. Then multiply by the average row length then subtract that result from the page size. That is about how much wasted space you will have per page.

    Andy

  3. #3
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Depends on whether or not you will use the row-compression feature in v9.7
    or any simpler compression techniques.
    You don't mention whether your workload is DSS/BI or OLTP.
    For OLTP you might want fewer rows per extent, but for DSS perhaps the opposite.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by db2mtrk View Post
    Hi,
    we have two version of databases running on windows.
    db2 v8.2 and db2 v9.7 fp 3a.

    in v8.2 there is a dms tablespace with 8K page. Only one table in this TBS.
    the average row length is 165 bytes. how do I calculate that we are wasting any space or not?

    In v9.7 same tablespace with 4K page. again the row length is 165 bytes.
    Again I need to see this will fit correctly into the page.

    Please advise.

    Thanks.
    With a row size of 165, you can not possibly be wasting any amount of space that is even worth worrying about, regardless of page size.

    What is more import, is how the data is typically accessed and how many rows are in the table. If the data is almost always accessed a few rows (or one row) at at time via an index, 4K or 8K page size is fine. If the table has a large number of rows which are typically accessed via table scan, then larger page sizes are better, maybe even 32K.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Nov 2011
    Posts
    87
    Thanks so much for your comments.
    yes, the workload is OLTP.

    what i heard is, only 255 rows can store in a page. It means only 32 bytes data can store in a row on 8K page. in that basis, how can I work out that I am wasting any page?

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    The MAXIMUM number of rows per page is 255 (for a regular tablespace). That does not mean that the rows have to be 32 bytes in length. If you rows, like yours are 165 bytes, then you will have approximately 49 rows per page. Your unused space per page would be less than 80 bytes.

    Andy

  7. #7
    Join Date
    Nov 2011
    Posts
    87
    Thanks Andy. I got it now.

    Really helpfull information.
    Thanks all again.

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    For newly created tablespaces in 9.7 the default is LARGE tablespace, which is not limited to 255 rows per page. For a variety of other reasons, I would recommend migrating any tables created in old tablespaces prior to 9.7 to a newly created 9.7 LARGE tablespace.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  9. #9
    Join Date
    Nov 2011
    Posts
    87
    Thanks Marcus.
    Any db2 tools or commands available to migrate the v8.2 TBS to V9.7 ?

    I think only way to migrate the TBS is, Alter to LARGE type then REORG the tables and Indexes.

    OR, create new LARGE TABLESPACE then move all the tables/indexes using ADMIN_TABLE_MOVE funcation.

    OR Export and Import.

    Our system is almost 24/7, also HADR is in place. however we have 12 hour weekly maintenance windown. So I will be able to use this window for this work.

    Which option is more stright forward ?

    Thanks.

Posting Permissions

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