Category: News from the labs

This month, I want to delve into one of those newish, but well-hidden, features of Db2 that arrived some years ago but did not get that much publicity!

Compress Yes!

We all love compression! It saves tons of disk space and, over the years, we even got Index Compression. Yes, I know it is *not* really compression at all – just the leading bytes – but it is better than nothing! We also got better, newer, Table compression algorithms (Looking at you Mr. Huffman!) and we always had the problems of un-compressing data if “we” needed to look at it off-line.

Dictionaries Anymore?

Db2, at least for Tables, uses dictionaries of varying sizes to save “common strings” mapped to small numbers. These dictionaries are pretty obviously only valid for one set of data from one table and/or partition. If you do a REORG after inserting a ton of new data then the new dictionary can be very different from the prior one. Db2 writes the *previous* dictionary to the log and we carry on fine …

Or Do We?

Well, what happens when you wish to read data from the log that was inserted *before* your current dictionary was created? This is, and will always be, a small technical problem! The programs out there now simply crawl back through the Db2 log looking for the compression dictionary that matches to your timescales. This obviously takes time, I/O and CPU!

Online?

Even if the dictionary you are looking for is the current one, accessing it might well cause DBD Locks to happen when it has to be opened, and it can even cause Db2 GBP dependency problems. The fun continues if the logs you need have been archived to tape, of course.

DCC

Data Capture Changes are the keywords here. DCC for the standard TLA. As you are probably aware, setting this at the table level enables Db2 to support data replication using the log. IBM Db2 call their version DataPropagator. The DDL syntax in CREATE/ALTER TABLE is DATA CAPTURE NONE/CHANGES:

DATA CAPTURE Specifies whether the logging of the following actions on the table includes additional information to support data replication processing:

• SQL data change operations

• Adding columns (using the ADD COLUMN clause)

• Changing columns (using the ALTER COLUMN clause)

NONE Do not record additional information to the log. This is the default.

CHANGES Write additional data about SQL updates to the log.

This Changes Everything!

Indeed, it does! DATA CAPTURE CHANGES (DCC and also CDC sometimes!) causes a full row to be logged for every update. If there was an update of just the very last byte of data in a row then it was a tiny log record of basically just that last byte. With DCC you always get the full row. For INSERT and DELETE you always got the full row anyway. Why the change? So that the replication software has it easy! Simple as that!

Side Effects?

There are a few side effects, of course. Mainly, the log size grows as you are logging more data, and any use of the SQL TRUNCATE will behave like a MASS DELETE (so no IGNORE DELETE TRIGGERS or IMMEDIATE options, for example). There are also a few ZPARMs that must all be reviewed and/or changed. Firstly, the ZPARM UTILS_BLOCK_FOR_CDC with options YES/NO and default NO. If set to NO then no utilities are barred from working on these DCC tables. If set to YES then:

• CHECK DATA with DELETE YES LOG NO
• LOAD with SHRLEVEL NONE or REFERENCE
• RECOVER with TOLOGPOINT, TORBA, TOCOPY, TOLASTCOPY, or TOLASTFULLCOPY
• REORG TABLESPACE with DISCARD
• REPAIR with LOCATE DELETE

Terminated with Extreme Prejudice!

Will all be terminated. Why, you may wonder? Well, all of these can obviously *flood* the log with rows, millions of rows of data. If using data replication then their usage should be “evaluated” before attempting them – hence the ZPARM. Too many customers shot themselves in the foot with a badly timed LOAD, for example, so we got this “protection” ZPARM. Naturally, it is changeable online, so once you have reviewed the requirement for the utility you can still fire it off, if desired, and then, very quickly, switch back!

More?

Yes, there is always more! ZPARM REORG_DROP_PBG_PARTS with options DISABLE/ENABLE and default DISABLE. If set to ENABLE to allow REORG to drop empty PBG partitions after a successful REORG, and the table within is DCC, then this drop of empty partitions will be ignored! Finally, we have the ZPARM RESTRICT_ALT_COL_FOR_DCC with options YES/NO and default NO. It specifies whether to allow ALTER TABLE ALTER COLUMN for DCC tables. If it is set to YES you will get an SQLCODE -148 if ALTERing a DCC object using any of the options SET DATA TYPE, SET DEFAULT or DROP DEFAULT. Again, this is to stop accidental log and data replication flooding!

