Hi,

one of my customers has the idea to use just a tablespace with 32K pagesize for the creation of all tables to be able to add as much columns he needs during customizing the database without running into the problem to move the table to a different tablspace.
Does anybody know which disadvantages this might have regarding space, merory usage and performance ?

Regards Dirk

1- each page can accupay 255 rows, if your row lenght is small you will lose a lot of spaces
2- in point of performance if 32 page zise is extra large for your environment is not good
3- in point of memory I do not think so is related to this matter.

Having 32K pagesize is not a direct impact on system memory, but it is on the usage of bufferpool memory. If the 255 rows could fit into a 4K page, 32 K of bufferpool memory will have 255*8 records. If your pagesize is 32K, then only 255 records are in memory. Hence, you may need more physical I/Os.

Cheers

Sathyaram

As a general rule, use 4K for OLTP databases (each SQL statement affects a small number of rows that are accessed via indexes) and 32K for data warehouse databases (data often accessed via table scans).

One exception would be if the row length was too large for 4K in an OLTP system, you obviously need to make the page size larger. The other exception was mentioned above, regarding the maximum 255 rows per page in a table.

Hi,

thank you all for answers.
This is useful information.

Regards Dirk

Hi Dirk,

I sounds like your customer is using the (in my opinion) misguided "design as you go" iterative approach to db design. My sympathy to you for having to deal with this type of mindset. I have seen far too many projects ruined by poor database ad-hoc design and "customization." Oh well...

As for 4, 8, and 32 KB pages, our company uses a 3rd party vendor supplied db that uses only 32K pages. It's a complex OLAP db, but many of the tables have OLTP characteristics. While some tables use the 32 K pages well, most would be better off with 8 or 4 K pages.

I've been working with this database for about a year now. I no longer bother running reorg checks on this database because each page it is always 50% or less filled on the smaller tables. Since only 255 rows can fit per page and the rows are tiny, there is a lot of wasted space.

Performance was poor too until I convinces the vendor to allow us to reduce the page size of some of the larger and critical tables to a more appropriate 8 Kb size. SQL run times on some of the largest extremely complex queries were reduced from 12 minutes to about 1 minute. That's pretty significant just for changing page size from 32 to 8 K. They also told us that we would be allowed to change the smaller 32 K tables to 4 K. Why the vendor didn't do this up front is unknown. I guess they had the same mindset as your customer has (i.e. make it generic 32 K, then design and build as you go.) The result of this attitude can be wasted space and slow performance – your mileage may vary.

Best of luck.

Steve
IBM Certified LUW DB2 DBA