Implementing table partitioning

Table partitioning can be implemented on tables that have high volumes, usually in DSS environments. The typical configuration is to use the sales date for a range. Consider using a data partition for each period, either monthly or quarterly.

Getting ready

Decide on your partitioning range, and create table spaces/buffer pools to support this configuration.

How to do it...

1. Create buffer pools, one for each quarter:

   1st quarter:

   [db2instp@ nodedb21 posp]$ db2 "CREATE BUFFERPOOL BP_POSP_Q1_DSS8K
     IMMEDIATE DATABASE PARTITION GROUP POSP_DSS
     SIZE AUTOMATIC PAGESIZE 8 K"
   DB20000I  The SQL command completed successfully.
   

   2nd quarter:

   [db2instp@ nodedb21 posp]$ db2 "CREATE BUFFERPOOL BP_POSP_Q2_DSS8K
     IMMEDIATE DATABASE PARTITION GROUP POSP_DSS
     SIZE AUTOMATIC PAGESIZE 8 K"
   DB20000I  The SQL command completed successfully.
   

   3rd quarter:

   [db2instp@ nodedb21 posp]$ db2 "CREATE BUFFERPOOL BP_POSP_Q3_DSS8K
     IMMEDIATE DATABASE PARTITION GROUP POSP_DSS
     SIZE AUTOMATIC...