2022-04 A brief history of the Universal Tablespace (UTS) Part Two

This month I wish to finish off, what I started last month, my musings about UTS over the releases and years.

Db2 12 Changes to the UTS Picture

For Partitioned By Range (PBR), a brand-new space was created called the UTS PBR Relative Page Number (RPN) which was, in my opinion, the best thing in Db2 12! Quite simply, it allows the dynamic ALTERing of the partition and all of the related partitioned indexes DSSIZE on-the-fly even when a LOAD is running! This was great! Any users out there who have had a nightmare LOAD? Yep, now, as long as you are actively monitoring your data and index partition sizes, you can issue these ALTERs automatically and never hit the buffers like you do today.

DSSIZE gets propagated through

To enable this feature DSSIZE was improved to be settable at the data partition level and also extended to partitioned indexes. The available values were changed to allow any integer from 1 GB to 1024 GB. This allows extreme flexibility for all sizing requirements. Note, however, that NPSIs are still stuck in the middle of nowhere with just PIECESIZE as a helping hand…

Everything groovy???

So, what was wrong with this picture? Well, the change from PBR to PBR RPN was, shall we say, a little bit painful. The RID size got extended and as the RID is stored in *every* header page it required a tablespace reorg with partition-based inline image copies. Now, as you can well imagine, most people’s PBRs are pretty big, and allocating 4096 Virtual Tapes in parallel was just not going to happen! After a while IBM enhanced REORG so that you could put multiple copies on one tape, sort of like STACK, but much better – and not just for Tape but also for DASD. This has really accelerated the acceptance and usage of PBR RPN.

The future is bright!

Check this Blog entry:

https://www.idug.org/blogs/emil-kotrc1/2021/03/12/why-universal-table-spaces-uts

It is revealed that in Apollo (Db2 for z/OS vNext – Which has now been released as Db2 13 for z/OS), the ability to migrate from a PBG to a PBR will become available instead of the UNLOAD, DROP, CREATE, LOAD method which is the only way up until Db2 12. This will be very handy as the PBR RPN is the best way to go forward with large tablespaces (>60 GB) as long as you have *some* sort of available partitioning scheme, of course!

No more worries??

What do you need to worry about now? Well, you remember that huge LOAD I mentioned earlier that comes at the partition level? You must simply monitor the sizes of your partitioned objects and add a few GBs of space when required, on the fly, with no outage.

How?

Well, we have a little product called SAX+ which does exactly that! It starts the required OPx IFCID traces and computes the values against given thresholds, then automatically issues the ALTERs giving you a seamless experience with PBR RPNs. The only “problem” left now is when you are approaching the 1024 GB absolute PBR RPN physical limit. SAX+ warns you about this as well. Then you will have to either schedule a REBALANCE or a new LIMITKEY definition and REORG to spread the load again. However, when you know this well in advance it is no longer a serious problem!

Not yet at PBR RPN? – No problem!

PBRs which are not yet RPNs are monitored to warn when a threshold of usage in a data or index partition is exceeded. This also gives you more than enough lead time to get the REORG ready to switch it to RPN or just resize/rebalance your partitions.

What about PBGs?

PBGs are also fully supported within SAX+ as it can automatically add partitions, if desired, which additionally avoids SQLCODE -904’s. Plus, SAX+ adjusts the way it works depending on the MAXPARTITIONS and the actual number of allocated partitions. For example, MAXPARTITIONS 10 with a 90% warning would alert you when the ninth partition is allocated and in use. When the tenth partition gets allocated the warning switches from an LDS warning (Running out of available partitions) to a “last partition” filling up warning similar to PBRs which are not yet RPNs. This obviously helps a lot when you have MAXPARTITIONS 1 which is the IBM recommendation these days.

Anything else?

Naturally, SAX+ also takes care of the other problems that can catch you unawares:

  • Running out of Linear Datasets (LOB, PBG, XML, non-UTS space and NPSI)
  • Running out of space
  • Running out of extents and also badly fragmented extents
  • Running out of volumes
  • Running out of physical range for SEQUENCES or IDENTITY columns
  • Running out of physical range for Numeric Primary key columns
  • Running out of DBATs
  • Running out of space in SMS Storage Groups

All of the entries in the above list are very annoying when they hit you with no warning, especially running out of Linear Datasets. Think NPSIs here! Every PIECESIZE is an LDS and so you can run out of these quicker than you think.

LOG problems?

One last thing that SAX+ can help you with is detecting Db2 Log problems together with stalled logs… Not something you normally think of with a space management tool! When Db2 starts running out of space in active logs it issues the DSNJ110E message and I know shops who have “missed” this alert. The problem was that the Db2 Log SMS Storage Group was getting full *and* the tape offload had stalled… As you can imagine this scenario is not pretty, so checking you Log storage groups is probably a good idea to guarantee you do not hit this problem!

That’s enough about UTS for this month. If IBM bring out another TLA for a TLA I will scream!

TTFN,

Roy Boxwell

2022-03 A brief history of the Universal tablespace (UTS)

To understand the UTS concept it helps to look back and see what we, at least in the Db2 world, started with!

In the beginning it was simple

In the beginning were simple tablespaces with the possibility of multiple tables all mixing rows with each other that meant performance got very bad after a very short period of time. We also got single table partitioned tablespaces that required a Partitioning Index (PI) to be created before you could use the table at all. You could also create non-partitioned indexes (NPIs) that were Unique or Non-Unique and were based on the data from all partitions. You could, sort of, manage the size of these using the PIECESIZE parameter, but most shops just took the default and never used it.

Something new in V2R1

Then in DB2 V2R1, we got segmented spaces where all of any given segment only held rows for one table. This improved performance a lot, especially for insert and mass delete processing. It stayed like this right up until DB2 V8.1 when the requirement for a PI was dropped as now you could “partition by table”. This was a leap forward and also brought in the new Data-Partitioned Secondary Index (DPSI) to allow a different partitioning scheme as opposed to the actual partitioning scheme. First, only non-unique DPSIs were allowed (Just think about up to 4096 index checks for breaking a unique rule!) Unique was then finally actually allowed in DB2 V9.1 as long as the DPSI columns were a superset of the partitioning columns. 

DB2 V8.1 the *big* release!

DB2 V8.1 was also the release that renamed our good old NPIs to be non-partitioned secondary indexes (NPSIs) The rules for “When is an index a partitioned index (PI or DPSI)?” or “When is it a non-partitioned secondary index (NPSI)?” are still pretty hard to explain – and most users just seem to guess! 

Death of a PI

The drop of the PI was very good but most shops kept them hanging around as the application code was using them all over the place. DPSIs became a problem right out of the get go causing massive index probes and performance headaches right up to this day. Nowadays, the vast majority of indexes are NPSIs with a few, hopefully well chosen, DPSIs and any required PIs.

DB2 V9.1 new things appearing

Then in DB2 V9.1 along came the new kid on the block – UTS.

IBM has mentioned on multiple occasions that any new Db2 features will only be developed for UTS usage, which has been seen with the developments of CLONE spaces, HASH spaces, Currently committed locking behavior, Pending DDL, Inline LOBs, XML multi-versioning, and ALTER TABLE with DROP COLUMN. This list just keeps on growing. 

The Universal TableSpace was born!

The UTS was created with two distinct and very different flavors.

Partition-By-Range (PBR) is a single-table, segmented tablespace that is partitioned on data values that are given at the table level. Partitions can be from 1 GB DSSIZE up to 64 GB DSSIZE in DB2 V9.1 (In DB2 10 it rose to 256 GB DSSIZE.) The maximum physical size of the partitions and the corresponding partitioned and non-partitioned indexes are all, relative to each other, the same. This was a modernization of the original partitioned table space with its index-based partitioning scheme. 

