I recently had a conversation with a friend about the additional work they may have made for themselves by misinterpreting one sentence for a keyword when they creating a whole pile of their partition-by-growth universal table space……
I looked up the keyword in both the DB2 10 SQL Reference and DB2 9 SQL Reference manuals; both description where exactly the same.
Here’s the deal.. better yet, here’s the W A R N I N G ! ! ! ! ! This is something to be careful of as you create new partition-by-growth table spaces.
Required when creating a partition-by-growth table spaces is the MAXPARTITIONS keyword. This keyword lets DB2 know how long you will allow a partition-by-growth table spaces to grow. In other words, what is the maximum number of partitions you’ll let it expand to. What happened next, is a two step process.
First, they decided to forgo all the messing around with modifying MAXPARTITIONS as the number of partition in use increased in production so it was simply set to 4096 (the max value) at the initial CREATE TABLESPACE time. Setting MAXPARTITIONS to 4096 in theory meant it would never have to changed (modified, increased) again.
Sometimes after creating a few hundred of these partition-by-growth table spaces, even if only a few partitions are actually allocated, an increase in DB2 agent storage can be observed.
It turned out that DB2 allocates the storage necessary per thread for the partition-by-growth table space based on the value set in MAXPARTITIONS. Using a value of 4096 caused a good chunk of storage to get initially allocated for each thread.
It seemed the logical solution to removing the storage alerts was to reduce the value of MAXPARTITIONS.
From both the DB2 9 and DB2 10 SQL Reference manual:
Specifies that the table space is partition-by-growth. integer specifies the
maximum number of partitions to which the table space can grow. integer must
be in the range of 1 to 4096, depending on the value that is in effect for
DSSIZE and the page size of the table space, and must not be less than the
current maximum number of partitions for the table space.
As mentioned earlier, I, just like my friend, reads the above highlighted sentence to mean the number of partition allocated for use, not the number of partitions allowed, limited what MAXPARTITIONS could be altered to.
When the ALTER TABLESPACE … MAXPARTITIONS was attempted to a considerably lower integer value, it resulted in the following error message:
DSNT408I SQLCODE = -644, ERROR: INVALID VALUE SPECIFIED FOR KEYWORD OR CLAUSE
MAXPARTITIONS IN STATEMENT ALTER
DSNT418I SQLSTATE = 42615 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXIATS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 11 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X’0000000B’ X’00000000′ X’00000000′ X’FFFFFFFF’
X’00000000′ X’00000000′ SQL DIAGNOSTIC INFORMATION
It turns out, and this is the entire point of today’s post, you can increased the value of MAXPARTITIONS keyword with the ALTER SQL statement but you CANNOT reduce the value of the MAXPARTITIONS keyword.
The warning will be that if you pick very high values for MAXPARTITIONS, something like 4096, you cannot change it without a drop/create table space.
I have no idea if this particular behavior is the way it will remain, or if there might be a plan in the future to change the ALTER to allow a lower MAXPARTITIONS value. I, like you, will have to watch future APARs for a possible change. Personally, I would be surprised it ALTER was left this way, but again…