It’s a legitimate question that hopefully you are giving some serious thought to every time you create a new table space. If you are on DB2 9, we know you’re not using simple table spaces… so glad we’re finally seeing the demise of the simple table space. In fact, even the DB2 catalog will stop using simple table spaces in DB2 10 and migrate to partition-by-growth universal table spaces…..
Then there are segmented table spaces. If you are considering segmented AND you are running DB2 9 new function mode (NFM), you need to update your thinking to partition-by-growth universal table spaces. Oh ya, I know… there’s that thing that says you can only have one table per universal table space. However, I was doing a recovery seminar at a major conference and I clearly remember strongly recommending one table per table space for a number of reasons… that seminar was in 1994. So hopefully, you don’t have to worry about multiple tables per segmented table space getting in your way. Keeping all the previous in mind, I think you should be giving serious consideration to converting all of your segmented table spaces over to partition-by-growth universal table spaces.
Now your thinking of the slide in my conference presentation that states the migration path from segmented to partition-by-growth table space is an unload, drop, re-create, and load. That’s kind of a true statement… only if you are still running DB2 9. However, DB2 10 will aloow you to switch to a partition-by-growth table space from a segmented table space using an ALTER TABLESPACE statement. Pretty cool, right… of course there is one small catch to even the DB2 10 solution… the segmented table space can only have one table in it (see previous paragraph). Now if you have been following my long standing recommendation for one table per table space for recovery reasons, you’re going to be all set. But what happens if you’re still defining multiple tables per segmented table space? Then you’re back to performing the “unload, drop, re-create, and load” migration process.
BTW, whenever you need to create a table space:
- that could grow significantly in size but you don’t know when it will grow or by how much it might grow,
- you have the need to create a table space that needs to be very large (more than 64 GB) but you have no idea what would make a good partitioning key,
then you have two examples of excellent reasons why you would want to create a partition-by-growth table space.
Partition table spaces aren’t quite as easy… I can’t just recommend that you simply migrate from classic partitioning to range-partitioning because it’s such a great idea. I need to ask a few questions about you partition table space first. Do you do a lot of insert processing? Are you using VARCHAR columns? What about mass deletes, are they of interest? If you’ve answered yes to one or more of the previous question, then you have a partitioned table space that is a good candidate to be converted to a range-partitioned universal table space.
What if you have a classic partitioned table space (that’s what we are going to start to call the old style partitioned table space to make easy to tell the two apart in a conversation) that is read only… updates, inserts, and deletes never happen again… it is only refreshed via the LOAD utility… something like a data warehouse table… then there probably isn’t a lot of advantage to go through the trouble of moving it to a range-partitioned universal table space in DB2 9. Maybe in DB2 10 with the simplified migration method (ALTER) you might reconsider migrating them then. For me, it just doesn’t seem like it would be worth the cost of the outage.
So, in summary (plus some)…
Simple table space are gone (or going)… at least you can no longer create them.
Segmented table spaces in my opinion should be moved to partition-by-growth table spaces if you can handle the outage in DB2 9, but definitely once you move to DB2 10 and have the ALTER command and REORG to do all the heavy lifting for you.
Segmented table spaces are no longer the default in DB2 10. Partition-by-growth universal table spaces are.
If you ALTER a segmented table space adding MAXPARTITIONS, it becomes a partition-by-growth universal table space. (Careful. This ALTER will cause plans and packages that use the table space to be invalidated.)
DB2 10 will allow an ALTER… ADD PART up to the number specified on the MAXPARTITIONS keyword on a partition-by-growth universal table space. DB2 9 does not allow an ALTER.. PART for a partition-by-growth universal table space
If you create a table space with NUMPARTS and SEGSIZE you are going to get a range-partition table space.
If you ALTER a table space that was created with NUMPARTS (classic partition table space) and add SEGSIZE to the table space definition, you get a range-partition table space (after the REORG).
DB2 10 will have a DSNZPARM, DBSEGSZ on the DSN6SYSP macro, so you can specify the default SEGSIZE.
Range-partitioned table spaces are the default for partitioning in DB2 10. (see above)
DB2 10 will once again allow an ALTER … MEMBER CLUSTER.
DB2 10 allows the ALTER … MEMBER CLUSTER for both partition-by-growth and range-partition table spaces.
All of these DB2 10 ALTER changes have added lots of new values to the SYSIBM.SYSCOPY table.
Side fact: DB2 10 will allow table spaces in the WORKFILE data base to be defined as partition-by-growth table spaces.
Hash tables added in DB2 10 are only available with a universal table space.
That’s all I wanted to say… This is kind of like a review for me… I was going over my IOD 2010 presentation on universal table spaces for Las Vegas today when writing this post as I looked over my slides came to mind.
Trivia: The IBM Certified Database Administrator for DB2 10 for z/OS (Exam 612) will be offered at no charge at IOD 2010 in Las Vegas later this month.
….While writing today’s blog entry, I was listening to Peter Muller‘s 2008 release “The Flow”. I’m not sure why I’ve not come across this bassist before; he’s terrific. He plays a style of bass that I very much enjoy listening to, very funky. I’ll be tracking more of his music this weekend.