PBRs got used straightaway as the benefits of the segmented definition were quickly realized. They gave fast spacemap usage for variable length rows and much faster mass delete processing. Changing the maximum possible size, the DSSIZE, of a partition or even of a partitioned index was still very nasty as it required a complete tablespace REORG to action. For NPSIs there was no real change – still just PIECESIZE here to, sort of, manage the maximum size of the datasets.

Calculating the maximum possible sizes is also “non-trivial”. For the data partition it is relatively straightforward but for the PI, the DPSI, and the NPSI it is a pretty nasty set of calculations you must do – and any mistake can quickly lead to an SQLCODE -904!

Partition-By-Growth (PBG) is a single-table, segmented tablespace that can have from 1 to 4096 partitions, each of which can be from 1 GB DSSIZE up to 64 GB DSSIZE in DB2 V9.1 (In DB2 10 it rose to 256 GB DSSIZE.) The PBG cannot have a partitioning key but can have multiple NPSIs, if desired. The PBG was basically viewed as just one huge container where you could just insert rows forever. This has benefits and drawbacks! 

Benefits are: No worries about PI and DPSI definitions as you can only have NPSIs. No worries about running out of space – I mean 4096 huge partitions is a vast amount of space! 

Drawbacks are: No PI and so no way to balance the partitions, a terrible space map search algorithm that simply kills mass insert processes and a huge problem with utilities as it is “one huge space” – and reorganizing one part in the middle of 100’s just causes grief. Plus the inherent problems in copying or cloning data are more complicated, especially if the number of parts on source and target are not the same.

It all started so easily…

In the beginning people used MAXPARTITIONS 4096 thinking “I will never need to change this” but very very quickly it turned out that this was a disastrous idea! The problem being that *all* of the partitions required some control block storage in memory, even if not physically defined or even in use! This caused massive problems and the recommendation to drop down a *lot* in the usage of MAXPARTITIONS. Over the years it has dropped down even more and so now, as of Db2 12, the IBM recommendation for a PBG definition is:

MAXPARTITIONS 1 DSSIZE 64 GB

For the older readers amongst us it should be apparent that this is the good old Linear Dataset (LDS) limit for simple and segmented spaces but rolled out in a new way! For the younger people out there, when DB2 began you could only have 2GB in one VSAM dataset, and so simple and segmented datasets got the ability to allocate up to 32 VSAM datasets, thus giving us the *massive* amount of 64GB space! Coincidence? I think not!

That’s enough about UTS for this month, come back next month for a wrap up about how Db2 12 has changed this picture and all the ways to handle these spaces in the best possible way!

TTFN,

Roy Boxwell

2022-02 ZPARMs never stop changing!

This month I want to go through some of the absolutely most important ZPARMs that control how your Db2 systems behave in a very significant manner. All of the following ZPARMs have a performance impact of some sort and we are always trying to squeeze the last drop of performance out of our Db2 sub-systems, aren’t we?

Starting with the Easy Stuff…

CACHEDYN. YES/NO, default YES. Should always be set to YES unless you do not care about saving dynamic SQL performance. Back a few decades ago, the recommendation was to have this set to NO as default! Hard to believe that these days, where most shops have 80% – 90% dynamic SQL during the day!

Now we Get to the Numerics!

OUTBUFF. 400 – 400,000, default 4,000. This is *extremely* important and you really should set it to the highest possible value you can afford in real memory! As a minimum, it should be 102,400 KB (100MB). This is the buffer that Db2 uses to write log records before they are “really” written to disk. The larger the buffer, the greater the chance that by a ROLLBACK the data required is in the buffer and not on disk. This is a big win and the default of 4,000 KB is crazy low!

Skeletons in the Closet?

EDM_SKELETON_POOL. 5,120 – 4,194,304, default 51,200. This is one of my personal favorites (I wrote a newsletter solely on this a few years ago) The default is way to small these days. I personally recommend at least 150,000 KB and actually even more if you can back it with real memory. Just like OUTBUFF, pour your memory in here but keep an eye on paging! If Db2 starts to page you are in serious trouble! Raising this can really help with keeping your DSC in control.

DBDs are Getting Bigger…

EDMDBDC. 5,000 – 4,194,304, default 23,400. The DBD Cache is getting more and more important as, due to UTS usage, the size of DBDs is increasing all the time. The default just doesn’t cut the mustard anymore so jump up to 40,960 as soon as you can.

DSC is Always too Small!

EDMSTMTC. 5,000 – 4,194,304, default 113,386. The EDM Statement Cache (really the Dynamic Statement Cache) is where Db2 keeps a copy of the prepared statements that have been executed. So when the exact same SQL statement with the exact same set of flags and qualifiers is executed, Db2 can avoid the full prepare and just re-execute the statement. This is basically a no-brainer and should be set to at least 122,880 KB. Even up to 2TB is perfectly ok. Remember: A read from here is *much* faster than a full prepare, so you get a very quick ROI and great value for the memory invested! Keep raising the value until your flushing rates for DSC drop down to just 100’s per hour, if you can! Remember to cross check with the EDM_SKELETON_POOL ZPARM as well. It always takes two to Tango…

How Many SQLs?

MAXKEEPD. 0 – 204,800, default 5,000. The Max Kept Dyn Stmts parameter is how many prepared SQLs to keep past commit or rollback. It should be set to a minimum of 8,000 or so. Raising this might well cause a large memory demand in the ssidDBM1 address space so care must be taken.

RIDs Keep Getting Longer…

MAXRBLK. 0, 128 – 2,000,000, default 1,000,000. RID POOL SIZE is the maximum amount of memory to be available for RID Block entries. It should be at least 1,000,000 and, if you can, push it to the maximum of 2,000,000. Unless you want to switch off all RID Block access plans in which case you set it to zero – Obviously not really recommended!

Sorts Always Need More Space

MAXSORT_IN_MEMORY. 1000 to SRTPOOL. The maximum in-memory sort size is the largest available space to complete ORDER BY, GROUP BY or both SQL Clauses. Remember that this is per thread, so you must have enough memory for lots of these in parallel. The number should be between 1,000 and 2,000, but whatever value you choose, it must be less than or equal to the SRTPOOL size.

Sparse or Pair-wise Access?

MXDTCACH. 0 – 512, default 20. Max data caching is the maximum size of the sparse index or pair-wise join data cache in megabytes. If you do not use sparse index, pair-wise join, or you are not a data warehouse shop, then you can leave this at its default. Otherwise, set it to be 41 MB or higher. If it is a data warehouse subsystem, then you could set this as high as 512 MB. (This ZPARM replaced the short-lived SJMXPOOL, by the way.)

Sort Node Expansion

SRTPOOL. 240 – 128,000, default 10,000. SORT POOL SIZE is the available memory that is needed for the sort pool. The default is 10,000 KB and should really be set to 20,000 KB at least, if not more! IFCID 96 can really help you size this parameter. Remember that the number of sort nodes leapt up from 32,000 in Db2 11 to 512,000 nodes for non-parallelism sorts and 128,000 nodes for a sort within a parallel child task in Db2 12. This means raising this ZPARM can have an even greater positive effect than before.

Your “Top Ten List”

These ten ZPARMs really influence how your Db2 system works and so must always be changed with great care and attention to detail. Always do a before and after appraisal to see whether or not changing them helped or hindered your system!

And Finally…

I have updated our Pocket Tool, Performance Health Check, to check and report all these ZPARMs, as well as all the other checks like the 6 Byte RBA/LRSN or the Mapping table changes or the reason for REORG etc etc. Feel free to download and run it, as it is but a click away!

If you have any comments, or other ZPARMs you think are also important for performance, feel free to drop me a line!

TTFN,

Roy Boxwell

2022-01 Fazed by phases?

