Author: Roy Boxwell

This month, I wish to do a brief overview of the pros and cons of using COMPRESS YES at the tablespace and index level.

Starting with Tablespaces

Compression came in with DB2 V3 and required a special processor for the CMPSC instruction to work at any really useful speed. Nothing changed for a loooooong time until DB2 V9 when XML compression was added. Then, in DB2 10, “partial compression” came. This includes building the dictionary after 1.2MB data has been inserted by the LOAD utility and afterwards, in Db2 12 FL504, we got a brand-new compression routine, only for UTS, called “Huffman” which also renamed the “old” routine to “fixed length”. Then, in FL509, Huffman got fully externalized into the Db2 Catalog. Full utility support was also introduced. All of these methods require the creation, and maintenance, of a dictionary, where Db2 can look up the byte codes/words that actually do the compression.

A new ZPARM as well

Naturally, to specify the default compression routine in use, a new ZPARM appeared: “TS_COMPRESSION_TYPE” – If set to HUFFMAN, then you could simply issue an ALTER like: ALTER TABLESPACE ROY.BOY COMPRESS YES, but if not set to Huffman, then the ALTER must be specific: ALTER TABLESPACE ROY.BOY COMPRESS YES HUFFMAN to get the new method.

Indexes

Indexes came in DB2 V9 and is not really the same as tablespace compression at all! Firstly, there is no dictionary as it is all handled in the Db2 I/O engine. It is a “prefix” compression really and forces a larger index bufferpool size to get it done – this has benefits and costs, of course. The major difference is that the indexes are always compressed on disk down to 4k page size and expanded in the bufferpool up to the 8K, 16K or 32K size you have given them. The other thing to remember, is that index compression is only for leaf pages – nothing else gets compressed.

XML

XML spaces supported “fixed length” compression in DB2 V9 but also needed a special processor to actually work.

LOB

Yes, you can nowadays also compress LOBs in Db2 12 and above, but it needs a zEDC Express feature installed to do so.

zEDC requires the following:

• z/OS® V2R1 (or later) operating system.
• One of the following:
  • IBM® z15®, or later, with the Integrated Accelerator for zEDC
  • IBM zEnterprise® EC12 CPC (with GA2 level microcode) or zBC12 CPC, or later, with the zEDC Express feature.
• zEDC software feature enabled in an IFAPRDxx parmlib member. For more information, see Product Enablement for zEnterprise Data Compression.
• Adequate 64-bit real storage that is configured to this z/OS image.

Huffman?

The first compression algorithms were Lempel-Ziv and the dictionary, of typically 4096 entries, each has a 12 bit “key” for the value to be replaced. This worked great, but over the years newer methods have appeared. One of them is the Huffman Entropy Compression. It is similar to Lempel-Ziv but sorts the data in the dictionary on frequency before assigning a variable number of bits to the value. (This is why the old method is called “fixed length” of course!) So, if you have millions of THE it would compress down to one single bit! A huge win over the 12 bits for Lempel-Ziv. But remember the great sayings: “It Depends” and “Your mileage my vary,” as not all data is good for compression and certainly not all data is good for Huffman compression.

Suck it and see!

IBM supplied a very nice utility, way back in the day, called DSN1COMP which has become very good over the years and is much more user friendly than when it first came out! Basically, you give it the VSAM cluster name of the object you wish to test. A handful of parameters to give it a clue about what you have defined (FREEPAGE, PCTFREE, PAGESIZE and DSSIZE), and then Bob’s Your Uncle – you get a nice report:

DSN1COMP Idiots Guide

The basic JCL looks like this:

//DSN1COMP EXEC PGM=DSN1COMP,                                        
// PARM='PCTFREE(0),FREEPAGE(0),ROWLIMIT(9999),REORG'                
//STEPLIB  DD DISP=SHR,DSN=xxxxxx.SDSNEXIT.xxxx                       
//         DD DISP=SHR,DSN=xxxxxx.SDSNLOAD                           
//SYSPRINT DD SYSOUT=*                                               
//SYSUT1   DD DISP=SHR,DSN=xxxxxx.DSNDBD.dbname.tbspace.I0001.A001

The parameters are quite long and complex…

PAGESIZE(nnK) – Must be the page size of the object you are running against. Get it wrong and you “might produce unpredictable results”.

DSSIZE(nnnG) – Must be the DSSIZE of the object you are running against. Get it wrong and you “might produce unpredictable results”.

