2026-10 IDUG EMEA 2026 Review

As usual after an IDUG, I wish to run through the presentations and highlight what I found interesting, newsworthy or just worth propagating to the community at large.

AI or FL511?

This year it was either migrating to FL511 or AI – that was basically it!!!

Location, Location, Location

The food was excellent and the location was great. The AV could have been better, some of the rooms were a bit small, or you were blinded by the sunlight from outside. This never normally happens in late September but the weather was glorious!

Disclaimer

Here is my normal Caveat Emptor – I am going through the downloaded presentations and I did *not* attend anywhere near as many as I wanted but I do also have to work at these events so please accept my apologies if I missed your favorite session or if I quote from a slide that was removed by the presenter or I missed a great quote as the presentation has not yet been updated or is perhaps not even available!

Where are they all?

You must have attended the IDUG and be a member of IDUG to download these! Go to the IDUG web site, login and navigate to:

https://community.idug.org/organization/idug-emea-2026-db2-tech-conference/paths

Here you can download the PDFs in Track sequence.

Off we go!

We start, as always, with the A track for Db2 z/OS

A01 Db2 for z/OS Trends and Directions from Haakon Roberts was the usual great update about all things Db2 for z/OS. Slide 10 with PH69217 is a must-have!

A02 Hard Lessons Learned when using High Performance DBATs with Db2 for z/OS from John Campbell (Now at Broadcom) was a typically great John presentation. Slide 18 all about getting safe before you start is a great tip! The WLM changes are also an absolute must to check and verify when switching to HP DBATs.

A03 What’s New in Db2 for z/OS Performance from Akiko Hoshikawa. A really good update, as usual, about all things performance in Db2. The use of CDDS got a much-needed mention as this really is a “hidden chestnut” at this time! APAR of major interest is PH70190 for work files.

A04 Supercharging Db2 with Generative AI: From Natural Language Queries to Autonomous Performance Optimization from Chris Crone. As well as the longest title ever, this was naturally all about the brave new world of trying to tell newbies that z/OS is not a green-screen dead-end but a VS Code vibrant world ready for the future!

A05 Starter’s Guide to Db2 for z/OS Group Buffer Pool Monitoring and Tuning from Bart Steegmans covered a bunch of Group BP tuning topics very close to my heart as these Baby Brothers of BPs are nearly always, sadly, ignored by most of us! The final slide Summary contains what you must take away!

A06 Strategic Efficiency: Maximizing Db2 Utilities from Denis Tronin was a run-through and update about all the Broadcom utilities that one could possibly use with Db2 for z/OS.

A07 Db2 Utilities: Best Practices from Haakon Roberts was the usual update about all things we should be doing with the IBM utilities today, but are probably not doing! Slide 19 with the “hint” that MODIFY RECOVERY makes REORGs faster was sublime …

A08 AMI Db2 utilities – What’s new was Roberto Cason talking all about the BMC Utilities and what they can do for you.

A09 Db2 z/OS Utility Internals — how to get the most out of them from Preetham Kannan was all about Utilities and which parameters and options are best for doing various tasks. From REORG to RECOVERY all utilities were well covered with examples about why which option should be used and when.

A10 Linuxification of Db2 for z/OS –a journey from ISPF and JCL to Shell and Playbooks from Marketa Muzikova & Emil Kotrc scared me at the title line! Pulls you through Unix on z/OS, ZOAU, Ansible and ends with a string of tips to make sure it all hangs together!

A11 From Pay Slips to P1 Incidents: Db2 for z/OS in a Payroll Company from Tim Vermeulen. A great look into a real company with real applications and real data including some good Buffer pool change results. The BPs were set up years ago and then were basically ignored. The BP, and as Bart said the GBP, are a real gold mine for performance as long as you have the memory for it of course! Then a very nice exposé about HTTP/REST call usage or, as it was put, “API Calls – A solution for the poor” !

A12 Db2 13 for z/OS – DDF Connection Security Controls from Gayathiri Chandran – Everything you ever wondered and more, about DDF and how it securely connects. Basically, we should all be moving to RACF Identity Token (IDT) usage or whichever similar system your security system offers…Or you could go down the road of Client Certificate Authentication for Protected IDs using Trusted Contexts. It was rounded off with a review of SYSTEM Profiles and what they can do to help & control DDF.

A13 was the z/OS expert panel discussion which I missed …

A14 DDF Best Practices from Derek Tempongko was all about DDF and how to find out where everything is coming from! Included is a list of things about VTAM/SNA deprecation which is very useful.

A15 JC Greatest Hits, War Stories and Best Practices 2026 – John’s latest updates about everything! Always worth seeing / reading and reviewing! Once again, GBP Tuning was discussed here in detail…

A16 Get ready for Db2 version 13+1 from Martin Ålund. A presentation after my own heart! The Aha idea DB24ZOS-I-1480 was mentioned about allowing REBIND of PLANs while they are in use and I voted for it straightaway!

A17 DB2 for z/OS Workfile Deep Dive – How to Survive and Thrive from Adrian Collett. A detail-packed deep dive into these very misunderstood beasts of burden that we all take for granted! After the presentation is a ton of data going into crazy detail about all you will ever need to know…

Track B up next!

Onto the B track for Db2 z/OS which starts with B02 as “B01” would have been parallel to A01.

B02 Assessing Your Db2 Security for z/OS Without losing your mind (or Your Data) from Jørn Thyssen. The Six Pillars are all exposed and detailed. Including a step-by-step guide to assessing your current level of security as well as prompts for the AI of your choice to generate stored procs to do some of the heavy lifting for you. Slide 24 is a very nice Ownership best practices review. Included is “the triumvirate” of RACF Classes.

B03 My new besties: the Utilities tables from Soledad Martínez. This interesting presentation includes details on rows that are DELETED by conversion REORG processing. Includes a whole bunch of handy SQLs for doing interesting things and finding stuff out using these tables. Aha ideas get a page as well – so please look and go vote!

B04 Z Data Strategy & Integration Update from Cuneyt Goksu. This was more a C level presentation about how good IBM hardware/software stands at the moment in the business and AI world. Wrapping up with Kafka use cases and Confluent.

B05 Beyond the Basics: Backup and Recovery for Resilient Db2 Environments from Aysen Svoboda. Went into good details about what is a disaster, how are they caused, what you can do about it and how you can recover as quickly as possible. Plan, plan and plan again! REPORT RECOVERY should be your best friend!

B06 From Complexity to Operational Intelligence: Modernizing Database Administration with AI on IBM Z from Akiko Hoshikawa – like A03, no download available here.

B07 Revisiting Query Parallelism: Targeted Use of zIIP Processors for Db2 Queries from Thomas Baumann. Thomas shows us a way to revisit Query Parallelism in the light of the fact that we all have lots of zIIP processors these days as opposed to DB2 V4 in 1995 when it was introduced! Extremely good and interesting query rewrite examples leveraging parallel processing and ending with “in a Nutshell” on slide 41. Great stuff!

B08 Get Cozy with WLM: Why Every Db2 DBA Needs to Understand Workload Management from Denis Tronin. A complete introduction to all things WLM and distributed workload identification possibly requires application changes to actually use the _CLIENT fields. A nice round up of pitfalls and common mistakes followed by examples.

B09 Buffer Pool tuning process with a reverse application analysis from Pawel Hryb. Back to one of my favorite topics but with the twist of working backwards from the IFCID 199 output – Great idea! Good result on slide 26. One of the biggest finds was that simply REORGing SYSCOPY tablespace fixes a huge problem! My personal Mantra is SYSCOPY and SYSLGRNX reorged once a week every week.

B10 Db2 13 latest real customer experiences – new functions, best practices and some more…from Ute Kleyensteuber. Ute’s usual very good presentation about all things Db2 13 FL508 plus a page full of APARs that you should all have written down somewhere! Also included were the details of her on-going migration to 100% UTS and finally a review of early FL509 experiences.

B11 Bringing Mainframe into the Future: OpenTelemetry for Db2 for z/OS from Christoph Theisen. Gave a full introduction to the Whats, Hows and Whys of OpenTelemetry including the APARs PH67971 & PH68073 required for OTel support and the new commands for starting and stopping it all. Finishing off with a lessons-learned section and the difference between IFO and OTel today.

B12 Db2 critical events monitoring from Roberto Cason. This session went through a whole gaggle of messages and codes  and how to decode from where and how they were issued, then listing out all critical events that require instant responses!

B13 Introduction to SQL-PL from Les King. This was marked as being “cross platform” but I am pretty certain it is mainly of interest to LUW people. But it does contain a ton of example SQL PL language usage.

B14 The Db2z Game – No download available.

B15 Db2 Analytics Accelerator: V8, Migration experiences, future and customer updates from Cuneyt Goksu. Here we get a run through of the IDAA and Accelerator ecosystem including the IDAA on LinuxONE. Then the V8 update info including SQL Data Insight Pro.

B16 From Lilliput to Brobdingnag. Compression flavors in Db2 for z/OS from Ivan Losada. Absolutely everything you have ever wanted to know and more about Db2 on z/OS compression! Sadly, the graphics did not make it to the presentation but you can still read it all!

B17 Leave the dark side – Turn on the lights in Db2 performance management from Gerald Pfeiffer & Tim Meisner. They ran through the SVA product zInsights showing all the data you can capture and they can post process into clear graphs and drill downs. Snazzy! Lots of WLM set up and modernization data as well.

Track E now

Now onto track E – Themes I, where I have picked out all the z/OS relevant ones.

E02 Db2 Evolution to UTS from Haakon Roberts. Here, he went through the deprecated items again and then went through in great detail how and why to get to a 100% UTS world. The interesting point for me was the comment “Not wise to go beyond 100 tables at a time” during a MOVE TABLE statement. Another good point is to watch out for more open datasets, DSMAX could be hit nowadays! Finally, the old problem of OBID Limits could also easily be hit. You can view my Know your limits presentation where I explain this and other limits in detail.

E03 Autonomous Auditing: High Score on Db2 Security Features from Steen Rasmussen. Steens very last EMEA IDUG after being at 73 different IDUG style of things … impressive!!! The presentation is a quick review of all the autonomous security features that have arrived over the years to enable us to actually get nearly Autonomous Auditing!

E04 Smarter Db2 REORGs: A Real-World User Story from Shivam Pratap Singh. A real deep dive into just REORG! When you should do one, how you should do one and why you should review the reasons why you are doing it, and also: “The best REORG is possibly no REORG”!

E08 No Pain – No Gain: Story of Optimizing DELETE Purge Processing from Tereza Binarova discussed how one delete statement triggered six months of investigation and then this presentation as well! All you ever needed to know about DELETE processing and more! An interesting walk through what you can change or tweak and how it helped or did not – all summed up nicely on slide 29!

E09 Seamless SNA/VTAM migration (preconditioning for next version of Db2) from Derek Tempongko. A lot of firms are being bitten by this deprecated item. I think more than IBM Db2 development figured! So, it is to be expected that we get help in migrating to TCP/IP.

E10 Stabilize your dynamic SQL and don’t let it remain “unpredictable” from Colin Oakhill. Stabilized Dynamic SQL has been around a while but not many shops are leveraging its usefulness. This presentation takes a nice deep dive into Dynamic SQL and how to use and manage Stabilized Dynamic SQL.

E11 Exploring Db2 Explain Facility for the next Gen of Db2 DBAs and using AI to bridge the skills gap from Phill Baker. A deep dive into all about EXPLAIN and what the Optimizer “knows” about things before it decides its best lowest-cost access path. Using AI to help drive EXPLAIN understanding with a ton of prompts and answers.

E12 SQL Injection on SterAIds: Is Your Db2 Ready for AI-Assisted Attackers? from Petr Plavjanik. Showed us that SQL Injection, especially backed by AI, is alive and kicking! The “Is Your Db2 Ready?” checklist on slide 40 is a must-read for us all! All a bit scary, really …

E14 Detect, analyze and reduce IX Splits using RTS History from Laurent Kuperberg. Does what it says on the label! I have seen vast amounts of logging caused by index splits so I know it is a great area to win performance! Includes how to handle the three different IX splits that can occur and how to choose correct values for PCTFREE and FREEPAGE for your indexes based purely on RTS data.

E16 Getting Started with OpenTelemetry for Db2 from Angelika Heinrich and Rabah Beggar. All about OTel and the shift from Monitoring everything to Observing everything. OTel does not replace IFCIDs but it does supplement them and works at the Enterprise level.

E17 Db2 13 for z/OS – Security updates from Gayathiri Chandran. She runs through all the latest security updates with the keywords we should all know these days: Quantum Safe, Audit, Compliance etc. A note about PRIVATE_PROTOCOL as this parameter will die soon: you could start IFCID 374 to tracks its usage and start GRANTing EXECUTE ON PACKAGE now!

Finally Track F

Now onto track F – Themes II, where I have picked out all the z/OS relevant ones.

F03 Connect, Visualize, Predict from  Niraj Garudkar. All about using Connect with Python to get and run data from your z/OS using lots of different output types and rendering. Crazy what you can do these days!

F04 Insights and experiences from Garanti BBVA’s Fast Traverse Block index implementation in production from Hakan Kabayel. Here we learn all about FTBs with some SQL to find and select them as well as the tip to keep monitoring. The Conclusion on slide 27 shows you the numbers!

F05 When REORGs Go Wild: Managing Unexpected Partition Growth in Db2 PBG Tablespaces from Zoe Andreopoulou and Michal Bialecki echoed their journey through unexpected partition number growth after a REORG. After discovering the cause, they developed a set of tests to try and find the best way to “fix” the problem, basically a tradeoff between the number of indexes and number of partitions before switching to a TS Level Reorg or even abandon PBG and go PBR, if possible! The SORTNPSI YES parameter and the REORG_PART_SORT_NPSI AUTO/YES ZPARM are “your friends”.

F07 The Anatomy of Universal Tablespaces from Emil Kotrc. A really deep technical deep-dive into everything about UTS including all the usual suspects! Contains a great history of Db2 Tablespace from V1 on up – Nostalgia time! Warning: Contains maths! 😊