This month I wish to do a quick run through, and review, of the effects of REBIND with active packages and how phases, both in and out, have given us some interesting problems to deal with!

Phase-In

As I mentioned in an earlier blog, the phase-in/phase-out was a very good idea indeed! It finally meant that we could do REBINDs whenever we wanted and no-one had to shut down servers to simply action a REBIND. This was always especially galling when it was an “empty” package or just to get an FL upgrade.

Allowed?

Remember, Phase-in is only allowed with PLANMGMT(EXTENDED) style packages and if the package is not a generated package for a trigger or SQL routine, such as a procedure or user-defined function.

Problems ahead?

The problems began soon after it was introduced in June 2019 with PH09191 and FL505. Now, it looked really good, and indeed it was, but it had one little flaw… it still required a SIX lock to do the business.

Problem solved?

This was solved in January 2021 with APAR PH28693 which changed the lock from a SIX to a much better U lock. With this APAR all of our problems were fixed and life would be good!

Nope…

Sadly, we then found out that the inactive/phased-out packages were actually causing rapid space growth in the SYSPACKCOPY (Not in SYSPACKAGE!) Remember that the phased-out packages all get copied across with a new number to the “back-up” SYSPACKCOPY for use until the active thread finally disconnects.

The Problem Gets Worse

Now these backed-up, old packages are sitting around in SYSPACKCOPY waiting until the next REBIND comes along whereupon Db2 will attempt a FREE. Various customers noticed that, for their intensely used packages, this free never really happened and so they “hit the buffers” of 14 copy_ids for packages… very nasty!

Another APAR to the Rescue!

IBM then created a new APAR, PH33295, that enhanced the FREE PACKAGE command to have a new PLANMGMTSCOPE sub-clause PHASEOUT. All this does is delete all of those old, no longer used packages. However, I can testify that doing a:

FREE PACKAGE(*.*.(*)) PLANMGMTSCOPE(PHASEOUT)

in production is one of the scariest commands I have ever issued!!! Personally, I would have preferred a brand new command like FREE PHASEDOUT PACKAGES or so…

Invalid as Well

While you are ridding yourself of 100’s of packages you could also issue the PLANMGMTSCOPE(INVALID) command which removes the phased-out and also gets rid of the dead, invalid packages which cannot ever be used anyway.

Do not Forget to Tidy up!

Once all these commands have been done, a REORG of the tablespace DSNDB06.SYSTSPKC is very highly recommended!

How have you experienced phase-in and phase-out? Do you think it’s a great feature or not?

Whatever you think, I would love to hear from you!

TTFN,

Roy Boxwell

2021-12 What’s in a Log anyway?

Hi! This month I wish to briefly delve into the inner workings of the D2b Log. The topic is very broad and complex and so this newsletter is only really skimming the surface of this topic!

What is the Log?

The Db2 Log is the central area where all data changes, plus a whole lot more, gets written away by Db2 as it does its normal work. Most shops allocate several large VSAM datasets for the Active Logs which, when full, get written off to Archive Logs.

Rules of the Log

How much Log do you need? The golden rules are all time based:

1) At least 24 hours of coverage on your Active Logs

2) At least 30 days of coverage on your Archive Logs

Any less and you could seriously run into trouble, breaking the 24 hour rule means that possibly normal ROLLBACKs might suddenly be requesting tape mounts which is not pretty and breaking the 30 days rule might put you into a world of pain when data sets get migrated off and scratched in a recovery scenario.

Take Care of Your Logs!

These Logs must be looked after and cared for as they save your company’s life on a daily and hourly basis. Normally, no-one really cares about the logs as they just “work” and that’s it! However, the Db2 Log is actually a bit of a bottleneck these days.

Remember LOG NO?

Many years ago, Roger Miller said “LOG NO datasets will be implemented in DB2 over my dead body” as he was pretty adament that LOG NO was a bad idea. The driver behind the requirement was just the sheer size of the logs being written by Db2 and the belief that writing fewer logs would make application faster.

How Many?

When you look at the history of Db2 you can see it started with between two and 31 active logs and between 10 and 1000 archive logs. We all thought “that will be more than enough”. Nowadays we have between two and 93 active and between 10 and 10,000 archives! Some shops rotate through their six byte RBAs in 24 hours and go through 1000’s of logs so we have grown up a little bit!

Before it Hits the Log…

Remember that there is another really important ZPARM that affects the LOG before the log is even hit – OUTBUFF it started out at a value between 40K and 4000K and is now between 400(K) and 400000(K). Just set it to the highest value you can! Db2 will always look here first before even looking at the active log and so if the data is here it is much faster than VSAM access!

Bottleneck?

Some customers were convinced that one area that was slowing down Db2 was the log write and the externalization of the data within. Roger was 100% against this but even he lost this battle and so the LOG NO space was born. However, if you ever do a ROLLBACK then the space goes into COPY pending which is horrible!

Checkpoint Charlie

The number of system checkpoints also has a direct impact on log size and usage. You must decide whether you wish to go time based (IBM-recommended way is three minutes) or transaction based – or even a mix of the two methods. I am in favour of keeping my life simple so I would always recommend the checkpoint every three minutes rule. You could argue that at “quiet times” overnight too many checkpoints will be taken, but I counter that argument with “When was the last time you ever had a quiet time on your machine?”

Index Split?

Believe it or not, index splits seem to take up some 55% of the Db2 Log at one shop I visited. We decided to try different index pages sizes , including compression, and this all really helped in reducing the log load pressure. This has a direct effect on DASD, elapsed and CPU time which was an allround winner!

Crystal Ball?

So, how do you look into the Db2 Log? If you have no tooling then you only get the IBM stuff which is – shall we say – basic. It was here that I decided to write a little COBOL program, that I have called Db2 Archive Log Viewer for Db2 z/OS, that would read all of the Archive logs (No VSAM – keep it simple!) and handle normal non-spanned log records to give me a glimpse into what on earth Db2 was writing into the darn things!

Log HealthCheck

So what does Db2 Archive Log Viewer for Db2 z/OS then do? Well, it reads as many Archive Logs as you can give it and reports on the contents of the Logs in absolute numbers and in size. Using this data enables you to get a new view into what your Db2 system is actually doing and who is causing all your logs to be written.

Surprise!

I was pretty surprised by what I saw inside my logs and I hope you find it interesting to peer inside your logs!

If you have any ideas or desires about log datasets, feel free to email me!

TTFN

Roy Boxwell

2021-11 When is an NPI not an NPI?

This month, I wish to discuss everything about indexes but focusing on Non-Partitioned Index’s (from now on NPIs). I must confess that the real name of an NPI is a Non-Partitioned Secondary Index but I really dislike that as a term…

In the Beginning was the Index

Well, actually, it was the data, but then we needed a quick way of accessing the data and so the index was born. In Db2, these are b-tree based indexes with a few system pages and then a hierarchy of non-leaf pages starting with the root page, which then branches down in levels until it finally hits the leaf page – and these contain the direct pointers to the data. Very fast and just a few getpage I/Os to read the non-leaf/leaf pages in.

Hope Springs Eternal

The hope is that all the system stuff, the root page, and quite a few other non-leaf pages will be hanging around in the bufferpool and so no real I/O is required, but you all know the real world? It never is like that!

Bigger and Bigger

Tables and tablespaces got bigger and bigger over time, so then the Partitioned Tablespace was born. At first it required a partitioning index (PI), but nowadays you should all be using table based partitioning.
Brief aside: Recently, there was a mega outage caused by a really nasty bug in Db2 that brought everything crashing down (PH41947). Root cause was the use of index-based partitioning – still! Do IBM test all these old things on all levels of code? Nope. Time to get my Migration HealthCheck freeware and migrate away from index-based partitioning as fast as you can!

A Star was Born

