Sorry, but I could not resist the Dirty Harry quote there…and yes I know it really should be “his/her” but that would not then be a quote, so please forgive me!
I have spoken/blogged about limitations in Db2 a few times over the years and we have a nice little tool called Space AssuranceExpert (SAX) that stops a whole bunch of nasty events (read -904’s) at sites around the world.
It grows and grows
SAX started out “just” monitoring the size and number of Extents/Linear Datasets that Db2 was allocating. It then either did a dynamic ALTER of the SECQTY to allow the dataset to get to its physical maximum size before running out of extents, or it warned people that the number of physical datasets was running out.
First enhancements
– SMS Storage groups Monitoring
We pretty quickly enhanced it to then also monitor SMS Storage groups as when your copypool runs out of space it can be a tad embarrassing!
At the same time, we changed the logic to work with the -1 -1 style allocations when IBM brought out their sliding scale logic.
– Universal support
When PBG Tablespaces came out, we again enhanced the product due to the special processing limits of these Universal Tablespaces.
Think MAXPARTITIONS here!
– Db2 12
Along came Db2 12 with its new RPN format and the ability to have a different DSSIZE for each partition, and each index got its own DSSIZE.
– Sequences and Identities
Then we were asked if we could also monitor Sequences and Identities that are defined as non-cyclic. A lot of these beasts are defined so that they just “hit the buffers” at a certain number. So we added that to the system as well.
– Numeric Primary Key support
Next came a request to monitor Numeric Primary Key columns. These were actually sequences before sequences were even invented! You have, typically, a SMALLINT field which keeps being incremented – Fine for the first 32,767 but after that?
No time to rest on our Laurels!
Are we now finished? No! There is *always* more to do!
- What if you are nearing the column limit, or worse yet, the Index column limit?
- What if the size of your index is getting dangerously close to the absolute physical maximum?
- Talking about absolute physical maximums – What if you are running out of DBIDs?
- Or, even worse, running out of OBIDs within a database – Do you even check these?
- What happens when you try to ALTER it? Or create that new index?
- Or even just a view with an INSTEAD of function?
BOOM! Is what happens!
SAX saves the day again!
We have enhanced SAX yet again, so that
you can now run a batch job to review all of these limits.
This gives you plenty of time to take corrective action before the bridges are burning…
What are the limits?
In the Db2 SQL Reference Guide in the Appendix “Limits in Db2 for z/OS” it lists them out, but here are the important ones for you all:
750 Columns in a table.
Actually it can be less, depending on the complexity of any VIEWs, but to keep it simple this value is used as the upper limit.
64 Columns in an index. This includes INCLUDE columns.
For a partitioning index (That is an “old style” index that is actually used to partition the data) you get a maximum size for PADDED indexes of 255 – n bytes and, if NOT PADDED, then 255 – n – 2m – 2d bytes.
For any other indexes you get a maximum size for PADDED indexes of 2000 – n bytes and, if NOT PADDED, then 2000 – n – 2m – 2d bytes.
Where:
- n is the number of columns which are NULLable
- m is the number of varying length columns
- d is the number of DECFLOAT columns
An FTB (News from the Labs 2019-07) is only possible for indexes that are 64 bytes or less. If you ALTER add a one byte column to a 64 byte index then you have broken this limit and that index is no longer eligible for FTB processing.
65,217 Databases may be defined. This includes any widow databases of course (Typically DSNnnnnn style empty databases where the implicitly created tablespace has been dropped when the table was dropped, but the database was “forgotten”).
32,767 OBIDs within a single database. Remember, that the number of objects (OBIDs) within a database is not a simple 1:1 relationship. Each tablespace, index or referential relationship takes two, whereas each table, check constraint, aux for LOB, XML for XML, trigger or view with INSTEAD OF takes one.
Stay up to date
If Db2 development bring any new ones out, think Agile here, then our SAX will be enhanced quickly to check for it!
What is the plan?
Simply schedule the SAX limit checker to run once a week or so.
- It will report all its findings and, if required,
- issue WTOs to alert you to any dangers before hitting any of these limits.
And you?
What do you do at your shop now? Do you monitor these hard limits? Does anyone care?
Go ahead, make my day and install a SAX trial!
For a SAX trial please email to : techsupport@seg.de
Consult our Space AssuranceExpert (SAX) page
As always I would be pleased to hear from you!
TTFN,
Roy Boxwell
Senior Architect