DB2-Managed Disk Space Allocation - An Overlooked Gem?
Last week I was again teaching a DB2 for z/OS database administration course, this time for the half of the client's DBA team that minded the store while their colleagues attended the prior week's class. As I mentioned in my last blog entry, the organization had recently completed the migration of their production system to DB2 for z/OS Version 8, so we spent a good bit of class time discussing new features and functions delivered in that release of the product. In such a situation, it's always interesting to see what really grabs the attention of class participants. This time, a top attention-grabber was the automatic data set extent-size management capability introduced with DB2 V8 and enabled by default with DB2 9 for z/OS (more on this DB2 9 change momentarily). Facial expressions during our talks on this topic communicated the unspoken question, "DB2 can do that?"
It does seem almost too good to be true, especially to a DBA who has long spent more time than desired in determining appropriate primary and - especially - secondary space allocation quantities for DB2-managed (i.e., STOGROUP-defined) tablespace and index data sets. As you're probably aware, a non-partitioned tablespace (this will typically be a segmented tablespace) can grow to 64 GB in size by occupying thirty two 2 GB data sets; however, the first 2 GB data set has to fill up before DB2 can go to a second (and the second has to fill up before DB2 can go to a third, and so on), and DB2 won't fill up that 2 GB data set if it runs into the data set extent limit (255 extents). The same applies to non-partitioned indexes, except that the data set size is not set at 2 GB - rather, it's determined by the PIECESIZE specification on a CREATE INDEX or ALTER INDEX statement. In either case, you want to set the primary and secondary space allocation quantities (PRIQTY and SECQTY on the CREATE or ALTER statement for the object) so that the object can reach the data set size limit (and thus expand to multiple data sets) without first running into the aforementioned limit on the number of extents for a data set. A partition of a partitioned tablespace or partitioned index isn't going to spread across multiple data sets, but you still want to be able to reach the maximum data set size (specified via DSSIZE on the CREATE TABLESPACE statement) before hitting the data set extent limit.
What should your PRIQTY and SECQTY amounts be? Too small, and you might hit the extent limit before reaching the desired maximum data set size. Too large, and you might end up with a good bit of wasted (allocated and unused) space in your tablespaces and indexes. Even if reaching a maximum data set size is not an issue (and it won't be for smaller tablespaces and indexes), you still want to strike a balance between too many extents and too much unused space. Multiply this by thousands of objects in a database, and you've got a big chunk of work on your hands.
Enter the "sliding scale" secondary space allocation algorithm introduced with DB2 for z/OS V8. Here's how it works: First, set three ZPARM parameters as follows (all are on the DSNTIP7 panel of the DB2 installation CLIST):
Pretty great, huh? No muss, no fuss with regard to space allocation for DB2-managed data sets. Best of all, it works. When you let DB2 handle data set extent sizing, it is highly unlikely that you'll hit the data set extent limit before reaching the maximum data set size, and the start-small-and-slowly-increase approach to secondary allocation requests keeps wasted space to a minimum. What I find interesting is the fact that many DB2 people don't know about this great DBA labor-saving device. In a recent thread on the DB2-L discussion list, Les Pendlebury-Bowe, a DB2 expert based in the UK, referred to DB2-managed data set space allocation as "a real success story that never seems to get much press." In that same thread, DB2 jocks Myron Miller (a Florida-based consultant) and Roger Hecq (with financial firm UBS in Connecticut) added their endorsements of DB2-managed space allocation. Myron noted that with a specification of PRIQTY -1 and SECQTY -1, "I never have to even worry about the number of rows at any time in the tablespace" (and keep in mind that the -1 values are for ALTER TABLESPACE or ALTER INDEX - as noted previously, just don't specify PRIQTY and SECQTY on CREATE statements to enable DB2 management of space allocation for new objects). Roger stated that he's been pleased to "let DB2 do all the work" related to disk space management, and that his organization has "not had any issues" with their use of this DB2 capability.
[By the way, if you are not a DB2-L subscriber, you should be. It's a great - and free - DB2 technical resource.]
There you have it - a real gem of a DB2 feature that has been overlooked by plenty of DB2 people. It's easy to use, and people who have implemented DB2 management of data set space allocation like it a lot. Give it a go.
It does seem almost too good to be true, especially to a DBA who has long spent more time than desired in determining appropriate primary and - especially - secondary space allocation quantities for DB2-managed (i.e., STOGROUP-defined) tablespace and index data sets. As you're probably aware, a non-partitioned tablespace (this will typically be a segmented tablespace) can grow to 64 GB in size by occupying thirty two 2 GB data sets; however, the first 2 GB data set has to fill up before DB2 can go to a second (and the second has to fill up before DB2 can go to a third, and so on), and DB2 won't fill up that 2 GB data set if it runs into the data set extent limit (255 extents). The same applies to non-partitioned indexes, except that the data set size is not set at 2 GB - rather, it's determined by the PIECESIZE specification on a CREATE INDEX or ALTER INDEX statement. In either case, you want to set the primary and secondary space allocation quantities (PRIQTY and SECQTY on the CREATE or ALTER statement for the object) so that the object can reach the data set size limit (and thus expand to multiple data sets) without first running into the aforementioned limit on the number of extents for a data set. A partition of a partitioned tablespace or partitioned index isn't going to spread across multiple data sets, but you still want to be able to reach the maximum data set size (specified via DSSIZE on the CREATE TABLESPACE statement) before hitting the data set extent limit.
What should your PRIQTY and SECQTY amounts be? Too small, and you might hit the extent limit before reaching the desired maximum data set size. Too large, and you might end up with a good bit of wasted (allocated and unused) space in your tablespaces and indexes. Even if reaching a maximum data set size is not an issue (and it won't be for smaller tablespaces and indexes), you still want to strike a balance between too many extents and too much unused space. Multiply this by thousands of objects in a database, and you've got a big chunk of work on your hands.
Enter the "sliding scale" secondary space allocation algorithm introduced with DB2 for z/OS V8. Here's how it works: First, set three ZPARM parameters as follows (all are on the DSNTIP7 panel of the DB2 installation CLIST):
- TSQTY (aka "table space allocation"): 0 (this is the default value)
- IXQTY (aka "index space allocation"): 0 (this is the default value)
- MGEXTSZ (aka "optimize extent sizing"): YES (for DB2 V8 the default value is NO, and for DB2 V9 it's YES - this is what I meant by my "enabled by default" comment regarding DB2 V9 in the opening paragraph of this blog entry)
Pretty great, huh? No muss, no fuss with regard to space allocation for DB2-managed data sets. Best of all, it works. When you let DB2 handle data set extent sizing, it is highly unlikely that you'll hit the data set extent limit before reaching the maximum data set size, and the start-small-and-slowly-increase approach to secondary allocation requests keeps wasted space to a minimum. What I find interesting is the fact that many DB2 people don't know about this great DBA labor-saving device. In a recent thread on the DB2-L discussion list, Les Pendlebury-Bowe, a DB2 expert based in the UK, referred to DB2-managed data set space allocation as "a real success story that never seems to get much press." In that same thread, DB2 jocks Myron Miller (a Florida-based consultant) and Roger Hecq (with financial firm UBS in Connecticut) added their endorsements of DB2-managed space allocation. Myron noted that with a specification of PRIQTY -1 and SECQTY -1, "I never have to even worry about the number of rows at any time in the tablespace" (and keep in mind that the -1 values are for ALTER TABLESPACE or ALTER INDEX - as noted previously, just don't specify PRIQTY and SECQTY on CREATE statements to enable DB2 management of space allocation for new objects). Roger stated that he's been pleased to "let DB2 do all the work" related to disk space management, and that his organization has "not had any issues" with their use of this DB2 capability.
[By the way, if you are not a DB2-L subscriber, you should be. It's a great - and free - DB2 technical resource.]
There you have it - a real gem of a DB2 feature that has been overlooked by plenty of DB2 people. It's easy to use, and people who have implemented DB2 management of data set space allocation like it a lot. Give it a go.
posted by Robert Catterall at
2:32 PM
4 Comments:
Mr Catterall, thanks very much, I read always your blog and there are so many good things thanks ! for this feature, i'm going to test and propose my customers this !!! I'me sure it will have a real success just one thing please I understand that the PQTY and SECQTY are staying a -1 and when we reorganize TS online reorg utility will growth AND/OR reduce physical size automatically based on the -1 or before reorgs should I have to alter PQTY & SECQTY to -1 (in fact customers are very interested to occupy less disk as possible (question of cost..) (sorry for my english, you can update and reformulate if you want)
Thanks for the positive feedback.
I'm not sure if I've interpreted your question correctly, but I'll try to provide a useful answer. Suppose you alter an existing tablespace to specify -1 for PRIQTY and SECQTY. Suppose, too, that the ZPARM parameters MGEXTSZ and TSQTY are set to YES and 0, respectively (and you'd probably set IXQTY to 0, too). If you were to then do an online REORG of the tablespace, the shadow data set(s) would be allocated with a primary quantity of 1 cylinder and an initial secondary quantity of 1 cylinder. Subsequent extents would be progressively larger in size, up to the 127th extent (after which further extents would be fixed in size). If this sliding scale allocation is able to fit the contents of the tablespace in a smaller amount of allocated disk space versus the original tablespace, then yes, you will have saved some space.
Excellent Blog.
Question :
Do we still need to mention "PIECESIZE" for index creation, to make use of Db2 managed Space for index creation.
Glad you like the blog.
No, you do not need to specify PIECESIZE, when creating an index, to make use of DB2-managed space (space is DB2-managed when you specify USING STOGROUP), nor is it necessary to specify PIECESIZE to make use of DB2-managed space allocation (that's done by leaving PRIQTY and SECQTY out of the CREATE INDEX statement).
As stated in the DB2 for z/OS SQL Reference, in the section on CREATE INDEX, "PIECESIZE has no effect on primary and secondary space allocation as it is only a specification of the maximum amount of data that a data set can hold and not the actual allocation of storage."
Robert
Post a Comment
Subscribe to Post Comments [Atom]
<< Home