Compression Woes

So now you can fully see the quandary! If the rows are all compressed on the log you must access the dictionary to un-compress them on the “other side”, where you do the replication, or just locally if you want to see the data. As mentioned, getting our grubby little hands on the dictionary can cause performance issues so what can we do?

CDDS!

This arrived in Db2 11 for data-sharing people out there with a, and I quote, “GDPS® Continuous Availability with zero data loss environment.” It actually stands for Compression Dictionary Data Set and it brought in a couple of new ZPARMs:

• CDDS_MODE with options NONE, SOURCE and PROXY and default NONE.
• CDDS_PREFIX a 1 – 39 Byte z/OS dataset prefix for the VSAM Dataset where the “magic” happens.

VSAM???

Yep you read that right! The CDDS VSAM dataset (CDDS_PREFIX.CDSS) stores up to three versions of the dictionaries that your table/tableparts are using, thus enabling fast, problem-free access to the dictionaries. Naturally, the CDDS must be available to *both* systems – and this is why VSAM was chosen, of course!

Start Me Up!

Some new commands also came in here. -START CDDS instructs all members of a data-sharing group to allocate the CDDS and start using it. -STOP CDDS tells all members to stop using CDDS, close and deallocate it. This must be done before recovering the CDDS, for example.

So???

So now you have a possible way of getting great performance in data replication, but how do you get it all running and how do you review what’s going on? This is where REORG and a new standalone utility DSNJU008 come in.

Reorg Yes But No…

The way to initialize your CDDS is simply to REORG all of your tables/table partitions  with the INITCDDS option. Do not panic! If this option is set to YES it will *not* do a REORG! It will purely externalize the current dictionary and carry on. This means you can use a “generator” SQL statement like this:

SELECT DISTINCT 'REORG TABLESPACE ' CONCAT STRIP(A.DBNAME)
                         CONCAT '.' CONCAT STRIP(A.TSNAME)
                         CONCAT ' INITCDDS YES'
FROM SYSIBM.SYSTABLES A
WHERE A.DATACAPTURE = 'Y'
;

To get all your REORG cards, and then one mass pseudo Reorg later your CDDS is up and running!

Standalone

The DSNJU008 utility can be executed using JCL like this:

//CDDSPRIN EXEC PGM=DSNJU008

//STEPLIB  DD DSN=<Your Db2 exit lib>,DISP=SHR

//         DD DSN=<Your Db2 load lib>,DISP=SHR

//SYSUT1   DD DSN=<Your CDDS prefix>.CDDS,DISP=SHR

//SYSPRINT DD SYSOUT=*

//SYSIN    DD *

LIMIT(9999)

