Tuesday, December 08, 2015

Easily Convert to Table-Controlled Partitioning

Up through DB2 V8 for z/OS, the only way to control partitioning of DB2 table spaces was by using a clustering index that specified the range of key values for each partition. With V8, though, DB2 adds the ability to specify the partitioning criteria in the CREATE TABLE specification. This is known as table-controlled partitioning and it is the preferred method for creating (non-Universal) partitioned table spaces. With table-controlled partitioning you can cluster on a different column (or set of columns) than you are partitioning on. Furthermore, you can make changes such as dropping a partitioning index or creating a table in a partitioned table space without defining any indexes at all.


But given the long history of DB2, many existing partitioned table spaces are index-controlled. 

Fortunately, there is a quick-and-dirty technique that you can use to easily convert from index-controlled to table-based partitioning. Simply follow these steps:

  • Identify the index-controlled partitioned table space you wish to convert
  • Convert the clustering index on the table to NOT CLUSTER using ALTER INDEX. (Alternately, you could drop the clustering index, but I wouldn’t recommend that unless you no longer need that index at all.)
  • Convert the index back to CLUSTER, again using ALTER INDEX
Voila! DB2 will have converted your table space to table-controlled partitioning.



Note: DB2 will also convert from index-controlled to table-controlled partitioning if you use ALTER TABLE to add a new partition, change a partition boundary, or rotate a partition to last on an index-controlled partitioned table space. But these are more intrusive methods than simply altering the index from clustering to non-clustering and back again.

2 comments:

Anonymous said...

Hello,

I'm doing some research into converting some tablespaces into PBR and found this article helpful. I just wanted to ask about your other blog at

http://www.bmc.com/blogs/its-not-your-daddys-db2-part-2/

IBM's documentation says "You can implement range-partitioned universal table spaces by specifying the NUMPARTS keyword, or both keywords SEGSIZE and NUMPARTS on a CREATE TABLESPACE statement."

In your post you warn "If you only code the NUMPARTS parameter without SEGSIZE, then you will create a traditional partitioned table space."

My question is: Which is correct?

Craig S. Mullins said...

The chart on this page https://www.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/inst/src/tpc/db2z_ipf_dpsegsz.html documents the exact combinations of MAXPARTITIONS, SEGSIZE and NUMPARTS, along with the DPSEGSZ zparm, and what the resulting type of table space will be. Hope this helps!