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.
Comments