dear all,

i need advise from you all..

while create a table, how do we decide whether we can place the table in 4k or 8k or 32k tablespace.

is there any calculation involved to decide.

pls advise.

regards

Sanjai

You determine the maximum possible width of a row in the table, add some more bytes to accommodate for future addition of more columns, and then you pick the tablespace size that can contain such rows. The alternative is that you just let DB2 decide which tablespace to pick. Have a look at the documentation for the CREATE TABLE statement, where the algorithm for choosing a tablespace is explained.

Unless you row size is the limiting factor, you would normally use smaller page sizes for OLTP, and larger page sizes for data warehouse applications.

There are two considerations:

1. Memory usage (bufferpools) - If you just need to access one row (or a few rows) for a query that will be retrieved via an index, the entire page must be placed in the bufferpool memory. The smaller the page size, the less memory that is used. This is not a factor if the entire database can fit into your bufferpools and stay resident in memory, otherwise, if you need to use memory to retreive a page, DB2 must kick some other pages out of memory to make room for the new page.

2. Speed of table scans - When you have tables that require a table scan to return the result of a query or to process an update, etc, then DB2 can usually process a table scan faster with a larger tablespace page size. Make sure the prefetch size is set appropriately.