Well ok, not really a star but the PI started life a little bit dazed and confused, and the formula for calculating “how big can my PI can get before it goes *boom*” is also impressive! (Check out my Know Your Limits presentation for the formulae!)

And Then Came the NPI

Now this PI was naturally unique, but we all needed a way to check for other data and possibly non-unique column combinations. So the non-partitioned index (NPI) was born. This is an index over the *complete* partition range and was normally heavily used by application SQL.

Pain points began

First major problem was the REORG of a single partition. It did the Tablepart (TP) data and it did the Index Part (IP), but it also had to update all the pointers within the NPI. This used to be terribly slow. At one shop, when reorging 22 partitions of a 254 partition TS it took 26 hours at the TP level and only 18 hours at the complete TS level. This is when we added to our RealTime DBAExpert utility generation software the ability to “roll up” from a TP reorg to a TS reorg based on number of partitions being REORGed, percentage of partitions being REORGed, or whether or not at least one NPI existed.

A Piece of Cake?

The other major problem with NPIs is that they can have a PIECESIZE, which is how large a Linear Page Set (LPS) can get before a new one is created. You can have from 1 to 4096 pieces for a single NPI, and the calculation to work out how many pieces you are allowed to have is another brain-numbingly complex one – which is also in my limits presentation.

Some sample SQLs for you:

-- FIRST YOU MUST CALCULATE THE MAXIMUM NUMBER OF POSSIBLE PARTITIONS
SELECT INTEGER(MIN(4096 , POWER(2E00 , 32)
/ (
(64E00 * 1024E00 * 1024E00 * 1024E00) -- DSSIZE TS
/ (32 * 1024) -- TS PGSIZE
)
)) AS MAX_NBR_PARTS
FROM SYSIBM.SYSDUMMY1 ;
-- THEN YOU PLUG THAT NUMBER INTO HERE
SELECT MIN(
(64E00 * 1024E00 * 1024E00 * 1024E00) -- DSSIZE TS
, (POWER(2E00 , 32) / 2048 -- NBR PARTS
) * 4096 -- IX PGSIZE
) AS MAX_INDEX_SIZE
FROM SYSIBM.SYSDUMMY1 ;
-- FOR AN NPI YOU JUST NEED THE PIECESIZE AND THE INDEX PAGESIZE
SELECT INTEGER(MIN(4096 , POWER(2E00 , 32)
/ (
(8E00 * 1024E00 * 1024E00 * 1024E00) -- PIECESIZE
/ 4096 -- IX PGSIZE
)
)) AS MAX_PIECES
FROM SYSIBM.SYSDUMMY1 ;

Death by Index

Suffice it to say you can be killed by running out of space in your PI, running out of space in your NPI, or even running out of NPI LPS pieces…

A New Type of Index

A brand new index type, data-partitioned secondary index, DPSI was created in Db2 V8 so that you could access partitions without having to scan thousands of datasets. Naturally, DPSIs only work if the application SQL is cognizant of their definition and index. Failure to do so can give terrible performance. These are naturally also bound to their respective partitions. It was also at this time that NPIs got rebranded to be NPSIs…

Separation for Utilities is Good

The major utility benefit that partitioned TSs give, is the ability to run them in parallel, including RECOVER. It was a really great idea so that you would only REORG, COPY or RUNSTATS the partitions that required it, and all the partition level utilities could run in parallel.

Db2 9 was All Change

To make these REORGS faster, IBM changed the way REORG worked with partitioned objects in Db2 9. They did speed it up but it meant that you could no longer work in parallel! You could add ranges to the PARTs being reorged but still not in parallel. This caused much grief and gnashing of teeth amongst loads of customers. Thus, we developed the so-called NPI Job. Anything that has a dependency on an NPI could be squirreled away in the NPI job(s) to then be run sequentially by the job scheduler after all other jobs were finished. Nasty!

Faster, Better, Cheaper

Now, after over 35 years, the b-tree is showing its age. IBM came up with the FTB or FIT which, simply put, copies all the non-leaf pages into a private area of the bufferpool and then, using L2 cache-aware code, removes the need for I/Os, apart from the very last two, leaf and data page, and thus reduces I/O and CPU.

Limits Again

Of course this is not for *every* index. Only unique 64 bytes long or less with no IOE, TIMESTAMP with TIMEZONE, or versioning allowed. But hey, it was a start!

Duplicate Allowed

Now even duplicate indexes are there. Well, to start with, only 56 bytes or less but we are getting there! You will require PH30978 UI75643 and then set the new ZPARM FTB_NON_UNIQUE_INDEX to be YES.

Is an NPI Always an NPI?

Now to the core of the matter. As you should all be aware we can, talking about base tablespaces, only create Universal Table Spaces (UTS) in the three flavors we know and love – Partitioned by Growth, Partitioned by Range, and Partitioned by Range Relative Page Numbering.

And???

Well, imagine you have a TS with MAXPARTITIONS = 1 – by definition it cannot go to multiple partitions but all indexes created on it are also by definition NPIs. This is also sort of true for MAXPARTITIONS > 1 if the current number of defined and existing partitions (NUMPARTS) is equal to one.

“Fake” NPIs

These are therefore, in my humble opinion, fake NPIs – You could argue that the NUMPARTS = 1 is “risky”, as Db2 could add a new partition by the next INSERT/UPDATE, but I think the risk is low. For these cases I think the jobs should not go to special NPI handling.

What are your Db2 maintenance jobs doing with these fake NPIs?

I would be very interested to hear from you all about your thoughts and plans here!

TTFN

Roy Boxwell

2021-10 Creating Clones

This month I’m reviewing CLONE Table usage. It is one of several, what I call “esoteric”, Db2 abilities/functions that I will be running through over the coming months, plus some blogs that are either badly misunderstood, not used by anyone, or just very odd.

Attack of the Clones

Clones arrived in a blaze of glory way back in DB2 9 (remember that capital B?) and then promptly disappeared. I have had nothing to do with them – ever – and I only received one question about their usage. Until now…

What Changed?

Well, what happened, is that I was asked if our RealTime DBAExpert utility generating software worked with CLONE tables, and I had to do some quick checking in my head about *what* these things were!

How Do They Work?

So what is a CLONE Table? It is basically a duplicate table that lives in the “same” tablespace but with a different INSTANCE. This is the first place where people make mistakes. You read a lot about renaming the VSAM LDS. That *never* happens with CLONEs. The “trick” that IBM uses is the INSTANCE, but I am getting ahead of my self here!

In the Beginning…

Create a Database, Tablespace and a Table with a couple of indexes:

CREATE DATABASE "TESTDB"
BUFFERPOOL BP0
INDEXBP BP0
STOGROUP SYSDEFLT
;
COMMIT ;
CREATE TABLESPACE "TESTTS" IN "TESTDB"
USING STOGROUP SYSDEFLT
PRIQTY -1
SECQTY -1
ERASE NO
FREEPAGE 5
PCTFREE 10
GBPCACHE CHANGED
TRACKMOD YES
LOG YES
DEFINE YES
DSSIZE 1 G
MAXPARTITIONS 1
BUFFERPOOL BP0
LOCKSIZE ANY
LOCKMAX SYSTEM
CLOSE YES
COMPRESS NO
MAXROWS 255
SEGSIZE 32
;
COMMIT ;
CREATE TABLE BOXWELL.TEST_BASE
(COL1 CHAR(12) NOT NULL
,COL2 INTEGER NOT NULL
,COL3 INTEGER NOT NULL)
IN TESTDB.TESTTS
;
COMMIT ;
CREATE UNIQUE INDEX BOXWELL.TEST_BASE_IX1 ON BOXWELL.TEST_BASE
(COL1, COL2, COL3)
USING STOGROUP SYSDEFLT
PRIQTY -1
SECQTY -1
ERASE NO
FREEPAGE 5
PCTFREE 10
GBPCACHE CHANGED
BUFFERPOOL BP0
CLOSE YES
COPY NO
;
COMMIT ;
CREATE INDEX BOXWELL.TEST_BASE_IX2 ON BOXWELL.TEST_BASE
(COL2, COL3)
USING STOGROUP SYSDEFLT
PRIQTY -1
SECQTY -1
ERASE NO
FREEPAGE 5
PCTFREE 10
GBPCACHE CHANGED
BUFFERPOOL BP0
CLOSE YES
COPY NO
;
COMMIT ;

