Well now I can finally talk about DB2 11 for the first time …
We have had it for over a year now and have been in the ESP from the beginning. In this newsletter I want to give you a „first look“ at what I think is cool, great or odd!
DB2 11 – here’s my personal list of *brilliant* down to *why*?
Brilliant
REORG TP with NPSIs got a nice boost in performance and the new ability to do TP copies during a DSNUM ALL REORG is *very* good. REORG can now do much better and faster inline statistics including histograms. The switch phase got a massive boost as well. Mapping tables also became history in this release! You can finally drop ‘em all from your system. DB2 will create and use them on the fly. In NFM you *must* use the new format mapping table so you might as well let DB2 do the work for you!
RUNSTATS now works *without* a profile – Hooray! Terry Purcell can now sleep better at night but the update of the RTS TOTALROWS and TOTALENTRIES is *still* only done if you run with SHRLEVEL REFERENCE – and how many of you do that?? A new option RESET ACCESSPATH finally gives you the chance to *remove* all access path data in the catalog for the given objects. This then enables a new RUNSTATS to collect only the information you actually need/want. ZiiP offload has been greatly increased as well.
For the Optimizer there is Index Skipping to enable faster duplicate discarding processing, a new In Memory Data Cache (IMDC) to enable better run time optimizer choice depending on the current amount of free memory (This memory cache value (MXDTCACH) is default 20MB and can be increased) and a bunch of new query rewrite techniques to help „automatic bad SQL generators“ get a chance of index access. E.g. WHERE YEAR(date_column) = 2013 is rewritten to WHERE date_column BETWEEN ‚2013-01-01‘ AND ‚2013-12-31‘. Finally OR and IN list with NULLs gets a rewrite boost as well so WHERE COLUMN IN (‚A‘, ‚Y‘) OR COLUMN IS NULL is simply rewritten to COLUMN IN (‚A‘, ‚Y‘, NULL). All these changes allow index usage where it was not possible before.
The optimizer team also delivered a seriously good boost for embedded CASE structures that used to be anathema to DBAs:
SELECT COL2, COL3
FROM TAB1
WHERE COL1 = CASE (CAST(? AS INT))
WHEN 1 THEN 'R'
WHEN 2 THEN 'O'
ELSE 'Y'
END
;
SELECT TAB2.COL1, TAB1.COL2, TAB2.COL2
FROM TAB1, TAB2
WHERE TAB2.COL1 = CASE WHEN TAB1.COL1 = 'Y'
THEN TAB1.COL2
ELSE TAB1.COL3
END
;Both of these used to be stage2 but now can actually use indexes!
Great
Expanded LRSN from six to ten bytes. DB2’s six byte RBA meant it could store 2 to the power of 48 bytes of data on the Log or 256TB of data. Back in the late 80’s this was thought to be „more than enough“ … So now we have 2 to the power of 80 or 1YB. Yep, that’s a Yottabyte of Log! This is optional but from NFM on DB2 will internally always store ten bytes – the externalized data is dependent on whether you do the migration or not. However, it is highly recommended as you will get a small performance hit to convert down to six bytes. The other boost this gives is a reduction in spins waiting for a unique LRSN in data sharing which – with the faster and faster hardware – was becoming a bottleneck.
DSN1COPY – The first time a dataset is opened for normal business after a DSN1COPY, some basic „sanity“ checks are run to try and flag „bad“ DSN1COPY runs before too much work, hopefully any, has actually been done. This is very handy as the DSN1COPY is notoriously difficult with its parameters and input control cards.
AUTOSIZE on Bufferpools gets a wonderful fix to stop „runaway“ autonomic mismanagement. In other words, you can set upper and lower bounds which will *not* be exceeded – very nice!
Pseudo-empty index page cleanup daemon – A very nice „set it and forget it“ feature that actually removes completely empty pages from indexes, thus reducing the need for REORG INDEX and boosting performance.
Useful
Global Variables – Pretty handy feature in DB2 11 to easily pass data along a chain of programs. A bit like a SPA in good old IMS!
JVM only being started once and using 64 Bit storage is a major winner for most shops as nearly everyone uses JAVA these days and sometimes even on Big Iron.
Archive Tables – Similar to „time travel“ tables from DB2 10 but looked upon as an „extension of the current table“. This is very handy indeed when all you want is a bucket to throw any DELETEs into. Plus you can use this bucket as input to be added to the current table or not – Very nice feature this!
DROP COLUMN – I will be using this as I have *always* : created a copy table, cross loaded it, dropped the original table, created the new table without the column, cross loaded back to it, created any dependent views, GRANT access back. Reorg, Runstats, Rebind – Done *phew*. Now you can simply Drop the column and then schedule a reorg and you are also done!
Autonomous procedures – This gives the chance to COMMIT any work done *just* in the stored procedure. The main task can still ROLLBACK but that will not affect the data COMMITted in the proc. This could be very handy or a complete disaster depending on your business logic!
Faster recovery of directory data because certain directory tables/indexes update RBA/LRSNs are now stored in the SYSLGRNX. This is a very good feature for recovery of catalog objects of course!
Why?
GROUP BY multiple columns and aggregates has been added – now here is another new SQL feature, like OLAP queries, that nearly no-one will ever use!
Enforce NUMTCB=1 for stored procedures … this is an enhancement?!?!?
And last but not least
Finally my personal little favorite – LIKE_BLANK_INSIGNIFICANT is a new ZPARM – it makes the rules for LIKE of static and varchar get a little bit more aligned. This is, of course, a change in behavior and you must make sure that your programs react as you still think they should!
That’s all for now –next time I will dive into details on some of the above topics.
If anyone has any wishes please email me and I will see what I can do!
As usual any questions or comments are welcome,
TTFN Roy Boxwell
Senior Software Architect