If you omit these two options, DSN1COMP will attempt to extract the data from the header page. Depending on how old the VSAM/Full image copy dataset is, this data might be found – or not!

NUMPARTS(nnnn) – DSN1COMP assumes the object is not partitioned. Get it wrong and you “might produce unpredictable results”. For UTS data, this parameter is not used as DSSIZE takes care of the requirement.

FREEPAGE(nnn) – This must be set to the current FREEPAGE of the object. From 0 – 255. Default is 0.

PCTFREE(nn) – This must be set to the current PCTFREE of the object. From 0 to 99. Default is 5.

MAXROWS(nnn) – This must be set to the current MAXROWS of object. From 1 to 255. Default is 255.

FULLCOPY – Informs DSN1COMP that the dataset is a full image copy dataset. If it is a partitioned dataset then you must also use the NUMPARTS parameter.

REORG – This switches DSN1COMP from “LOAD” mode to “REORG” mode and generally gives a more accurate compression report as DSN1COMP then simulates full record compression and not “after 1.2MB rows” or whatever internal threshold is met. Even using REORG, you might not get a perfect match with the real compressed data as DSN1COMP uses sampling. Not valid for LOB spaces!

LOB – Informs DSN1COMP that it is now working with a LOB space – If used, *no* other parameters are allowed! You must have the zEDC for this option!

COMPTYPE(x-x) – HUFFMAN, FIXED or ALL. If not specified, DSN1COMP will check for hardware support and output HUFFMAN and FIXED, otherwise just FIXED.

ROWLIMIT(n-n) – An absolute must! From 1 to 99,000,000. Forget this and DSN1COMP will trundle through the *entire* dataset! I would set this to 99999 to begin with.

EXTNDICT(xxxxxxxx) – An eight-byte name for a generated externalized object deck compression dictionary to DD card DSN1DICT. This is not normally used.

LEAFLIM(n-n) – Limits the number of index leaf pages that DSN1COMP reads to calculate index compression rates. From 1 to 99,000,000. Default is all index leaf pages. Remember that index compression only compresses leaf pages. Note that this is the only parameter allowed for index datasets.

How it looks

DSN1999I START OF DSN1COMP FOR JOB BOXWELL$ STEP1                         
DSN1998I INPUT DSNAME = xxxxxx.DSNDBD.dbname.tbspace.I0001.A001  , VSAM
DSN1944I DSN1COMP INPUT PARAMETERS                                        
         INPUT DATA SET CONTAINS NON-COMPRESSED DATA                      
           4,096  DICTIONARY SIZE USED                                    
               0  FREEPAGE VALUE USED                                     
               0  PCTFREE VALUE USED                                      
                  COMPTYPE(ALL) REQUESTED                                 
           9,999  ROWLIMIT REQUESTED                                      
                  ESTIMATE BASED ON DB2 REORG METHOD                       
             255  MAXROWS VALUE USED                                      

DSN1940I DSN1COMP COMPRESSION REPORT                                                            
  HARDWARE SUPPORT FOR HUFFMAN COMPRESSION IS AVAILABLE                                         
  +------------------------------+--------------+------------+------------------+
  !                              !              ! Estimated  ! Estimated state  !
  !                              ! UNCOMPRESSED ! Compressed ! Compressed       !
  !                              !              ! FIXED      ! HUFFMAN          !
  +------------------------------+--------------+------------+------------------+
  ! DATA (IN KB)                 !        2,269 !        582 !              506 !
  ! PERCENT SAVINGS              !              !         74%!               77%!
  !                              !              !            !                  !
  ! AVERAGE BYTES PER ROW        !          235 !         62 !               54 !
  ! PERCENT SAVINGS              !              !         73%!               77%!
  !                              !              !            !                  !
  ! DATA PAGES NEEDED            !          589 !        154 !              134 !
  ! PERCENT DATA PAGES SAVED     !              !         73%!               77%!
  !                              !              !            !                  !
  ! DICTIONARY PAGES REQUIRED    !            0 !         64 !               64 !
  ! ROWS ... TO BUILD DICTIONARY !              !      1,149 !            1,149 !
  ! ROWS ... TO PROVIDE ESTIMATE !              !      9,999 !            9,999 !
  ! DICTIONARY ENTRIES           !              !      4,096 !            4,080 !
  !                              !              !            !                  !
  ! TOT PAGES (DICTNARY + DATA)  !          589 !        218 !              198 !
  ! PERCENT SAVINGS              !              !         62%!               66%!
  +------------------------------+--------------+------------+------------------+
                                                                                                