Insert some data:

INSERT INTO BOXWELL.TEST_BASE VALUES ('A', 1 , 2);
INSERT INTO BOXWELL.TEST_BASE VALUES ('B', 1 , 2);
INSERT INTO BOXWELL.TEST_BASE VALUES ('C', 1 , 2);
INSERT INTO BOXWELL.TEST_BASE VALUES ('D', 1 , 2);
INSERT INTO BOXWELL.TEST_BASE VALUES ('E', 1 , 2);
INSERT INTO BOXWELL.TEST_BASE VALUES ('F', 2 , 2);
INSERT INTO BOXWELL.TEST_BASE VALUES ('G', 2 , 2);
INSERT INTO BOXWELL.TEST_BASE VALUES ('H', 2 , 3);
INSERT INTO BOXWELL.TEST_BASE VALUES ('I', 2 , 3);
INSERT INTO BOXWELL.TEST_BASE VALUES ('J', 2 , 3);
COMMIT ;

What Says RTS?

First, make sure the real-time statistics (RTS) have all been externalized:

-ACCESS DATABASE(TESTDB) SPACENAM(*) MODE(STATS)

Then run a little SQL:

SELECT *
FROM SYSIBM.SYSTABLESPACESTATS
WHERE DBNAME = 'TESTDB'
;
SELECT *
FROM SYSIBM.SYSINDEXSPACESTATS
WHERE DBNAME = 'TESTDB'
;

You should see one row from SYSTABLESPACESTATS with 10 TOTALROWS and 10 REORGINSERTS etc. and two rows from SYSINDEXSPACESTATS with 10 TOTALENTRIES and 10 REORGINSERTS etc. Now we have what I call the “base” table.

Use ISPF as well…

In ISPF 3.4 you should see datasets like this:

DB2DC1.DSNDBD.TESTDB.TESTRBAS.I0001.A001
DB2DC1.DSNDBD.TESTDB.TESTTS.I0001.A001
DB2DC1.DSNDBD.TESTDB.TEST1BZC.I0001.A001

The Fun Begins …

Now we create a CLONE. To do this, you do *not* CREATE – that would be way too easy – a CLONE Table. You actually issue an ALTER statement like this:

ALTER TABLE BOXWELL.TEST_BASE
  ADD CLONE RINGO.AARDVARK
;
COMMIT ;

Now do that RTS select and the ISPF 3.4 again … As if by magic you will now see double the rows in the RTS … Check out the INSTANCE column:

------+---------+---------+
PSID  PARTITION  INSTANCE
------+---------+---------+
   2          1         1
   2          1         2

Aha! We now have two sets of RTS Counters – This is a good thing! ISPF also looks different:

DB2DC1.DSNDBD.TESTDB.TESTRBAS.I0001.A001
DB2DC1.DSNDBD.TESTDB.TESTRBAS.I0002.A001
DB2DC1.DSNDBD.TESTDB.TESTTS.I0001.A001
DB2DC1.DSNDBD.TESTDB.TESTTS.I0002.A001
DB2DC1.DSNDBD.TESTDB.TEST1BZC.I0001.A001
DB2DC1.DSNDBD.TESTDB.TEST1BZC.I0002.A001

Notice all the INSTANCE values here?

Finally the Boss Guy – SYSTABLESPACE. Here is where the access is controlled using, yet again, INSTANCE and its good friend CLONE:

SELECT *
FROM SYSIBM.SYSTABLESPACE
WHERE DBNAME = 'TESTDB'
;
--+---------+--
INSTANCE CLONE
--+---------+--
       1 Y

This is showing you all the information you need. The current base table is still the original table and this tablespace is in a “clone relationship” – slightly better than “it’s complicated” but close!

Test Select

Run this to see what you get back:

SELECT COUNT(*) FROM BOXWELL.TEST_BASE ; 
SELECT COUNT(*) FROM RINGO.AARDVARK    ;

You should get ten from the first count and zero from the second.

So What Is the Point?

Now we, finally, get to the raison d’être of CLONEs. The idea is that using table name ringo.aardvark you can INSERT data, perhaps very slowly over a period of days, into the CLONE TABLE and the application is not aware of and cannot be affected by it. Once the INSERT processing is completed you may then do the EXCHANGE DATA command to instantaneously swap the tables around. OK, it must actually just do a one byte update of the INSTANCE column in the SYSTABLESPACE, but I digress…

Here’s How it Looks

EXCHANGE DATA BETWEEN TABLE BOXWELL.TEST_BASE
                        AND RINGO.AARDVARK
;
COMMIT ;

Now do those COUNT(*) SQLs again:

SELECT COUNT(*) FROM BOXWELL.TEST_BASE
---------+---------+---------+--------
0
DSNE610I NUMBER OF ROWS DISPLAYED IS 1
SELECT COUNT(*) FROM RINGO.AARDVARK
---------+---------+---------+--------
10
DSNE610I NUMBER OF ROWS DISPLAYED IS 1

Whoopee! You can see that the table name has not changed but all the data has! This is pretty cool!

Downsides …

Never a day without pain, my mother used to say, and CLONEs come with a bunch of pain points!

Pain Point Number One

Reduced utility support. You can only run MODIFY RECOVERY, COPY, REORG (without inline statistics!) and QUIESCE against these. Why? Because there is only one set of catalog statistics for them. A RUNSTATS would destroy all of the data for *both* objects and the current object access paths might all go south; further, you absolutely *must* add the keyword CLONE to the utility control cards. You *cannot* rely on LISTDEF to do this for you and it is documented:

This utility processes clone data only if the CLONE keyword is specified. The use of CLONED YES on the LISTDEF statement is not sufficient.

but people still miss this and then *think* they are working with their clones but they are not! This can get very embarrassing…

Pain Point Number Two

You must remember to RUNSTATS the “new” base after the EXCHANGE has been done. The RTS is always in step, the Catalog is probably way out of line…

When You Are Quite Finished

Once a CLONE table is no longer required you can easily drop it but naturally not with a DROP but with another ALTER statement:

ALTER TABLE BOXWELL.TEST_BASE
      DROP CLONE
;
COMMIT ;

Pain Point Number Three

The problem here is not that bad, but, depending on when you do the DROP CLONE, your “base” could be the instance two! You, and your vendors, must make sure your Db2 and non-Db2 utilities are happy with this state of affairs!

RealTime DBAExpert?

Yep, we are in the clear! Our software does indeed support these esoteric beasts.

Over To You!

Do you use CLONEs? If so, why? Any war stories or is everything hunky dory?

As usual I would love to hear from you!

TTFN,

Roy Boxwell

Updates

I got quite a few updates about clones:

DDL Disaster

One of the major issues that I missed was another pain point: DDL Changes. These are really nasty as you must throw away your clone before you can do the ALTER and then recreate the CLONE relationship.

Commands

I also did not mention that various commands also need the CLONE keyword to be applied to CLONE spaces. For example -START DATABASE(xxx) SPCENAM(yyy) CLONE

2021-09 Fast Index Traversal – Update

This month, I wish to review the changes we have seen for FIT or FTB (Fast Traversal Block) over the release of Db2 12 – including changes to the calculation, APARs of interest and updates to the basic functionality.

Way back when…

