[Note that my use of the word template as it pertains to DDL should not be confused with the TEMPLATE control statement that can be used to facilitate data set allocation when executing some IBM DB2 utilities. Note, too, that I'm writing about application table spaces and indexes, not work file or system (e.g., catalog and directory) objects.]
Let me tell you up front that I'm not going to comment here on every single clause of the CREATE TABLESPACE and CREATE INDEX statements -- I don't have time for that, and even if I did it wouldn't add a lot of value. I'm fine with the default values for many of these clauses (e.g., LOGGED and TRACKMOD YES for CREATE TABLESPACE, and CLOSE YES and GBPCACHE CHANGED for both CREATE TABLESPACE and CREATE INDEX). If you are also OK with the default values for these clauses, you can leave them out of your template DDL (in order to accept the default values), or you can specify some or all of them if you want to see in the DDL a reminder of just what the default values for the clauses are. What I'll provide from here on out are my thoughts concerning some CREATE TABLESPACE and CREATE INDEX clauses about which there is some apparent misunderstanding within the mainframe DB2 community, and/or for which I have some opinions regarding specified values.
For CREATE TABLESPACE and CREATE INDEX
- USING STOGROUP -- You might think that this would go without saying -- that the use of DB2-managed data sets (indicated by USING STOGROUP) versus user-defined data sets (indicated by USING VCAT) is understood by everyone as being the way to go. I hope that's the case, but I'm including this recommendation anyway, just to make sure. Who wants to mess with z/OS Access Method Services statements to create the data sets that DB2 will use for table spaces and indexes? Let DB2 do it. Note, by the way, that this convention has moved beyond application-related objects: starting with DB2 9, work file table spaces can be DB2-managed, and with DB2 10 the catalog and directory objects become DB2-managed (I have some information about DB2-managed work file table spaces in an entry that I posted to this blog last spring, and information about DB2- and SMS-management of catalog and directory objects in a DB2 10 environment in another entry, posted in the fall of 2011). Note also that, starting with DB2 9 (in new-function mode), you can specify SMS data class, management class, and storage class names in a CREATE STOGROUP statement. That could reduce the need for automatic class selection (ACS) routines used for DB2 data sets at your site.
- PRIQTY and SECQTY -- Leave them off of your CREATE TABLESPACE and CREATE INDEX statements, or specify them with a value of -1 (i.e., PRIQTY -1 and SECQTY -1). Either way, what this means is that you're going with a default primary quantity for the object's underlying data set(s), and you're letting DB2 handle allocation of secondary space as needed for the data set(s). The default primary quantity is specified via two ZPARM parameters (TSQTY for table spaces, and IXQTY for indexes -- both with a default value of one cylinder), and DB2 manages secondary space allocation by way of what's known as the "sliding scale" algorithm (this when the ZPARM parameter MGEXTSZ is set to YES). Letting DB2 manage space allocation for table space and index data sets makes all kinds of sense: it works, it helps to keep you out of trouble (in terms of avoiding data set extend failures), and it frees up DBA time for more high-value tasks. I wrote about DB2 management of data set space allocation in an entry that I posted to the blog that I maintained while working as an independent DB2 consultant.
For CREATE TABLESPACE
- MAXPARTITIONS -- What I'm getting at here is the idea of a default table space type for your environment. Your first decision is universal versus non-universal. You might want to give serious consideration to making universal partition-by-growth your default table space type (that would be indicated by having the MAXPARTITIONS and SEGSIZE clauses in the CREATE TABLESPACE statement). The primary reason for this recommendation: to an increasing extent, exploitation of new DB2 features will require the use of universal table spaces. With DB2 9 (the version with which universal table spaces were introduced), it was clone tables. With DB2 10, it's hash-organized tables, LOB-inlining, and the CURRENTLY COMMITTED mode of data access. I fully expect that this trend will continue with future versions of DB2. Using universal table spaces gives you maximum flexibility when it comes to leveraging DB2 technology. If you're going to go universal, the right choice for most table spaces is likely to be partition-by-growth (PBG) versus partition-by-range (PBR). Partition-by-range has its advantages, but I believe that PBR is best used for certain larger tables in a database (e.g., tables holding at least one million rows) that have columns or column sets that would make for good partitioning keys. Is non-universal ever the right choice for a table space? It could be if you want to assign several tables (perhaps a number of small-ish reference tables) to a single table space -- something you might do to keep a lid on the number of data sets in a DB2 system, and to simplify the backing up of said set of tables (via the COPY utility, which operates at the table space level). A traditional segmented table space can hold several tables, whereas one universal table space can hold one table. Bear in mind that the MEMBER CLUSTER table space definition option, required at some sites to support very high volumes of insert processing, is valid for universal table spaces in a DB2 10 new-function mode environment (it could not be used with universal table spaces in a DB2 9 system).
- SEGSIZE -- Most all of your tables should be segmented -- either traditional segmented table spaces or universal table spaces (the latter are also segmented). An exception to this rule: in a DB2 9 environment, you might choose a traditional range-partitioned table space over a PBR universal table space if the volume of inserts into the associated table will be very high, as the former table space type will likely deliver a performance advantage over the latter (with DB2 10, high-volume insert performance for a universal PBR table space is pretty much equivalent to what you get with a traditional range-partitioned table space). Aside from that case, segmented -- whether universal or non-universal -- is the way to go. The default SEGSIZE value is 4, and that might make sense for a non-universal segmented table space to which several really small tables are assigned. For single-table table spaces, the defualt SEGSIZE of 4 is probably not the best choice. I'd be more inclined to go with something like SEGSIZE 32, with an eye towards boosting prefetch efficiency.
- LOCKSIZE -- The default value here is ANY, which allows DB2 to use any type of locking granularity when accessing the table space. Yes, DB2 will typically use page-level locking for a table space that is defined with LOCKSIZE ANY, but I like to make the locking granularity choice myself. I prefer to go with LOCKSIZE PAGE as a default for table spaces. I'd change that to LOCKSIZE ROW for a table space only if page-level locking resulted in an unacceptable level of lock contention. Some people believe that you can't use row-level locking in a DB2 data sharing environment, or that doing so would be a big mistake. In fact, selective use of row-level locking in a DB2 data sharing system can be quite beneficial in relieving lock contention issues, as I pointed out in a blog entry that I wrote a few years ago. Another thing about a change to row-level locking from page-level locking for a table space: doing that could cause some data-changing programs that target the table space to acquire considerably more locks between commits, and THAT could conceivably cause said programs to hit the limits you've established (via ZPARMs) on the number of locks held by one process across all the tables that it is accessing, and/or the number of locks acquired on a single table space (the latter put into effect for a table space by way of the LOCKMAX SYSTEM clause of CREATE TABLESPACE). To avoid a surprise in this department, consider raising the values of the ZPARM parameters NUMLKUS and NUMLKTS if you change from page-level to row-level locking for one or more of your table spaces. Also keep in mind that if you change the locking granularity for a table space from page-level to row-level (or vice versa), and you want the new locking granularity to be utilized for static SQL statements that target the table (or tables) in the table space, you'll need to rebind the associated packages after altering the LOCKSIZE specification.
- COMPRESS -- DB2 for z/OS data compression is very CPU-efficient, thanks to a hardware assist, and it is therefore widely used. I'm a big fan of COMPRESS YES myself, but I don't think that it should be used for every table in a database. A compression dictionary takes up 64 KB of space in memory, so it's possible that compressing a really small table space could lead to that table space actually having a somewhat larger virtual and real storage "footprint" than it would have were it not compressed. I'd certainly lean towards compressing table spaces with 1000 or more pages.
- PCTFREE -- For a table space, the default value for PCTFREE is 5 (that's 5% of the space in a page). If a table's rows are not clustered by a continuously-ascending key, it would be good to have some "holes" in pages (following a REORG or a LOAD) into which newly-inserted rows could go -- the better to maintain good data organization between REORGs. In that case, PCTFREE 5 might be too small (5% of a 4K page is about 200 bytes, and that may or may not be enough to hold a table row -- and remember to consider compressed row length if you specified COMPRESS YES for the table space). A PCTFREE value of 10 might be better for maintaining data organization than PCTFREE 5. Of course, if a table's rows are clustered by a continuously-ascending key, a non-zero PCTFREE value would probably result in wasted space.
- FREEPAGE -- The default value of FREEPAGE is 0. Many indexes are defined on keys that do not have continuously-ascending values. For these indexes, new entries will go into the "middle" of the index. Some index leaf page split activity has to be expected, and when that happens it would be nice to be able to put the "split out" index entries into a page that is not too far from the page that was split. For that to happen you need to have some empty pages throughout the index (following an index REORG or a LOAD of the underlying table), because index entries that are relocated as a result of a leaf page split have to go into an empty page. If FREEPAGE 0 is specified for an index, the only empty pages will be at the end of the index, and that's where "split out" entries will go when page splits occur. The result could be fairly rapid disorganization for an index defined on a non-continuously-ascending key, if a lot of rows are inserted into the underlying table. For such an index, consider a FREEPAGE value of between 5 and 10 (FREEPAGE 0 is appropriate for an index on a continuously-ascending key).
- BUFFERPOOL -- Starting with DB2 9 in new-function mode, indexes could be assigned to buffer pools used for pages larger than 4K in size. Going with 8K, 16K, or 32K pages for an index is required if you want to utilize the index compression capability introduced with DB2 9, but even if you aren't interested in compressing an index, a larger page size could be a good choice. In particular, if the index in question is defined on a non-continuously-ascending key, and the volume of inserts into the underlying table is high, a large index page size (32K, for example) could substantially reduce the occurrence of index page splits, and that could deliver a nice performance dividend.
Do you have template DDL that you use as a starting point for CREATE TABLESPACE and CREATE INDEX statements in your DB2 environment? If not, perhaps you should. If you already have DDL that you use as a pattern for CREATE TABLESPACE and CREATE INDEX statements, how long ago was that pattern set up? Has the template DDL kept pace with DB2 changes that provide new options for table spaces and indexes? If not, a review and an update of the template DDL could be in order. It's all part of being ready to put the latest DB2 technology to the best use at your site.
Re: "e.g., tables holding at least one million rows)"
ReplyDeleteDislike this . . . This strategy ignores simple laws of physics. A table defined with a row size that allows the maximum rows/page (255) will consume ~16 MB – which is great …. I have an active project that will have a CLOB whose size will average 1.5 MB. At 1,000,000 rows that table’s partition would be 1.5 TB – that a T, not a G or M. Recovering this puppy (if a 1.5 TB partition was even allowed) would not be nice."
Most shops I am familiar with (in cluding where I work) have changed to a partition-by-size strategy - i.e. every 1 GB or 2 GB.
Michael Harper, TD Bank
Michael,
DeleteIf your point is that "one million or more rows" is an overly simple criterion with respect to gauging the appropriateness of partition-by-range for a table, I agree with you. I did not posit "one million or more rows" as THE threshold beyond which you should consider range-partitioning for a table - I mentioned it as A threshold that could be useful for this purpose. That's why I preceded "tables holding at least one million rows" with "for example" (abbreviated as "e.g."). And I do believe that a row-oriented perspective is useful. Many DBAs have a pretty good knowledge, off the top of their head, as to the approximate number of rows in the key tables in a production database ("That table has about 50 million rows in it"). These same DBAs often don't have at front-of-mind the space occupied by a table - they might have to look that up. In a meeting during which range-partitioning of a certain table comes up, I want someone to be able to say, "Range-partitioning that table might not buy us much - it only has about 100,000 rows." Obviously, if the table has one or more long LOB columns, even with just a few hundred thousand rows range-partitioning could make sense.
You're right in that the impetus for range-partitioning can vary from one situation to another. Sometimes, a key consideration is the run time of various utilities (REORG, RECOVER, etc.), and in such cases a partition-by-size approach can be very appropriate. In other situations, the range-partitioning driver is more application-oriented. Sometimes people want a rather large number of small-ish partitions to boost query performance (with quite a few partitions based on one key, and intra-partition clustering based on another key, you get something like 2-dimensional data clustering, and that can improve performance for certain queries). Other times, there is a desire to physically separate sets of rows (again, in potentially not-too-big partitions) so that data-changing processes can run in parallel without contending with each other.
If range-partitioning can make sense for certain larger tables, the question then is, "What's a 'larger table'?" Some would say, "A 'larger table' is one with X or more rows." Others would say, "A 'larger table' is one that occupies at least X GB of space on disk." There's no one "right" way to consider the "larger table" question. I gave an example of one way, and did not mean to imply that it's the only way. Size matters, too - no argument there.
Robert
Robert, but I would suggest the default position should be UTS and in particular PBR. It should be supported by answers to questions about processing, query, reporting and other requirements. Of course, this only works if you have someone familiar with current DB2 for z/OS physical design options and implications, along with excellent judgment.
DeleteTo invent an example, I might decide that my banking applications should have a partitioning key of:
Country, State, Timezone (in honor of Daylight Savings Time ;-)), SUB_GEO_GROUP, Branch
and apply it to all related tables, including the Branch table which might only have 5,000 rows and no BLOBs.
I might want this since I want batch for Tokyo branches to run at a different time than batch for Berlin branches. I might run different batch for each State because Bavaria, New York and Ontario (province, not state, I know) might have different banking and tax regulations. I might run batch just for a branch in Lower Manhattan because it may have more activity than the whole of North Dakota or Staten Island.
Robert, to continue with the above theoretical example, you may not want the redundant partition key data: Country, State, Timezone,SUB_GEO_GROUP, Branch added to your child tables. Or you may not care about Timezone.
DeleteYou might instead add a PART# column to the child tables so you can have Branch as the driver with predicates on its partitioning key and join on PART# with its children. All the Berlin data might be in the partitions with PART# IN (123,124,125). All the Unter der Linden Strasse branch data might be in the partitions with PART#=125.
"I would suggest the default position should be UTS and in particular PBR."
DeleteI'm not ready to go there. The UTS part, yeah, but not the PBR part. I'd be more inclined to make UTS PBG my default database type. The thing about PBR is, you a) need to have an effective partitioning key, and b) ought to have a clear idea as to how range partitioning might be beneficial for the table in question. Not every table has what I'd call a suitable partitioning key. More importantly, in my book: for plenty of tables, there would not be a clear advantage to the use of range partitioning. Range partitioning can be good for physical separation of high-volume, data-changing batch processes from transactional processes (or other high-volume, data-changing batch processes); however, lots of tables either don't have a high-volume of associated data change activity, or they do but it's accomplished via transactions which have much smaller commit scopes and which can be effectively separated (as needed to reduce lock contention) through clustering or through row-level locking. Similarly, range-partitioning can be an effective driver of query parallelism, but for many tables the targeting queries are such that query parallelism would not be a performance winner. The point here: if range-partitioning a table wouldn't deliver significant benefits, why bother with coming up with a partitioning key and such? Why not just go the easy route and make the corresponding table space partition-by-growth? PBG is particularly attractive for tables that aren't going to get that big. Perhaps a table will never go to a second partition of 1G or 2G or 4G or whatever. If range-partitioning won't provide much of any benefit for that table, why not just have it occupy the one data set?
So, I'd start with UTS PBG, and then go with either UTS PBR or maybe multi-table segmented (perhaps for a set of relatively small and stable code or reference tables) if I see significant advantages associated with one of those alternatives.
Robert
A great feature to use is ISPF Models. A few *cough* decades ago I coded DB2 DDL and Utility command templates as models making it simple for people to enter in edit commands such as MODEL DB2UTIL REORG. Models liberally sprinkled with ")CM" are wonderful productivity tools.
ReplyDeleteAnother method I have see is to store the templates in an edit macro.
Michael Harper, TD Bank
Done a few decades ago, when, as a 10-year-old, you began your IT career...
DeleteGood information, Michael - thanks for sharing it.
Robert
That was a lame attempt at humor, by the way. It later occurred to me that my comment could be misinterpreted as an insult - as my saying that you had made a suggestion worthy of a 10-year-old. I was just thinking that people shouldn't assume that you're an old guy just because you've been working in IT for *cough* a few decades (as have I). Age is attitude as much as years, I say.
DeleteRobert
I had no problem with the comment. I started the humorous vein with "*cough*" and I assume you continued it.
DeleteMichael Harper, TD Bank
Robert and Michael, do you have observations about how sites using data modeling design tools that generate DB2 for z/OS DDL adapt the Template, Model or Mask techniques?
ReplyDeleteSites might consider a DDL checklist, integrated with site standards and well commented best practices examples, that are repeatedly updated to reflect current knowledge and APARs. Along with a change control process that checks for reasons for deviations which may or may not be very justified by particular processing demands.
I don't have particular observations to offer with respect to data modeling design tools as they pertain to a template approach to DDL.
DeleteI like your suggestions about standards and change control. To be effective, template DDL can't be a "back of the envelope" thing. There needs to be some process around it to ensure that 1) it's being adhered to, and 2) it's being updated as needed over time.
Robert
Data management is not strong where I work. However a number of years ago I did have the opportunity of working with Sybase's PowerDesigner (then Quest's QDesigner). It has a templating feature for generating SQL. This is where one can place shop standards.
Delete"Templating" can be applied in many places. As an example, I developed a set of utility templates here so that basic DB2 utilities require just a few lines of JCL, a control card with the tablespace name and some GDGs ... commands and their parameters are all in standard cards that can only be overriden with my group's permission.
Made changing from CONCURRENT to SHRLEVEL CHANGE very, very simple.
Michael Harper, TD Bank
I like the idea of utility templates, Michael. Promotes utility standardization, makes modification of utility standards easier, and probably helps newer people on your team to become more productive, more quickly.
DeleteRobert
Robert and Michael, thank you for sharing your insights and experiences.
DeleteHey Robert, I notice you didn't really mention what you thought the template should be for LOCKMAX. Most places I have been at we would use LOCKMAX 0 to prevent escalation of a lock. In the case a thread hits the maximum, they then fail and our response was "you aren't using the commit routine?" However, in my most recent assignment I am seeing the standard as LOCKMAX SYSTEM. I see quite a few tables where the locksize is escalating multiple times per day and I see quite a few -911's as well. I, for one am having a hard time convincing them the benefits they would see by changing their default here.
ReplyDeleteDave Nance
Sorry about the delayed response, Dave - crazy schedule last week.
DeleteIt's quite possible that in your current environment LOCKMAX is set to SYSTEM because SYSTEM is the default. IF that's the case, it's possible that the value of NUMLKTS (the ZPARM that determines the actual value of LOCKMAX if it is set to SYSTEM) is also at its default value, which is 2000. That would probably be a not-good thing, because in a large DB2 for z/OS system one could certainly imagine a batch data-update process acquiring 2000 locks within a commit scope. If your subsystem's NUMLKTS value is 2000, I am not surprised that you're seeing multiple escalations per day.
I personally like to go with LOCKMAX 0 when defining a table space. Why? Because I want to be in control of when lock escalation occurs - I don't want DB2 doing that for me (if I want an exclusive lock on a table space - and there are some situations in which you might want an application to get such a lock - then I'll ask for that programmatically via a LOCK TABLE statement).
One reason LOCKMAX 0 doesn't bother me the way it might have some years ago: lock control blocks are stored in the IRLM address space, and in a 64-bit addressing world you have LOTS of headroom for lock acquisition (in the old days some sites had IRLM lock control blocks going to extended common storage, a quite-limited resource - that lock control block storage option is no longer available). Yeah, if a lot of lock control blocks accumulate in the IRLM address space, I want to have sufficient real storage to back that up, but large real storage sizes are increasingly common in System z land.
In considering LOCKMAX 0, a person might be concerned, thinking, "What, then, would keep an application process from going crazy in acquiring locks?" That's what NUMLKUS in ZPARM is for, in my mind. The default NUMLKUS value of 10,000 is quite reasonable in many DB2 for z/OS systems. In a system characterized by some really big data-changing units of work, a NUMLKUS value of 10,000 might be a little on the load side - you might bump it up so that the only processes hitting the limit are likely to be processes that really are doing too much between commits (in other words, you wouldn't want "normal" application processes to run into this limit with any kind of frequency).
Robert