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.

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

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.

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.

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?

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

Thanks Andy. I got it now.

Really helpfull information.
Thanks all again.

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.

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.