I wrote a blog back in 2019-08 all about FTB, where I mentioned that INCLUDE could kill you. Just include a one-byte column and you are over the limit of 64 bytes and your FIT-eligible index is no longer eligible…

All changed in FL508

In Db2 12 FL508, the docu got a significant update here: “Columns in the INCLUDE list do not count toward the size limit” – YooHoo! Clarity! Sadly, that also means that the INCLUDED columns are naturally *not* used for FIT Access… You win some, You lose some.

What are eligible indexes?

The rules for this have not changed, (apart from the INCLUDE bit): 64 bytes or less, no versioning, no timezone, maximum of 10,000 FTBs per subsystem and not more than 2,000,000 leaf pages. What causes an index to be made FTB or not FTB can be gleaned from the list below. The FTB Daemon re-evaluates “traverse count” every 2 minutes, adjusts priority queue, internal threshold applied to priority queue:

  • Any random index traversal, index only or index plus data (+1)
  • Index lookaside (-1)
  • Sequential access (-1)
  • Index leaf page splits (/2)

So you can see that index splits really kill FTB candidates…

Do these look the same to you?

The massive change in FL508 was of course the introduction of duplicate index support! This was a fantastic improvement of course but, as always, someone has to pay the ferryman… In this case, duplicate index lengths can only be a maximum of 56 bytes.

Setting a good example?

The IBM supplied example queries cannot be correct… the calculation for index key length is missing a bunch of column types, and for duplicate indexes it has also got incorrect values.

Working Examples

Here are my two queries, updated for FL508, to find all of your FTB/FIT eligible indexes:

WITH INPUT (NLEVELS, LENGTH, TABLE_NAME, INDEX_NAME) AS
(SELECT B.NLEVELS
      , SUM(CASE D.COLTYPE
            WHEN 'DECIMAL'  THEN
                            SMALLINT(CEILING((D.LENGTH + 1 ) / 2 ))
            WHEN 'GRAPHIC'  THEN D.LENGTH * 2
            WHEN 'VARG'     THEN D.LENGTH * 2
            WHEN 'LONGVARG' THEN D.LENGTH * 2
            ELSE D.LENGTH
            END)
      + SUM(CASE B.PADDED
            WHEN 'Y' THEN 0
            ELSE
                CASE D.COLTYPE
                WHEN 'VARG'     THEN 2
                WHEN 'LONGVARG' THEN 2
                WHEN 'VARCHAR'  THEN 2
                WHEN 'LONGVAR'  THEN 2
                WHEN 'VARBIN'   THEN 2
                WHEN 'DECFLOAT' THEN 2
                ELSE 0
                END
            END)
      + SUM(CASE D.NULLS
            WHEN 'Y' THEN 1
            ELSE 0
            END) AS LENGTH
      , STRIP(D.TBCREATOR) CONCAT '.' CONCAT STRIP(D.TBNAME)
      , STRIP(B.CREATOR)   CONCAT '.' CONCAT STRIP(B.NAME)
 FROM SYSIBM.SYSINDEXES B
     ,SYSIBM.SYSKEYS    C
     ,SYSIBM.SYSCOLUMNS D
WHERE B.UNIQUERULE NOT IN ('D','N')         -- NOT DUPLICATE
  AND D.COLTYPE        <> 'TIMESTZ'         -- NOT TIMEZONE
  AND B.DBID            > 6                 -- NOT DIR/CAT
  AND B.OLDEST_VERSION  = B.CURRENT_VERSION -- NOT VERSIONED
  AND C.ORDERING       <> ' '               -- NO INCLUDE/IOE
  AND B.TBNAME          = D.TBNAME
  AND B.TBCREATOR       = D.TBCREATOR
  AND B.NAME            = C.IXNAME
  AND B.CREATOR         = C.IXCREATOR
  AND C.COLNAME         = D.NAME
GROUP BY D.TBCREATOR, D.TBNAME, B.CREATOR, B.NAME, B.NLEVELS)
SELECT NLEVELS, LENGTH , INDEX_NAME
FROM INPUT
WHERE LENGTH <= 64
-- ORDER BY NLEVELS DESC, LENGTH DESC -- IF STATISTICS ARE GOOD
ORDER BY LENGTH DESC, INDEX_NAME
FOR FETCH ONLY
WITH UR
;

And now for the duplicate ones:

WITH INPUT (NLEVELS, LENGTH, TABLE_NAME, INDEX_NAME) AS
(SELECT B.NLEVELS
      , SUM(CASE D.COLTYPE
            WHEN 'DECIMAL'  THEN
                             SMALLINT(CEILING((D.LENGTH + 1 ) / 2 ))
            WHEN 'GRAPHIC'  THEN D.LENGTH * 2
            WHEN 'VARG'     THEN D.LENGTH * 2
            WHEN 'LONGVARG' THEN D.LENGTH * 2
            ELSE D.LENGTH
            END)
      + SUM(CASE B.PADDED
            WHEN 'Y' THEN 0
            ELSE
                CASE D.COLTYPE
                WHEN 'VARG'     THEN 2
                WHEN 'LONGVARG' THEN 2
                WHEN 'VARCHAR'  THEN 2
                WHEN 'LONGVAR'  THEN 2
                WHEN 'VARBIN'   THEN 2
                WHEN 'DECFLOAT' THEN 2
                ELSE 0
                END
            END)
      + SUM(CASE D.NULLS
            WHEN 'Y' THEN 1
            ELSE 0
            END) AS LENGTH
      , STRIP(D.TBCREATOR) CONCAT '.' CONCAT STRIP(D.TBNAME)
      , STRIP(B.CREATOR)   CONCAT '.' CONCAT STRIP(B.NAME)
FROM SYSIBM.SYSINDEXES B
    ,SYSIBM.SYSKEYS    C
    ,SYSIBM.SYSCOLUMNS D
WHERE B.UNIQUERULE     IN ('D','N')         -- DUPLICATE
  AND D.COLTYPE        <> 'TIMESTZ'         -- NOT TIMEZONE
  AND B.DBID            > 6                 -- NOT DIR/CAT
  AND B.OLDEST_VERSION  = B.CURRENT_VERSION -- NOT VERSIONED
  AND C.ORDERING       <> ' '               -- NO INCLUDE/IOE
  AND B.TBNAME          = D.TBNAME
  AND B.TBCREATOR       = D.TBCREATOR
  AND B.NAME            = C.IXNAME
  AND B.CREATOR         = C.IXCREATOR
  AND C.COLNAME         = D.NAME
GROUP BY D.TBCREATOR, D.TBNAME, B.CREATOR, B.NAME, B.NLEVELS)
SELECT NLEVELS, LENGTH, INDEX_NAME
FROM INPUT
WHERE LENGTH <= 56
-- ORDER BY NLEVELS DESC, NLENGTH DESC -- IF STATISTICS ARE GOOD
ORDER BY LENGTH DESC, INDEX_NAME
FOR FETCH ONLY                  
WITH UR                         
;                               

APARs of Interest

Now FTB/FIT did have, shall we say, a difficult birth. Some people just said “Switch it off for now,” and there are quite a few APARs out there for it… Here’s a list of APARs, all closed or opened within the last year.