/*

Please refer to the Db2 for z/OS Utilities Guide and Reference for all the details about other keywords, but the interesting factoid is that VERSIONS are stored 1, 2, 3 with 1 being the most recent and the rest going backwards in time.

What Time Was It?

The program outputs everything you could ever wish for about your data sharing systems’ use of compression dictionaries. Including the Dictionary timestamp, which I find very nice as every now and again it really can pay to analyze the data with a new compression test, just to see if you can squeeze any more onto your SSDs!

Why All This Trouble, Again?

The idea behind all of this work is to make using IFCID 306 better, faster and cheaper. It also has a knock-on affect into the “vendor scape”, as lots of vendors offer tooling for data replication or log reading/analysis and they all should now work with this feature enabling you, the DBA, to be more productive at less cost. If their version of REORG/LOAD does *not* handle the CDDS then you must always schedule a new pseudo Reorg with INITCDDS afterwards to handle the VSAM Updates correctly.

Using It?

My question for this month, dear readers, is: Are any of you using this feature or are planning to use it soon?

TTFN

Roy Boxwell

In this, obviously, never ending series of new features, I will roll up all the new ones since my “SOUNDEX and other „cool“ features – Part seven All new for Db2 12” newsletter from 2021-05.

Starting with Db2 12 – PTFs first

APAR PH36071 added support for the SUBSTR_COMPATIBILITY ZPARM parameter with default PREVIOUS and other valid value CURRENT. In Db2 12 FL500 and above, with this APAR applied and the value set to CURRENT, then the SUBSTR Built-in Function (BiF) will return an error message for invalid input.

APAR PH42524 added MULTIPLY_ALT support to the IBM Db2 Analytics Accelerator (IDAA).

APAR PH47187 added support for UNI_90 locale in the LOWER, TRANSLATE and UPPER BiFs.

APAR PH48480 added LISTAGG and RAND to the IDAA offload support. Note: you must enter YES in ENABLE ACCELERATOR SPECIFIC RESULTS field on panel DSNTIPBA to get this boost.

Db2 12 FL 100

Yes, they introduced a new BiF for this level way after I wrote my last newsletter all about BiFs and Functions. The new BiF is the BLOCKING_THREADS table function. This is very handy when DBAs are about to start doing DDL work. Adding or ALTERing a Column or what have you! The output is a table about who is blocking access to the database in question and can really save a massive amount of work if you can check *before* you do all your ALTERs that you can indeed succeed in getting them all done!

Now Db2 13 – PTFs first

APAR PH47187 added support for UNI_90 locale in the LOWER, TRANSLATE and UPPER built-in functions (BiFs).

APAR PH48480 added LISTAGG and RAND to the IBM Db2 Analytics Accelerator (IDAA) offload support. Remember, you must enter YES in ENABLE ACCELERATOR SPECIFIC RESULTS field on panel DSNTIPBA.

APAR PH51892 introduced vector prefetch for SQL Data Insights and improves the BiF AI_SEMANTIC_CLUSTER. You must also go to Db2 13 FL504.

APAR PH55212 enhanced SQL Data Insights and added support of numeric data types to the BiF AI_ANALOGY.

Db2 13 FL500

This was the release that introduced the Db2 SQL Data Insights with the new BiFs AI_ANALOGY, AI_SEMANTIC_CLUSTER and AI_SIMILARITY.

Db2 13 FL504

A new AI BiF: AI_COMMONALITY was released and when you use LISTAGG you can now add an ORDER BY to the full select.

Db2 13 FL505

Another new BiF: INTERPRET which can change nearly any argument to nearly any other data type. The most useful thing you can do with it is something like:

INTERPRET(BX'0000000000B0370D' AS BIGINT)    --     11548429

So this is taking a hex RID and interpreting it as a BIGINT. This is very useful when you get RID problems with the LOAD utility, for example. You can then simply plug in the BIGINT value into a query like:

SELECT * FROM TABLE1 A WHERE RID(A) = 11548429;

And then you’ll find the bad guy(s) very easily!

Naturally, I will be keeping this newsletter up-to-date, as necessary.

Any questions or ideas, do not hesitate to drop me a line,

TTFN,

Roy Boxwell

A really brief blog this month, as I found something out while playing with buffer pool tuning (I have no hobbies!)

Buffer Pools …

There has been a long debate down the years about buffer pool management. How big should they be? How many should you have? Which parameters for which type? And so on. I have written numerous blogs, held IDUG Presentations and discussed myriad times with colleagues and customers about this topic.

Something New???

I found something new! Well, new to me anyways!!!

There was a trend a few years ago, called “big memory” where people were encouraged to recombine many data-sharing members downwards so you went from a 14 way to eight way or six way to four way. This was then backed up with more DBATs and expanded buffer pools. The argument was pretty convincing: Let Db2 do the magic!

Too Much Work!

You, as a puny human, have no chance of really knowing what is going on. So just splitting the buffer pools at the highest possible level is all you can actually reasonably do! The argument went that it is easiest to go for around 9 – 10 buffer pools.

DISPLAY GROUPBUFFERPOOL Done on a Regular Basis?

Of course you do!!!

What I found out recently, is the ominous counter in message DSNB415I titled “PREFETCH DISABLED NO READ ENGINE”. Nowadays, each Db2 subsystem/member of a datasharing system has 900 read engines but before, it was split out in 14 or eight sub-systems and is now being used in far fewer.

Zero Counter No More!

This counter was *always* zero whenever and wherever I checked, until last week… Now I see PREFETCH DISABLED NO READ ENGINE on a regular basis. There is a great blog from Robert Catterall.

In the environments I am looking at, they also have PREFETCH DISABLED NO BUFFER occurrences and well over 100 I/Os /second, so it does indeed mean trouble!

Time to Tune!

All you can do here is:

• Check your VPSEQT and see if you can nudge it up a bit.
• Throw some memory at the problem by increasing the VPSIZE and hope that the requested pages are then found in the buffer pool.
• Move smaller objects into PGSTEAL(NONE) buffer pools thus requiring no PREFETCH.
• As a last gasp, try and tune the SQL to not use PREFETCH as a method – very tricky of course!

Keep on Truckin’

But let’s be honest for a minute, if you have 900 read engines all humming along then your system is really running under pressure and you should *expect* to get this counter, I guess!

As Robert says: No need to panic!

TTFN,

Roy Boxwell

This month I wish to share an interesting voyage of discovery to do with TIMESTAMP calculations.

How Interesting …

It all started decades ago, when I wanted to find out how many microseconds there were between two timestamps. A simple idea for a trace in various programs which, when analyzed, could highlight problematic code paths.

Use DISPLAY Stupid!

Just DISPLAY the current timestamp at the start of each SECTION. The trace analysis program would then simply subtract two timestamps – et Voila! You have a difference!

Nope … That Fails

Well, I found out very quickly that math on TIMESTAMPs does *not* work like that! What you actually get is a “duration” which, in my humble opinion, is worthless! Here’s an example:

SELECT TIMESTAMP('2023-12-21-15.06.40.120234') -
       TIMESTAMP('2023-12-21-15.06.40.120034') 
FROM SYSIBM.SYSDUMMY1  ;                       
---------+---------+---------+---------+--------
---------+---------+---------+---------+--------
           .000200

Looks good doesn’t it? Exactly 200 microseconds – as it should be.

Now look at this example:

SELECT TIMESTAMP('2023-12-21-15.06.41.120234') -
       TIMESTAMP('2023-10-22-17.08.55.130234') 
FROM SYSIBM.SYSDUMMY1  ;                        
---------+---------+---------+---------+--------
---------+---------+---------+---------+--------
  129215745.990000

What?

Yep, what the calculation does is really “subtract”… What you get is a decimal 14 containing the YYYY (leading zeroes blanked of course!) years, MM months, DD days HHMMSS then a decimal point and then your usual six digits for microseconds.

Not Good!

This, for me, was not actually usable! So, what I did, was then extract all the fields multiplying by the different values and adding them all together to get a SECONDS field. This gave me what I wanted but was a bit messy.

Months?

Whoever dreamed up the western calendar obviously was not a programmer! 31, 30, 28 sometimes 29 days in a month but then not always… The days in month has been, and always will be, a real PITA. However, IBM Db2 has a nice Built-in Function (BiF) called DAYS (not DAY – That just extracts the day portion of the calendar!)

DAYS ( expression ) – The result is 1 more than the number of days from January 1, 0001 to D, where D is the date that would occur if the DATE function were applied to the argument.

All in UTC and this is *very* handy as it bypasses the days-in-the-month problem completely!

Armed with this it made the COBOL code simpler and better.

TIMESTAMPDIFF to the Rescue!

Then in Db2 V8 came a new BiF – TIMESTAMPDIFF – will it be our savior?

When it was announced I thought: Wow! This is great – it will do all the work for me – just tell it what you want as output and give it two timestamps and you are done!

TIMESTAMPDIFF( numeric-expression, string-expression)

Not Really …

The problem is in the first sentence of the docu that most people do not bother to read:

The TIMESTAMPDIFF function returns an estimated number of intervals of the type that is defined by the first argument, based on the difference between two timestamps.

Seconds is Good?

Naturally, I used 2 (to get seconds) as the numeric-expression and, to begin with, was a happy bunny with the results.

Guestimate?

Then I noticed that all was not well in the world of TIMSTAMPDIFF and that the “estimated” number can be difficult to judge! The problem gets clearer when you review the “assumptions” list at the end of the docu:

The following assumptions are used in estimating a difference:

• One year has 365 days
• One year has 52 weeks
• One year has 12 months
• One month has 30 days
• One day has 24 hours
• One hour has 60 minutes
• One minute has 60 seconds

Now we all know that this is not true… Not all years have 365 days or even 52 weeks and nearly all months do not have 30 days! Now in my trace program it was just a bit irritating, but if you are using TIMESTAMPDIFF believing you really get the number of DAYS between two dates then it is time to think again!

Seeing is Believing

Here’s an example showing where it first works fine and then one day earlier and it all goes astray:

SELECT                                                              
 TIMESTAMPDIFF( 2, CHAR(TIMESTAMP('2023-12-21-00.00.01') -          
                        TIMESTAMP('2023-10-22-00.00.01')))  AS TSDIFF
,           DAYS(       TIMESTAMP('2023-12-21-00.00.01')) -         
            DAYS(       TIMESTAMP('2023-10-22-00.00.01'))   AS DAYS 
FROM SYSIBM.SYSDUMMY1  ;                                            
---------+---------+---------+---------+---------+---------+---------
     TSDIFF         DAYS                                             
---------+---------+---------+---------+---------+---------+---------
    5184000           60                                             
DSNE610I NUMBER OF ROWS DISPLAYED IS 1                              
SELECT                                                              
 TIMESTAMPDIFF( 2, CHAR(TIMESTAMP('2023-12-21-00.00.01') -          
                        TIMESTAMP('2023-10-21-00.00.01')))  AS TSDIFF
,           DAYS(       TIMESTAMP('2023-12-21-00.00.01')) -         
            DAYS(       TIMESTAMP('2023-10-21-00.00.01'))   AS DAYS 
FROM SYSIBM.SYSDUMMY1  ;                                            
---------+---------+---------+---------+---------+---------+---------
     TSDIFF         DAYS                                            
---------+---------+---------+---------+---------+---------+---------
    5184000           61                                            
DSNE610I NUMBER OF ROWS DISPLAYED IS 1

 As you can easily see, the day has changed by one but the TSDIFF has not…Not good! The problem is naturally caused by going over some internal threshold. If you do not care about accuracy, it is fine.

I Do!

So, what I have done is write my own “timestampdiff” in SQL:

SELECT                                                              
       MIDNIGHT_SECONDS(TIMESTAMP('2023-12-21-00.00.01')) -         
       MIDNIGHT_SECONDS(TIMESTAMP('2023-10-22-00.00.01'))           
      + ( 86400 * (DAYS(TIMESTAMP('2023-12-21-00.00.01')) -         
                   DAYS(TIMESTAMP('2023-10-22-00.00.01')))) AS DIFF 
,TIMESTAMPDIFF( 2, CHAR(TIMESTAMP('2023-12-21-00.00.01') -          
                        TIMESTAMP('2023-10-22-00.00.01')))  AS TSDIFF
,           DAYS(       TIMESTAMP('2023-12-21-00.00.01')) -         
            DAYS(       TIMESTAMP('2023-10-22-00.00.01'))   AS DAYS 
FROM SYSIBM.SYSDUMMY1  ;                                            
---------+---------+---------+---------+---------+---------+---------
       DIFF       TSDIFF         DAYS                               
---------+---------+---------+---------+---------+---------+---------
    5184000      5184000           60                               
DSNE610I NUMBER OF ROWS DISPLAYED IS 1                              
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100         
---------+---------+---------+---------+---------+---------+---------
SELECT                                                              
       MIDNIGHT_SECONDS(TIMESTAMP('2023-12-21-00.00.01')) -         
       MIDNIGHT_SECONDS(TIMESTAMP('2023-10-21-00.00.01'))            
      + ( 86400 * (DAYS(TIMESTAMP('2023-12-21-00.00.01')) -         
                   DAYS(TIMESTAMP('2023-10-21-00.00.01')))) AS DIFF 
,TIMESTAMPDIFF( 2, CHAR(TIMESTAMP('2023-12-21-00.00.01') -          
                        TIMESTAMP('2023-10-21-00.00.01')))  AS TSDIFF
,           DAYS(       TIMESTAMP('2023-12-21-00.00.01')) -         
            DAYS(       TIMESTAMP('2023-10-21-00.00.01'))   AS DAYS 
FROM SYSIBM.SYSDUMMY1  ;                                            
---------+---------+---------+---------+---------+---------+---------
       DIFF       TSDIFF         DAYS                               
---------+---------+---------+---------+---------+---------+---------
    5270400      5184000           61                                
DSNE610I NUMBER OF ROWS DISPLAYED IS 1                              
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100         
---------+---------+---------+---------+---------+---------+---------
SELECT                                                               
       MIDNIGHT_SECONDS(TIMESTAMP('2023-12-21-00.00.01')) -         
       MIDNIGHT_SECONDS(TIMESTAMP('2023-10-20-00.00.01'))           
      + ( 86400 * (DAYS(TIMESTAMP('2023-12-21-00.00.01')) -          
                   DAYS(TIMESTAMP('2023-10-20-00.00.01')))) AS DIFF 
,TIMESTAMPDIFF( 2, CHAR(TIMESTAMP('2023-12-21-00.00.01') -          
                        TIMESTAMP('2023-10-20-00.00.01')))  AS TSDIFF
,           DAYS(       TIMESTAMP('2023-12-21-00.00.01')) -         
            DAYS(       TIMESTAMP('2023-10-20-00.00.01'))   AS DAYS 
FROM SYSIBM.SYSDUMMY1  ;                                   
---------+---------+---------+---------+---------+---------+
       DIFF       TSDIFF         DAYS                       
---------+---------+---------+---------+---------+---------+
    5356800      5270400           62                      
DSNE610I NUMBER OF ROWS DISPLAYED IS 1

It all looks a lot better and actually returns the correct number of seconds between two timestamps!

How Does it Work?

The key part is just some math:

       MIDNIGHT_SECONDS(TIMESTAMP('from ts')) -         
       MIDNIGHT_SECONDS(TIMESTAMP('to ts'))           
      + ( 86400 * (DAYS(TIMESTAMP('from ts')) -         
                   DAYS(TIMESTAMP('to ts')))) AS DIFF

I use the MIDNIGHT_SECONDS BiF (First appeared in DB2 V6.1) that returns the number of seconds from midnight up to the given timestamp. I then simply subtract the “to ts” value from the “from ts” value and then use the DAYS BiF again subtracting from each other to get the days difference multiplying by 86400 as seconds/day. There is no need to subtract an extra day as, if that was required, it is automatically handled by the MIDNIGHT_SECONDS subtraction. Then these two results are simply added together.

Code Review Time?

I have re-reviewed all my code to make sure that any use of TIMESTAMPDIFF can accept “approximate” answers and in all other cases replaced it with the above code.

I might even open an Aha! Idea about getting this as a BiF – it is not that complex and is “better” than the best guess system we have now. In fact, I have – DB24ZOS-I-1599 – please vote if you think it would be great to get this as a simple BiF!

Db2 Does Not Stand Alone Here!

We are also not alone in this problem! Oracle, MySQL and JAVA all have the same “approximation” routines. I did a Google search for a web-based timestamp calculator and it made exactly the same mistakes. I guess that there is a “fast algorithm” out there that does the best guess quickly…but sadly not accurately!

This problem is right up there with Daylight Saving Time and the grief that causes! See my earlier blog.

TTFN,

Roy Boxwell