Db2 V13 - what is new and exciting from a user perspective

Db2 Version 13 is upon us! After V12, IBM was a bit vague about when a new version number for Db2 would be created. Why? IBM was using Db2 “continuous delivery” and function levels to introduce new functionality to Db2 Version 12. IBM and Db2 were agile and fast. Great. New functionality would come constantly and we would not need to wait years for a new version number to bring new functionality.

Previously, for the past few version numbers, IBM would introduce a new version number to Db2 every 3 years. But it has been almost 6 years since Version 12 was introduced (Db2 V12 GA - Oct 21, 2016). IBM has now decided it is time for Db2 V13 which was announced in April for GA on May 31, 2022. 

Basically, I think the new functionality that IBM wanted to deliver with Db2 became too much to be bundled with the current V12 existing continuous delivery model. It seems that significant new functionality required significant changes to Db2 software. It is probably just easier to put it all together in a new version number and recompile everything as necessary! Besides, new version numbers generate more buzz! So now we get lucky V13.

There are several locations for discovering information about Db2 V13:

• Of course, you could attend an IDUG conference. At IDUG NA Boston 2022, IBM made a big presentation on the highlights of V13. It was an excellent summary.
• The IBM Documentation website (formerly known as the knowledge center) has the official what is new in Db2 V13. The IBM documentation is always my place to start for any Db2 question, after all, it is the official reference documentation.
  • One should always read the IBM documentation What’s New summary for the official low-down on new functionality. Do not forget> Read both the new functionality delivered via APAR section and the section on function levels! Not all new functionality comes through a function level.
• The IBM lab has a blog for Db2 community where key announcements are always made: IBM News from the lab community blog V13 GA function level summary
• While the IBM Documentation is all about being good reference, the IBM Redbooks describe WHY we do something. As a result, IBM has a new Redbook for V13 called IBM Db2 13 for z/OS and more (SG24-8527) which is a great 300 page book on what is new and why and how to use it! Very detailed! Is it relaxing summertime reading? It depends.

You can read all the above official IBM documentation on why V13 is important and what is new. This blog here will highlight what seems important to me, as a user of Db2 software. Basically, I read the What’s New section of the IBM announcements for V13 to find what is exciting for me and my end-users (and my managers)! 

• After all, if I can’t get them excited then they may drag their feet and slow down my hopes for a reasonable and fast implementation of V13 at my shop.
• I look at new functionality and think what is exciting for them and what is exciting for me?

Db2 V13 implementation

For the system folks and dbas who worry about these things, IBM has made V13 implementation easier and smoother then ever. I personally have not had to fall back a Db2 release upgrade in my shop (knock-on-wood). BUT, this time the base V13 comes with three function levels out of the gate (not the two that came with V12) and these 3 make it even safer to upgrade. First, one goes to V13FL100 which is totally compatible with V12 and allows for fallback back to v12 (in the sad case when that fallback is necessary). Then when you know you are sticking with V13 (and all members of the Data Sharing group are V13FL100) then you go to V13FL500 to begin to get the real V13 new functionality, but no catalog changes yet! And then the CATMAINT changes the catalog to allow you to go to V13FL501 and then you get the final bits of additional new functionality. This prudent and safe way to upgrade to V13 should minimize risk of fallback and help reduce the worries about those that worry about that type of thing!

Db2 V13 GA and V13FL100

V13FL100 delivers the most changes to Db2 compared to FL500 and 501. But of course, it is mostly stuff that is not application specific. The changes are more of a system level for performance and what not. They are all good things. But what is exciting to me?

• Relative page numbering can now be default for new PBR tablespaces. Apparently, all NEW PBR tablespaces really should use relative page number - no downsides to it - so IBM is encouraging that PBR type by making it more of a default.
• More concurrent open datasets. The ZPARM of DSMAX has a new max possible value of 400,000. The default remains at 200,000. This might benefit a busy non-prod Db2 with MANY db2 databases (dev/test/accept, etc) so that more and more can be open concurrently. If that is your problem today then this enhancement will be good for you. I learned a few years ago that it was detrimental to hit DSMAX. Especially in prod. But it is more likely to be hit in non-prod (with all those test/dev/accept dbs)
• Column names can now be greater then 30 characters in length. This is interesting. It is not a requirement for me today but it is interesting. 
  • Although, there is a caveat that some Db2 API that report back column names might be limited to passing the first 30 characters of the column name. So don’t be naming multiple columns with the same first 30 chars!
• New accounting for longest wait time for different suspension types. Again, interesting for the keen dba who is worried about performance. For a Db2 transaction that takes longer then desired, we need to understand where the application time is spent, is it one long wait or many little waits and what is it waiting for! This enhancement adds accounting info on the longest wait by type. I think this will be useful and interesting. I assume my Db2 performance monitor (and the perf db) will soon be ready and keep up with this new Db2 accounting!
• Enhanced space-level recovery with the RECOVER utility. I must have missed the details or emphasis of this new functionality while at IDUG Boston. But it seems interesting that we can now recover a whole tablespace using partition level backups. I will think about the implications for my shop.

Db2 V13 GA and V13FL500

V13FL500 delivers most of the high profile new changes for V13

