I have recently enhanced our SpaceAssuranceExpert (SAX) product to automatically help out if partitions, or partitioned indexes, are getting too big for their boots in a productive system.
SAX – What is it?
The idea behind SAX, is to stop any and all chances of getting a dreaded SQLCODE -904 in production, especially “out of the blue”.
Our idea was, in Db2 12, with the new PBR RPN to do an on-the-fly ALTER to the DSSIZE, just like SAX does now with the SECQTY to avoid running out of extents.
RTFM Time
A quick look in the manual tells you that :
it is an immediate ALTER (as long as you make the new DSSIZE larger than the old one!) and there are no package invalidations or REORGS required.
This is fantastic!
So I created a nice little PBR and then ran a horrible Cartesian join SPUFI to flood the first partition with data. This join had a TIME card of (,1) to limit it to one second of CPU before getting an Abend S322.
SAX Monitor
The SAX monitor reacted perfectly and did the TP alter to 513 GB DSSIZE (I had an increment size of 512 as a test), and got an SQLCODE -666 (I just love that SQLCODE…), as the INSERT was still running.
This ALTER was then internally queued to be attempted later, like in any other failure case.
All well and good.
Wham! Nasty errors !
Then I did the same for a DPSI on my PBR RPN… Oh dear!I got a nasty IO Error and then an even nastier ROLLBACK loop, meaning I had to cancel the IRLM to stop Db2… (There is an APAR for this problem PH18977.)
Rollback loop?
The ROLLBACK loop was caused by me choosing to use NOT LOGGED as a tablespace attribute. We have a finite amount of log space, and when the transaction was S322’d after one second of CPU,
the rollback could not find one of the archive logs and then we had to cold start Db2
– Not pretty!
Proper test!
I then wrote a couple of little test programs that actually COMMITted after 5000 inserts and then the ALTERs all worked as designed.
Do I worry too much about extended format and extended accessibility?
Next, I worried about the ominous “extended format and extended addressability“ attributes in the DATACLASS for a PBR RPN, and wondered what would happen if a customer has SAX running and it happily ALTERs a TP to say 6 GB when they can only address 4 GB…
IBM to the rescue!
Luckily for us, Db2 development had thought about this!
If you attempt to create a PBR RPN (even with a very small size) and your DATACLASS does not have the two attributes set, you get an error message 00D70008 telling you this detail.
If you ALTER an existing tablespace to be a PBR RPN and your DATACLASS does not have the two attributes set, then the ALTER works fine. But remember, this is a pending alter and you *must* do a REORG at the TS level with inline TP level copies. This REORG then fails – also with 00D70008.
So, in other words, SAX cannot hurt you here!
What about PBGs?
After all this we also considered PBGs. They have a limit as well – MAXPARTITIONS in their case. So we added an ability to also ALTER MAXPARTITIONS as well. Here you must be more careful though, as these ALTERs are still immediate *but* they invalidate any referring packages!
ABIND YES or NO?
If you work with ABIND set to YES all is good as the ALTER comes in, Db2 invalidates your packages and the auto rebind happens so fast that you do not even notice it happening, however, if you work with ABIND NO then any packages, even the package actually running and doing the inserts, will fail! If you are just using dynamic SQL then it is 100% OK otherwise – Buyer beware!
Aha!
I have opened an Analytics Idea (DB24ZOS-I-1057) to try and get this loophole closed, as I cannot see what access path change could be affected by going from 32 to, say, 36 MAXPARTITIONS.
Db2 keeps adding the parts dynamically and nothing happens then… Go figure… Anyway, if you would like it – Please vote for it!
There are already some nice comments attached to it:
DP commented
this is just a limit in the catalog. So how could access path be affected? The actual growth of a partition doesn’t invalidate the package so how just changing the limit do so ?BW commented
I opened a Case on this asking why packages are being invalidated in this situation and it is still open waiting for a reply.BD commented
With only impact to catalog, not sure how Access Path would be impacted. Seems wasteful and counter productive to invalidate packages.
Remember – You never stop learning!
As always I would be pleased to hear from you!
TTFN,
Roy Boxwell
Senior Architect