F08 Ready Player one? from Roy Boxwell (me!) A run through absolutely everything that will bite you before getting to Db2 13 FL511. I showed how to find the bad guys, how to fix the bad guys (apart from VTAM/SNA!) and how to handle SYNONYMS as they can be especially nasty! I rounded off with a quick review of our freeware MigrationReadiness HealthCheck that does it all for you!

F11 SQL Oldies but Goodies and the Power of the SQL Rewrite from Tony Andrews. Tony gave a review of all the new SQL stuff from DB2 V8 up to today and then concentrating on the power of SQL rewrite.

F12 Hidden in plain sight: Db2 recovery from Dhiren Chaudhary. Here he covers the complete RECOVERY scenario asking good questions especially about COPY YES Index usage pros and cons as well as FLASHCOPY benefits etc. He also has a good Aha idea that you could go and vote for : DB24ZOS-I-1852.

F14 Beyond COPY: Modern Recovery Strategies In Db2 for z/OS from Andrea Boaretto and Martin Crippa. They map and document a complete strategy for RECOVERY at any shop highlighting the elapsed and CPU savings of them all dependent on when the error occurs and how the RECOVERY progresses. Fascinating stuff!

F15 Loading large Db2 tables from Rob Gould. This was all about how you should process large amounts of data, by large he means over 1.7 billion rows! Great little tip was the use of z/OS Jobgroups which are basically a poor man’s job scheduler – Great for such a one off, obviously not great for normal production work!

F16 Db2 Connect – New Challenges and New Opportunities from Christoph Theisen. Starting out explaining licensing was a brave call in my humble opinion! I think the IBM naming commission made a bit of a mess of all this to this day! Important tip was EOF for V11.5 on the 30th April 2027. Another “deprecated” item we must address – In the V12.1 a bunch of changes including to the license were rolled out as well as a lot of new functionality. He also mentioned one of my favorite pains and that is the unuse of CLIENT information fields. Guys & Girls please USE THESE FIELDS! They are fantastic for tracking and monitoring – We all know that the application must be updated here but it should be a C level choice to absolutely know which transaction is “owned” by which “process”. It also enables the WLM to manage critical transactions so, for me, this is a no-brainer and this is all simply done by filling in the CLIENT Id fields.

F17 Architecting the Perfect Restore: Mastering Db2 Copies, and Compliance from Ahmed Hamed. A deep dive into the internals of Full Image Copies, Incremental Image Copies and VSAM-Level (SLB/FLASH) Copies leaning into improving your RTO times and then meeting your compliance goals.

Closing words

The winner of the IDUG EMEA 2026 Best User Speaker was Zoe Andreopoulou with F05 – Congratulations and it is good to see that z/OS rules again!

I managed a respectable “mentioned in dispatches” Top Ten speaker entry for which I am also very happy! On a related note, I am also very happy that my surname starts with a B!!!

I thought that this was a most excellent IDUG as the location was great, the people were, as usual, fantastic and it is always a pleasure to present and to meet and greet colleagues, customers and Db2 professionals. Of course, with the Barista coffee machine next  to my booth, my life was better than usual! ☕

After the IDUG is before the IDUG!

The next EMEA IDUG will be held from 26th to the 30th April 2027. Monday the 26th is the pre-conference workshop day. Location is at the new IBM labs in Ehningen, Germany.

As you can see, at the bottom left, we will be south west of Stuttgart next year! Just over a five-hour drive from where I live… Should be fun!

What did you think of the IDUG this year?

TTFN,

Roy Boxwell

2026-09 Utility check up!

This month I wish to run through the big three utilities that you are *all* using and make sure that you are up to date and modern with regards to the parameters that we are all using in them.

Automate this!

I know that once I have set up a batch job to automate something I rarely touch it again. And so it often happens that we have written something years ago that works fine but could work much, much better!

Which ones?

Pretty obviously COPY, RUNSTATS and REORG are the meat-and-potatoes of any Db2 Database maintenance scenario and nearly every shop out there has a bought-in Utility generator like SEG’s RealTime DBAExpert, or they have “rolled their own” over the years.

SHARE your Experiences!

What I mean here is that all modern utilities should, with very few (100% understood and 100% required) exceptions, be running only with SHRLEVEL CHANGE. Naturally, there is a “problem” or two here…

  • RUNSTATS has historically *not* updated the Real-Time Statistics (RTS) if you run with SHRLEVEL CHANGE. This is very annoying and I have complained for years – but it is what it is. If this bites you the best approach is to add an EXEC SQL to manually update the RTS after every RUNSTATS which our very own RealTime DBAExpert does.
  • COPY is *not* recommended if you run with SHRLEVEL CHANGE for RECOVER TOCOPY purposes as it is, by definition, a dirty COPY. This is where FlashCopy and System Level Backup (SLB) technology can come to the rescue. If you use FlashCopy and/or SLB, why do normal sequential COPYs at all?

COPY

Taking an image COPY is one of the absolute standard things we do day in and day out without even truly thinking about it! We *must* safeguard our data and we *must* take regular copies of *everything* but if you have modern disk sub-systems, and we all do these days, why are you not taking System Level Backups (with the VTOC catalog data!) and then just the occasional COPY using FlashCopy with CONSISTENT? Once these FlashCopies have been done you can still copy them to sequential files, like tape, if they must be transported off site – but that is then a secondary process that does not interrupt or hurt real work.

Downside?

There is *always* a downside … For SLBs you need at least *double* the space of your productive Db2 sub-system … Recommended is three times the size … You must make sure that the VTOC Catalog data is also dumped or you cannot use the SLB to recover after a REORG for example. Why so? Well, if the tablespace has “moved” from one disk to another problems used to happen here! This was fixed ages ago by dumping the VTOC Catalog at the same time and should no longer be a concern. The only concerns should be “Can you meet your Service Level Agreements (SLAs) and Recovery Time Objectives (RTOs)?” and “Do I *not* have a single point of failure?” If both can be answered with YES then you are good to go!

RTS is your Buddy!

For COPY the usual rules apply – Do not copy everything everywhere everyday unless SLBs are being used, of course. For the “during the day” copies, FlashCopy CONSISTENT based on updated pages & row count changes in the RTS are the triggers you should use. Sometimes you also need to copy non-updated, and even read-only, data especially if the oldest copy is being threatened with being PURGEd. Basically, you have to work hand-in-hand with your SLAs and RTOs to guarantee that you are up and running again in record time! You should also be running regular checks to guarantee that you are 100% recoverable.

RUNSTATS

RUNSTATS is *only* required when the data has changed significantly, thus affecting the statistics that the Db2 Optimizer uses, or a new index has appeared or been altered and you must “inform” the Db2 Optimizer of these new statistics to choose the best, cheapest and fastest access path.

RTS is your Friend!

Base all RUNSTATS decisions, if no schema changes, on the RTS counters and *not* on any Db2 Catalog column and definitely not on the “every month” or “once a week” style plan!

To BIND or Not To BIND? That is not actually a Question!

Remember that running RUNSTATS without doing a REBIND is relatively pointless as no access path change will ever happen if you do not REBIND. If you are afraid of access path regression, and who isn’t, then always REBIND with APREUSE(ERROR) and APCOMPARE(ERROR) to lock you in to the last “good” access path. But “Buyer beware”, you will never get a better access path either!

What does “relatively pointless” mean, exactly?

Well, it is naturally not 100% true as this completely ignores Dynamic SQL which probably makes up 90% of your SQL workload these days. These *will* use the new statistics *immediately* at the next PREPARE and can thus cause access path problems. This is why RUNSTATS execution times should be carefully chosen and not “willy-nilly” every weekend or second Sunday of the Month!

RUNSTATS Rescued?

There are tools out there, our very own RUNSTATS Rescue for example, that enable you to fall back to a previous set of “good” statistics if the RUNSTATS has indeed killed you. There are bugs in RUNSTATS and sometimes simply bad data in the SYSCOLGROUP as well, both of which can cause the Db2 Optimizer grief!

RUNSTATS went wrong?

Yes, indeed dear readers, RUNSTATS can go wrong… or even worse someone might attempt to manipulate the statistics to try and force some special access path. This is, of course, dangerous and possibly no longer needed, perhaps this update was done years ago and nowadays is not needed. You can download our Statistics HealthCheck which will, very quickly and comprehensively, run though all your Db2 Catalog statistics and point out the bad guys!

How big is yours?

Here I am talking about the widest set of columns that you have in SYSCOLGROUP for a given table. Remember that RUNSTATS must SORT all of these and it uses just *one* sort for *all* of your column groups … Makes me cry when I type this … This has led, and does still lead, to massive amounts of sort work being requested due to excessive SYSCOLGROUP usage which, in turn, is normally fed by overzealous SYSSTATSFEEDBACK usage. In one case there were 23 colgroups all of which were over 1,000 bytes wide on a five-partition tablespace with 120 million rows. A complete waste of time and money to even execute such a beast – even if you had the TBs of sort work handy!!! Please check your colgroups!

REORG

Back in the good old days we had to execute RUNSTATS on every single tablespace just to see if you needed a REORG? Remember that? I really hope you are *not* still doing that!!!

RTS is your Friend, really!

Base all REORG decisions on the RTS counters and *not* on any Db2 Catalog column, unless you have schema changes or DBET requirements for the REORG. If doing a REORG then it is probably also the time to refetch your statistics so an INLINE RUNSTATS is highly recommended. Sampling is allowed these days to save a bit of CPU. There is one large exception here and that is that a REORG INDEX should, normally, never get inline statistics. Why? Well, normally, we REORG INDEX far more often than the base tablespace and this can very quickly lead to statistics drift, up to the point where the Db2 Optimizer starts losing faith in the numbers it is getting. This is then a “very bad thing” and not a place you want to go to!

What’s in an Index anyway?

One extra problem that we have is all the different index types… A PI (Partitioning Index) is no longer required or probably even there anymore, a DPSI (Data Partitioning Secondary Index) is used quite a lot these days but must contain at least the partitioning columns, and then comes the Ugly Big Brother of them all – the NPSI (Non-Partitioned Secondary Index) aka NPI – and is partition independent. This means if you are doing partition-based REORGs you must execute them serially one behind another. Yes, you can use the PART(n, nn:nn, n) style syntax up to a point, but it still does not stop all the trouble! This is a pain and there is no real fix for this. My Rule of Thumb is: 25% or more of all partitions being REORGed then flip it to a TS level REORG. This is probably faster than 20 Parts and 20 times the NPSI being REORGed. As always, your mileage may vary and the cheque is in the post, but you can probably measure this at your site with your own data and easily see where the “break-even” point is. This is also fully supported by our RealTime DBAExpert as well, by the way.

REORG Parameters

Right, hard hats on – here comes the checklist portion. To check that you are running with modern REORG parameters, run through all the following items:

  • Verify that your ZPARM REORG_DROP_PBG_PARTS is set to DISABLE (The default). Why? If you allow REORGs to prune unused PBG Partitions at the end then that will completely stop Point in Time (PiT) recovery from working. If, on the other hand, you *never* want to, or will need to, do a PiT then it does not matter!
  • Verify that your (MAXRO * DRAIN_WAIT parameters) are greater than (ZPARM IRLMRWT * four). This is to try and force the REORG to die before any transactions, which is what you would normally want! Better to have wasted some CPU and I/O on a dud REORG than killed your CEO doing a transaction!
  • Check that the following REORG parameters are set: DRAIN ALL, LASTLOG NO, DROP_PART NO, RETRY less than 30 and RETRY_DELAY > 10. Note that for LASTLOG NO you must have DRAIN ALL and be using SHRLEVEL CHANGE, by the way.
  • Check that your SORTDEVT is set and a good SORTNUM value in the range 2 – 255 to maximize sort processing – unless you have the ZPARM IGNSORTN set to YES in which case the SORT program does the allocation much better than we can!
  • Inline statistics should only be used for TS/TP and not for IX/IP level.

To BIND or Not To BIND? Still not a Question!

Remember that after running an inline RUNSTATS or REORG without doing a REBIND is relatively pointless as no access path change will ever happen if you do not REBIND. The same APREUSE(ERROR) and APCOMPARE(ERROR) advice applies here as it did for standalone RUNSTATS – lock yourself in to the last “good” access path if access path regression frightens you.

What does “relatively pointless” mean, again?

Here, the answer is different and more interesting! For a REORG it is naturally not 100% true, because a REORG *can* drastically improve performance, especially a reorg of an index, *without* a rebind being required. Performance indexes are restored to health after a REORG INDEX and as for the tablespace, well, all the data is put back in clustering sequence and all empty space is tidied up, thus making sequential access and prefetch much, much faster. The CLUSTERING index drives the data order in the tablespace, of course.

REORG Avoidance

The best REORG is simply no REORG, and you can avoid REORGs in various ways. If the clustering is always going wrong, it could well be that the current CLUSTERING index is not actually the best choice! Mostly, the data design was done decades ago and never changed. It can pay handsome dividends to check all the index definitions and see if it is possible to juggle the CLUSTERING index around. Failing that you can always play with the SEGSIZE and/or PCTFREE and PCTFREE FOR UPDATE values to alleviate pain in this area at the cost of requiring more disk space. There is always a price to pay, isn’t there?

No REORG Strategy

If you have a workload monitoring system, like our SQL WorkloadExpert, you can see before-and-after REORG snapshots showing you the performance improvement. If you are *not* getting a dramatic reduction in access times after a REORG then you should probably forget the REORG altogether!

REORG Index Strategy

If the indexes are getting too many splits it may well be time to change the index page size and possibly even go COMPRESS YES, however please remember COMPRESS YES indexes do *not* work at all with FlashCopy and SLB as they work at the disk sub-system level and do *not* access via the bufferpool – which is where Index compression and decompression is all done. To recap: COMPRESS YES for indexes is fine, but not if you are depending on FlashCopy / SLB to do the back-up!

Thresholds?