DSN1994I DSN1COMP COMPLETED SUCCESSFULLY,            605  PAGES PROCESSED                        

I have edited the report to make it a bit thinner!

As you can easily see, the original dataset is 589 Pages. After “normal” compression it is down to 218, which is a 62% reduction. However, with Huffman it squeezes down even more into 198 or 66%. So, according to my golden rule, it fits well! Once done and actioned by a REORG, remember to performance-test, as compression can bite you. The SYSIBM.SYSTABLES column PCTROWCOMP is used by the Db2 Optimizer for access plan selection! In other words: Test, Test and Test!

How do Indexes look?

JCL is similar, but different of course, due to the lack of parameters!

//STEP1  EXEC PGM=DSN1COMP,                                          
// PARM='LEAFLIM(9999)'                                              
//STEPLIB  DD DISP=SHR,DSN=xxxxxx.SDSNEXIT.xxxx                       
//         DD DISP=SHR,DSN=xxxxxx.SDSNLOAD                           
//SYSPRINT DD SYSOUT=*                                               
//SYSUT1   DD DISP=SHR,DSN=xxxxxx.DSNDBD.dbname.ixspace.I0001.A001

DSN1999I START OF DSN1COMP FOR JOB BOXWELL$ STEP1                         
DSN1998I INPUT DSNAME = xxxxxx.DSNDBD.dbname.ixspace.I0001.A001  , VSAM
                                                                          
DSN1944I DSN1COMP INPUT PARAMETERS                                        
                  PROCESSING PARMS FOR INDEX DATASET:                     
           9,999  LEAF LEAFLIM REQUESTED                                  
                                                                          
DSN1940I DSN1COMP COMPRESSION REPORT                                      
                                                                          
           9,999  REQUESTED LEAF LIMIT REACHED                            
           9,999  Index Leaf Pages Processed                              
         621,333  Keys Processed                                          
         621,333  Rids Processed                                          
          32,096  KB of Key Data Processed                                
          11,947  KB of Compressed Keys Produced       

    EVALUATION OF COMPRESSION WITH DIFFERENT INDEX PAGE SIZES
                                                             
    ----------------------------------------------           
 8  K Page Buffer Size yields a                              
51  % Reduction in Index Leaf Page Space                     
    The Resulting Index would have approximately             
49  % of the original index's Leaf Page Space                
    No Bufferpool Space would be unused                      
    ----------------------------------------------           
                                                             
    ----------------------------------------------           
16  K Page Buffer Size yields a                              
63  % Reduction in Index Leaf Page Space                     
    The Resulting Index would have approximately             
37  % of the original index's Leaf Page Space                
32  % of Bufferpool Space would be unused to                 
    ensure keys fit into compressed buffers                  
    ----------------------------------------------           
                                                             
    ----------------------------------------------           
32  K Page Buffer Size yields a                              
63  % Reduction in Index Leaf Page Space                     
    The Resulting Index would have approximately             
37  % of the original index's Leaf Page Space                
66  % of Bufferpool Space would be unused to                 
    ensure keys fit into compressed buffers                  
    ----------------------------------------------           
DSN1994I DSN1COMP COMPLETED SUCCESSFULLY,          9,999  PAGES PROCESSED                   

Here you see the huge difference in output!

The DSN1COMP computes the three possible new page sizes for your index (8, 16 and 32), which enables you to decide which one is “best” for the index to live in. In this example, the 8K BP is the clear winner as no space is wasted and it provides over 50% savings.

Problems?

The Db2 optimizer does *not* “know” that the index is compressed, but it *does* know which bufferpool you have moved it to. Remember to correctly size all of these new bufferpools so that you do not get paging or excessive flushing!

All clear?

Now it is clear: You must still weigh up the pros and cons here! Do not blindly compress the world and expect to save TBs of disk space! The CPU required to decompress/compress is not free, and the Db2 I/O Engines must also work for Index Compression.

Rules Of Thumb

Ignore very small rows – typically, it makes no sense to compress row sizes under 16 at all! Remember you can still only get a maximum of 255 rows in a page (even if compressed!) and so very small rows would hit that limit and make the exercise pointless.

Very large rows – take a row that is 4,000 and let’s say it compress at 45%, leaving you with 2,200 – It still only gets one row in a page. (Previous 4,000 fits due to the page size limit of 4,054 and now with 2,200 still only one is allowed!) In this case, I would change the bufferpool to actually realize any benefits.

