Yes, I admit it, I was surprised that IBM actually called it Db2 13 for z/OS in the end. I know IMS also had a 13 release but I still believed they would jump to 14! After all Apollo 13 was a disaster, albeit with a happy ending caused by a whole bunch of engineers working really well with the hardware and the design. I guess that was the reason for the „Apollo“ code name of this release of Db2. Anyways, on to my review of all things new and interesting in Db2 13.
What’ve We Got?
The most important bit is that you *must* be on Db2 12 FL510 to even think about getting to Db2 13. This is done to make the migration as easy as possible with no gotcha’s happening along the way!
System Check First!
Remember that automatic rebind will occur for any packages created before Db2 11. This should not happen but is never a good idea in production! Further, a whole bunch of ZPARMs have been removed and so you must check to see if you set these differently than default, if so then check what will happen in your shop with the „new“ values. Here’s a direct link to the docu all about new, changed and deprecated ZPARMs: https://www.ibm.com/docs/en/db2-for-zos/13?topic=13-subsystem-parameter-changes-in-db2
FL100 Hits
FL100 allows you to fallback to Db2 12 FL510 and also permits co-existence in data-sharing. This enables 24×7 availability as you go round-robin with the load libraries and bounce your Db2 members.
What Else do You get at FL100?
Index look-aside optimizations are there for INSERT, UPDATE and DELETE.
Sort
It gets seriously enhanced:
- the ability to generate machine code enabling DECFLOAT
- if you use grouping set, multiple distincts and PERCENTILE you also get generated machine code
- Sort can use its very own workfile
- larger sort tree size
- a check for ordered data on first iteration
- LISTAGG gets SUBSTR support
- Ability to avoid rereading a workfile, but only if Watson Machine learning is enabled
- Shrinking the length of long varchars (over 100 bytes), again only if Watson Machine learning is enabled
- z15 expanded support of SORTL, this also requires Watson Machine learning to be enabled
PBG Insert
The insert mechanism for PBGs got a nice update to drive a retry if the partition lock fails on the first insert attempt. This could well stop excessive growth of PBGs with heavy insert activity.
Below-the-Bar (BTB) Reduced and Above-the-Bar (ATB) Enhanced
As in all releases of Db2, the amount of BTB storage has been reduced primarily by reducing the agent BTB storage to use ATB storage instead. This applies to dynamic SQL statement text and attribute strings. When BTB storage exceeds 64% Db2 will automatically trigger a storage contraction of all private storage pools. Further, if you are at z/OS 2.5 you can use a new dynamic allocation function by updating the ALLOCxx parmlib member and set the SYSTEM SWBSTORAGE to ATB. This reduces the overhead per open dataset from 5KB down to 4KB. It might not sound much but when you have 200,000 open datasets it all adds up! The ATB storage clean-up also got improved to stop the usage of the IARV64 REQUEST(DISCARDDATA) during thread deallocation. Now a system level timer is used to trigger these clean-ups.
ECSA Reduced
For IFI users the ECSA usage has also been reduced from 50MB down to 8MB, however, you must make sure that you set aside around 50MB in HVCOMMON and 25MB for private storage to compensate for this. There is no such thing as a free lunch after all! The DDF per thread ECSA is now down to be the same as a local thread whereas before it was always 2KB more for a remote than a local thread. When the ECSA exceeds 85% Db2 will automatically trigger a storage contraction of all allocated pools within the ECSA.
DBAT Optimizations
These reduce the frequency and number of terminations and also flatten the spike when a surge of short-term DBATs increase usage quickly.
External Security Improvements
Db2 13 now caches the plan authorization checks as long as you have z/OS 2.5 and zparm AUTHEXIT_CACHEREFRESH set to ALL. At the same time, more IDs per plan are now cached.
Enhance RECOVERY Feature
RECOVER is enhanced so that it can use TP/IP level copies when a DSNUM ALL recover is requested. Pre-requisite is that it is only allowed on objects that are based on a UTS. Previously, RECOVER required a TS/IX level Image Copy to process this. Now it will check to see if it can do the RECOVER based upon the TP/IP copies that have been done.
FL500 Hits
AI
Artificial Intelligence finally comes to Db2 for z/OS with SQL data insights. These give cognitive intelligence to Built-in-Functions and can be used in SQL queries. More on these new functions later!
Role-Based Package Ownership
This completes the ability to use Trusted Context which assigns a ROLE to an inbound connection. It can now use this role as the package owner.
Utility Changes
Page sampling is now allowed for inline statistics. Within a REORG TABLESPACE or a LOAD utility you can now use page sampling (Like you can within RUNSTATS). Before this, only row sampling was allowed and page sampling can give a much bigger saving in CPU and elapsed time.
Online Conversion from PBG to PBR
As long as you have some sort of usable partitioning key you can now use ALTER to migrate from PBGs, which are getting very big and unwieldy, to the new and much better PBR RPN type UTS spaces. After the ALTER, a REORG simply completes the migration.
FTB Expanded
Unique index maximum size for FTB is now raised up to 128 bytes and for duplicate indexes up to 120 bytes. This greatly increases the possible scope of FTB usage.
Lock Timeout Controls
CURRENT LOCK TIMEOUT special register can be used to change the TIMEOUT by the application or even the SQL Statement. This can help reduce lock contention.
Profile Updates
The profile table can now be used for local threads as long as DDF is started with AUTO or COMMAND. These tables can now also contain CURRENT LOCK TIMEOUT and also the RELEASE_PACKAGE keyword with one of the COMMIT attributes.
Active LOG Delete
You can now delete an active log while Db2 is running, using the new REMOVELOG option from the -SET LOG command without having to stop and start D2b afterwards. This is very handy when you wish to resize all of your active logs!
How fast? – Very fast!
The first „real“ FL version 500 has *no* catalog changes! This simplifies migration again as there is no „real“ CATMAINT. There is still a CATMAINT job but it just sets internal flags and does *not* do any changes to the Catalog.
FL501 Hits
Deadlock Priority
New built-in global variable SYSIBMADM.DEADLOCK_RESOLUTION_PRIORITY gives you the ability to set your relative weighting about „who should die“ when a deadlock is detected. This is also added to the profile tables by the way.
Catalog Changes!
One duplicate catalog index is, finally, dropped and the brand new SYSIBM.SYSUTILITIES table is created and will start to be INSERTed into, more about this table later! Some of the real-time statistics (RTS) statistical columns got uprated from INTEGER to BIGINT or SMALLINT to INTEGER. This helps for really big shops where the RTS numbers just maxxed out all the time! Further, lock escalation is disabled on all RTS and RTS History tables.
Index Splits
The RTS also got three new columns in the SYSINDEXSPACESTATS table:
- REORGTOTALSPLITS – How many index splits since last REORG
- REORGSPLITTIME – Aggregated elapsed time for all index splits since last REORG
- REORGEXCSPLITS – Number of abnormally long (over one second) index splits since last REORG
All of these columns give good data about your index structures and possible changes to their definitions.
AI BiFs
We get three new Built-in Functions in Db2 13 FL500:
- AI_ANALOGY – Computes an analogy score between two values
- AI_SEMANTIC_CLUSTER – Computes a semantic clustering score of a member argument against a set of clustering arguments
- AI_SIMILARITY – Computes a similarity score between two values
In fact, these are the *only* BiF changes in Db2 13 up to FL501! I will not go into detail about how they work here but there is a bit of work that must be done „behind the scenes“ to get these beasts working… again – no such thing as a free lunch!
SYSIBM.SYSUTILITIES
Is a very interesting catalog table that contains a list of all Utilities that have run on the machine. It is updateable by anyone with the necessary rights so I recommend at least a yearly purge or it could grow to crazy sizes… The first support is pretty basic, just EVENTID, NAME, JOBNAME, UTILID, USERID, STARTTS, ENDTS, ELAPSEDTIME, CPUTIME, ZIIPTIME, RETURNCODE, CONDITION: Blank – Active or stopped utility, E – Execution has ended, F – Execution terminated by -STA db(x) sp(x) ACCESS(FORCE) or T – Execution terminated by -TERM UTILITY, RESTART: N – not restarted, Y – restarted, NUMOBJECTS, LISTNAME, STARTLOGPOINT: RBA for non-data-sharing or LRSN for data-sharing, GROUP_MEMBER, SORTNAME, SORTCPUTIME and SORTZIIPTIME. All of the other columns are „for future use“ but with those columns that are filled you can do some interesting Utility analysis! The interesting factoid about this table is that all the time columns were originally planned as being CHAR(8)… Thankfully IBM decided to change them to BIGINT columns containing microseconds thus making the table actually usable!
Getting there…
Of course you have to get to Db2 13 before you can start using all the stuff I have just been discussing. To make sure you get there as quickly and painlessly as possible, why not download our Migration HealthCheck software? It is free-of-charge, with licenced extensions, software that lists out the „state“ of your Db2. This includes all the prereqs for Db2 12 FL510 which must be reached before going to Db2 13 FL100 as well as all other deprecated features!
Any Plans?
What are your plans for going to Db2 13? I would be delighted to hear from you!
TTFN
Roy Boxwell
Update: One of my valued readers asked me if I could check whether or not LISTAGG accepts ORDER BY or not in Db2 13. Sadly I can confirm that only SUBSTR was added. ORDER BY still dies a death with an SQLCODE -390. My reader had even opened an AHA Idea request for this support, which I also voted for by the way, but it was rejected by Db2 support. The question that both of us have is „Why not?“ What is so hard about adding sort support? My personal theory is that, internally, it uses a LOB style object format which is not sortable, but why not just tell us? In Boston I will ask around…