There are so many but a brief list of my faves:

  • General: Any DBET requirements (ARBDP, AREO*, AREOR, RBDP, RBDP*, RBDPM, REORP and PSRBD) or Extents > 200.
  • Tablespace/Tablepart: > 25% deleted rows, > 5% indirect refs, > 10% unclustered inserts or any MASS DELETES.
  • Indexspace/Indexpart: > 30% deleted rows, > 20% appended rows, > 10% leaf far, >5% pseudo deleted , >5% empty leaf, any Level changes or any MASS DELETES.
  • Lob: > 50% disorganized.

and Finally…

One last Utility that often gets overlooked is MODIFY. It is hugely important to clean up the tablespaces DSNDB01.SYSLGRNX and the DSNDB06.SYSTSCPY (formerly known as DSNDB06.SYSCOPY) as well as enabling you to delete old, unusable backups. The problem here (there is *always* a problem, isn’t there) is that the RTS has no counters for the DSNDB01 data and so you must “tandem” REORG these. If you ever generate a REORG for SYSTSCPY then always generate a REORG for SYSLGRNX as well and then you are good! Naturally, you can add them to your automation system as it is “just a normal REORG these days”, but remember: no INLINE STATISTICS for SYSLGRNX as it is in the Db2 Directory and you cannot run RUNSTATS on Db2 Directory objects!

Over to you!

Have you any recommendations, best practices or Rules of Thumb that I have missed, trampled upon or completely ignored? If so, then please drop me a line!

TTFN,

Roy Boxwell

2026-08 EXPLAIN it to me again, please!

Once more into the breach, dear friends! Yes, we are doing a very quick EXPLAIN update again … This is one topic that never ever ever gets old or cold! This month it was triggered by a DBA colleague who emailed me for help as he tried to use the CTE Opthints that I wrote about way way back in 2021 and hit trouble …

The Story begins …

Nearly all good stories start with a misdirected email … Then it was finally forwarded to me, and I saw the SQL attempt that he was making. Now it is a bit odd that he *wants* multiple index access because that is like Hybrid Join … That’s not really “up there” with desired access paths!!! However, he made the case very well in this one very, very special situation, and with host variables, that the data skew was so bad that multiple index access worked best.

How did it look?

Here’s the DDL and SQL, redacted to protect one and all so you can try this one at home!

Table:

CREATE TABLE CTETEST.CTETABTEST
(                                    
 COL1  TIMESTAMP NOT NULL            
,COL2  TIMESTAMP NOT NULL            
,COL3  TIMESTAMP NOT NULL WITH DEFAULT
          '9999-12-31-24.00.00.000000'
,COL4  TIMESTAMP NOT NULL            
,COL5  CHAR(24)  NOT NULL WITH DEFAULT
,COL6  TIMESTAMP NOT NULL WITH DEFAULT
          '0001-01-01-00.00.00.000000'
,COL7  TIMESTAMP NOT NULL WITH DEFAULT
          '0001-01-01-00.00.00.000000'
,COL8  TIMESTAMP NOT NULL WITH DEFAULT
          '0001-01-01-00.00.00.000000'
,COL9  TIMESTAMP NOT NULL WITH DEFAULT
          '0001-01-01-00.00.00.000000'
)                                    
   CCSID EBCDIC                      
;      

All Indexes:

CREATE UNIQUE INDEX CTETEST.INDEX000
ON CTETEST.CTETABTEST
(
COL1 ASC
,COL2 ASC
,COL3 ASC
)
USING STOGROUP SYSDEFLT
PRIQTY -1
SECQTY -1
CLUSTER
BUFFERPOOL BP0
;
CREATE INDEX CTETEST.INDEX001
ON CTETEST.CTETABTEST
(
COL9 ASC
)
USING STOGROUP SYSDEFLT
PRIQTY -1
SECQTY -1
NOT CLUSTER
BUFFERPOOL BP0
;
CREATE INDEX CTETEST.INDEX002
ON CTETEST.CTETABTEST
(
COL1 ASC
,COL4 ASC
,COL3 ASC
)
USING STOGROUP SYSDEFLT
PRIQTY -1
SECQTY -1
NOT CLUSTER
BUFFERPOOL BP0
;
CREATE INDEX CTETEST.INDEX003
ON CTETEST.CTETABTEST
(
COL2 ASC
)
USING STOGROUP SYSDEFLT
PRIQTY -1
SECQTY -1
NOT CLUSTER
BUFFERPOOL BP0
;
CREATE INDEX CTETEST.INDEX004
ON CTETEST.CTETABTEST
(
COL5 ASC
,COL3 ASC
)
USING STOGROUP SYSDEFLT
PRIQTY -1
SECQTY -1
NOT CLUSTER
BUFFERPOOL BP0
;
CREATE INDEX CTETEST.INDEX005
ON CTETEST.CTETABTEST
(
COL4 ASC
)
USING STOGROUP SYSDEFLT
PRIQTY -1
SECQTY -1
NOT CLUSTER
BUFFERPOOL BP0
;
CREATE INDEX CTETEST.INDEX006
ON CTETEST.CTETABTEST
(
COL6 ASC
)
USING STOGROUP SYSDEFLT
PRIQTY -1
SECQTY -1
NOT CLUSTER
BUFFERPOOL BP0
;
CREATE INDEX CTETEST.INDEX007
ON CTETEST.CTETABTEST
(
COL7 ASC
)
USING STOGROUP SYSDEFLT
PRIQTY -1
SECQTY -1
NOT CLUSTER
BUFFERPOOL BP0
;
CREATE INDEX CTETEST.INDEX008
ON CTETEST.CTETABTEST
(
COL8 ASC
)
USING STOGROUP SYSDEFLT
PRIQTY -1
SECQTY -1
NOT CLUSTER
BUFFERPOOL BP0
;
COMMIT ;

Feel free to only use the last four for any tests you want to run!

The Code?

The SQL looked like:

SELECT COL1                            
     , COL2                            
     , COL3                            
     , COL4                            
     , COL6                            
     , COL7                            
     , COL8                            
FROM CTETEST.CTETABTEST                
WHERE COL3 + 0 DAYS > CURRENT TIMESTAMP
  AND ( COL4        = ?                
    OR  COL6        = ?                
    OR  COL7        = ?                
    OR  COL8        = ? )              
;                                                                    

And the Aim of the Game was?

He wanted a multiple index access path on all four of the OR predicates. If you EXPLAIN the above query “asis” without data or RUNSTATS:

SELECT ACCESSTYPE                     
FROM PLAN_TABLE
WHERE QUERYNO = 1
;
---------+---------+---------+--------
ACCESSTYPE
---------+---------+---------+--------
R
DSNE610I NUMBER OF ROWS DISPLAYED IS 1

Surprise, surprise! You get a Tablespace scan (R for Relational scan in the Jargon).

First up!

First attempted SQL code looked like:

-- FIRST ATTEMPT AT GETTING MULTI-INDEX ACCESS           
EXPLAIN ALL SET QUERYNO = 2 FOR
WITH DSN_INLINE_OPT_HINT
(QBLOCKNO,TABLE_NAME, ACCESS_TYPE, ACCESS_NAME) AS
( VALUES (1, 'CTETABTEST' , 'MULTI_INDEX' ),
(2, 'CTETABTEST' , 'INDEX', 'INDEX005' ),
(3, 'CTETABTEST' , 'INDEX', 'INDEX006' ),
(4, 'CTETABTEST' , 'INDEX', 'INDEX007' ),
(5, 'CTETABTEST' , 'INDEX', 'INDEX008' ))
SELECT COL1
, COL2
, COL3
, COL4
, COL6
, COL7
, COL8
FROM CTETEST.CTETABTEST
WHERE COL3 + 0 DAYS > CURRENT TIMESTAMP
AND ( COL4 = ?
OR COL6 = ?
OR COL7 = ?
OR COL8 = ? )
;
---------+---------+---------+---------+---------+-------
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0

And???

Things to note: SQLCODE IS 0 should never be output for an inline CTE Opthint. If you read my old Newsletter, you will see you should only get a +394 or +395. In this case it simply ignored the CTE completely!

The result of the above EXPLAIN:

SELECT MIXOPSEQ, ACCESSTYPE, ACCESSNAME
FROM PLAN_TABLE
WHERE QUERYNO = 2
;
---------+---------+---------+---------
MIXOPSEQ ACCESSTYPE ACCESSNAME
---------+---------+---------+---------
0 R
DSNE610I NUMBER OF ROWS DISPLAYED IS 1

No change here!

Next up!

My try:

-- SECOND ATTEMPT AT GETTING MULTI-INDEX ACCESS
EXPLAIN ALL SET QUERYNO = 3 FOR
WITH DSN_INLINE_OPT_HINT
( ACCESS_TYPE, ACCESS_NAME) AS
( VALUES ( 'MULTI_INDEX', 'INDEX005' ),
( 'MULTI_INDEX', 'INDEX006' ),
( 'MULTI_INDEX', 'INDEX007' ),
( 'MULTI_INDEX', 'INDEX008' ))
SELECT COL1
, COL2
, COL3
, COL4
, COL6
, COL7
, COL8
FROM CTETEST.CTETABTEST
WHERE COL3 + 0 DAYS > CURRENT TIMESTAMP
AND ( COL4 = ?
OR COL6 = ?
OR COL7 = ?
OR COL8 = ? )
;
---------+---------+---------+---------+---------+---------+-+------
DSNT404I SQLCODE = 394, WARNING: USER SPECIFIED OPTIMIZATION HINTS USED
DURING ACCESS PATH SELECTION
DSNT418I SQLSTATE = 01629 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXOPCO SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 20 0 502 1115050228 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000014' X'00000000' X'000001F6' X'427650F4'
X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0

The result of this EXPLAIN:

SELECT MIXOPSEQ, ACCESSTYPE, ACCESSNAME
FROM PLAN_TABLE
WHERE QUERYNO = 3
;
---------+---------+---------+---------
MIXOPSEQ ACCESSTYPE ACCESSNAME
---------+---------+---------+---------
0 M
1 MX INDEX005
2 MX INDEX006
3 MU
4 MX INDEX007
5 MU
6 MX INDEX008
7 MU
DSNE610I NUMBER OF ROWS DISPLAYED IS 8

BINGO!

As you can see, I reduced the input to the CTE to the absolute minimum just telling the Db2 for z/OS Optimizer the four index names I wanted it to use in a MULTI_INDEX access type and Voila! It worked!

Really?

I emailed my test results to the DBA and he confirmed that my changes work in production and he is now a very happy bunny!

First Time for Everything!

This was the first time I have tried to force multiple index access for a query but you never know if this might be of use to some other DBA fighting the fight and struggling with dodgy access path decisions!

TTFN

Roy Boxwell

2026-07 Are you Quantum Safe?

Quantum is being really heavily hyped at the moment and even my good self has been caught up in some of the Hoo-ha around it! Specifically which parts of Db2 are not “quantum safe”?

Quantum?

Yep, not like the TV Series “Quantum Leap” at all! The idea of quantum data goes back many, many decades but has only recently really started earning a living instead of being a scientific curiosity! There are six basic concepts that you must know a little about to start to “grasp the basics” of Quantum Computing.

Have you Qubit off more than you can chew?

The most important bit about quantum computers is the fact that they have qubits and not bits! The difference is staggeringly huge and mind-bendingly weird but boils down to the simple fact that a bit can be 0 (off) or 1 (on) – That’s it folks! On the other hand, a qubit can be anywhere between 0 (off) and 1 (on), in fact an infinite number, and it gets weirder when you have more qubits to play with. They do not join linearly but exponentially! This means that with 1,024 Qubits you have a mind-meltingly high number of possible values.

Ice Ice Baby!

You gotta keep qubits cold and I mean really cold… best is 0o Kelvin (Absolute zero) which is pretty nippy in July! For me in Germany that is -273.15o Celsius and for those of you “over the pond” a very large negative number of Fahrenheit! Ok, the actual number is -459.67o Fahrenheit. Naturally, we never can reach 0o Kelvin but we can get pretty close these days and at this temperature it minimizes error rates and enables superconducting behavior as well. The warmer they get the more they are prone to losing their quantum state, or decoherence in the jargon.

Superposition – the first Concept

Qubits are in a superposition of all possible values between 0 and 1 until they are measured. Then they snap back, the quantum state collapses is the correct term, and resolve to a 0 or 1 – This gives you the “answer” if you like. We can never fully predict the outcome but we can calculate the probability of a given outcome.

Entangled – the second Concept

Entanglement is crucial to getting Quantum computers to do any meaningful work for us humans. It is, at a minimum, two particles that are paired up in such a way that the quantum state of one particle cannot be determined independently of the state of the other, irrespective of the distance between the two. In other words, you can work out the quantum state of a particle when it is a light year away from its partner! Einstein famously wrote in 1947 about “spukhafte Fernwirkung”, in English “spooky action at a distance” as he could not believe this! But it has now been proven over and over that entanglement does happen and with many more than just two qubits.

Exponential speed-up?

Using entanglement puts two or more qubits into a single shared quantum state, so they stop behaving independently and combined with interference (coming up) that is where the real speed-up lives. Please also remember that measuring one does instantly pin down the others, but no usable information actually travels faster than light. For one qubit we have probabilities distributed over two states. For two qubits the distribution goes over four states, for three qubits it is eight states etc. In general, the rule is for N qubits the probability is over 2n states.

Interference – the third Concept

We have all thrown stones into a pond or the sea and seen the ripples spread outwards. When the waves bounce back from the edge of a wall or boat you might have noticed that the waves interact with each other. When the peaks meet, you get a big wave. When troughs meet, you get a big hole. And when a peak meets a trough – nothing.

Give us a Wave!

In the quantum world this a wave function which is a mathematic description based on polynomials. To measure the entangled qubits, you just add the individual wave functions for every qubit until you have a single wave function of a single quantum state. This adding of the wave function gives us an interference pattern where we can change probabilities of the correct answer by using constructive interference (The peaks of the wave add up) or reduce the probability of a wrong answer by using destructive interreference (Where the two waves cancel each other out).

Tunneling – the fourth Concept

Now it gets weird! (Only now, you ask? Well …) This is where an object can pass through an energy barrier that within normal classical physics it should not be able to pass through! Think of a high mountain and then imagine that someone has bored a tunnel at the base of the mountain right on through. If the object can use the tunnel, it will make it much faster to the other side! Remember, the wave function is a probability so when this function encounters a potential barrier and if the barrier is thin enough it can indeed tunnel through and then there is a chance that the particle will appear on the other side.