There are cases where compressing (or using Huffman to compress more) can negatively affect an application that is using page-level locking.

Obviously, do not compress tablespaces defined with MAXROWS=1.

If the space saving is less than 10% – 20%, it generally makes no sense to use compression.

Db2 only actually does the compression if the row will then be shorter – It could well be, with lots of timestamp fields, that it ends up being the same size and so Db2 saves you CPU by not compressing it.

Compression is *not* encryption and should not be used as such! Furthermore, compressing encrypted data is probably pointless.

LOAD inserts data until a sweet spot is reached, and then it starts to insert compressed data (True for INSERT, MERGE and LOAD SHRLEVEL CHANGE of course).

REORG builds the dictionary in the UNLOAD phase so all rows, if eligible, will be compressed.

To share or not to share?

Sharing dictionaries can be a great idea or a terrible idea – your data decides! In the LOAD statement, if you are loading at PARTITION level, you may add “COPYDICTIONARY nnnn” to save the time of completely building a dictionary. This only makes sense if you *know* that the dictionary is good for your partition as well.

Keeping Dictionaries

You may decide to save all the time in building dictionaries by adding KEEPDICTIONARY to the LOAD or REORG utility cards. The only problem here, is sometimes data does change and a new dictionary will be required.

And what about Indexes?

Well, I tend to like the larger bufferpool here just to help reduce index page splits. So going to 8K and above can have a very nice knock-on effect into general performance and logging.

Looking for Candidates?

I would start with all big table partitions (over 199MB) that are not compressed at all and also perhaps take another look at any table partitions that compressed badly. Perhaps Huffman or rebuilding the dictionary could help? For indexes I would just list out the uncompressed ones larger than 99MB. Feel free to change the values!

Here’s some simple SQL to list out the interesting cases. For table partitions and tablespaces:

SELECT RTS.DBNAME                        
     , RTS.NAME                          
     , RTS.PARTITION                     
     , RTS.SPACE / 1024 AS MB            
     , TS.INSTANCE                       
     , TP.IPREFIX                        
     , TP.COMPRESS                       
     , TP.PAGESAVE                       
FROM SYSIBM.SYSTABLESPACESTATS RTS       
    ,SYSIBM.SYSDATABASE        DB        
    ,SYSIBM.SYSTABLESPACE      TS        
    ,SYSIBM.SYSTABLEPART       TP        
WHERE     RTS.DBNAME       = TS.DBNAME   
  AND NOT RTS.DBNAME    LIKE 'DSNDB0_'   
  AND     RTS.NAME         = TS.NAME     
  AND     RTS.DBNAME       = DB.NAME     
  AND     RTS.INSTANCE     = TS.INSTANCE 
  AND NOT DB.TYPE          = 'W'         
  AND     RTS.DBNAME       = TP.DBNAME   
  AND     RTS.NAME         = TP.TSNAME   
  AND     RTS.PARTITION    = TP.PARTITION
  AND     RTS.SPACE / 1024 > 199         
  AND    (TP.COMPRESS      = ' '         
      OR (TP.COMPRESS     IN ('Y' , 'F') 
      AND TP.PAGESAVE      < 20))        
ORDER BY 4 DESC, 1 , 2 , 3               
FOR FETCH ONLY                           
WITH UR                                  
;                                        

For indexes:

SELECT RTS.DBNAME                        
     , RTS.INDEXSPACE                    
     , RTS.PARTITION                     
     , RTS.SPACE / 1024 AS MB            
     , RTS.NLEAF                         
     , RTS.INSTANCE                      
     , IP.IPREFIX                        
FROM SYSIBM.SYSINDEXSPACESTATS RTS       
    ,SYSIBM.SYSINDEXES         IX        
    ,SYSIBM.SYSINDEXPART       IP        
WHERE     RTS.NAME         = IX.NAME     
  AND NOT RTS.DBNAME    LIKE 'DSNDB0_'   
  AND     RTS.CREATOR      = IX.CREATOR  
  AND     RTS.PARTITION    = IP.PARTITION
  AND      IX.CREATOR      = IP.IXCREATOR
  AND      IX.NAME         = IP.IXNAME   
  AND     RTS.SPACE / 1024 > 99          
  AND      IX.COMPRESS     = 'N'         
ORDER BY 4 DESC, 1 , 2 , 3               
FOR FETCH ONLY                           
WITH UR                                  
;                                        

What are your thoughts or experiences with compression? I would love to hear from you!

TTFN,

Roy Boxwell

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