APAR    Closed     PTF     Description
PH28182 2020-09-25 UI71784 INDEX LOOK ASIDE SUPPORT WHEN INDEX FAST TRAVERSE BLOCK(FTB) IS IN USE
PH29102 2020-10-27 UI72276 ABEND04E DSNKTRAV ERQUAL505B RC00C90101 FTB TRAVERSAL
PH29336 2020-09-22 UI71351 IRLM CORRECT RESULTANT HELD STATE FOR FTB PLOCKS WHEN PLOCK EXIT WOULD HAVE EXITTED WITH ERROR.
PH29676 2020-10-16 UI72118 ABEND04E RC00C90101 AT DSNKTRAV 5058 DURING INSERT VIA FTB
PH30978 2021-06-01 UI75643 SUBSYSTEM PARAMETER TO ENABLE INDEX IN-MEMORY OPTIMIZATION (FTB) FOR NON-UNIQUE INDEXES
PH34468 2021-04-20 UI75007 ABEND04E RC00C90101 AT DSNKTRAV ERQUAL5021 VIA FTB TRAVERSAL
PH34859 2021-05-05 UI75254 DB2 12 FOR Z/OS NEW FUNCTION FOR FTB (FAST TRAVERSE BLOCKS)
PH35596 2021-04-07 UI74814 INSERT SPLITTING PAGE INTO FTB LEAF NODE GOT DSNKFTIN:5002 ABEND BECAUSE OLD PAGE THAT CAUSE THE PAGE SPLIT WAT MISSING IN FTB.
PH36406 2021-05-07 UI75288 INSERT KEY INTO FTB PROCESS DETECTING INCONSISTENT STRUCTURE MODIFICATION NUMBER THEN GOT DSNKFTIN:5043 ABEND
PH36434 2021-05-13 UI75392 DB2 12 FOR Z/OS INTERNAL SERVICEABILITY UPDATE (Improve Create / Free FTB log recs)
PH36531 2021-05-13 UI75391 ABEND04E RC00C90101 AT DSNKINSN ERQUAL5009 AND DSNKFTIN ERQUAL5066 FOR FTB INSERT PLOCK FAILURE
PH36978 OPEN               FTB MESSAGE MSGDSNT351I ISSUED INCORRECTLY
PH38212 2021-07-07 UI76239 ABEND04E RC00C90101 AT DSNKFTBU ERQUAL5061 AND DSNK1CNE ERQUAL5006 DURING FTB CREATION
PH39105 OPEN               DB2 12 FTB INDEXTRAVERSECOUNT = 4294967295 FOR OBJECTS NOT ENABLED FOR FTB

FTB Monitor possibilities

Use of the -DISPLAY STATS command can show you what is going on in your system. Command format -DISPLAY STATS(INDEXMEMORYUSAGE), or the slightly easier to type -DISPLAY STATS(IMU) shows you the current indexes in the FTB and the memory allocated in message DSNT783I. The, newly delivered in APAR PH34859 PTF UI75254, variant -DISPLAY STATS(INDEXTRAVERSECOUNT) or the alias -DISPLAY STATS(ITC) gives you a list of traverse counts, in descending order, in message DSNT830I. You may filter this by using DBNAME, SPACENAM, or PART as in other commands.

IFICIDs as well?

Two new IFCIDS were created for FTBs:

  • IFCID 389 is part of statistics trace class eight. It records all indexes that use fast index traversal in the system.
  • IFCID 477 is part of performance trace class four and records the allocation and deallocation activities of FTBs for fast index traversal.

IFCID 2 (statistics record) got several new fields:

  • Current threshold for FTB creation.
  • Number of FTB candidates.
  • Total size allocated for all FTBs.
  • Number of FTBs currently allocated.
  • Number of objects that meet the criteria for FTB creation.

FTB Limit possibilities

The big wrench is the ZPARM INDEX_MEMORY_CONTROL field with values AUTO, DISABLE, or nnnnnnn. Where AUTO sets the upper limit at 20% of allocated buffer space or 10MB (whichever is larger,) DISABLE turns off FTB completely and nnnnnn is the maximum number of megabytes to allocate for FTB storage in the range from 10 to 200,000. New in FL508 is the ZPARM FTB_NON_UNIQUE_INDEX with its default of NO to control whether or not duplicate indexes can be FTBed or not.

By the Index?

You can even micro-manage the FTBs, down to the individual index, by using catalog table SYSIBM.SYSINDEXCONTROL but I would not recommend doing that at all – that way lies madness.

Any plans for tonight?

Do any of you want to start using FTBs or indeed are you already using them? I would love to hear your positive, or negative, stories!

TTFN,

Roy Boxwell

Updates

Here I wish to keep any “new information and feedback” that could be of interest.

First up is a positive update from one of my readers who said

“We had disabled FTB at IBM’s recommendation. About two months ago we turned it back on after I got information from John Campbell saying it was safe to turn it on. Since turning FTB back on we have had a very noticeable reduction in cpu usage. We are still at FL500 and have the zparm set to AUTO.”

Another reader is less impressed:

“Due to the number and frequency of PTFs for Fast Index Traversal our Db2 system DBAs have no plans to activate until it is more stable.  Just yesterday there was an All-Hands call where the upper echelon reiterated that the #1/2 priorities are Stable and Secure.   FIT is failing Stable.   Not a good showing by IBM on this significant performance feature.”

APAR List

  • PH40269 2021-09-16 UI77189 ABEND04E RC00E72068 AT DSNXSRME OFFSET01024 DUE TO A TIMING WINDOW WHEN USING INDEX FAST TRAVERSE BLOCK (FTB)
  • PH40273 2021-11-09 UI78000 IMPROVE PERFORMANCE OF FTB STORAGE POOL ADMF INDEX MANAGER CL20
  • PH40539 2021-10-07 UI77500 FTB DEADLOCK OCCURS WITH SYSTEM ITASK – CORRID=014.IFTOMK01

2021-08 ICI – Db2 12 Update

This month, I would like to review the ICIs that we have had for a few releases plus those that have recently appeared, and then the trouble with twelve …

It began …

Db2 development realised that something had gone horribly wrong when a bunch of Db2 users suddenly found that the output from their queries was no longer what it should be … After a bit of digging, the CHAR format rewrite was found to be the root cause and the fix was hastily created – BIF_COMPATIBILITY ZPARM.

What’s in a name?

Well, then VARCHAR happened and along came a very unpleasent problem with JAVA timestamps and, as I have documented in earlier BLOGs, it all started getting silly with one ZPARM being used for multiple format problems.

Along came the ICI (Incompatible Change Indicator)

So in Db2 10 we got a new IFCID, the 366, which was spat out at *every* prepare (bind) of any SQL that, possibly, contained an ICI. Now we started off pretty small with just three ICI’s: the first two being the reformatted output of CHAR and VARCHAR and the third being the TIMESTAMP format problem.

Db2 10 updates for Db2 11

Here they brought out numbers four to nine to handle all the little changes in Db2 11 so that you got the alert in Db2 10 before it bit you in Db2 11 – all well and good.

Db2 11 updates

The big change, was the brand new IFCID 376 – which is the evil twin of the 366. The only difference being that Db2 cached the entries, so you basically got a rolled up 366 – apart from one tiny little detail. The Execution count was missing. For the 366 it is 1:1, but for the 376 it is 1:nnnnn which could be any positive integer. They then added the 11nn range, going all the way up to 1111, and then they brought in 1112 for empty XML tags. Now all of these have been discussed in my earlier blogs.

What’s new in the ICI World?

Db2 12 of course! They brought out 1201 very early on due to POWER causing a problem. The output on overflow changed from a negative to a positive SQLCODE, which can of course cause “problems”… Why did this change even happen? IBM rewrote the code from using LE 32 bit assembler math calls to using C, and so the function “knew” if it overflowed and could return a warning saying so, whereas the 31 bit assembler just died a death and you got a negative SQLCODE.

Naming Convention?

Then it went quiet for a while until something weird happened: 1215031 and 1215032 appeared. Now, at first, I liked the idea of putting the FL into the ICI, but then I realised it was actually pretty pointless and just made it more confusing !

1215031 is issued when you could qualify a row with NULL in the DATA CHANGE OPERATION column using the FOR SYSTEM_TIME FROM/BETWEEN predicate on a system period temporal table with AUDITING.