Examples abound!

Nuclear fusion in stars is where protons overcome their electrostatic repulsion which allows the sun to shine, or radioactive decay where Alpha particles tunnel out of the nucleus. Modern electronics rely on this phenomena for tunnel diodes which were invented in 1957, for scanning tunneling microscopes invented in 1981 and also for flash memory as well.

Synchronization – the fifth Concept

We have all been to concerts where people start clapping. At some point all the clapping synchronizes and gets very loud! This is where the wave functions of independent particles can all “march to the same beat” and spontaneously synchronize – this is very important when using certain algorithms.

Quantum Parallelization – the sixth Concept

Behind this is the really weird idea of parallel universes where each universe does one calculation and with N parallel universes the time of one calculation is enough for *all* calculations. At the moment of “observation” the qubit ends in a “collapsed” state and you get a result. This is also used in quantum chip design where the second, or more, input can be influenced by the first input!

The Traveling Salesman gets his Route!

The Traveling Salesman is a very old problem, from 1930, where you must calculate the optimal route for a poor traveling salesman to visit N cities with the shortest route, visit each city only once and return to the starting city. It sounds “easy” but is actually NP-Hard. In math terms this means it is not solvable in polynomial time and for every city added the time goes up exponentially. If you have three cities anyone can do it but when you have just 20 it gets practically unsolvable!

Give me numbers!

A raw brute force “try every possible route” approach takes a running time within a polynomial factor of O(n!) where O is the Order of growth in Landau notation – fittingly, it comes from the German word “Ordnung” (Order). So you can see that after 20 Cities the numbers get silly. The Held-Karp algorithm from 1962 solves it in time O(N22N) whereas the latest quantum algorithm from Ambainis et al. from 2019 runs in just O(1.728N).

CitiesBrute ForceHeld-KarpQuantum
36725
103,628,800102,400237
202.43 x 1018419,430,40056,347

As you can clearly see, Quantum Computers will greatly help with this problem and all derivatives in logistics, and DNA sequencing, for example. Note that the quantum solution is still exponential but just not as bad as before as it is a NP-Hard problem after all!

Spinning on your Heels?

To get this level of improvement the Quantum Computer uses an Ising Machine where spinning particles are each a city and they will eventually all synchronize on the most efficient path. Calibration is on various factors but for the Traveling Salesman problem it is on distance.

Now we are Experts – Why?

Factoring of numbers is the number one reason, as of today, for doing all this! But, unlike the Traveling Salesman, factoring is not actually NP-Hard – it sits in a curious middle ground, and it is exactly that hidden structure a quantum computer can exploit (quantum computers are not believed to crack genuine NP-Hard problems). Encryption is basically two large prime numbers multiplied together. Computers can do that very easily! Going back, or factoring, from a huge semi-prime number to the two starting primes is, however, very difficult for a classic computer. Shor’s algorithm lets a quantum computer recover those prime factors efficiently – not by trying everything at once, but by exploiting interference – and that breaks the public-key (asymmetric) encryption such as RSA and ECC which protects key exchange and digital certificates.

Grover’s Algorithm

Lov Grover created this in 1996 and it is basically an enhanced search with O(√N) evaluations but it can brute-force a 128-bit key in 264 iterations or 256-bit in 2128 iterations. It is quadratically better than simple brute-force and there are improvements on their way – especially the class of non-local hidden variable quantum computers could do the search in O(∛N) – the cube root of N – evaluations instead of O(√N) evaluations!

Finally Db2!

Yeah! I made it nearly to the end before even getting into the reason for the newsletter!!! The problem is that all of our data is encrypted, most of you are using 128-bit keys which are ok and are still pretty hard to crack. Anything less than 128-bit keys is crackable now in a brute force attack. The recommended way forward and to remain “Quantum Safe” is to change all your encryption today to use at least 256-bit keys. These will be safe from Quantum Computers for a long time.

Post-quantum…

One nuance worth stating plainly: the Db2 ENCRYPT and HASH functions listed below are symmetric, where a quantum computer (Grover) only halves the effective key strength – so moving to 256-bit gives you 128-bit of real protection and is exactly the right fix here. The factoring threat from Shor described above instead breaks asymmetric public-key crypto such as RSA and ECC, where larger keys do not help and post-quantum algorithms are required – that protects your TLS handshakes and certificates and is a separate piece of work.

How long is long?

The so-called Q-Day is expected in 2030 when anyone with a Quantum Computer can then read all the data you are *now* storing…  

Stuff to do, then?

Yes indeed, any use of the following is no longer really secure for “tomorrow” anymore and steps must be taken to raise the level of encryption. If any of the following scalar functions are being used in your shops it is time to start a project group “Quantum Safe Encryption”:

DECRYPT_BINARY
DECRYPT_BIT
DECRYPT_CHAR
DECRYPT_DB
ENCRYPT           Triple Data Encryption Standard 128 Bits
ENCRYPT_TDES      Triple Data Encryption Standard 128 Bits
HASH(xxxx)        Message Digest 5                128 Bits
HASH(xxxx,0)      Message Digest 5                128 Bits
HASH(xxxx,1)      Secure Hash Algorithm 1         160 Bits
HASH_CRC32(xxxx)  Cyclic Redundancy Check          32 Bits
HASH_MD5(xxxx)    Message Digest 5                128 Bits
HASH_SHA1(xxxx)   Secure Hash Algorithm 1         160 Bits

Replacements, please!

If you find any of these in your SQL workload you must get the application teams to replace them with:

DECRYPT_DATAKEY_type     With the new full list of types.
ENCRYPT_DATAKEY(xxxx,
key-label-name,
AES256R or AES256D)      All 256 Bits. Using AES256D enables EQUAL
                         usage as the same value will encrypt to the
                         same encrypted value but is not as secure
                         as AES256R.
HASH(xxxx,2)             Secure Hash Algorithm 2 with 256 Bits
HASH_SHA256(xxxx)        Secure Hash Algorithm 2 with 256 Bits

The highest Bits have it!

As you can see, anything less than 256-bit keys is nowadays not deemed “Quantum Safe” which means someone will be able to easily decode your emails, data, transactions, logs etc. when that Q-Day comes – and it may come sooner than you would think or like!

Not easy to do!

So now, if you find any of these functions you must decrypt the data and then re-encrypt the data with one of the 256-bit keys so you are “safe for a while” – Of course how long we will be “Quantum Safe” with 256-bit keys is a very open question indeed!

Finally a small course from IBM

Here’s a nice intro to all of the above from IBM.

TTFN,

Roy Boxwell

2026-06 Db2 vNext Migration – Are YOU ready, Player One?

This month I wish to stroll along the dangerous and difficult road called Db2 Release Migration. We all love it as it means a new release with all new go-faster features and whizzbang things to play with but we also all hate it as it means we have things to do before we can actually migrate and get to this new promised land.

With Db2 for z/OS vNext coming up soon we are now back in the Driving Seat!

The list of deprecated items is pretty long and now about 80% of them have been finally deleted. If you have, or use, any of them you will *not* be joining the rest of us on the other side of the valley where the grass is definitely much greener!

The list of killed off items is:

  • Simple tablespaces
  • Segmented tablespaces
  • Classic partitioned tablespaces (With or without Index based partitioning)
  • Basic (Six byte) format
  • BRF (Basic Row Format so not yet at RRF – Reordered Row Format)
  • Hash Access
  • Synonyms
  • VTAM/SNA

Haakon Roberts shared this data at the IDUG EMEA 2024 in Valencia and it was the first time we had all seen an inkling of what would come. The real surprise, at least for me, was the requirement to remove VTAM/SNA support and move over to TCP/IP. The rest we had all known about for years but killing off VTAM/SNA was brand new!

CATMAINT to the Rescue?

Nope! None of these features will be automagically fixed by running a CATMAINT. It is all manual work and up to us, the DBAs fighting at the front, to fix “When we have some spare time…”

An ALTER a Day keeps the Dr at Bay

The basic “cure” for most of these problem children is simply an ALTER and a REORG, but it *never* is that easy, is it? To fix segmented or simple tablespaces with just one table within them it is an ALTER to MAXPARTITIONS 1 which will kick the tablespace into the world of UTS PBG. Follow up with a REORG with inline statistics and a REBIND of all invalidated packages and you are done.

More than one?

If you have multi-table tablespaces then you must be at Db2 12 FL508 or higher and create a new set of tablespaces that match *exactly* the current ones for BUFFERPOOL, CCSID and LOGGED attributes. Then you use the ALTER … MOVE TABLE syntax for each table. An actioning REORG followed by a full RUNSTATS on each new tablespace afterwards with SHRLEVEL REFERENCE is then required to get the RTS statistics inline. Now do a REBIND of all invalidated packages and you are done.

Time for the Classics?

If you have any really, really old index-based partitioning tables, you must do two ALTERs within one commit scope. The first flipping the Partitioning Index to NOT CLUSTER and then back to CLUSTER. Now you are at table-based partitioning so read on!

Table-based partitioning

At this point it is an ALTER to SEGSIZE 64 which will kick these babies into the fun world of UTS PBR.

For both Classic cases, follow up with a REORG with inline statistics and a REBIND of all invalidated packages and you are done.

Six Byte RBA/LRSN, anyone?

If any of your tablespaces or indexspaces have not got 10 Byte RBA/LRSN then you must REORG them to action this. One little problem here is if the space is an XML space (Type = ‘P’) then you must first check its base table’s tablespace to see if that is a UTS space. If so then all is good, otherwise you have a non-versioning XML space which will require this four-step fix:

1. DSN1COPY to generate image copy for XML data

2. Drop XML column from base table

3. Recreate the XML column in the base table

4. LOAD REPLACE to load the XML data from the image copy generated by step 1.

Invalidated Packages?

Yup! All of these ALTERs and the actioning REORGs with their inline/afterwards RUNSTATS will happily invalidate any and all packages that refer to the objects… This is a major pain as your access paths can then very easily go south! This is time for our tool BindImpactExpert (BIX) to ride to the rescue. As long as you are running with EXPLAIN(YES) – and I sincerely hope you are!!! – BIX can be used to highlight any and all changed access paths enabling you to be proactive with corrective measures.

Definitely evil DEFINE NO

DEFINE NO objects are great as SQL can SELECT from them really fast! They take up next to no disk space and require no REORG or COPY processing as there is no VSAM dataset. The problem with these little devils is that REORG is actually *not* permitted on them! One exception to this rule is if the TS/TP was created with BRF, as then you can REORG with the option ROWFORMAT RRF and this REORG will just flip a bit or two in the catalog/directory and nothing else.

And???

That means that all the ALTERs you might have done are “hanging in the wind”. IBM state that an INSERT/LOAD will materialize the VSAM cluster(s) but who wants to do that? The only way forward is to extract the DDL that created them, DROP them and then reCREATE them all as DEFINE NO again. As when recreated they will be valid for vNext, of course then a REBIND of all invalidated packages is also required and you are done.

Devil in the Details

For BRF partitions the fixing REORG will fail if any table has a validation procedure (VALPROC table column) or edit procedure (EDPROC tabel column) defined. If this is the case then the procedures must first be dropped before the REORG and afterwards added back.

Did they make a HASH of it?

HASH access came in with a big fanfare, we finally had an access path like IMS HDAM, but it died a death very quickly and the fix here is ALTER with DROP ORGANIZATION. Naturally, this drops the hash index and so you must then create a new index for SQL use and – guess what else you must do? Of course, a REORG with inline statistics and a REBIND of all invalidated packages and you are done.

Simply SYNONYMs

I wrote a Blog about all these beasts.

Yes, over ten years ago! The actual fix for every synonym is straightforward:

SET CURRENT SQLID = 'Synonym schema' ;
DROP SYNONYM 'Synonym name' ;
  COMMIT ;
CREATE ALIAS 'Synonym schema'.'Synonym name'
       FOR   'Table creator'.'Table name' ;
  COMMIT ;

The problems here are that the statement SET CURRENT SQLID requires SYSADM Authorization, so this is not something I would farm out to the next student doing work experience!!! Plus, guess what you have to do afterwards??? A REBIND of all invalidated packages and possibly a chain of dependent invalidated objects as well and you are done.

TCP/IP is the Future!

It came as a surprise but there are indeed shops out there who are still using VTAM/SNA for inter-Db2 communication. The problem here is that when it was released in 1974, we all trusted each other! If you were connected to DB2A and just hopped over to DB2B it happily trusted you as you “were already on a DB2A so you must be good guy!” Sadly, those halcyon days are long gone. These days we have “Zero trust” as the norm – a real shame but it is what it is!

Reason for Change?

TCP/IP has enhanced security (AT-TLS and JSON Web Tokens), it can use 64-bit communication buffers and is zIIP eligible. You will save CPU and be much more secure, so going to this is really no question – However, this is a project all on its own – not something for a Friday afternoon at 4 o’clock!

Big Switch?

To switch off VTAM/SNA you should do several things. First clear out any and all old entries in the CDB tables LULIST, LUMODES, MODESELECT and all but the empty LUNAME entry from LUNAMES. Then you should do a BSDS update with DSNJU003 setting the IPNAME for that subsystem to be the real host name. Just doing that switches off VTAM/SNA for that member. If doing all this then also go to SECPORT usage at the same time as then your Db2s are truly secure! Your auditors will love you.

IBM Details

Are here.

Let’s get it sorted!

Yes, even all the sort workspaces must be cleaned-up and moved to PBG spaces. You really need a lot of 32k, a few 4k, and you need to know exactly how many FOR SORT and how many FOR DGTT (The new syntax in Db2 13 FL508) you require and need. No more guessing at which workload is going into which work tablespace.

  • FOR SORT specifies that the table space is used for processing other than declared global temporary table (DGTT) work, such as sort, joins, created global temporary tables, query parallelism, trigger transition tables, and so forth.
  • FOR DGTT specifies that the table space is used for DGTT work and processes that use internal temporary tables, such as scrollable cursors and INSTEAD OF triggers.

