Blog
DB2utor

Categories

March 05, 2013

Is Classic Index-Controlled Partitioning Still Supported in DB2 10?

Back in October 2009 I said that, in the near future, all DB2 data would be stored in Universal Table Spaces (UTSs).

I hadn't given this much thought until recently. I had an old DDL script for creating a partitioned table using classic index-controlled partitioning. When I ran this script on a DB2 10 NFM environment, I received the following error:

DSNT408I SQLCODE = -662, ERROR:  A PARTITIONED INDEX CANNOT BE CREATED

         ON A TABLE SPACE, OR A TABLE SPACE CANNOT BE INDEX-CONTROLLED.

          TABLE SPACE CLASSP2, REASON 1

Reason 1:  The CREATE INDEX statement contains the PARTITION BY clause for index-controlled partitioning, but the table on which the index is created has one of the following characteristics:                

       The table is not in a partitioned table space.                

       The table is in a partition-by-growth or a range-partitioned universal table space.                                       

Initially I thought the day had finally arrived where DB2 no longer supported index-controlled partitioning. Then I wondered. It was weird that I hadn't heard any recent announcements about this. Could it just be an error in the script? 

Sure enough, when I checked the syntax diagram for CREATE TABLESPACE in the SQL Reference Guide, I noticed the change. The CREATE TABLESPACE statement now has three different specifications (partition-by-growth, partition-by-range and SEGSIZE). I thought SEGSIZE was for segmented table spaces, but once I read about each specification, I learned that SEGSIZE 0 and NUMPARTS must be specified when creating an index-controlled table space.

So my predication has yet to be realized. DB2 continues to support the classic index-controlled partitioned table space, though through a different set of parameters. Hopefully this helps those who are still creating DDL manually. For those using a catalog management tool, these parameters are automatically used in the create syntax.