Kategorie: Db2 12

What do you do if your critical production tablespace reaches its maximum of 32 datasets on a Saturday?

Could you actually get the REORG through before prime time starts on Monday morning?

I have recently been involved with trialing and testing our space monitor software (SpaceAssuranceExpert or SAX) on Db2 z/OS after some enhancements had been added. It was originally designed—many moons ago—to monitor the size of the secondary extents that Db2 was using and to dynamically issue an ALTER SECQTY to guarantee that the maximum size of the object was reached *before* users ran out of physical extents.

MGEXTSZ to the Rescue?

Now you might be wondering “What’s that got to do with the price of beef?” because, as we all know, Db2 V8 introduced a “sliding scale” to the size of the secondary extents so that it could also guarantee that a dataset hit its maximum size *before* you ran out of extents.

Extents are not everything

The “problem” is that extents are not everything. In fact, one major area of concern is the number of datasets. If it is 01:00 on a Saturday morning and your critical production tablespace has reached its maximum of 32 datasets – what are you going to do? Could you actually get the REORG through before prime time starts on Monday morning? Or what happens when Partition 26 completely fills up?

SAX to the rescue!

This is where our SAX tool saves the day. It is an STC that runs 24×7 catching the IFCIDs that Db2 throws whenever it issues an extent request for a dataset. Using the Db2 Catalog, SAX then determines the exact make-up (geometry) of the object being extended and can use two levels of warning percentages to start triggering alarm bells way, way before it all goes pear-shaped!

Here is my little “ready-reckoner” for Linear Dataset Allocations:

Object type: TABLESPACE        !   Maximum number of data sets 
-------------------------------+------------------------------- 
LOB tablespaces                !   254                         
-------------------------------+------------------------------- 
Non-partitioned tablespaces    !   32                          
-------------------------------+------------------------------- 
Partitioned tablespaces        !    1 (Percent used check)      
-------------------------------+------------------------------- 
Partitioned By Growth          !   MAXPARTITIONS. LPS check if 
tablespaces                    !   more than one. If on last   
                               !   partition then percent used.
-------------------------------+------------------------------- 
                                                                  
Object type: INDEX             !   Maximum number of data sets 
-------------------------------+------------------------------- 
Non-partitioned indexes on     !   MIN ( 4096 , 2 power 32 /   
tablespace with LARGE,         !       ( DSSIZE / TS PGSIZE)) 
DSSIZE, or more than 64        !   Eg: 128 GB DSSIZE with      
Partitions                     !         8 KB Tablespace Page  
                               !   gives 256 Pieces (datasets) 
                               !   Or    4 GB DSSIZE with      
                               !         4 KB Tablespace Page  
                               !   gives 4096 Pieces (datasets)
-------------------------------+------------------------------ 
Non-partitioned indexes        !   32                          
otherwise                      !                             
-------------------------------+------------------------------ 
Partitioned indexes            !   1 (Percent used check)      
-------------------------------+------------------------------

Here you can see that it is not as easy to calculate how many datasets are allowed as it used to be. You must also make sure you understand PBG space definitions. SAX allows two percentages and uses them in two different ways:

1. The number of datasets that have been allocated
2. The used space within a linear dataset

The second is also used if it is a PBG with MAXPARTITIONS 1, (e.g. The Db2 Catalog), or if the Partition being extended is the last allowable Partition.

How big can my PARTITION get?

There is a full description in the SQL guide all about the maximum size of a partition. Here is a little summary of this info:

Use the DSSIZE parameter to control how big a partition is (or for LOB spaces how big the LOB space can get):

To specify a value greater than 4G, the data sets for the table space must be associated with a DFSMS data class that has been specified with extended format and extended addressability.

How does the number of partitions affect my size?

If NUMPARTS is used along with DSSIZE then the maximum size of each partition depends on the value of NUMPARTS, as shown in the following list. Otherwise, the maximum size of each partition defaults to 4G.

Value of NUMPARTS  Maximum partition size (default for DSSIZE)

How does my size affect the number of partitions?

If NUMPARTS is greater than 254, the maximum partition size (and the default for DSSIZE) then depends on the actual page size of the table space.

If DSSIZE is explicitly specified, the maximum number of partitions that can be specified, or is the default, is limited by the maximum table space size. For example:

• For a partitioned table space with a 4K page size, if DSSIZE 64GB is specified, the maximum NUMPARTS value is 256.
• For a partitioned table space with an 8K page size, if DSSIZE 64GB is specified, the maximum NUMPARTS value is 512.
• For a partitioned table space with a 32K page size, if DSSIZE 128GB is specified, the maximum NUMPARTS value is 1024.