Remember that here no ALTER helps – you must DROP and CREATE the work spaces again… But hey! At least no REORG, RUNSTATS and REBINDs are required!

<PHEW> That’s a Ton of Stuff to do…

Wouldn’t it be cool if there was a nice small bit of freeware out there that listed out all the stuff you have to do to actually get ready for Db2 vNext! In fact, you cannot even get to Db2 13 FL511 as that will stop you with any of the above-mentioned items!

What Luck – SEGUS to the Rescue!

Yes indeed, we have a bit of freeware called MigrationReadiness HealthCheck for Db2 z/OS (MRHC) that runs through your Db2 subsystems and shows you all the KPIs you have as well as highlighting all the Migration Blockers, as I call them. There is also a pay-ware version that then actually creates all of the ALTERs, REORGS, RUNSTATS and REBINDs for you as well.

How does it look?

It looks cool, of course! Here are a few example screen grabs of how it looks in my little Db2 13 data-sharing test system and what it offers:

Db2 MigrationReadiness HealthCheck V2.1 for SD1 V13R1M509
               started at 2026-05-28-11.08.24
            Lines with *** are deprecated features                              
            Lines with MMM are migration blockers                               
            Lines with XXX are definition errors                                 
Number of DATABASES          :  225                                              
  # of empty DATABASES       :   48                                             
  # of implicit DATABASES    :  110                                              
  # of empty implicit DBs    :   46
                                              
Number of TABLESPACES        : 4995                                              
  of which HASH organized    :    0                                              
  of which PARTITION CLASSIC :    0                                              
    # Partitions             :    0                                              
  of which SEGMENTED         :   27 MMM                                          
  of which SIMPLE            :    3 MMM                                          
  of which LOB               :   99                                              
  of which UTS PBG           : 4833 
    # Partitions             : 4834                                              
  of which UTS PBR (Absolute):    1                                              
    # Partitions             :    6     
  of which UTS PBR (Relative):   10                                              
    # Partitions             : 2215                                              
  of which XML               :   22 
             
Number of TSs as LARGE       :    0                                              
Number of empty tablespaces  :    7                                              
Number of multi-table TSs    :   12 MMM                                          
  # of tables within these   :   49       
.
.
Number of table partitions   : 7211   
  of which DEFINE NO         : 2918   
  of which 6 byte RBA <11 NFM:    0   
  of which 6 byte RBA Basic  :    0   
  of which ten byte RBA      : 4293   
Number of TP in BRF          :   17 MMM
.
.
LULIST entries found         :    0   
LUMODES entries found        :    1 MMM
LUNAMES entries found        :    2 MMM
MODESELECT entries found     :    1 MMM
.
.
Total number of REORGs        21          
     REORGing                176 Cylinders
        also requiring      1409 REBINDs

   

Naturally, most of this is the Db2 Catalog and Directory. At the end it outputs a small list of KPIs with the Total number of REORGs required, the total numbers of Cylinders of space all the table and indexspaces take and how many REBINDs should then be done afterwards. This gives the experienced DBA a very good idea of how long and how potentially dangerous this whole thing could be!

Just the Facts, Ma’am!

Under DD card ALTERCAT in the pay-ware version are all the required actions, note that you must be at Db2 13 FL509 or above to get the CONVERTUTS REORG syntax:

-- REORG TABLESPACE DSNDB01.SCT02 SHRLEVEL CHANGE
--   CONVERTUTS 
-- REORG TABLESPACE DSNDB01.SYSUTILX SHRLEVEL CHANGE
--   CONVERTUTS 
-- REORG TABLESPACE DSNDB06.SYSALTER SHRLEVEL CHANGE
--   CONVERTUTS
-- REORG TABLESPACE DSNDB06.SYSCONTX SHRLEVEL CHANGE
--   CONVERTUTS 
--REBIND PACKAGE(IQA_COLLECTION_610.SQLDVDRC) APREUSE(WARN)       
--REBIND PACKAGE(IQA_COLLECTION_610.SQLDVDRC.(2026-01-21-06.55.23.099777)) APREUSE(WARN)
--REBIND PACKAGE(IQA_COLLECTION_DE.SQLDDLS.(2019-03-22-09.20.33.799998)) APREUSE(WARN)
--REBIND PACKAGE(IQA_COLLECTION_DE.SQLDDLS.(2022-08-01-06.08.43.796175)) APREUSE(WARN)
--REBIND PACKAGE(IQA_COLLECTION_DE.SQLDVCRC.(2018-08-21-07.21.16.927191)) APREUSE(WARN)
--REBIND PACKAGE(IQA_COLLECTION_DE.SQLDVDRC.(2022-08-01-06.21.52.371116)) APREUSE(WARN)
                                    

Notice that it always generates APREUSE(WARN) to try and keep the old access path.

Also, here are any of the nasty DEFINE NO REORG problems:

-- DEFINE NO TABLESPACE TS5941.TSTSDNQ REORG NOT POSSIBLE

and the SQL for CDB Clean-up:

-- DELETE FROM SYSIBM.LUMODES  
-- ;                           
-- COMMIT ;                    
-- DELETE FROM SYSIBM.LUNAMES  
-- WHERE NOT LUNAME = '        '
-- ;                           
-- COMMIT ;                    
-- DELETE FROM SYSIBM.MODESELECT
-- ;                           
-- COMMIT ;
  

Under DD card MIGRAREP are all the Migration Blockers listed out in detail:

Simple DB: DSNDB01 TS: SCT02      
Segmented DB: DSNDB01 TS: SYSUTILX
Segmented DB: DSNDB06 TS: SYSALTER
Segmented DB: DSNDB06 TS: SYSCONTX
Segmented DB: DSNDB06 TS: SYSDDF  
Segmented DB: DSNDB06 TS: SYSEBCDC
Simple DB: DSNDB06 TS: SYSGPAUT   
Segmented DB: DSNDB06 TS: SYSGRTNS
Segmented DB: DSNDB06 TS: SYSHIST 
Segmented DB: DSNDB06 TS: SYSJAVA 
Segmented DB: DSNDB06 TS: SYSROLES
Segmented DB: DSNDB06 TS: SYSSEQ  
Segmented DB: DSNDB06 TS: SYSSEQ2 
Segmented DB: DSNDB06 TS: SYSSTATS
Segmented DB: DSNDB06 TS: SYSTARG 
Segmented DB: DSNDB06 TS: SYSTSASC
Segmented DB: DSNDB06 TS: SYSTSUNI
Segmented DB: DSNDB06 TS: SYSTSXTM
Segmented DB: DSNDB06 TS: SYSTSXTS
Simple DB: DSNDB06 TS: SYSUSER    
Segmented DB: DSNDB06 TS: SYSXML  
Segmented DB: TS5941 TS: TSTSDNQ  
Segmented DB: WRKSD10 TS: DSN32K00
Segmented DB: WRKSD10 TS: DSN32K01
Segmented DB: WRKSD10 TS: DSN4K00 
Segmented DB: WRKSD10 TS: DSN4K01 
Segmented DB: WRKSD11 TS: DSN32K00
Segmented DB: WRKSD11 TS: DSN32K01
Segmented DB: WRKSD11 TS: DSN4K00 
Segmented DB: WRKSD11 TS: DSN4K01 
BRF tablespace DB: DSNDB01 TS: SCT02    
BRF tablespace DB: DSNDB01 TS: SYSUTILX 
BRF tablespace DB: DSNDB06 TS: SYSALTER 
BRF tablespace DB: DSNDB06 TS: SYSCONTX  
BRF tablespace DB: DSNDB06 TS: SYSDDF    
BRF tablespace DB: DSNDB06 TS: SYSEBCDC  
BRF tablespace DB: DSNDB06 TS: SYSGPAUT  
BRF tablespace DB: DSNDB06 TS: SYSGRTNS  
BRF tablespace DB: DSNDB06 TS: SYSHIST   
BRF tablespace DB: DSNDB06 TS: SYSJAVA   
BRF tablespace DB: DSNDB06 TS: SYSROLES  
BRF tablespace DB: DSNDB06 TS: SYSSEQ    
BRF tablespace DB: DSNDB06 TS: SYSSEQ2   
BRF tablespace DB: DSNDB06 TS: SYSSTATS  
BRF tablespace DB: DSNDB06 TS: SYSTARG   
BRF tablespace DB: DSNDB06 TS: SYSUSER   
BRF tablespace DB: DSNDB06 TS: SYSXML    
CDB table LUMODES has row - LUNAME DKLTEST MODENAME MODTEST.
CDB table LUNAMES has row - LUNAME DKLTEST SYSMODENAME MODTEST.
CDB table LUNAMES has row - LUNAME ROYTEST SYSMODENAME -none-.
CDB table MODESELECT has row - LUNAME DKLTEST AUTHID DKLTEST_AUTH_ID PLANNAME DKLTEST.

Note the WRKxxxx tablespaces…

Start to plan today!

Using our MigrationReadiness HealthCheck for Db2 z/OS freeware you can start to divvy up and plan all of the work that will be coming down the vNext road towards you. Start now and you still have over two years to get it all done and actioned. Start in two years and you will never make it – The choice is yours!

TTFN,

Roy Boxwell

2026-05 Central Canada Db2 Users Group 2026

I love this User Group – Great people, great location at the IBM Lab in Toronto and , this year, a great lunch! (You had to be there last year to understand this…)

This year they went all out and we got a full “Canadian Database Day” on the Monday split into a Db2 track called “Db2 for z/OS, Everywhere, All at Once!” – which covered, for Db2 z/OS, Bufferpools, Application dev, DDF Applications and Locking, Logging and Security. For Db2 LUW you got Semantic search, AI Systems and Amazon RDS. Then in the parallel IMS track everything about IMS as this old bird is still firmly nailed to its perch and rumors of its death have been greatly exaggerated!

And then?

Then we kicked off on Tuesday and Wednesday into the normal grid style with four parallel tracks. Now, as always, I must state that I did not go into *every* session so what I report here might just be a cut-and-paste from the grid and changes might well have happened that I did not know about! Further I did not go to any IMS or LUW tracks even though I started my Mainframe career writing IMS accessing COBOL programs with IMS 1.3 – Yep – I am THAT old!

Db2 Z DBA Tracks

Db2 Z Misc/App-Dev Tracks

Unlike the IDUG all the above links *should* take you directly to the download file. If not please contact the CCDB2 group and not my good self.

The missing link?

As of the 26th May the following presentations are *not* at the link address, just the abstract and speaker details:

  • ZOS 02 and 03
  • ZMISC 01, 02, 08 and 09

I hope that these presenters get their slides off to the CCDB2 organizers as soon as possible.

Reviews

ZOS-01 Akiko gave us her usual excellent stuff all about what we have and where we are going. Interestingly she mentioned that with Db2 13 FL509 we must run the REORGs for directory and catalog migration to UTS. This is *not* done by a CATMAINT. A nice tidbit was that the two new RTS columns NSYNCREADIO are not updated for Utilities which makes them very useful for utility decisions! She also reiterated that the CDDS functionality is now included in the base Db2 install and so we can all use this feature to get easy reachable copies of our compression dictionaries! Check out my blog all about using this facility called “2025-01 Compress and Replicate This!” at this link www.segus.com

She then went into what she called “Part 2” and mentioned that the new DDL Clauses FOR DGTT or FOR SORT got two APARs for problems: PH65632 and PH70190

Also note here that FOR SORT should use more than one for the MAXPARTITIONS value to benefit from the above performance improvement fixes.

Looking a little bit into the future she listed a few Scalar functions which are no longer deemed “Quantum safe” meaning that perhaps not today but certainly soon this level of encryption will be easily broken by a quantum computer. ENCRYPT_TDES or ENCRYPT for example.

Then she mentioned they are looking at changing the Lock Structure to get multiple versions which would make Datasharing even more scalable and resilient! Finally, she touched on a new possibility to access index’s using different column orders. No need for one index on COL1, COL2 & COL3 and another index on COL3, COL2 and COL1. This would be incredibly cool if they pull it off!

ZOS-02 Mark and Tori showing us all the fantastic things you can now do with System Profile Monitoring. If you have not looked into this start now! Just remember that START PROFILE starts ’em all and STOP PROFILE stops ’em all! This might *not* be what you wanted or expected! It was also repeated that user *must* start using Client Info fields. This should be standard but some shops still have one technical user id for *all* their CICS transactions so it is *impossible* to filter correctly or usefully. The other bug bear that is always asked is “Why cannot I filter on User Id and Location?” The answer is when it connects it knows just the location then a long time later it finally gets the User Id…Basically the code paths cannot handle this. If you are using cloud the Location address is normally very bad for filtering and so you must get Client Info added! A few interesting bits is that most occurrences of MAXDBATs being hit apparently are “ghost” transactions that just connect and sometimes run an SQL against SYSIBM.SYSDUMMY1. Then sit there doing nothing. Purely to see if Db2 for z/OS is there or not. Db2 for z/OS is always there, so if you can turn off these “ping” SQLs it would be a very good idea! Last bit was that the number of active profiles is limited to 4096… Do not exceed this number!

ZMISC-03 Ulf showed a relatively simple way to completely, safely and with no risk, copy your entire production metadata down to a sandbox and still guarantee access paths as if from production. Lots of people forget silly things like RTS or number of CPs & BUFFERPOOL numbers and sizes. Check it out to see how it can help you pre-check your next set of Db2 for z/OS APARs, Db2 Version release or machine upgrade or even downgrade if consolidating.

ZOS-04 Richard showed me how old I was as my data strategy is very very old indeed! More buzzwords than you can hit with large stick! The brave new would of data everywhere all over and only replicated when it absolutely must be. Great stuff! Favorite statistic “25% of all CPU MIPS is spent replicating data”. Discussion topic – Latency: 0 if virtualized to “it depends”. It depends on z/OS load, Network load and Target system, normally LUW, Load.

