What has changed for space management in Db2 12?
How to avoid SIZE limits in Db2 12 like in previous Db2 Releases?
How to monitor the maximum possible SIZE of table and index spaces and table and index partitions?
SIZE in Db2 12: Now that Db2 12 has gone GA, I thought it would be nice to do a quick re-recap of Space management and its problems over the releases. My “old” newsletter 2014-05: Why SIZE matters for Db2 still receives a lot of hits on our website, so I know that this is a big topic of interest for many of you. Some nifty things have been introduced in Db2 12 to make space a lot easier to use and manage.
In the beginning…secondary allocation for tablespaces and indexes since Db2 V7, Db2 V8…
Since Db2 V8, the DBAs of this world have all had the ability to forget about PQTY and SQTY in the DDL for Tablespaces and Indexes. At first, nearly no-one trusted the sliding scale algorithm, and SOFTWARE ENGINEERING’s product Space AssuranceExpert (aka SAX) monitored and reacted instantly to secondary allocations.
However, we now have Db2 12, and I thought it would be interesting to review what was done in Db2 V7 (when our SAX was launched), and the difference nowadays in the Db2 12 world.
IFCID issuing for space extents
Every time a secondary allocation is done in Db2, it can be made to spit out an IFCID. SAX runs as a started task, active 24×7, from Db2 start up until just before Db2 shut down. It catches all of these IFCIDs thrown by Db2, and performs an analysis with six basic questions:
| 1 | Can this dataset 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 object run out of datasets? (The number of datasets an object can have is, once again, dependent on the “geometry” of the object) |
| 3 | Is this partition nearing its maximum size? |
| 4 | Did Db2 ask for one extent but got more back? |
| 5 | Are any of my SMS disk storage pools running out of space? |
| 6 | Are there any SEQUENCES that are about to hit the wall? |
(Numbers five and six are actually triggered by a timer, naturally.)
Can this dataset reach its maximum size before running out of extents?
Remember, back in those old days of Db2 V7? We only had 255 extents and 254 partitions, but datasets could still get pretty big pretty fast.
The problem lots of shops had, was that an important dataset would “hit the buffers” of maximum number of extents *way* before it ever ran out of physical space. Thus causing grief, wailing and gnashing of teeth! SAX stopped all this by giving WTO “heads-up” style messages in two flavors. First, a warning message, and then a critical message. This gave DBAs and space managers much needed time to plan for the outage and the, inevitably, long running REORG to actually action the required ALTER, or perhaps even any DROP/RECREATE that had to be done.
IBM also noticed this problem and so introduced in Db2 V8 the “sliding scale” of secondary allocations, as long as the ZPARM OPTIMIZE EXTENT SIZING field (MGEXTSZ) was set to YES (this is the default from Db2 9, by the way). Of course, to really use this, you then had to ALTER all of the existing spaces PQTY and SQTY to be -1, and then remember to delete all PRIQTY and SECQTY lines in your DDL and also rely on the TSQTY and IXQTY ZPARMs giving a big enough “first default”. (By the way, defaults for these two ZPARMS are 0, which is actually translated to be 720k or one cylinder for normal spaces and 7200k or 10 cylinders for LOB spaces). This all probably explains why the take up of this great feature has not been that spectacular and, in fact, Listserv *still* gets questions about “How good is this feature?” This also explains why the primary reason for having SAX is still valid at most shops today!
However, most shops these days tend to ignore the extents problem and only REORG when over 1000 extents have been allocated. This is no problem for SAX, as it knows the SECQTY and the MGEXTSZ ZPARM settings and can decide to “ignore” an IFCID for extent and ALTER SECQTY processing if the SECQTY is -1 and the MGEXTSZ is YES.
Will this object run out of datasets?
Now the problem of running out of datasets is very, very evil indeed… For a non-partitioned space, you can have up to 32 datasets. Db2 will happily allocate away and you will never know, or even be informed, if, and when, the last possible dataset has just been allocated and, of course, you will not know that the 33rd one cannot be allocated until you get a -904 unavailable resource error! By definition this is “not good”, as you must do a HUGE REORG with a bunch of managers breathing down your neck and *not* make any mistakes with the new allocations. (Again, this is a very good reason to have SAX doing all the monitoring and triggering early warning “heads-up” style messages!)
Is this partition nearing its maximum size?
A partition running out of space is rare, but when it does happen it is, of course, a disaster! The idea in SAX, is to warn when “the end is near” for any of the partitions in use and thus, as before, allow time for the ALTER etc.
Did Db2 ask for one extent but got more back?
Degenerated extents are annoying as well. You have only 255 or 7,257 extents, Db2 requests one but gets up to five back! This is “wasting” your precious supply of extents and so SAX can also warn you if this starts happening. Remedial action can again be planned to correct the problem, (normally a volume defrag in this case). Now in z/OS 1.7 “Extent Constraint Removal” was introduced for the DATACLAS which, if set to “Y”, allows 7,257 extents but still limits you to 123 extents per volume and 59 volumes. So watch out if you are using huge “virtual” disks (E.g. MOD 54 or EAV), as you can end up wasting space because you still cannot exceed 123 extents per volume.
SAX also takes care of duplicate recording – This is where an Extent is registered but SMS “consolidates it into the primary/existing extent – This would normally get logged as an extent but SAX sees this and does not report it as an extent.
Are any of my SMS disk storage pools running out of space?
When an SMS Pool runs out of space, either for sort/work or image copy, it is *not* good! The idea here, is to also give a “heads-up” style alert. The DBA can trigger the space management people to have a look at the state and size of the SMS storage groups this time alerted by percentage used or GBs of space free.
Are there any SEQUENCES that are about to hit the wall?
The usage of SEQUENCES has taken off. Nowadays shops can run into the problem of SEQUENCES hitting the maximum/minimum number for a NOCYCLE defined sequence. SAX tests sequences at the same time as the SMS groups to warn about any encroaching problem with WTO/MSG and reporting.
What was new in Db2 V8?
Db2 V8 introduced a big change – Partitions went up to a maximum of 4,096, and the calculation about how many pieces your NPI can have got “a little bit complex” (see also my previous newsletter: “2014-04 Are you going to pieces”).
What was new in Db2 9? PBG and UTS spaces
In Db2 9 the next major advance came with UTS spaces. The one that caused the most grief was, of course, PBG. Why? Well, the first problem was that some people went mad and used MAXPARTITIONS 4096 right from the get-go. They then found out this could not simply be changed and ended up being a huge problem. IBM came out with a bunch of fixes for these people, but the recommendation is still true today: “Use the number you expect to use!”
PBGs, however, came with a new set of space management problems:
| 1 | By definition every partition is full, and so a TP REORG is “dangerous” -especially if you have VARCHAR, and even more so if compressed. |
| 2 | ALTER at TP level is not supported for PBG. |
| 3 | Getting rid of empty partitions was not supported |
| 4 | Adding partitions dynamically (by command) was not supported. |
| 5 | What to do if the partition that is “in use” is growing and is a – The last allowed Partition Now these are “non trivial” because the Db2 catalog is so defined and you would not want an alert every time someone created a table or index! The trick here, is to treat these conditions as if it was a normal space and so, instead of warning that you are using the last part, it waits until you are using, e.g. 80% of that part. Then, e.g. at 90% comes the critical threshold warning. |
Big changes happened here in Db2 12.
What was new in Db2 10?
With Db2 10 came the ability to ALTER PBGs to add parts which made using DSN1COPY to clone data around a lot better!
What was new in Db2 11?
In Db2 11 the REORG utility can be used to remove any empty parts in PBGs by the use of the ZPARM REORG_DROP_PBG_PARTS being set to ENABLE (DISABLE is the default).
What is new in Db2 12?
Now in Db2 12 there is partition independence for DSSIZE. Before, all partitions had to have the same maximum size (DSSIZE). Now you can have different sizes for different parts. This requires either making a new tablespace (UTS Relative Page Numbering), or an ALTER and TS level reorg of an existing UTS space. The tablespace goes relative page numbering and the RID increases in size to seven bytes hence the need for a TS level REORG. The Partitioning indexes also get DSSIZE so they can vary in size as well. Once you are there, all of the Partitions can then be ALTERed up in size with no outage! This is really, really good!
REORG of a PBG can “spill” into a new partition. This is also really good, as it was the major problem with PBG TP level reorgs. The chance of LOB data going into COPYP during the log apply phase has been stopped – Thankfully! Finally, delete of empty partitions is controlled with a utility DROP PART syntax.
The SAX way for Space monitoring
The SAX tool way of processing all this info is neatly summarised in the help panel of the tool itself:
SUPERVISE LPS
Supervise linear pagesets. If specified, a warning is issued
in case of high allocated reaches this percentage of the
maximum data set size for partitioned objects.
For non-partitioned objects, a warning is issued for every
newly allocated data set as soon as the data set number
reaches this percentage of the maximum number of data sets:
Two different values may be entered for warning and critical
values with different message ids. This may be useful for
automation reasons (see below).
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)
-----------------------------+----------------------------
To support automation based on WTO ids two different
thresholds may be specified:
Field (1) specifies a warning threshold using WTO ids
O2RTSU04 - 12W (non-partitioned spaces)
O2RTSU04 - 14W (partitioned spaces)
O2RTSU04 - 16W (partition by growth spaces)
Field (2) specifies a critical threshold using WTO ids
O2RTSU04 - 13W (non-partitioned spaces)
O2RTSU04 - 15W (partitioned spaces)
O2RTSU04 - 17W (partition by growth spaces)
AUDIT DEGENERATED XTS
Audit secondary quantity for de-generated extents. If
specified, a warning is issued in case of the last extent
does not reach this percentage of the SECQTY specified
in the Db2 catalog. If this field is left blank, no
auditing is performed.
AUDIT SMS STOGROUPS
Should the Space AssuranceExpert audit SMS stogroups. Y/N
If Y is entered, a pop-up window will allow you to enter up
to 24 SMS storage groups which will be audited.
If WARN IF % ALLOC > or WARN IF GB FREE < is specified and
exceeded, a warning (WTO) will be issued.
CHECK SYSSEQUENCES
Should the Monitor also check for SYSIBM.SYSSEQUENCES that
are running out of room every PING minutes?
N - do nothing. This is the default.
I - check Identity Columns and Doc Ids for XML.
S - check User Defined Sequences.
B - do both.
PERCENT USED
If checking of SEQUENCES is desired then a threshold
percentage must be given from 1 to 99. If this percentage of
the available sequences is exceeded then an action is
triggered.
EXCEEDED ACTION
When a percentage is exceeded this specifies what type and
and which style of message should be externalized.
N - do nothing. This is the default.
W - to write out a WTO.
M - to write a message to the job log.
B - do both.
To support automation based on WTO ids the following
messages are output:
O2RTS000 - 20W (SEQUENCES MAXVALUE)
O2RTS000 - 21W (SEQUENCES MINVALUE)
O2RTS000 - 22W (IDENTITY MAXVALUE)
O2RTS000 - 23W (IDENTITY MINVALUE)So now you know why size still matters for Db2 12! The big question now is: “Are your space management and monitoring tools up-to-date, or are they still Db2 V7?”
As usual, if you have any comments or queries please feel free to drop me a line!
TTFN
Roy Boxwell