Sometimes, an important DB2 for z/OS feature can be out there for years, and yet not be fully appreciated by lots of people in the DB2 user community. Table-controlled partitioning is such a feature. In this blog entry I want to generally set the record straight about table-controlled partitioning, and in particular I want to make sure that mainframe DB2 folks understand a key aspect of this physical database design option that for some reason eludes many users to this day.
First, the basics: table-controlled partitioning refers to a means of range-partitioning the data in a table, so that, for example, sales records for retail store numbers 1-25 go into table space partition 1, records for store numbers 26-50 go into partition 2, records for store numbers 51-75 go into partition 3, and so on. Table-controlled partitioning is so called to distinguish it from index-controlled partitioning, which for years and years was the only way to assign a table's rows to table space partitions based on the value of a user-specified partitioning key: you created a partitioning index for a table, and in the definition of that index you associated table space partitions with ranges of the partitioning key.
That changed with DB2 Version 8 for z/OS, and this brings me to another point of clarification: some people get table-controlled partitioning and universal table spaces a little mixed up, in that they believe that table-controlled partitioning was introduced along with universal table spaces. If you've been under that impression, you're off by one DB2 release. DB2 V8 delivered table-controlled partitioning. DB2 9 delivered universal table spaces. There are two types of universal table space, and one of these, called a partition-by-range universal table space, is conceptually very similar to a DB2 V8 table-controlled partitioned table space (the other type of universal table space is known as partition-by-growth). Those who lump DB2 V8-introduced table-controlled partitioning in with DB2 9-introduced universal table spaces may be under the impression that you cannot convert an index-controlled partitioned table space to a table-controlled partitioned table space until you're running with DB2 10, because that's the release in which a smooth path to universal table spaces from other table space types was provided. In fact, in a DB2 V8 or DB2 9 (or DB2 10) environment you can easily convert an index-controlled partitioned table space to a table-controlled partitioned table space. How is that done? It's as simple as this: for the partitioning index of the index-controlled partitioned table space, issue an ALTER INDEX with a NOT CLUSTER. Bingo -- you've now got a table-controlled partitioned table space.
This simple means of converting an index-controlled partitioned table space to a table-controlled partitioned table space highlights the really key feature of table-controlled partitioning that -- as I mentioned up front -- has escaped the notice of many a DB2 person: data in a table-controlled partitioned table space can be partitioned by one key and clustered within partitions by another key. For a number of mainframe DB2-using organizations, the fact that the partitioning index of an index-controlled partitioned table space had to also be the table's clustering index was a pain. I worked in the early 2000s in the IT department of a financial services company, and I well recall that we had, in our DB2 for z/OS V7 database, a partitioned table space that presented us with a Hobson's choice: we could go with a key that would be good for partitioning (benefiting the performance of high-volume insert operations) but lousy for clustering (hurting query result set retrieval performance), or vice versa. When DB2 for z/OS V8 was announced, and with it the ability to specify data partitioning at the table level (versus the index level) and -- as a consequence -- to specify a clustering index with no dependence on the partitioning scheme, we jumped on that like a cat on a mouse. In my mind, this feature ranked right up there with 64-bit addressing as the MOST valuable capability provided via DB2 V8.
Obviously, not everyone in the DB2 user community shared this assessment of mine, as evidenced by the fact that I still find people who are under the impression that the partitioning key for a table-controlled partitioned table space has to be the table's clustering key. Again, NOT SO. Suppose you have a table for which it is important, for data load and offload purposes, that rows be partitioned by date (so, for example, one week's data goes into partition 1, the next week's into partition 2, and so on). For that same table, good query performance may depend on rows within the date-based partitions being clustered by account number. FINE. NO PROBLEM. Define the table with a specification of the date column as the partitioning key, and create an index, with the CLUSTER option specified, on the account number column. What if this table exists today in index-controlled partitioned form, with the partitioning/clustering index defined on the data column (good for load/offload, not good for result set retrieval)? In that case, you could first issue, as previously mentioned, an ALTER INDEX statement, with the NOT CLUSTER option, targeting the existing partitioning/clustering index on the table. Because the partitioning index on an index-controlled partitioned table space must also be the clustering index on the table, execution of this ALTER INDEX statement will have the effect of changing the table space's partitioning scheme from index-controlled to table-controlled (it will still be partitioned by the date column, but that partitioning information will now be tied to the table definition instead of being tied to an index's definition). Next, if you already have an index defined on the account number column, issue an ALTER INDEX statement, with the CLUSTER option specified, for that index (if an index on the account number column doesn't exist already, create one and include CLUSTER in the CREATE INDEX statement).
Here's another potential benefit you can get by going from index-controlled to table-controlled partitioning for a table space: you might be able to get rid of an index that is useful ONLY for partitioning purposes (i.e., it provides no query access path performance benefit, isn't defined on a foreign key, etc.). For the purpose of illustrating this point, consider an index-controlled partitioned table space that is partitioned on a column, called PARTITION_KEY, that is populated with a value by the program that inserts rows into the table. Not only will users never search on this column, it's probably a stinko clustering key, to boot. Issue the handy-dandy ALTER INDEX statement with the NOT CLUSTER option for this index, and alter some other index on the table to make it the clustering index. After that (and after checking that the index on the PARTITION_KEY column, now no longer needed for partitioning, is not needed for any other reason), drop the index on PARTITION_KEY. You'll save the disk space that had been occupied by the index, and insert and delete operations targeting the table will be more CPU-efficient (due to your having one less index on the table that would have to be maintained by DB2). Remember, the table space is still partitioned by the PARTITION_KEY column -- it's just that you no longer need an index to effect that partitioning scheme.
There you go. Nice capability, eh? Could you take advantage of it? Have you taken advantage of it? I hope so. Thanks for reading.
Is there a simple way to back out a change from index-controlled to table-controlled partitioning, or would that require a table restore? We have a largeish table I'd like to try this on (358 Million rows, 2429K pages) but we're short on space for creating another copy to experiment with.
ReplyDeleteI'm not aware of a way to reverse a change from index-controlled to table-controlled partitioning, short of unloading the table, dropping it (and the table space), re-creating the object in an index-controlled partitioned table space, and re-loading the table. I'm not aware that going to table-controlled partitioning will introduce any kind of problem. The one thing I can think of for which you might need to watch out is the fact that the limit key of the "last" partition is strictly enforced for a table-controlled partitioned table space. This being the case, when an index-controlled partitioned table space is converted to table-controlled partitioning, the limit key of the "last" partition will be set to the highest possible value for an ascending key (or the lowest possible value, if the partitioning key is descending) -- just in case there are rows in the "last" partition of the index-controlled partitioned table space that have a partitioning key value that is greater than the limit key of the "last" partition (which is not strictly enforced for index-controlled partitioned table spaces).
DeleteRobert
Thanks for the quick response and the warning. I don't think we'll have the problem you mention as the "ranges" in our case are single values, which are foreign keys to a "partition control table". I suspect this approach is itself rather old fashioned, so we'll review that at the same time.
DeleteYes, but after converting the table space to table-controlled partitioned, check the limit key value of the "last" partition (LIMITKEY IN SYSTABLEPART). You should see a maximum-possible value there (e.g., a little over 32,000 if the partitioning key is a single-column SMALLINT value). That may not be what you want for the limit key value of the last partition. If it isn't, you can change it to what you want via an ALTER TABLE statement with an ALTER PARTITION clause. Doing that should put the partition in REORG-pending status, which could be removed by running a REORG of the partition with the DISCARD option specified (if no rows in the partition violate your specified limit key value, no rows will be written to the discard data set).
DeleteRobert
I'm a big fan of table-based partitioning, but I've run into a snag. I have an exisiting index-based partitioned tablespace, with the partitioning/clustering index defined on (col1, col2, col3). My limitkey only specifies values for col1, while the other 2 columns are there only for clustering reasons. What I'd like to do is:
ReplyDelete1) convert to table-based partitioning
2) partition the table using col1 ONLY
3) cluster on col2, col3
4) later on, define additional DPSIs using col1
If I try to simply convert to table-based partitioning, the PARTITION BY clause now reflects all 3 columns in the old index definition, but I really want it to just reflect col1 (same as the limitkey). This is a 5 billion row table, and I really don't want to drop/recreate, just to change the PARTITION clause. Is there any way to do this without the drop/recreate?
Randy
Randy, even though the table converted to table-controlled partitioning would be partitioned by three columns, if your limit key for the index-controlled partitioned table space only specified a value for COL1 then the implicitly assigned limit key values, for each partition, assigned to COL2 and COL3 should be the highest possible values for those columns; so, of course, the effect -- at least in terms of assignment of newly-inserted rows -- would be the same as if the partition limit key values depended only on COL1. Why do you want the partition limit key to only be COL1? Is it because you want to have one or more partitioning indexes (i.e., indexes on keys that begin with the table's partition limit key) on the table, and indexes starting with COL1 would make sense but indexes beginning with COL1 | COL2 | COL3 would not make sense? Is it because you need DPSIs to include the columns of the partitioning key so that they can be defined as UNIQUE, and having COL1 in the DPSIs makes sense but having COL1 and COL2 and COL3 in the DPSIs doesn't make sense? Whatever your reason for wanting the partitioning key to be just COL1, versus COL1 (different ENDING AT values for each partition), COL2 (ENDING AT value is max value for each partition) and COL3 (ENDING AT value is max value for each partition), I don't see that you can go from a three-column to a single-column partitioning key without a drop and re-create. If you have to drop and re-create, you could minimize data unavailability by creating a copy of the table that is table-controlled partitioned with a single-column partitioning key, then load data from the original to the new table, then get the data in the new table synched with data in the original using a log analysis tool to propagate data changes, then use RENAME TABLE to change the name of the original table to something else, and to change the name of the table-controlled partitioned table to that of the original table (same concept as online REORG).
DeleteBy the way, are you sure you want DPSIs on the table-controlled partitioned table? If you have queries that don't include predicates on the table's partitioning key, such indexes could negatively impact elapsed time for these queries.
When you speak of table-controlled partitioning are you talking exclusively about “Partition by Growth” and “Partition by Range” partitioning or are there other types of partitioning schemes available on DB2 for z/OS? We are using DB2 for z/OS version 9 if that makes any difference. Thanks.
ReplyDeleteBrian, when I refer to "table-controlled partitioning," I'm referring to range-based partitioning that is reflected in the table's definition, as opposed to being reflected in (and controlled by) a partitioning index. Such a table could be a universal table space (range-partitioned and segmented) or a non-universal table space (aka a "traditional" partitioned table space -- introduced with DB2 V8). Partition-by-growth is indeed another type of partitioning for a table space, but it's not what I'm talking about when I refer to "table-controlled partitioning."
DeleteHope that helps to clear up my meaning.
Robert
Is there a way to identify tables that are index partitioned, so we can begin to change them to table based partitioned?
ReplyDeleteSorry about the delayed response!
DeleteYes, there is a way to make this determination. Look for rows in the table SYSIBM.SYSTABLEPART in the DB2 catalog that have a non-blank value in the column IXNAME. When IXNAME is NOT blank, the table space is an index-controlled partitioned table space, and that non-blank value is the name of the table space's partition-controlling index.
Since, of course, SYSTABLEPART contains a row for each partition of a partitioned table space, and a single table space can have a lot of partitions, you may want to code your query to bring back just one SYSTABLEPART row per table space that has a non-blank value in the IXNAME column. You could try pulling only the row for the first partition of each table space (WHERE PARTITION = 1), or code a SELECT DISTINCT query (if you have a lot of table spaces that have a lot of partitions, I'd expect better performance for the query that limits returned rows via a WHERE PARTITION = 1 predicate, versus use of SELECT DISTINCT).
Robert
In your example of partitioning by date, you only have an index on account, so selecting by date only would scan all partitions in your date range, right? And even if you specified other columns (except account), DB2 would still scan the entire partition(s), because there is no good index to use?
ReplyDeleteI did not mean to imply that there would be only one index (on the ACCOUNT column) defined on the table. I was merely pointing out that an index on the partitioning key is not REQUIRED for a table-controlled partitioned table space. You could certainly have an index on such a table's partitioning key if you determined that the index would be beneficial for you. I can guarantee you that there are index-controlled partitioned table spaces out there for which the partition-controlling index is ONLY good for partition-control purposes. When, in that case, the table space is converted to a table-controlled partitioned table, the old partition-controlling index can probably be dropped to beneficial effect, since it's only use was for partitioning prior to the conversion to table-controlled partitioning.
DeleteFor a table in a table-controlled partitioned table space -- as for any table -- you define indexes as needed, balancing the benefits of the indexes (better response times for queries with index-matching predicates) against their costs: more disk space consumed; more expensive inserts, deletes (and updates of indexed columns), and utilities.
Robert
Hi Bob,
ReplyDeleteSay i have the following table columns partitioned by table based on COL1.
Col1 INT
Col2 char(10)
Col3 Char(3)
I have the following partitioned index on table - Col2.
I have a select query - select * from table where col1 = 1 and col2 = 'AAAAAAAAAA'
Do i need to have col1 and col2 as part of index? As what i understand is db2 optimizer automatically knows which part to look at (even though there is no index on partitioned column as in above case)
Will it scan entire index for col2 or it db2 optimizer will know that it has to scan only partition 1 and then use index on col2?
Hello, Bharat.
DeleteFor the situation you've described, DB2 should zero in on the one partition of the data-partitioned secondary index (DPSI) defined on column Col2 that is relevant to the query, because the query has a predicate that references the table's partitioning key (Col1). DB2 will then use that one partition of the DPSI to identify rows for which Col2 = 'AAAAAAAAAA', and will then examine those rows in the table to see which ones have 1 in Col1. This query should be very efficiently executed.
I suppose that if the DB2 optimizer estimates that a relatively high percentage of the rows in the target partition contain the value 'AAAAAAAAAA' in Col2, it might just scan the target partition versus using the associated partition of the DPSI.
Robert
Hi, We are in v11. Is there a way we can drop the partition from 256 to 150 when it is partition by range. It would be great if you can let me know the high-level steps if possible.
ReplyDeleteAt present, the only way to do this would be to unload the table, drop and re-create it with 150 partitions instead of 256, and re-load the table.
DeleteRobert
Hi Robert,
ReplyDeleteIs there any way by which we can find out significant columns in a IX-controlled partitioned table.
Akshay, I am not certain as to what you mean by, "significant columns." Could you clarify?
DeleteRobert