1215032 is issued when you attempt to call stored procedure SYSPROC.SET_MAINT_MODE_RECORD_NO_TEMPORALHISTORY as this is no longer supported for data replication calls.

1204 (note the FL has gone…) is issued when you use CURRENT_SERVER or CURRENT_TIMEZONE as a column or variable name.

In the Docu it says 1202, but you actually get a 1215031 – and there’s no mention of 1215032 and 1204!

More new ones

Meanwhile, not (nothing?) to do with Db2 12, IBM also brought out

11 for using SELECT INTO syntax with a UNION

[12 was thankfully skipped!]

13 for INSERT/UPDATE/DELETE using an attribute WITH UR

Both of these were more parsing bugs than anything evil, but both require code changes if they appear!

Utility Time

Now IBM have enhanced the LOAD utility with LOAD FORMAT DELIMITED for correct packed numeric data support when one or more virtual decimal digits exist. This has caused another problem, as the data loaded could be viewed (as it was by one of our customers), as inconsistent. IBM then created another APAR to roll back the change and introduce a new ZPARM LOAD_DEL_IMPLICIT_SCALE to control how these numbers should be loaded. Default is NO, like it used to be, with an implied decimal point at the far right of the data. At the same time, it will now alert users that they could have an incompatibility with the LOAD by changing IFCID 25 to set a new bit. This warns you that you have done a LOAD into a table where there is packed decimal data with one or more digits after the virtual decimal point. If YES, then load interprets the Scale setting in the LOAD statement. For details please see APARs PH28104 and PH36908.

Pain Point for you?

The above mentioned new notification is a bit strange (pardon the pun), as there is already the IFCID 376 for incompatibilities. Now you must also start the IFCID 25 and go checking bits. So my question to you all is: Have you got this problem and, if so, do you think it is worth it to integrate IFCID 25 bit checking into SOFTWARE ENGINEERINGs/SEGUSs current ICI/BIF Use Case in our WorkLoad Expert and/or our BIF/ICI Freeware software?

The future is bright

As far as ICIs are concerned they just keep on rolling!

As always if you have any comments, especially with regard to IFCID 25, please feel free to e-mail!

TTFN

Roy Boxwell

2021-07 IDUG NA 2021 Virtual – A Roy round-up

This month I would like to “roll through” the recently held IDUG NA Virtual 2021 – Another virtual conference, but still full of tons of good stuff! Read on dear reader to see what piqued my interest!

Do you like being Profiled?

Profiling is getting much better (not easier to use mind you!) The best bit was “Remove ‘ping’ and validation queries” and the two easiest ways to do this are either to enable Sysplex WLB, or to use the isVALID() JAVA method. The typical SELECT * FROM SYSIBM.SYSDUMMY1 is a real pain …

PBG Sad future…

The idea of PBGs has, from my POV, changed quite dramatically. From the “Set it to 100 MAXPARTITIONS and forget it” to “Set MAXPARTITIONS to 1 and DSSIZE to 64GB”. I still think this is more of a design flaw than a good idea. If the spacemap search is such a performance killer – fix it …

Performance tidbits

Just using the “new” feature PCTFREE xx FOR UPDATE yy or the PCTFREE_UPD Zparm is a bit of a no-brainer if you have variable length or compressed records and lots of updates that then cause an Overflow record, (better called an indirect reference) – these bloat your data and cause more getpages over time. A sort of creeping death! The only cure is periodic REORGs to remove all of these indirect references, but it is better to delay this as long as possible – hence the FOR UPDATE percentage is a very nice feature!

From Db2 11, you could make your DGTTs NOT LOGGED which can give dramatic performance boosts! Naturally, it is a code change in the create DDL. You must add NOT LOGGED to the back end of the DECLARE statement but there have been really amazing savings like 60% elapsed time reduction and greater than 20% cpu savings! Of course, your mileage may vary. Always test these things before blindly changing all of your DECLARE statements!

zHyperWrite: If you are running with DS8000 and Metro Mirror (PPRC) then this really is a great idea! Update the IECIOSxx HYPERWRITE=YES, issue the SETIO HYPERWRITE=YES z/OS command and set ZPARM REMOTE_COPY_SW_ACCEL to ENABLE to get large reductions in active log writing times! Note that HyperSwap must be enabled as well.

zHyperLink (Baby brother of zHyperWrite) is also fantastic if you have intensive reads (especially sync reads) and lots of active log writing. (Who does *not* have this combo???) Set the zHyperLink to ENABLE for both read and write, DATABASE for read only or ACTIVELOG for write only. Downside is, you may see cpu increase slightly but the payback of elapsed reduction (over 20%) is well worth it.

Too many statistics can be bad for you

Also heard a few times was the statement “Never collect STATISTICS as part of REORG INDEX” – I have firmly believed in this for years and am very happy that it is now the modern mantra!

Run RUNSTATS rarely, if at all! How often does the statistical data actually change? During a REORG – Always! But “on its own” ? Why? Creation/Change of index – Naturally. Mass delete and LOAD with brand new data – Clearly! Insert another million rows? Really needed?? Go logarithmic like our RTDX RunstatsAvoidance feature.

Utility updates

High speed REORG: SORTDATA NO RECLUSTER NO – Just for actioning DDL as fast as possible. Downside is the CLUSTERRATIOF might be killed and, even worse in my humble opinion, all of the RTS counters are set to zero! Of course, if you are clever, you could simply back up the relevant RTS rows before the REORG and replace them afterwards…

REORG got a parallelism boost when building PIs (PH28092 & PH33859), naturally only when no NPIs exist. If running on the z15, think about setting ZPARM UTILS_USE_ZSORT (PH28183) to improve performance.

LOAD got a bunch of improvements (PI96136, PH11255, PH19073, PH23105) and the capability to override row change timestamps with PH25572. It also got the ability to skip the MAXASSIGNEDVALUE update in Db2 12 with PH28476.

RUNSTATS got the new STATCLGSRT ZPARM (PI74408 and PH03678) which then added the STATCLGMEMSRT parameter. RUNSTATS also got an update to avoid “overdoing things”:

  • If BOTH or HISTOGRAM then the COUNT is limited to be 100
  • If sampling value is less than 50% and FREQVAL COUNT BOTH or LEAST is specified then change it to be MOST

These avoid flooding the SYSCOLDIST with basically useless data that just slows down PREPARE and BIND/REBIND as well as possibly causing the optimizer to make a bad judgment.

TEMPLATE support also finally got Large Block Interface! New BLKSZLIM parameter in PH30093.

Compressed?

Generally speaking, random table access will show better performance than sequential table access for compressed data, as the row is only decompressed if required.

Generally speaking, sequential index processing (index scans) works much better than random index access for compressed indexes. The opposite of tables! Why? The entire index page must be read in and decompressed in one call for indexes.

So, once again, know your data, know your access patterns!

New Compressed?

Huffman Object Compression: Came along in FL509 and is well worth a look, as it can give very good results but, as always, test and check before blindly setting all compression to Huffman!

Index room for growth?

On index design the Holy Grail is to set the PCTFREE equal to the number of new index entries between reorg runs divided by the totalentries. FREEPAGE should be used to avoid index page splits. It may also help to increase the index page size and go to index compression.

Last mix

DataSharing OA59122 which went PE and the OA60394 that fixed it, both correct a problem of “contention management” that can be CPU expensive when contention has gone – but what happens next?

Machine Learning – Unsupervised (sounds like me most of the time) … especially “data munging” …

And finally…

A whole ton of really interesting stuff all about deprecated items… oh wait a second, that was my presentation! Anyway, good for a read and learn all about the nasty things lurking in the catalog! Do not forget we have freeware that shows it all to you! MigrationHealthCheck (MHC) is the name!

As always it is a pleasure to hear any comments or criticisms!

TTFN,

Roy Boxwell