DB2 Table Partitioning Summary

[Former Member]

Table Partitioning – What is this ?

Table partitioning is a data organization scheme in which table data is divided across multiple storage objects, called partitions.

Partition by Range (PBR)

We can specify a range for each data partition when we create a partitioned table. A partitioned table uses a data organization scheme in which table data is divided across multiple data partitions according to the values of the table partitioning key columns of the table. The ranges specified for each data partition can be generated automatically or manually.

Partition-by-growth (PBG) table spaces let you partition according to data growth, which enables segmented tables to be partitioned as they grow, without the need for key ranges. These are best used when a table is expected to exceed 64 GB and does not have a suitable partitioning key for the table. DB2 manages PBG table spaces and automatically adds a new partition when more space is needed to satisfy an insert. The table space begins as a single-partition table space and automatically grows, as needed, as more partitions are added to accommodate data growth.

Table Partitioning – Why is this needed?

No Table in DB2 V9 can grow more than 64GB – DB2 Limitation.

Once Table reaches 64 GB – No more Data inserts are possible.

Due to high data volumes and limited Short Term Data Archiving opportunities for Business data.

Proactively identify and mitigate issues at early point in the life cycle to reduce outage and issues later.

Will take less time now – when tables are smaller.

Requires downtime – Based on the current size of the table.

Partition Options :

Partition By Range

Require extensive data analysis to define the limit keys

All Indexes can be partitioned.

Too Many Partitions – will hamper Index performance – unless Indexes are properly used.

Requires Downtime to convert Tables to Partition by Range

If all indexes are also partitioned – DBA can Reorg one partition at a time

Partition By Growth

Less upfront Analysis is needed

Indexes cannot be partitioned.

Requires Downtime to convert Tables to Partition by Growth

This is a default option from SAP BASIS 7.20.

Once Table grows really big , the table can never be Reorged and can only be Runstated.

T Code SE14 is used to check details of tablespace and partition details.