The idea behind table partitioning is that a large table is split into several smaller physical storage objects, the so called (data) partitions or ranges. This concept is transparent to an application, a partitioned table is presented to the application as a single logical object. Splitting large storage objects into smaller ones has several advantages:
- The administration of large tables becomes easier (e.g. due to a partition level REORG)
- The performance of the database may improve because the query optimizer can exclude partitions which are not accessed by a query ("partition elimination")
- Given a partitioning key which is somehow related to the creation time of the table records, operational ("hot") data which is accessed frequently can be separated from older ("aged") data which is accessed rarely. Since partitions can be placed into separate tablespaces this can be used to place aged data on to slower and cheaper disks and operational data on to faster and more expensive disks or SSDs.
Table partitioning is available in DB2 for Linux, UNIX, and Windows since version 9.1. The following operations on partitioned tables are supported:
- The ATTACH PARTITION allows you to add an existing table as a partition to a partitioned table. This is also called "roll-in".
- The DETACH PARTITION allows you to remove an existing partition from a partitioned table and to convert this partition into a normal table. This is called a "roll-out".
- The ADD PARTITION allows you to add new empty partitions to a partitioned table.
As of DB2 9.7 indexes can also be partitioned, a partitioned index is usually referred to as a "local" index (vs. a "global", non-partitioned index). Local indexes should be preferred whenever possible. Following an ATTACH or DETACH of a partition, each global index is no longer valid because data was removed or added to the partitioned table and a so-called "asynchronous index cleanup" must take place before the global index is valid again. This index cleanup is scheduled by DB2 automatically. For local indexes an index cleanup is not necessary.
Table Partitioning in SAP Systems
The concept of table partitioning is widely used in OLAP environments. Here, tables often have columns which contain a week, month, or year field. Usually the tables are partitioned based on these directly time-related columns. A typical, slightly simplified example is the partitoning of a SALES table based on a year field so that all sales of a specific year are contained in one partition.
SAP systems on DB2 for Linux, UNIX and Windows however use Multidimensional Clustering (MDC) in SAP NetWeaver BW installations. While MDC does not divide a table into separate storage objects, it allows you to group data that is related to one or more dimensions together in so called blocks. MDC has many benefits in a BW environment and is only available with DB2 for Linux, UNIX, and Windows.
Table range partitioning can still be applied in a standard SAP OLTP environment, thus providing the benefits mentioned above. Partitioning an existing table has been a manual DBA action so far, and you can find details about how to perform table partitioning in the SDN article "Using DB2 for Linux, Windows, and UNIX Table Partitioning in SAP Systems" and in SAP note 1379362.
You can now use a new tool, the DB6 Partitioning Administrator, that was developed by the DB2 for Linux, UNIX, and Windows porting team at SAP. It calculates a suitable partitioning for some of the largest tables in a typical AS ABAP OLTP environment.
The DB6 Partitioning Administrator
Partitioning a large table in (more or less) equal-sized partitions can be considered as a three- step approach:
- A suitable partitioning clause (which is part of the CREATE TABLE statement) must be calculated.
- The existing non-partitioned table must be actually partitioned.
- As an ongoing tasks, there should be some kind of partition maintenance. New records are inserted into the table, and, therefore, new ranges are required. On the other hand, old data is archived, and partitions which are nearly empty can be merged together.
The DB6 Partitioning Administrator can now be used to perform step 1. It takes the name of the table to be partitioned, the client in which the data of the table resides, and the desired partition size as input and calculates a CREATE TABLE statement with a table partitioning clause. To deal with forthcoming data, a number of empty partitions (spare partitions)are also added. You also have the option of creating separate tablespaces for each partition or leaving the partitioned table in one (regular, index and long-) tablespace only. The following is a screenshot from the entry screen:
A subset of the primary key (PK) of each table is used as partitioning key. The tables which we consider with this tool use numbers of SAP number ranges for the creation of their PK. These numbers are ascending and ,therefore, indirectly provide a time-related partitioning criteria. There is much more to say about this new tool, so I'd like to invite you to have a look at the current version of the documentation.
Directly after executing the DB6 Partitioning Administrator you can use DB6CONV Version 5.06 or higher to apply the table partitioning via an online table move.
Based on customer feedback on this new tool we will continue to work on it to extend its functions with regard to a regular partition maintenance as mentioned in step 3 above.
If you are interested in our new DB6 Partitioning Administrator and would like to test it, feel free to contact me via EMail.
- SAP Managed Tags:
1 Comment