ZOS-05 John doing his usual fantastic stuff this timing taking aim at High Performance DBATs. One Golden Rule: Only for high volume simple transactions. The problem is to find them in all of your workload! They are a trade off : More memory & More DBATs versus Lower CPU. Tracing is good with IFCIDs 411, 412 and the best is 365. The one disadvantage is that at least one of the packages they use MUST be bound with RELEASE(DEALLOCATE).

              He then mentioned a small change in behavior that crept in with the NULLID packages in that they all got changed to RELEASE(DEALLOCATE) and he strongly advises everyone to REBIND them all with RELEASE(COMMIT) and, if you want High Performance DBATs, create a new COLLID where the packages are there bound with RELEASE(DEALLOCATE) and use System Monitoring to redirect to this new COLLID or change the JDBC path.

              When you issue the -MODIFY DDF PKGREL(COMMIT) this is not an *instant* fix to switch these things off. It will take time for all accessing threads to eventually be reset. Remember the DBAT only gets recycled after 500 times in Db2 13, before it was after 200.

ZMISC-06 Michael walked us through a great review of things over the years that come and sometimes go! That people tend to neglect or forget. ZPARM DISABLE_EDMRTS for example is very good one! RUNSTATS not updating the RTS in certain scenarios is another. A quick review of all the new and snazzy commands we have now got including DISPLAY BLOCKERs of course. My personal favorite due to it being the *only* command that outputs the Timestamp when it was executed. Sounds minor but if all you have is command outputs and you have to work out who did what when and where then timestamps are great. The details and hints about use of RECOVER with BACKOUT was also very illuminating!

ZOS-07 Roy doing his usual great stuff (What else am I going to say? 😊 ) and wrestling with the darn mike as it did not want to work with the British guy! Nice questions about how long til doomsday etc etc. Just download and run our freeware tool: MigrationReadiness HealthCheck for Db2 z/OS at www.segus.com to find out, very quickly, exactly where you currently stand. The worst things are SYNONYMs, Work tablespaces and TCP/IP. I have customers up in the 8,000 synonyms and some with zero. I guess it all goes back to when you started down the road of Db2! With work files if you have 400 on 87 subsystems that could be a lot of work. Here the DB2 13 FL508 DDL enhancement FOR WORK / FOR DGTT will help as well but you must still DROP those spaces!

ZMISC-08 Mark and Anna with standing room only! Absolutely packed room where no-one even offered me a chair – The Audacity!!! The dynamic duo gave us all their data about the lifecycle of packages for static and dynamic SQL diving down into all the grubby details that we all love so much! The question of the day was “How does Python on the Mainframe get allocated to a Package, and which one?” No one in the room knew the answer! The hope is directly to some package or possibly even using Type 4 going out and back in again on the network! I have noted this down as something to try and find out!

Update: Found out from Jørn Thyssen that it will use ODBC and then CAF or RRSAF with default PLAN DSNACLI and one of the seven packages within. The BINDs are all in <your Db2 HLQ>.SDSNSAMP(DSNTIJCL). Sadly I still have no idea which package but probably DSNCLIC1. Here is a great BLOG all about Python including, right at the end, a hint about APF Authorizing its load lib to get Python ZiiP enabled… Pretty neat! Python ibm_db on IBM z/OS UNIX (USS): a field ready install & troubleshooting guide

ZMISC-09 Kranthi showed us all the details and intricacies behind the “certificate” problems we all have or will have! With the lifecycle of certificates dropping very quickly you must try and get automated systems to cope with the renewal. For a few PCs or laptops and phones it is ok to do it yourself but in a firm? Fun fact: How many certificates does your average lap-top have installed? Between 100 and 600 is normal(!)

They think it’s all over!

Then we had to leave to get our flights but I was very happy indeed to be there again and I already look forward to next year and being there once again!

The Food?

Chris, the food was excellent this time but I still remember that Beef Bacon horror at the IFL!!!

Many many thanks to all the volunteers who put on the whole show and IBM for allowing the whole thing to be run from their Labs and I personally thank all the IBM Employees who were asked nicely to “leave the Canteen at 12:30” so a bunch of DBA crazies could sit down and have lunch!

Lest we forget!

For those who are now retiring and not there next year:

Thanks for all you have done and enjoy your curling!

TTFN,

Roy Boxwell

2026-03 taking a REST

This month, I wish to talk all about RESTful APIs and why they are sooooo cool. At the end I will give you a github link to a German GUIDE DBA Colleague who wrote some fantastic stuff using REST services and SQL to trigger REORGs from Excel… I kid you not!!!

Give it a REST

These beasts arrived in Db2 11 and are basically “one shot wonders” as a REST (Representational State Transfer) service has no “brain” and cannot remember anything. In SQL terms, is one single static SQL statement. This is really called a RESTful API using HTTP GET, PUT, POST and DELETE. Fun factoid to start with: Db2 only supports the POST method and so is really REST and not RESTful! An example REST looks like:

POST https://<host>:<port>/account/update    

This is a URI (Uniform Resource Identifier). Please remember that just because Db2 only accepts POST it does not mean you cannot run any SQL you like!

What is JSON?

JavaScript Object Notation (JSON) is what is used in all of the payloads. Here’s an example:

{
  "account": "1121345",
  "lastName": "Boxwell",
  "action": "Deposit",
  "amount": "€10000.00"
 } 

Data is represented as a series of name/value pairs. This is serialized and passed in with the URI or returned with a response.

I don’t GET it…

Well, actually, GET can be used for “some system related functions”:

https://<host>:<port>/services

But what can make RESTful real, is when you use z/OS Connect, as that is 100% RESTful but is out of scope for my newsletter!

Pretty dull, then?

Not actually! The beauty of REST services (giving them their proper name) is that they can execute any static SQL statement *including* stored procedure calls! Try doing that in DSNTEP2 or SPUFI!!! For this reason alone, they are worth looking at!

Special Rules?

Of course, nothing is “normal” in the Db2 for z/OS world, is it?

In Db2 11 we got BIND SERVICE and FREE SERVICE as they are not normal “packages” as we know and love them but special. REBIND PACKAGE worked but EXPLAIN(ONLY) was not possible, the only way to see an access path was to BIND/REBIND completely!

In Db2 12 we got START, STOP and DISPLAY for the RESTSVC to review all of our REST services.

The SQL gets squirreled away in a Db2 pseudo-catalog table called SYSIBM.DSNSERVICE. This data is also the “link” to a real package which, after being bound, can *only* be executed as a service (This is also a security feature.)

With APAR PH34544 we got the RESTSERVICEDEFAULT option added to REBIND PACKAGE which was nice to be able to change the default version from V1.

In Db2 13:

  • With APAR PH63990 the EXPLAIN(ONLY) syntax was allowed for REST services allowing access path extraction with no outages.
  • With APAR PH54129 the FREE PACKAGE was extended to free inactive REST services. Before this, you had to issue a FREE SERVICE, which freed everything – not just the phased out inactive services.

Any authorized user can then discover and invoke these services using a REST HTTP client. They support a buffer size of up to 2GB for the complete request and reply content as well.

Enable them first!

Make sure you are up to date on IBM Service (use keyword RESTful on your search) and make sure that APARs PI70652, PI98649 are applied. Even better for Db2 13 is PH68057 (UO06137) and all its prereqs to give you a good level. Use the DSNTIJRS job in your SDSNSAMP library to create the aforementioned pseudo-catalog table and its index. Then run the DSNTIJR2 sample job to enable Db2 REST service versioning support, only if you want versioning, of course!

REST while you work!

Versioning enables you to “swap out” a REST service while the old one is still running. Before this, you had an outage as you must first FREE the service and then BIND the new one. Not good for 24×7 shops! It also enables full versioning, of course, so that you can decide which version for which application etc. but I would never walk down that road personally… Too much like micro-management for me. If you need a different SQL or parameter etc. then just create a new REST service!

Authorized to REST?

Naturally, it all must be authorized! You can do this with your local RACF, TOPSECRET or ACF2 system. The defaults supplied for RACF are adding a REST profile to the DSNR resource class. This is purely to allow a user to access Db2 REST services and not the APIs themselves. That is done with either:

  • HTTP basic authentication using User ID password, RACF PassTicket etc. all in the HTTP Header in clear text or Base64. Db2 for z/OS then uses the System Authorization Facility (SAF) to authenticate these.
    or
  • Db2 REST Client certificate authentication. This is used if:
    1. It is a secure connection to Db2 using HTTPS and AT-TLS.
    2. The client certificate is registered with RACF, TOPSECRET or ACF2.

The second approach is the recommended one due to the obvious security concerns of HTTP Header usage! This then requires SECPORT to be set up of course!

Do you trust me?

Here you can, and probably should, start using Trusted Contexts as REST can exploit these and REST services do lend themselves to ROLEs.

Here’s one I prepared earlier…

To save me typing, here’s a direct link to the IBM Docu all about “Creating a Db2 REST service”:

https://www.ibm.com/docs/en/db2-for-zos/13.0.0?topic=services-creating-db2-rest-service

Your answer is in the POST

Then you issue a POST to kick off the REST service. Here, again, is a link to the IBM Docu:

https://www.ibm.com/docs/en/db2-for-zos/13.0.0?topic=services-invoking-db2-rest-service

Of CORS you can…

Bad pun I know… CORS support was rolled out in Db2 13 as well! Up until then this failed, due to the fact that Cross-Origin Resource Sharing (CORS) was not implemented. To get it up and running, you will need APAR PH59837: NEW FUNCTION (Great title huh?) PTF UI96535 applied. You will then need another RACF change, as it introduces a new Resource Class: DSNRAUTH with ACTIVE and RACLISTED. (If you intend to use generic then you must also enable GENERIC). It uses the user id of all your xxxxDDF address spaces for this as it is subsystem-wide and not user-specific. Then you must create one or more RACF resource profiles for the Db2 REST service CORS and Permit the DDF User Id READ access to these just created resource profiles. A quick refresh:

SETROPTS RACLIST(DSNRAUTH) REFRESH

and you are done!

If you wish to trace all of this CORS stuff then switch on IFCID 416 Audit Class 12.

Am I in Trouble?

Using these beasts, you will end up with a new list of error codes and messages…

The HTTP world has a few that you need to know:

1xx "Informational" Communicates transfer protocol-level info

2xx "Success" Hooray!

3xx "Redirection" Oh Oh - The client must do something else in order

     to complete the request

400 "Bad request" is normally badly formatted input

401 "Unauthorized" The user authentication failed

403 "Forbidden" The user might not have the required permission…

4xx "Client Error" any other 400'er numbers

5xx "Internal Server Error" comes normally with an SQLCODE so you
    can work out what has gone wrong!

Does my PROFILE look good?

These work brilliantly with system profile tables! Product identifier for example is HTP01010 for non-secure HTTP which you should never use! Better is HTS01010 for HTTPS secure connections.

Look-a-here

DISPLAY RESTSVC by default shows you all the REST services that exist in your Db2 subsystem:

DSNL601I  -SD10 DISPLAY RESTSVC REPORT FOLLOWS-                      
DSNL610I  -SD10                                                      
---- COLLECTION=MDB2VNEX_TEST                                        
SERVICE                                      VERSION          STATUS 
SAX_DRILLDOWN_S_O2RTSEXTSH_P_EXCEPTIONS_ANY  V1               STARTED*
SAX_DRILLDOWN_S_O2RTSEXTSH_P_EXCEPTIONS_NONE V1               STARTED*
SAX_DRILLDOWN_S_O2RTSEXTSH_P_EXCEPTIONS_ONLY V1               STARTED*
SAX_DRILLDOWN_S_O2RTSEXTSH_P_EXCEPTIONS_VARI V1               STARTED*
 ABLE                                                                
SAX_OVERVIEW_S_O2RTSEXTSH_P_EXCEPTIONS_ANY   V1               STARTED*
SAX_OVERVIEW_S_O2RTSEXTSH_P_EXCEPTIONS_NONE  V1               STARTED*
SAX_OVERVIEW_S_O2RTSEXTSH_P_EXCEPTIONS_ONLY  V1               STARTED*
SAX_OVERVIEW_S_O2RTSEXTSH_P_EXCEPTIONS_VARIA V1               STARTED*
 BLE                                                                  
SAX_PTF_LEVEL                                V1               STARTED*
SAX_USECASES_BY_LOCALE_PRODUCT               V1               STARTED*
DSNL610I  -SD10                                                       
---- COLLECTION=MDU_COLL                                             
SERVICE                                      VERSION          STATUS 
MDUMMY                                       V1               STARTED*
UPD_SAL_3                                    V1               STARTED*
DSN9022I  -SD10 DSNLJDSS 'DISPLAY RESTSVC' NORMAL COMPLETION      

We have a few on my test system! A little heads up about this command, is that if you are running with data sharing then it is executed on all the remote sites under the SYSOPR authorization id! This is also shown in IFCID 90 “Command Trace” as being correlation ID “016.TLPKN5F” – This requires that a SYSOPR Id is defined at the remote sites.

What can You do with it all?

As I mentioned right at the very start of this blog, at the last German Db2 Guide, a DBA did a great presentation all about bundling all this knowledge, including a bunch of stored procedures to allow, for example, an Excel table entry to trigger a REORG on the host. All great stuff and available from github with a ton of excellent documentation. Something for the weekend I would say!

Here’s the github link to Veit Blaeser’s code:

https://github.com/VauBe/Db2_UPS

Great stuff indeed!

TTFN,

Roy Boxwell

2026-01 Things I learnt last year…

Hi all! This month I wish to go though a few of the interesting, annoying and odd things that I bumped into last year. Some were new for me and some were just interesting for me!

COMPRESS THIS!

One of my customers is now starting down the road of compressing their very, very large NPSI’s as the RECOVER utility is actually way faster than a REBUILD. Nothing new here, is there? But wait! What if they are using FLASH COPY?

It gets very, very ugly very, very quickly is what happens!

Why?

Remember how FLASH COPY works? It is sooooo blindingly fast because it does all the actual copy stuff “in the DS8000, or equivalent, box” and *not* on your mainframe. This is really cool as you just shoot off a FLASH COPY and, as long as a few really basic rules are not broken, the copy is finished the moment it starts!

So, what about Indexes?

