Creating a dbspace with a non-default page size

You can specify a page size for a standard or temporary dbspace if you want a longer key length than is available for the default page size.

About this task

The root dbspace uses the default page size. If you want to create a dbspace with a different page size, the size must be an integral multiple of the default page size, and cannot be greater than 16 KB.

For systems with sufficient storage, the performance advantages of a larger page size include:
  • Reduced depth of B-tree indexes, even for smaller index keys.
  • Decreased checkpoint time, which typically occurs with larger page sizes.
Additional performance advantages occur because you can:
  • Group on the same page long rows that currently span multiple pages of the default page size.
  • Define a different page size for temporary tables, so the temporary tables have a separate buffer pool.

A table can be in one dbspace and the index for that table can be in another dbspace. The page size for these partitions can be different.

Procedure

To create a dbspace with a non-default page size:
  1. If you upgraded from a version before version 10.00, run the onmode -BC 2 command to enable the large chunk mode.
    By default, when HCL OneDB™ is first initialized or restarted, HCL OneDB starts with the large chunk mode enabled.
  2. Optional: Create a buffer pool that corresponds to the page size of the dbspace. You can use the onparams utility or the BUFFERPOOL configuration parameter.

    If you create a dbspace with a page size that does not have a corresponding buffer pool, HCL OneDB automatically creates a buffer pool using the default values for the BUFFERPOOL configuration parameter as defined in the onconfig file.

    You cannot have multiple buffer pools with the same page size.

  3. Define the page size of the dbspace when you create the dbspace. You can use the onspaces utility or ON-Monitor.

Example

Tip: If you use non-default page sizes, you might be required to increase the size of your physical log. If you perform many updates to non-default pages you might require a 150 - 200 percent increase of the physical log size. Some experimentation might be required to tune the physical log. You can adjust the size of the physical log as necessary according to how frequently the filling of the physical log triggers checkpoints.