• SQL Data Insights, SQL Data Insights, SQL Data Insights! This is the big one. The whole of Chapter 6 in the REDBOOK is devoted to this topic of SQL Data Insights. This is high profile new SQL functionality that should get everyone excited. Data Insights is an optional installable feature to V13 and requires some effort to install and configure the first time. There is also a SQL DI (web-browser) GUI component. But after it is all installed, configured and ready then it should be useful. To oversimplify. Data Insights (DI) brings AI capability directly to the Db2! You tell it a set of Db2 tables to watch and then you can run AI semantic queries against the tables using actual new SQL functions called: AI_SIMILARITY, AI_SEMANTIC_CLUSTER, and AI_ANALOGY. Basically, the new functions can be used to find interesting things like rows that are like other rows. The REDBOOK has many examples. Of course, IBM has presentations and blogs out there. 
  • I suppose part of the point of SQL Data insights is it allows you to ask sophisticated questions of your data without the usual ETL often done today where specialists look at the data in this remote environment. SQL DI will run with the Db2 with low cost (it is ziip eligible like most new application functionality is ziip eligible) and SQL DI allow quick reiterations questions to find valuable answers.
• Online conversion of tables from growth-based (PBG) to range-based (PBR) partitions. This is another high profile new functionality! Apparently, some people have been waiting for this Db2 functionality for a long time. So if you were waiting… you are now happy! Basically, if you make a tablespace PBG (segmented) and then later regret that choice, it is now possible for Db2 to convert it into PBR via ALTER and online REORG.
  • I suppose the logical extension question: can Db2 help you go back from PBR to PBG? Not yet. Should it be possible? Good question. It could be useful in some cases. Or do we just suffer with our PBR which have too little data. Maybe there is an aha (RFE) idea.
• Ability to delete an active log data set from the BSDS while Db2 is running. Now we can add and remove active log datasets while Db2 is up and running. Useful for the few times I think about changing my log sizes and I want to be neat and tidy. I suppose the system dba is excited by this one.
• SPT01 and SYSLGRNX table spaces are converted to DSSIZE 256 GB. This is important for the non-prod Db2 with many many packages and many many tables in various schemas. I am surprised these two tablespace were not already bigger. 256GB is pretty big. 
  • Also, I guess IBM thinks a PBG with DSSIZE of 256GB is reasonable. Good to know. Maybe I should do that more often.
• CREATE TABLESPACE uses MAXPARITIONS 254 by default. Instead of 256 in V12. IBM has a reason to reduce this default (when you read the details, you will see). But still, I thought the rule was to always pick an explicit and reasonable MAXPARTITIONS value for the tablespace! (and the reasonable rule, when knowing nothing else, is to start with 1 or a low number). 
• New special register CURRENT LOCK TIMEOUT allows applications to specify how long they will wait to acquire locks. Previously, it was only the system parameter IRLMRWT that controlled how long to wait and it had a max value of 3600. This special register can go 32,767 or indefinite! But be careful, you don’t want to wait too long because the locks owned by the application already may be desired by another application and you may cause it grief! Basically, this allows disparate applications to live in the same Db2 but control how they wait for locks. Today, if it was critical, one application would have to suffer and live with the IRMLRWT value required by another application. Or the two applications might need to go in separate Db2 (which seems like a lot of work just to have different waiting times!)
• Increased flexibility for package ownership. This will help the neat and tidy DBA get things in order.
• Page sampling for inline statistics. Now inline statistics is more like RUNSTATS. In hindsight, it should have always been this way. It will save a bit of CPU

Db2 V13 GA and V13FL501

V13FL501 requires a CATMAINT and introduces the V13 catalog changes. Including some new catalog columns and catalog tables

• There is a new Db2 GLOBAL VARIABLE (not to be confused with a special register -> global variables and special registers are similar but different). The global variable is SYSIBM.DEADLOCK_RESOLUTION_PRIORITY. The higher the value, the less likely that lock requests that are requested by an application are denied when the application is involved in a deadlock situation. The point is to control an important application so that it is not the victim in a deadlock (if that is your “problem” for some reason). Because DEADLOCK_RESOLUTION_PRIORITY is a global variable, the DBA can control WHO can set this value! After all, the application that uses this global variable can impact OTHER applications so you might want to think about WHO is allowed to set it!
• New Db2 catalog table, SYSUTILITIES, for the collection of real-time and historical information about ALL utility executions. This is similar to everyone’s favourite SYSIBM.SYSCOPY which has the primary purpose to document all utility executions and actions that impact tablespace recoverability. I use SYSCOPY every day. The new table SYSUTILITIES is more then just history of utilities that impact recoverability. I think that SYSUTILITIES will be new favourite catalog table. It is different then SYSCOPY in a few ways. Rows are inserted into SYSUTILITIES when the utility starts. And the rows are updated as the utility progresses. This will allow one to query SYSUTILITY to check the status of a utility run. Interesting. The Db2 V13 redbook (link above) provides many sample scenarios and SQL for querying SYSUTILITIES and even joining with SYSCOPY. With time we will probably see this new SYSUTILITIES is a useful table for us DBAs!
• Profile table are enhanced for application environment settings.
  • Note to self, I really must use profile tables more aggressively.
• Real-time statistics are changed for scalability and support for index splits. 

When reading the official IBM documentation, it is important to note the difference that some new functionality comes simply via APAR and not new function level. The new functionality via function level tend to be utility level changes whereas function level changes tend to be more application specific. 

In Version 12, you may remember that the super useful “redirected recovery” was introduced via APAR (first for tablespaces and importantly separately for indexes). And then as an example of something super useful via function level, I always loved LISTAGG in V12FL501