Firstly, if you want to do a FLASH COPY of a COMPRESS YES index you *cannot* also do a sequential copy. Further, remember that a FLASH COPY is a VSAM dataset and you cannot do a COPYTOCOPY of one of these either, meaning you have just one single VSAM dataset as a copy – This is, at least for me, a single point of failure and not good. But it gets much worse!

Really, how so?

Please now remember how index compression works… It is done purely “in memory” in the bufferpool. This means that when you have an insert, delete, or key update in memory and it has not yet been externalized to disk that when you now do a FLASH COPY you are copying garbage… This is, to coin a phrase, “not good” whereas a normal COPY index goes through the bufferpool and so a sequential copy is naturally ok!

Bottom Line

If using COMPRESS YES indexes in no way use FLASH COPY. Perhaps, at least for storage, dataset compression of the sequential copy datasets might save space…but then that defeats the purpose of COMPRESS YES on the index purely in the Db2 world which is primarily reducing I/O and secondarily reducing index page splits.

What about SYSTEM LEVEL BACKUPS?

Guess what? These are FLASH COPY as well! If you use SLBs and you have COMPRESS YES indexes you better be careful what you “replay” and make sure to always REBUILD, or at least CHECK, all indexes after a RECOVER has been run!

Docu?

All of the above is documented of course but it is like Douglas Adams wrote “in the bottom of a locked filing cabinet stuck in a disused lavatory with a sign on the door in saying ‘Beware of the Shark.'” – [The Shark is my idea geddit? Originally it was Leopard of course!]

Death By RUNSTATS

It is surprisingly easy to kill yourself with a simple RUNSTATS these days.

How?

Let’s say you have PROFILE on and you are using SYSSTATFEEDBACK for all your tables. I know you are as it is all on by default and who changes defaults?

And?

Now a third-party vendor sells you some software with ridiculously long VARCHAR fields containing possible NAMEs and ADDRESSes. In this case VARCHAR(2000) is being used. The SQL in question is using dynamic SQL with literals, not parameter markers, in the WHERE clause against these columns and SYSSTATFEEDBACK “sees” the requirement for column groups as these columns have, naturally, no index and a column group is a “poor man’s” index for frequencies and cardinalities, right?

So?

You end up with 23 column groups for a five partition table with over 120 million rows.

But what has that got to do with the Price of Beef?

So, dear, friends, what does our poor old RUNSTATS utility do now? It must farm out these column groups to DFSORT — and can you guess how it works out the allocation size??? You guessed it: 2000 + 8 for the maximum record size then multiplied by 23, for the number of column groups, then the result multiplied by the number of rows: 120,000,000. Do the math and you end up with a DFSORT storage requirement of over 5 PB (Yep that’s PETA bytes!), then the Storage Admin freaked out!

Easy fix: Delete all COLGROUP definitions for this table not backed by a real index. RUNSTATS DELETE PROFILE is a great help here! Then switch off SYSSTATFEEDBACK for this table and control *all* other column groups because as I like to say “where there is one, there are probably more”.

Bugblatter Beast of Traal

It is possible to wrap your head in a towel so the beast does not see you, but it is sometimes better to actually look for these things before they get really really bad. I hate to think how long this RUNSTATS was just “growing and growing and growing” with no-one noticing that it was quite simply insane!

SYSSTATFEEDBACK is good but not that good!

Remember, it is doing the best it can based on the SQL usage and what the Db2 Optimizer thinks is missing or might improve performance. In this case, the excessive number of column groups and the excessive size of the groups was actually way more of a problem than a help!

Parameter Markers…

It would also have been fine if the developers had coded good SQL with parameter markers so that SYSSTATFEEDBACK would not have started the whole problem in the first place! As a secondary bonus, moving to parameter markers stops any SQL Injection attack vectors as you could do a lot of damage with a VARCHAR(2000) text field!!! Just using “A’ OR ‘A’ = ‘A” would be nice and evil, wouldn’t it!!! Returns every single row because in the code it is stringed into a delimited string. So, if the customer gives A as input it builds this string:

SELECT all my columns FROM mytable WHERE ADDRESS = 'A' ;

Now add my injection code:

SELECT all my columns FROM mytable WHERE ADDRESS = 'A' OR 'A' = 'A' ;

What does that OR do? Yep – It is always true so every row is always returned…Not what you would want with 120 million rows…

Stale Stats?

A last bit about SYSSTATFEEDBACK is that it recommends STALE quite a lot and some of these bogus entries are, in fact, just created by a STALE recommendation so one other way of clearing them all out is to run a little SQL like the following:

-- THIS SQL WILL CORRECT THE PROBLEM OF BOGUS COLUMN COLGROUP CAUSING
-- EXCESSIVE SORT ALLOCATION AND FAILING RUNSTATS.
--
-- WHAT IT DOES IS:
--
--  1) STOP SYSSTATFEEDBACK GENERATION FOR A GIVEN TABLE
--  2) CLEAN UP SYSCOLDIST AND SYSCOLDISTSTATS "F" ENTRIES WHICH
--    ARE LISTED IN SYSSTATFEEDBACK WITH A "F" AND "STALE" ENTRY
--  3) DELETE ALL "F" AND "STALE" ENTRIES FROM SYSSTATFEEDBACK
--
-- TWO VARIABLES WILL BE CREATED AND USE THE DEFAULT FOR NAME AND
-- CREATOR:
--
CREATE VARIABLE TAB_NAME    VARCHAR(128)
   DEFAULT 'MY_BAD_TABLE'
;
CREATE VARIABLE TAB_CREATOR VARCHAR(128)
   DEFAULT 'MY_BAD_CREATOR'
;
--
-- STOP SYSSTATFEEDBACK PROCESSING FOR THIS TABLE
--
UPDATE SYSIBM.SYSTABLES
SET STATS_FEEDBACK = 'N' 
WHERE CREATOR      = TAB_CREATOR
  AND NAME         = TAB_NAME
  AND TYPE         = 'T'
;
COMMIT ;
--
-- DELETE ANY STALE COLDIST FREQ VALS FOR THIS TABLE
--
DELETE FROM SYSIBM.SYSCOLDIST A
WHERE A.TBOWNER    = TAB_CREATOR 
  AND A.TBNAME     = TAB_NAME
  AND A.TYPE       = 'F'
  AND EXISTS (SELECT 1 FROM SYSIBM.SYSSTATFEEDBACK B
              WHERE B.TBCREATOR = TAB_CREATOR
                AND B.TBNAME    = TAB_NAME
                AND B.TYPE      = 'F'
                AND B.REASON    = 'STALE'
                AND B.TBCREATOR = A.TBOWNER
                AND B.TBNAME    = A.TBNAME
                AND B.COLNAME   = A.NAME)
;
COMMIT ;
--
-- DELETE ANY STALE COLDISTSTATS FREQ VALS FOR THIS TABLE
--
DELETE FROM SYSIBM.SYSCOLDISTSTATS A
WHERE A.TBOWNER    = TAB_CREATOR
  AND A.TBNAME     = TAB_NAME
  AND A.TYPE       = 'F'
  AND EXISTS (SELECT 1 FROM SYSIBM.SYSSTATFEEDBACK B
              WHERE B.TBCREATOR = TAB_CREATOR
                AND B.TBNAME    = TAB_NAME
                AND B.TYPE      = 'F' 
                AND B.REASON    = 'STALE'
                AND B.TBCREATOR = A.TBOWNER
                AND B.TBNAME    = A.TBNAME
                AND B.COLNAME   = A.NAME)
;
COMMIT ;
--
-- DELETE ANY STALE SYSSTATFEEDBACK FREQ VALS FOR THIS TABLE
--
DELETE FROM SYSIBM.SYSSTATFEEDBACK
WHERE TBCREATOR    = TAB_CREATOR
  AND TBNAME       = TAB_NAME
  AND TYPE         = 'F'
  AND REASON       = 'STALE'
;
COMMIT ;
--
-- DROP THE CREATED VARS FOR NEXT RUN
--
DROP VARIABLE TAB_NAME    ;
DROP VARIABLE TAB_CREATOR ;
COMMIT ;

Take care out there!

Caveat Emptor!

Remember to always review DELETEs like this *before* you do them in production. Blindly deleting stuff is sometimes dangerous and hazardous to your career path!

I hope you found this info interesting on a cold and dark January day, at least here in Germany!

TTFN,

Roy Boxwell

2025-09 Poor performing SQL – A buyers guide

This month I wish to run through a bunch of, sadly, pretty common SQL coding mistakes that lot of beginners fall into. Not just humans either! AI is increasingly generating code snippets which developers simply cut-and-paste in the hope that it is:

  1. Correct SQL
  2. Does what they want it to do
  3. Runs acceptably fast!

In the Beginning was the SELECT

So, let us begin with the number 1 mistake all coders make at some time or other:

SELECT * from mytable ;

Yep, SELECT * FROM mytable. We have all done this, and in SPUFI it is the absolute norm! But if you are coding SQL that must run fast it is deadly… Why?

You are *never* alone in this world and that is doubly true of SQL running on big iron. Think buffer pools, think work space, think FTB etc. RAM is cheap these days, but if it is all being used it is no good to anyone else! The reason I mention space here, is when you code a SELECT * you are, pretty obviously, selecting every single column in the Table, View, MQT etc. This is pretty clearly going to cost you in CPU and I/O as Db2 must access every column in every row, format it, and return the value.

It gets worse, of course, as we live in a buffered world. All of this data is read and written into your precious little buffer pools and also is externalized into your darling little sort work spaces… You get the idea! Every column extra you add to the SELECT adds to the colossal amount of storage and cpu you are using. It gets even worse: If the optimizer sees this, it will sometimes, and pretty often, switch off using an index as it thinks “Well, I gotta get back every column value so an index might not really save cpu here!” Whereas a SELECT with just the three columns you really want, which also happen to be in an index, will then tell the Optimizer: Just scan this small, in comparison, index dataset! The savings can be vast.

It gets still even worse, of course… Coding a SELECT * in static or dynamic SQL will require a code change whenever you add or remove a column, as the cursor and/or program *is* aware of the columns and types of data being returned. Failure to do so is very bad news indeed!

Interesting little fact: Removing a column and then running with SELECT * will not actually cause a run time error (A negative SQLCODE), but it will cause you to possibly select garbage into fields. It is one of my pet bug bears that you can always have more columns on a FETCH than on a SELECT and Db2 does not warn or tell you!

Bad Index Usage

Using functions on columns tends to kill index access outright. If you can code around it – super! If you cannot then an IOE (Index On Expression) might be your only hope. No one likes IOEs though… The expression used must match 100% to the expression in the SQL and so for UPPER or LOWER it is not a problem but for SUBSTR(COL3 , 1 , 5) and SUBSTR(COL3 , 1 , 4) it will fail – and not tell you that the IOE was there but was not a 100% Match. EXPLAIN is naturally your friend here!

Missing Index Columns

Another absolute favorite of programmers is forgetting a join column… We have all done it, and so I am not going to throw the first stone here, but if you have two tables, both with four column indexes all with the same column names and/or functional content, then when you EXPLAIN and it joins with just one or two columns – Alarm bells should start ringing. Sometimes it must be like this but most times a JOIN / WHERE predicate has simply fallen under the table – These can be evil little problems, as sometimes the cardinality of the missing column is one so the returned data is all perfect… Nasty! Here our SQL PerformanceExpert for Db2 z/OS (SPX) software can really help out with the hunt!

Cartesian Join

If I had a Euro for every cartesian join I have seen in production I could retire! The major problem here, is sometimes you do not even see it, and sometimes the tables are so small the results are still OK. So these little problems fall under the radar until one day, that single row table you are mistakenly *not* joining to, grows up into 1,000,000 rows and then your little SQL just grinds to a halt, sucking the whole system down with it! These cartesian joins are sometimes caused by SQL changes where a programmer removes a JOIN to a table and accidentally deletes one row too many in the code. The syntax is still fine, the query might still run fine – especially in test – but then that little table grows… Boom! Here you need to use EXPLAIN, like in the index query, to see *exactly* how the Db2 Optimizer is actually joining all the tables. If you are using SYSIBM.SYSDUMMYx style tables, then you can sometimes want, and even expect, a cartesian join as you are 100% guaranteed to not ever have more than one row in that table! In all other cases, you had better make sure that there are correct JOIN … ON or WHERE criteria to avoid this pitfall.

Correlated Death

I read many years ago that “Correlated Queries are better and faster than Non-Correlated Queries unless they are not”. I always loved this advice as its right up there with “It Depends” in the list of non-helpful helpful advice! However, it has spawned an industry of correlated queries where programmers are 100% sure that writing SQL with correlated queries *all* the time is the absolute bee’s knees in performance. It isn’t! Here is a classic case:

SELECT C.CUSTOMER_ID, C.NAME
FROM CUSTOMER C
WHERE EXISTS (SELECT 1
              FROM ORDERS O
              WHERE O.CUSTOMER_ID = C.CUSTOMER_ID)
;

Please do not do this as the correlated query will be executed for *every* row in the CUSTOMER table, better could well be:

SELECT DISTINCT C.CUSTOMER_ID, C.NAME
FROM CUSTOMER C
   , ORDERS   O
WHERE O.CUSTOMER_ID = C.CUSTOMER_ID
;

Sharp eyes will have noticed the additional DISTINCT on the second query. Why? Well, we (I) do not know whether the chance of multiple rows joining on orders is wanted or not, probably not, so you must then add the DISTINCT to remove all the duplicates. This is actually one case where I would run both queries with EXPLAIN and in Production to compare side by side the results and CPU, I/O etc. and check that the join result was/is the better choice. Remember: It Depends! There are so many possible good reasons for a correlated join that it is nearly impossible to simply say “always bad or always good”. You must always test what you think might help as you might actually make things worse.

DISTINCTly a Bad Idea

I have so often seen queries like:

SELECT DISTINCT blah blah from blah
UNION
SELECT DISTINCT blah blah from blah
ORDER BY blah blah
;