Special rules for LOBs

For LOB table spaces, if DSSIZE is not specified, the default for the maximum size of each data set is 4GB. The maximum number of data sets is 254.

What about PBGs?

To use these UTS types you must specify the MAXPARTITIONS clause. It specifies that the table space is a partition-by-growth table space. The data set for the first partition is allocated unless the DEFINE NO clause is specified for the partition. The data sets for additional partitions are not allocated until they are needed. (Unless you use the NUMPARTS clause)

You specify the maximum number of partitions to which the table space can grow, which must be in the range of 1 to 4096, also depending on the corresponding values of the DSSIZE and page size clauses.

How does my MAXPARTITIONS affect my size?

The maximum value for MAXPARTITIONS is a function of DSSIZE and table space page size:

WTO to Job Ticket

These warnings are issued as WTOs and can easily be picked up by system automation tools to open job tickets or send e-mails to alert DBAs—days or weeks before the system stops working.

For a warning SAX issues WTO ids:

O2RTSU04 - 12W (non-partitioned spaces)

O2RTSU04 - 14W (partitioned spaces)

O2RTSU04 - 16W (partition by growth spaces)

For a critical SAX issues WTO ids:

O2RTSU04 - 13W (non-partitioned spaces)

O2RTSU04 - 15W (partitioned spaces)

O2RTSU04 - 17W (partition by growth spaces)

This is not all that SAX does, in fact, it covers all of these problems:

1. Can this data set reach its maximum physical size *before* running out of physical extents? (The actual size is dependent on the “geometry” of the object of course!)
2. Will this dataset run out of datasets? (Again, how many datasets an object can actually have is dependent on the “geometry” of the object)
3. Is this partition nearing its maximum size?
4. Do I have a SEQUENCE/IDENTITY problem coming up?
5. Did Db2 ask for one extent but got more back?
6. Are any of my SMS disk storage pools running out of space?

New in SAX – SEQUENCE Support

Number four on that list is brand new. We have a customer who got bitten by a nasty problem. They had a SEQUENCE defined with the NO CYCLE parameter so it could not loop around and then finally they hit the last available number. Not good! They asked if SAX could be modified to also take care of this hidden nasty and we readily agreed so that all customers can benefit. The parameters panel got this set of new Parameters:

As mentioned in the screen shot above from our online help panel, it will check the SYSSEQUENCES every PING minutes which, by default, is 30. When the WARN SUPP INTVL is set, you can reduce the number of warnings issued so as not to overload your problem ticket System!

Degenerated Extent support

There is another WTO that can be issued by degenerated extents (Number five in the list)

O2RTSU04 – 10W (Audit SECQTY)

These occur when Db2 requests one extent but gets back, say, five. This is ok, but it eats through the number of extents quite quickly and it implies the need for a disk defragmentation to be scheduled.

Let’s talk about Extents

While talking about extents, what I have also seen, is that the number of extents is sometimes getting very large indeed. At one customer site they had numerous datasets with over 4,000 extents! Now we all know that no-one knows where data is really stored on the modern disk sub-systems, but still… I would schedule a reorg at say 1,000 extents. The number of extents changed a *long* time ago in z/OS 1.7 to raise it from 255 to 7,257, spread over 59 volumes, *but* still limited to 123 extents per volume. This little nugget of information is *very* important if you are thinking of going down the “one huge EAV volume for all my data” road, (these disks can have up to 262,668 cylinders or about 223GB), as the extents per volume limit is still there.

In comparison, the good old MOD-3s had 3,339 cylinders and 3GB of space.

SMS Storage Group Checks

Finally, SAX can also check and alert if your SMS storage groups start getting full. This is especially handy for your Db2 Catalog, Copy Pools and Work Pool SMS Storage groups. Depending on the thresholds you define you can get either

O2RTSU05 - 05W SMS STOGROUP XXXXXXXX: % ALLOC = XXXX

Or

O2RTSU05 - 05W SMS STOGROUP XXXXXXXX: GB FREE = XXXXXXXXXXXX

WTOs being issued. Not really normal DBA work, but very handy nevertheless!

The Future?

SAX will continue to be updated and enhanced for the new features and functionality that Db2 brings in future releases. For example: Relative Page Numbers in Db2 12, where all partitions can get their own DSSIZE with seven byte RIDs.

Of course, you could do all of this on your own too—but then you’d have to maintain it! And that’s enough of me being a sales guy. Back to what I really love the most: solving Db2 problems.

As usual any questions or comments are welcome,

TTFN Roy Boxwell