Why do people do this still? Db2 is pretty clever but if you tell it to sort everything and remove duplicates and then you tell it again to do basically the same it will do it! In this case just remove the DISTINCTs and try and get the column order into the ORDER BY sequence so that the last sort is a tick faster – it will save you a ton of I/O and CPU. Remember: Only remove duplicates or sort if you really want to, but also remember: If you do not have an ORDER BY, the sequence of returned data is basically random! It might come back in beautiful sequential order just because of an index, and then tomorrow a different index could be used and POUF! Random order. Golden rule here: Use the minimum number of DISTINCTs and UNIONs and at least one ORDER BY if the order of returned rows is important. If the sort order is not important then do not add it just for the humans!

Bonus point: Some coders add DISTINCT because they get “duplicate” rows back from their query. Adding a DISTINCT fixes their problem, but all it actually does is hide it under a heap of CPU and I/O!

The Bachelor Problem – (Borrowed from Craig Mullins)

This is the failure to COMMIT. It might seem trivial at first but after you have output about 10,000 log records and you are up to over 1,000 row updates, it might be time to think about COMMITing all that work and freeing up all the rows of data and memory in the buffer pool you are blocking at the moment. Remember that COMMIT will cause you to lose Cursor position unless you have WITH HOLD defined on them and always remember to commit at Transaction boundaries – Never “half way through” a transaction for example! I also never let a subroutine issue a COMMIT – it must be the “main” program that controls COMMIT frequency. My rule of thumb here, is to COMMIT at 500 “transactions” that cause many more than 500 updates to the database. I also always use a variable that can be set at run time to raise or lower this frequency. Commits after ever update will kill you – Never Committing will also kill you – Pick a good middle path!

Too Much Data can hurt!

Following on from not COMMITing when you should, is the other cardinal sin of fetching the world to see if one row exists… I have really seen this query in *production*:

SELECT 1
FROM SYSIBM.SYSTABLES
;

This was being used as a “ping” to see if the z/OS Db2 sub-system was up and accepting work! Utterly crazy and a grand example where adding LIMIT 1 would be a great help but really, why run this query at all? These days the z/OS Db2 is *always* there and so the query works 99.999% of the time and the 0.001% where it fails, well, the world is ending anyways! Please review all queries to make sure they are only returning the rows you wish to actually process!

Einstein was Right!

He is famously quoted as saying “Insanity is doing the same thing over and over and expecting different results, like rebooting Windows PCs” – This has at least two or three areas where it hits the SQL Db2 world!

  1. Automation! If you do the same thing every morning every day – automate it!
  2. If you are doing COMMIT, ROLLBACK, ROLLBACK after ever transaction – Stop!
  3. If you are doing CLOSE CURSOR at CICS start of transaction – Stop!

The first point is a no-brainer. If you can write a little script, or even a program, that stops you wasting 30 minutes every day, it will add up very quickly! Remember the clever DBA gets the machine to do his/her work and not the other way around!

Number two is, sadly, a real life Hibernate problem that I discovered. The frame work, after a COMMIT, always issued two ROLLBACKs – I have mentioned before, that frameworks are all well and good but when you have *no* idea what is going on you have a problem brewing. In this particular case, the COMMIT and ROLLBACKs were not being traced, as they are not really SQL, so you had to look a bit deeper under the covers to see a crazy number of ROLLBACKs. Remember that Db2 is not the cleverest piece of silicon on the block … When it gets told ROLLBACK it dutifully saunters off and checks against the log to see if anything has been updated since the last COMMIT, and when you issue a ROLLBACK immediately after the last ROLLBACK Db2 goes off and checks again … This is a tremendous waste of CPU and I hope none of you out there “find” this little gem …

The third point was also found live in production. Way, way, way back when some bright CICS programmer had the brilliant idea to start every transaction with a CLOSE CURSOR in case the cursor had been left OPEN by the last transaction … We all know this cannot happen, right? Anyways, this genius got his/her way and *every* CICS transaction issued a CLOSE CURSOR and got an SQLCODE -501 “The cursor you are closing was not open” – surprise, surprise! Just think, if you had over 20,000,000 CICS transactions per day how much CPU just this tiny error handling path would cost you? Again, it is an SQLCODE that needs to be traced and they are not so easy to handle, but it is also doable. Here, check for how many weird or even “normal” SQLCODEs you get every day from all your running SQL – The contents can be quite scary!!!

No-one eats Stale Bread, do they?

But some people never care about Stale Statistics. If your STATSLASTTIME column in Real-Time Statistics (RTS) is over five years ago, and you have STATSINSERTS, STATSDELETES, STATSUPDATES or STATSMASSDELETES in the RTS, then you have to wonder, “Would a RUNSTATS possibly help me?”. Remember to always do a before/after check of the access paths – Here our Bind ImpactExpert for Db2 z/OS (BIX) can really help you.

Do you like Feedback?

Have you checked your SYSSTATFEEDBACK table to see what the Optimizer is crying out for? It is a gold mine of required data for your RUNSTATS, but beware, dear friends … It also holds the SQL query from Hell that the Management tried running once and got hopelessly wrong! In other words: review the contents, take a copy of the recommendations, delete them all and wait a while … If, after a couple of weeks, that row comes back into the SYSSTATFEEDBACK – BINGO! This is probably data that the Optimizer really needs and not a one-off query from hell! Here, our Real-Time DBAExpert for Db2 z/OS (RTDX) can really help you out!

Just the Facts, Ma’am

Well, I just wanted to run through a bunch of common errors and problems that I have seen around the world! I would love to hear from you if you have any other “Top Ten” style lists of evil things that have happened!

TTFN,

Roy Boxwell

2025-08 AI writes a “Db2 for z/OS Newsletter”

This month I thought I would let an AI do all the work… here is the result from the following prompt “write me a Db2 for z/OS newsletter about an interesting topic either to do with SQL or utility usage.”

Topic: Getting the Most from Temporal Tables in DB2 for z/OS

Why Temporal Tables Matter

In many industries—especially banking, insurance, and government—regulations require that you be able to reconstruct historical data exactly as it was at a given point in time. Prior to DB2 10 for z/OS, this was often done with custom history tables, triggers, and a lot of application code.

Today, temporal tables make this far simpler, with built-in DB2 features to maintain and query historical versions of data.

System-Period vs. Application-Period Temporal Tables

DB2 supports two main types of temporal tables:

  1. System-period temporal tables – DB2 automatically maintains historical versions of rows when changes occur. Two timestamp columns (ROW BEGIN and ROW END) and a TRANSACTION START ID are used to track validity.
  2. Application-period temporal tables – The application controls the validity period by providing its own start and end timestamps. Useful when the “effective” date of data is different from the transaction date.

You can also combine them into bi-temporal tables for the ultimate in time-based data tracking.

Basic Example: System-Period Temporal Table

CREATE TABLE CUSTOMER (
    CUST_ID         INTEGER NOT NULL,
    NAME            VARCHAR(100),
    STATUS          CHAR(1),
    ROW_BEGIN       TIMESTAMP(12) GENERATED ALWAYS AS ROW BEGIN NOT NULL,
    ROW_END         TIMESTAMP(12) GENERATED ALWAYS AS ROW END   NOT NULL,
    TRAN_START_ID   TIMESTAMP(12) GENERATED ALWAYS AS TRANSACTION START ID 
                                                                NOT NULL,
    PERIOD SYSTEM_TIME (ROW_BEGIN, ROW_END)
) WITH SYSTEM VERSIONING;

Once the table is system-versioned, DB2 automatically creates and manages a history table. You can query historical data using the FOR SYSTEM_TIME clause:

SELECT *
FROM CUSTOMER
FOR SYSTEM_TIME AS OF TIMESTAMP('2024-07-01-10.00.00')
WHERE CUST_ID = 1001;

Performance Considerations

  • Indexing Matters: DB2 will maintain history indexes for you, but ensure they support your most common time-based predicates to avoid costly table scans.
  • Partitioning History Tables: If your history tables grow large, consider partitioning by date to keep queries efficient.
  • Utility Implications: REORG, COPY, and RUNSTATS operate on both the base and history tables. REORG with SHRLEVEL CHANGE can keep them online, but be mindful of increased elapsed time when history data is large.

Utility Tip: REORG and Temporal Tables

When running REORG on a temporal table, DB2 handles both the base and history table in a single execution if they are bound together. However, if you need to reorganize only the history table (for example, to improve access to older data without touching current production data), you can explicitly REORG just the history table.

Example:

REORG TABLESPACE HISTTS     <-- History table's tablespace
    SHRLEVEL CHANGE
    SORTDATA
    STATISTICS INDEX(ALL);

This can help reduce impact on current transactions while still maintaining query performance for historical reporting.

Best Practices

  • Use temporal queries rather than joining base and history tables manually; it ensures optimizer support and cleaner code.
  • Regularly RUNSTATS on both base and history tables to keep the optimizer informed about growth patterns.
  • For reporting, consider materialized query tables (MQTs) on top of temporal queries for faster access to common time-based reports.

Final Thought:

Temporal tables take much of the heavy lifting out of managing historical data in DB2 for z/OS. When paired with smart indexing, partitioning, and utility usage, they can deliver both compliance and performance—without the spaghetti code of the old days.

Maintaining Large Temporal Table Environments in DB2 for z/OS

(Utility Scheduling & Best Practices)

When you start using temporal tables—especially system-period or bi-temporal—your history tables grow quickly. This can cause issues if utilities aren’t tuned for their unique workload patterns.

Here’s a real-world maintenance workflow you can adapt.

1. Understand the Data Growth Pattern

  • Base table – Moderate growth, mainly current records.
  • History table – Potentially huge growth, as every UPDATE or DELETE adds rows.
  • Data skew – History tables are often append-heavy with few deletes.

Before setting schedules, run:

SELECT COUNT(*), MIN(ROW_BEGIN), MAX(ROW_END)
FROM HISTORY_TABLE;

This gives you volume and time-span, helping you plan partition-level REORGs.

2. Recommended Utility Workflow

Nightly (or per batch cycle):

  • RUNSTATS on base table only (small, keeps optimizer fresh for OLTP).
RUNSTATS TABLESPACE BASETS TABLE(ALL) INDEX(ALL) UPDATE ALL;

Weekly:

  • REORG history table partitions with recent activity.
    • Use PART to target only active partitions.
    • SHRLEVEL CHANGE to keep reporting jobs running.
REORG TABLESPACE HISTTS PART 15:17
    SHRLEVEL CHANGE
    SORTDATA
    STATISTICS INDEX(ALL);

Monthly:

  • COPY both base and history tables for backup compliance.
COPY TABLESPACE BASETS FULL YES
COPY TABLESPACE HISTTS FULL YES

Quarterly:

  • REORG full history table if query performance declines or RUNSTATS indicate high clustering ratio loss.
  • RUNSTATS on full history table.
  • Consider MERGECOPY to consolidate image copies.

3. Tips to Keep Utilities Efficient

  • Use LISTDEF to dynamically select only affected partitions:
LISTDEF HISTPARTS INCLUDE TABLESPACE HISTTS
    PARTLEVEL
    PARTCOND (LASTUSED > CURRENT DATE - 7 DAYS);

  • Parallelism – Large history REORG jobs benefit from PARALLEL keyword if CPU and I/O capacity allow.
  • Inline Stats – For history tables, you can take inline stats during REORG to avoid a separate RUNSTATS step.
  • Automation – Many shops integrate this with DSNACCOX to decide when REORG or RUNSTATS are needed.

4. Example End-to-End Utility JCL Snippet

//STEP1  EXEC DSNUPROC,SYSTEM=DSN1,UID='HIST.REORG'
//SYSIN    DD *
  LISTDEF HIST_PARTS INCLUDE TABLESPACE HISTTS
    PARTLEVEL
    PARTCOND (LASTUSED > CURRENT DATE - 7 DAYS)
  REORG LISTDEF HIST_PARTS
    SHRLEVEL CHANGE
    SORTDATA
    STATISTICS INDEX(ALL) UPDATE ALL
/*
//STEP2  EXEC DSNUPROC,SYSTEM=DSN1,UID='HIST.COPY'
//SYSIN    DD *
  COPY TABLESPACE HISTTS FULL YES
/*
//STEP3  EXEC DSNUPROC,SYSTEM=DSN1,UID='BASE.RUNSTATS'
//SYSIN    DD *
  RUNSTATS TABLESPACE BASETS TABLE(ALL) INDEX(ALL) UPDATE ALL
/*

5. Key Takeaways

  • Don’t treat history tables like “just another table”—their size and access patterns demand dedicated utility schedules.
  • Use partition-level maintenance wherever possible to cut elapsed time.
  • Integrate RUNSTATS and REORG with automation tools to keep the optimizer happy without manual intervention.

Whaddya Think?

Well, I do not think I will unemployed anytime soon based on this, although, at first glance, it is not that bad. Then you look at the details and it starts to all go horribly wrong…

  • Table creation does not know of the keyword SYSTEM VERSIONING, this hints to the fact that the HIST table is not actually automatically created either!

The example SELECT SQL naturally dies with a:

DSNT408I SQLCODE = -20524, ERROR:  INVALID PERIOD SPECIFICATION OR PERIOD
         CLAUSE FOR PERIOD SYSTEM_TIME. REASON CODE = 02

until you manually create the history table and issue the required ALTER:

CREATE TABLE HIST_CUSTOMER (                       
 CUST_ID       INTEGER NOT NULL,                    
 NAME          VARCHAR(100),                       
 STATUS        CHAR(1),                            
 ROW_BEGIN     TIMESTAMP(12) NOT NULL,             
 ROW_END       TIMESTAMP(12) NOT NULL,             
 TRAN_START_ID TIMESTAMP(12) NOT NULL              
)
;                                                 
COMMIT 
;                                           
ALTER TABLE CUSTOMER                               
  ADD VERSIONING USE HISTORY TABLE HIST_CUSTOMER 
;
  • LISTDEF does not support conditional partitions (PARTCOND) although the idea is pretty cool.
  • Utility statements do not end in a semi-colon.
  • Finally, and the very worst mistake of all: seven uses of the capital B! The audacity!!!

What About You?

Have any of you had “interesting” AI suggestions in the world of Db2 for z/OS? I would love to hear from you!

TTFN,

Roy Boxwell