Author: Roy Boxwell

Posted on

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

Posted on

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

Posted on

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

Posted on

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

Posted on

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

Posted on

2021-06 How large is my system?

After I held my Deprecated Items IDUG talk at the Virtual 2021 North American IDUG one of the attendees asked me if I also had some SQL for quickly retrieving the current “size of the database” – Normally I use our very own SpaceManager product for this as it uses CSI access calls to get up to the second info about the size of the VSAM LDS, however for a quick and dirty query the Db2 catalog can be partially trusted!

Where to begin?

The easiest places to start are the two SYSIBM.SYSxxxxxPART tables. These contain all the partition details and if the PARTITION is zero then it is a non-partitioned tablespace or it is a NPSI defined index.

What to select?

SELECT COUNT(*)                          AS INDEX_PARTS
      ,SUM(IP.SPACEF)                    AS KB
      ,INTEGER(SUM(IP.SPACEF) / 1024)    AS MB
      ,INTEGER(SUM(IP.SPACEF) / 1048576) AS GB
FROM SYSIBM.SYSINDEXES   IX
    ,SYSIBM.SYSINDEXPART IP
WHERE     IX.NAME    = IP.IXNAME
  AND     IX.CREATOR = IP.IXCREATOR
  AND NOT IP.SPACE   < 0
;

This query just shows you the current size of all of your indexes. Notice the NOT IP.SPACE < 0 – This is to not count any DEFINE NO indexes as these use no space of course! I simply added the two INTEGER functions – naturally you can change these to any format you would like.

Table time!

SELECT COUNT(*)                          AS TABLE_PARTS
      ,SUM(TP.SPACEF)                    AS KB
      ,INTEGER(SUM(TP.SPACEF) / 1024)    AS MB
      ,INTEGER(SUM(TP.SPACEF) / 1048576) AS GB
FROM SYSIBM.SYSTABLESPACE TS
    ,SYSIBM.SYSTABLEPART TP
WHERE     TS.NAME   = TP.TSNAME
  AND     TS.DBNAME = TP.DBNAME
  AND NOT TP.SPACE  < 0
;

This does the same but now for tableparts of course!

More detail?

Now these two SQLs are OK for quick results but it is probably more interesting to see which index types and which tablespace types are taking up how much space on your disks. So here the expanded queries follow:

SELECT COUNT(*)                          AS INDEX_PARTS
      ,IX.INDEXTYPE
      ,IX.IX_EXTENSION_TYPE
      ,IX.PAGENUM
      ,SUM(IP.SPACEF)                    AS KB
      ,INTEGER(SUM(IP.SPACEF) / 1024)    AS MB
      ,INTEGER(SUM(IP.SPACEF) / 1048576) AS GB
FROM SYSIBM.SYSINDEXES   IX
    ,SYSIBM.SYSINDEXPART IP
WHERE      IX.NAME    = IP.IXNAME
   AND     IX.CREATOR = IP.IXCREATOR
   AND NOT IP.SPACE   < 0
GROUP BY IX.INDEXTYPE
        ,IX.IX_EXTENSION_TYPE
        ,IX.PAGENUM
ORDER BY IX.INDEXTYPE
        ,IX.IX_EXTENSION_TYPE
        ,IX.PAGENUM
;

The INDEXTYPE column can be Blank (For ancient Type 1 indexes which I hope no-one has), 2 for “normal” Type 2 Indexes, D for Data-partitioned Secondary Indexes (DPSIs) and P for Indexes which are both Partitioned and Partitioning (which I also hope no-one has anymore!)

What’s in a TYPE?

The IX_EXTENSION_TYPE column can be Blank for “normal” indexes, N for Node ID indexes on XML spaces, S for Scalar Expressions, T for Spatial or V for real XML Indexes.

Using new features yet?

PAGENUM is just telling you how many of your indexes are enjoying the great feature of fully variable DSSIZE, adjustable on-the-fly with no outage (As long as you adjust upwards of course!) If set to “A” for Absolute then sadly not, much better is “R” for Relative!

SELECT COUNT(*)                          AS TABLE_PARTS
      ,TS.TYPE
      ,TS.PAGENUM
      ,SUM(TP.SPACEF)                    AS KB
      ,INTEGER(SUM(TP.SPACEF) / 1024)    AS MB
      ,INTEGER(SUM(TP.SPACEF) / 1048576) AS GB
FROM SYSIBM.SYSTABLESPACE TS
    ,SYSIBM.SYSTABLEPART  TP
WHERE     TS.NAME   = TP.TSNAME
  AND     TS.DBNAME = TP.DBNAME
  AND NOT TP.SPACE  < 0
GROUP BY TS.TYPE
     ,TS.PAGENUM
ORDER BY TS.TYPE
     ,TS.PAGENUM
;

What’s in a tablespace TYPE?

Now at the tablespace level we have, again, the TYPE column which can be Blank for a “normal” table, G for UTS PBG, L for Large, O for LOB, P for XML or R for UTS PBR. Going forward you will have to migrate any of the Blank tablespaces to PBG/PBR and L should be gone anyway!

My Output

When I run these here in Düsseldorf in my little Db2 12 FL508 test system I get this output:

---------+---------+---------+---------+---------+---------+--
INDEX_PARTS                       KB           MB           GB
---------+---------+---------+---------+---------+---------+--
       4453  +0.5951116000000000E+07         5811            5
---------+---------+---------+---------+---------+---------+--
TABLE_PARTS                       KB           MB           GB
---------+---------+---------+---------+---------+---------+--
       3733  +0.3255953100000000E+08        31796           31
---------+---------+---------+---------+---------+---------+-
INDEX_PARTS INDEX IX_EXT PAGE                     KB    MB GB     
            TYPE  TYPE   NUM
---------+---------+---------+---------+---------+---------+-
       3280 2            A   +0.5100220000000000E+07  4980  4
         25 2     N      A   +0.1800000000000000E+05    17  0
          3 2     S      A   +0.1224000000000000E+05    11  0
          8 2     V      A   +0.3744000000000000E+04     3  0
         15 D            A   +0.8112000000000000E+04     7  0
         24 P            A   +0.1392000000000000E+05    13  0
       1098 P            R   +0.7948800000000000E+06   776  0
---------+---------+---------+---------+---------+--------
TABLE_PARTS TYPE PAGENUM                     KB     MB  GB
---------+---------+---------+---------+---------+--------
        363      A      +0.5242134000000000E+07   5119   4 
       2094 G    A      +0.2109695400000000E+08  20602  20
          4 L    A      +0.1312000000000000E+04      1   0
        116 O    A      +0.1186235000000000E+07   1158   1
         25 P    A      +0.2952000000000000E+05     28   0
         33 R    A      +0.6932800000000000E+05     67   0
       1098 R    R      +0.4934048000000000E+07   4818   4

So you can see I already have over 1000 UTS PBR RPN table parts in use! Not bad! As a vendor I cannot just migrate everything of course so those 24 Index Controlled Partitioning indexes and the 33 Absolute Partitions aren’t going anywhere soon!

How does your site look? How are your plans for migrating to UTS PBR RPN going?

As always, I would love to hear the results!

TTFN,

Roy Boxwell

Posted on

2021-05 Soundex and other cool features part 7 for Db2 12

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 six All new for Db2 12” newsletter from 2018-08 where I first covered the new stuff in Db2 12.

What was new in FL100

At that level, a new table function called BLOCKING_THREADS was introduced, which is primarily used by the command DISPLAY BLOCKERS to help in looking for bad guys who are ready to BLOCK your change to FLxxx

FL500 Changed Scalars

Two scalar BiF got changed: TRANSLATE and UPPER can now both use UNI_60 as a locale.

FL500 New Scalars

In FL500, we got a nice bunch of new Scalar functions:

GENERATE_UNIQUE_BINARY (like GENERATE_UNIQUE except it returns a BINARY(16) value)
HASH_CRC32
HASH_MD5
HASH_SHA1
HASH_SHA256
PERCENTILE_CONT
PERCENTILE_DISC
WRAP

FL500 Changed Aggregates

One aggregate BiF got updated: ARRAY_AGG can now be used for associative arrays.

FL501?

It brought in just one new Aggregate BiF, which was the very famous LISTAGG. This is famous, as it was the “proof of concept” that Agile actually worked and could function in the wild.

FL502?

Two changes to Scalar BiFs in this release:

GRAPHIC The first argument now accepts numeric data types, including SMALLINT, INTEGER, BIGINT, DECIMAL, REAL, DOUBLE, FLOAT, and DECFLOAT.
VARGRAPHIC The first argument now accepts numeric data types, including SMALLINT, INTEGER, BIGINT, DECIMAL, REAL, DOUBLE, FLOAT, and DECFLOAT.

FL503?

Nothing new for us in that release!

FL504?

It brought in a brand new concept for Db2 on z/OS. The concept of “pass-through BiFs”. These are just checked for valid syntax before being passed on down to the Accelerator that would actually execute them. Obviously, you must be careful about their usage!

CUME_DIST
CUME_DIST (aggregate)
FIRST_VALUE
LAG
LAST_VALUE
LEAD
NTH_VALUE
NTILE
PERCENT_RANK
PERCENT_RANK (aggregate)
RATIO_TO_REPORT
REGEXP_COUNT
REGEXP_INSTR
REGEXP_LIKE
REGEXP_REPLACE
REGEXP_SUBSTR

FL505?

Brought in a whole bunch of encryption and decryption BiFs:

ENCRYPT_DATAKEY converts a block of plain text to a block of encrypted text using a specified algorithm and key label.
DECRYPT_DATAKEY_datatype functions return the block of encrypted text from ENCRYPT_DATAKEY as a block of the datatype specified by the function used. List of datatypes: DECRYPT_DATAKEY_INTEGER, DECRYPT_DATAKEY_BIGINT, DECRYPT_DATAKEY_DECIMAL, DECRYPT_DATAKEY_VARCHAR, DECRYPT_DATAKEY_CLOB, DECRYPT_DATAKEY_VARGRAPHIC, DECRYPT_DATAKEY_DBCLOB, and DECRYPT_DATAKEY_BIT.

FL506?

Here IBM came up with a slew of “alternative names” for existing BiFs and a new method of calling HASH routines. The background here was to make porting to Db2 on z/OS that little bit easier!

CHAR_LENGTH CHARACTER_LENGTH, which returns the length of its argument in the number of string units that are specified
COVAR_POP COVARIANCE or COVAR, which return the population covariance of a set of number pairs
HASH HASH_MD5, HASH_SHA1, or HASH_SHA256, which return the result of applying a hash algorithm to an input argument, depending on the value specified for the second argument for the HASH function:

        0 (default) HASH_MD5 Returns VARBINARY(16) instead of BINARY(16)
        1 HASH_SHA1          Returns VARBINARY(20) instead of BINARY(20)
        2 HASH_SHA256        Returns VARBINARY(32) instead of BINARY(32)

POW POWER, which returns the value of one argument raised to the power of a second argument
RANDOM RAND, which returns a double precision floating-point random number
STRLEFT LEFT, which returns a string that consists of the specified number of leftmost bytes or the specified string units
STRPOS POSSTR, which returns the position of the first occurrence of an argument within another argument
STRRIGHT RIGHT, which returns a string that consists of the specified number of rightmost bytes or specified string units
TO_CLOB CLOB, which returns a CLOB representation of the first argument
TO_TIMESTAMP TIMESTAMP_FORMAT, which returns a timestamp for a character string expression, using a specified format to interpret the string

FL507?

Here IBM development added another slew of “pass through” BiFs:

ADD_DAYS
BTRIM
DAYS_BETWEEN
NEXT_MONTH
REGR_AVGX
REGR_AVGY
REGR_COUNT
REGR_INTERCEPT or REGR_ICPT
REGR_R2
REGR_SLOPE
REGR_SXX
REGR_SXY
REGR_SYY
ROUND_TIMESTAMP if invoked with a date expression

FL508?

None that I have read of yet!

FL509?

None yet…

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

Posted on

2021-04 PBG Partition Pain

One of the things I dearly love about Db2 is that I never ever stop learning. I have been working with Db2 since V1.3 and I was completely surprised by what I just learnt!

PBG What is it?

For those that do not know, PBG is a Partitioned-by-Growth Universal Tablespace (UTS) that came into existence in Db2 V9 as well as its twin – Partitioned-by-Range (PBR). IBM have repeatedly said that all new developments will focus on tables/indexes etc that are backed by UTS and so deprecated all the others types (Simple, Segmented, Classic Partitioned). When you create a PBG you specify how many partitions it can get to using the MAXPARTITIONS clause and it starts by allocating just the very first partition.

First Part – First Problem

Quickly after GA the first problems with this data model started appearing. First up were the people who used 4096 as MAXPARTITIONS and then started running out of memory as all the internal control blocks were being allocated for 1000’s of partitions that would most probably never exist!

Next problem – Copy problems

Then users of DSN1COPY and other cloning methods hit the next really nasty problem. This was: What happened if in subsystem A you had three partitions but in subsystem B only one and you use DSN1COPY to copy the data? This problem was unsolvable until another new DDL parameter was quickly rolled out: NUMPARTS. This controls how many partitions are physically allocated at DDL creation time. This should naturally *only* be used if you intend to cross-clone datasets where the number of partitions do not match.

Running out of space?

Various people have written monitoring software over the years that intercept IFCIDs from Db2 and audits the data within. Our very own SpaceAssuranceExpert (SAX) does this to warn you if you are about to hit any one of the many Db2 limits of space management. Naturally one of these limits is when you are nearing, or have allocated, the last partition, in other words you are soon reaching or have indeed reached MAXPARTITIONS. This means you are about to run out of partitions and, if in the last partition, SAX monitors how full it is to warn the DBA group of possible -904’s coming up. All well and good so far!

Newest problem – Death by descending key

It then came to my attention that Db2 12 does not, in fact, work that way in one special case. If the DDL has the same number for MAXPARTITIONS and NUMPARTS (So all possible partitions are allocated at creation) and you have a CLUSTER defined index where all columns are also DESCENDING then the following happens:

First data is inserted in partition one and extents are taken as required until the partition is full (So DSSIZE has been reached) then the inserts are targeted at the *last* partition! Once this partition is full it then goes to MAXPARTITIONS – 1 and fills that one up!

This naturally kills any attempt to see how full the dataset is online. Obviously you can do a spacemap analysis to review exactly how much free space is there, or hope that the Real-time Statistics table can help you, but doing this instantly for every IFCID is just crazy!

Is that a must?

Thankfully you can toggle off this very strange behaviour by setting MAXPARTITIONS to one more than NUMPARTS. Then Db2 behaves itself and goes back to 1 , 2 , 3 etc.

An ALTER to the rescue?

But what if you already have such a definition? If you already have MAXPARTITIONS 3 and NUMPARTS 3, what about doing an ALTER like:

ALTER TABLESPACE SAXTDB06.SAXTTS06
  MAXPARTITIONS 4 ;

Nope. Then Db2 gets *very* dumb! It then inserts to partition one until it is full then it skips to the *old* MAXPARTITIONS partition three and fills it up and then goes backwards until partition two is full before then jumping to partition four! Whoever thought this up must have been crazy! In a space with three partitions which is then ALTERed to have a MAXPARTITIONS of four you then fill partitions 1, 3, 2, 4 in that order… In my opinion this is a bug in Db2 as this space should be handled exactly as if it was defined with MAXPARTITIONS 4 and NUMPARTS 3!

What about an Index ALTER?

BINGO! Doing an ALTER on the index like:

ALTER INDEX SAXTEST.SAXTTB06_NEW_INDEX
  NOT CLUSTER ;

Then gets the inserts going “back” into the normal partitioning sequence. All very odd, confusing and misleading!

There must be reason!

I have thought long and hard about “Why?” and I cannot think of one answer that makes *any* sense! If Db2 started inserting in the last partition I could sort of get it – It might reduce the unclustered insert counts – but only doing this *after* filling the first partition just makes it all seem so pointless…and then after an ALTER it just gets worse!

One “pointer” found

While looking around the web for any help I did stumble across this little gem:

The new V12 looping through partition function, can be either ascending or descending order. If the search is in descending order, then it is possible to skip a partition between the original target and last partition of the table space. Therefore, a sanity check of 04E-00C90101 LOC=DSNIDM DSNISGNS ERQUAL50C4 will be encountered, to catch this situation.

In APAR PI68087 from 2016.

I think this undocumented behavior should be either corrected or, even better, removed as soon as possible!

As usual, any questions or comments please feel free to mail me!

TTFN

Roy Boxwell

Posted on

2021-03 Set Phasers to Stun!

This month I am going to talk about phasers – Nah! Actually, just about REBIND phase-in but still interesting even, if you cannot vaporize an evil alien with a bad access path!

A short history of REBIND

It all started way way way back when… REBIND is used to rebuild the internal structures of plans and packages. I mention plans as even these days an “empty” plan *still* has structures within it that get rebuilt at REBIND time. It is a major prerequisite of Db2 release migrations to make sure that any and all active plans and packages which are older than Db2 vnext – 3 get a REBIND. For example, Migrating to Db2 12 will cause an auto-rebind of any plans and packages last bound earlier than Db2 10, and we all know how bad that will be – In fact, the recommendation is to REBIND all of these in Db2 11 NFM well *before* the migration anyway!

Where’s the beef?

OK, so you see you must REBIND for migration. Any other reason? Well, what about a new index or a RUNSTATS with COLGROUP or HISTOGRAM or whatever! You get the gist? You might be using our Bind ImpactExpert (BIX) to minimize the number of REBINDs, but even so, you cannot get by not doing any REBINDs or the optimizer has no chance to get a different, better access plan all worked out!

What’s the problem?

Sadly, most shops are 24×7 these days and so lots of packages are *always* active! To REBIND a package you must get an exclusive lock of course. This caused a serious amount of pain in Db2 12 as it requires regular REBINDs.

Why that?

FL Levels cause all the grief… the APPLCOMPAT is tied to the package and so when an application, in this case think JDBC or SPUFI etc., wishes to use a new feature then that package *must* be REBINDed at the appropriate level. The very first FL501 with just LISTAGG caused endless problems as companies could not get the simple REBINDs of the JDBC access packages (SYSLHxxx etc.) through without stopping all of their servers… sub optimal!

Ah! Got it…FL505 to the rescue!

So IBM development came up with “phase-in” REBIND – A really brilliant idea! In a nutshell the REBIND *always* works. You get a new package, possibly with new access plans, and any new work that arrives runs with the new package. Meanwhile, back in the old world, the currently running transactions are still using the old version until they commit and de-allocate. Over time, and hopefully a brief period of time, all users of the old package will have gone and now only the new package is in use.

For all REBINDs?

Not all, but most! The supported syntax is any form of APREUSE, PLANMGMT must be extended or you cannot do phase-in at all and the package is *not* a generated package for a TRIGGER, an SQL Routine or a UDF.

Next REBIND and you are FREE

The next time this package gets a REBIND, Db2 looks in SYSPACKAGE and sees there are phased-out packages and attempts a FREE to get rid of them. If, however, the package is *still* in use, Db2 will write out an IFCID (393) to warn the DBA that something is hanging on desperately to an old phased-out package for far longer than it is good for!

How does that help?

If you have started that IFCID, and you capture and process them, you get a nice little list of the bad guys causing you to not be able to FREE up the phased-out packages. This gives you all the data you need to issue a term thread, as it even gives you the TOKEN, or stop the servers as there is a limit to how many of these old packages can be left hanging around in limbo.

14 and you are out!

Yep, after 14 packages have been phased-out you get a really nasty RC 00E30307 and the REBIND will fail.

A question for you all

Do any of you think you will ever get anywhere near 14 phased-out copies? Do you think that adding support for such an IFCID and then a Use Case to our SQL WorkloadExpert (WLX) monitor for online reporting in Eclipse or ZOWE would be worth it?

I await your answers with bated breath!

TTFN

Roy Boxwell

Posted on

2021-02 Hidden features of EXPLAIN

EXPLAIN has been with us since DB2 V2.1 and, as I have previously written in older newsletters, (2019-03 EXPLAIN data review and 2012-11 EXPLAIN table maintenance), it has gotten bigger and better over the years. In this newsletter, I wish to quickly bring everyone up-to-date, based on my two older newsletters, and then discuss the usage of CTE Opthints.

Up to speed

Back in 2012, I listed out the tables EXPLAIN can use and ended with Db2 10, so first up are the new and changed tables for Db2 11 and Db2 12:

Db2 11

PLAN_TABLENow with 66 columns
DSN_COLDIST_TABLE
DSN_DETCOST_TABLE
DSN_FILTER_TABLE
DSN_FUNCTION_TABLE
DSN_KEYTGTDIST_TABLE
DSN_PGRANGE_TABLE
DSN_PGROUP_TABLE
DSN_PREDICAT_TABLE
DSN_PREDICATE_SELECTIVITYNew but input for the BIND QUERY command only!
DSN_PTASK_TABLE
DSN_QUERYINFO_TABLETwo LOB tables as well
DSN_QUERY_TABLEOne LOB table as well
DSN_SORTKEY_TABLE
DSN_SORT_TABLE
DSN_STATEMENT_CACHE_TABLEOnly for DSC
DSN_STATEMNT_TABLE
DSN_STAT_FEEDBACKNew table containing RUNSTATS recommendations when EXPLAIN is executed.
DSN_STRUCT_TABLE
DSN_VIEWREF_TABLE

Db2 12

PLAN_TABLENow with 67 columns
DSN_COLDIST_TABLE
DSN_DETCOST_TABLE
DSN_FILTER_TABLE
DSN_FUNCTION_TABLE
DSN_KEYTGTDIST_TABLE
DSN_PGRANGE_TABLE
DSN_PGROUP_TABLE
DSN_PREDICAT_TABLE
DSN_PREDICATE_SELECTIVITY
DSN_PTASK_TABLE
DSN_QUERYINFO_TABLETwo LOB tables as well
DSN_QUERY_TABLEOne LOB table as well
DSN_SORTKEY_TABLE
DSN_SORT_TABLE
DSN_STATEMENT_CACHE_TABLEOnly for DSC
DSN_STATEMNT_TABLE
DSN_STAT_FEEDBACK
DSN_STRUCT_TABLE
DSN_VIEWREF_TABLE

So you can see, that not a lot really happened in Db2 12 as far as any new EXPLAIN tables, but the one big change was the new column sprinkled throughout all of them: PER_STMT_ID

Something new

PER_STMT_ID BIGINT NOT NULL The persistent statement identifier for SQL statements in Db2 catalog tables.
For example, this column corresponds to the following catalog table columns that identify
SQL statements:
• STMT_ID in SYSIBM.SYSPACKSTMT, for SQL statements in packages.
• SDQ_STMT_ID in SYSIBM.SYSDYNQUERY, for stabilized dynamic SQL statements.

This column makes it *much* easier to track your mix of dynamic and static SQL all through the system!

CTE Opthints

I was using one of these the other day and the customer I was working with was amazed to see what it is and how it works. As in all things to do with OPTHINTs, caution must always be used! The best OPTHINT is no OPTHINT!

In the Beginning

Many, many years ago, sometime around DB2 V8, I found a CTE Opthint documented in the internet and thought “Wow! That is the future of hints!” Then they completely disappeared… try doing a google search and you will see what I mean. The cool thing is – They still work! I do not know for how long, but they still work in Db2 12 FL507 at least.

Time to Test

First create a couple of test table candidates and one index:

CREATE TABLE BOXWELL.T1 (C1 CHAR(8) NOT NULL
                        ,C2 CHAR(8) NOT NULL
                        ,C3 SMALLINT NOT NULL);
CREATE TABLE BOXWELL.T2 (C1 CHAR(8) NOT NULL
                        ,C2 CHAR(8) NOT NULL
                        ,C3 SMALLINT NOT NULL);
CREATE INDEX INDX1_T2 ON T2 ( C1 ) CLUSTER ;

The SQL of interest is:

SELECT T1.*        
 FROM T1            
    , T2            
 WHERE T1.C1 = T2.C1
 ;

So first you do a normal EXPLAIN and get this output:

---------+---------+---------+---------+---------+---------+---------+---------+---------+--------
LINE   QNO  PNO  SQ  M  TABLE_NAME          A   PA  CS  INDEX         IO  UJOG  UJOGC  P
---------+---------+---------+---------+---------+---------+---------+---------+--------
01000  01   01   00  0  T1                  R       00                N   ----  ----   S
01000  01   02   00  1  T2                  I   T   00  INDX1_T2      Y   -Y--  ----   S

So Db2 accesses T1 first with a tablespace scan (A = R) and then uses the index to access T2. Now add the CTE to the front so the SQL looks like:

WITH DSN_INLINE_OPT_HINT
(TABLE_CREATOR
, TABLE_NAME
, ACCESS_TYPE
, ACCESS_CREATOR
, ACCESS_NAME
, JOIN_SEQ) AS
(VALUES (NULL
, NULL
, 'INDEX'
, NULL
, NULL
, NULL)
)
SELECT T1.*        
 FROM T1            
    , T2            
 WHERE T1.C1 = T2.C1
 ;

Rules, Rules, Rules

The golden rules of CTE Opthint are that the CTE must be the first CTE, and it must be called DSN_INLINE_OPT_HINT, your ZPARM OPTHINTS must be set to YES to allow them, of course. Just include the columns and the rows you actually need. Every additional column or row is one more step towards optimizer disapproval and the CTE Opthint being ignored.

In the example above, the value NULL is a wild card short hand for all values and that row is simply telling the optimizer “If nothing else is found try and enforce INDEX access”.

So now the access path looks like:

---------+---------+---------+---------+---------+---------+---------+---------+---------+--------
LINE   QNO  PNO  SQ  M  TABLE_NAME          A   PA  CS  INDEX         IO  UJOG  UJOGC  P
---------+---------+---------+---------+---------+---------+---------+---------+--------
01001  01   01   00  0  T2                  I       00  INDX1_T2      Y   ----  ----   S
01001  01   02   00  1  T1                  R   T   00                N   -Y--  ----   S

Seeing double?

As you can see, Db2 now uses the index first. So what if you had two indexes?? Create an index on the first table:

CREATE INDEX INDX1_T1 ON T1 ( C1 ) CLUSTER ;

Now the “normal” SQL EXPLAIN shows:

---------+---------+---------+---------+---------+---------+---------+---------+---------+--------
LINE   QNO  PNO  SQ  M  TABLE_NAME          A   PA  CS  INDEX         IO  UJOG  UJOGC  P
---------+---------+---------+---------+---------+---------+---------+---------+--------
02000  01   01   00  0  T1                  R       00                N   ----  ----   S
02000  01   02   00  1  T2                  I   T   00  INDX1_T2      Y   -Y--  ----   S

We are back to tablespace scan on T1 and then IX on T2. Now, using the CTE with just INDEX (That is the one we just used) gives you:

---------+---------+---------+---------+---------+---------+---------+---------+---------+--------
LINE   QNO  PNO  SQ  M  TABLE_NAME          A   PA  CS  INDEX         IO  UJOG  UJOGC  P
---------+---------+---------+---------+---------+---------+---------+---------+--------
02001  01   01   00  0  T1                  I       00  INDX1_T1      N   ----  ----    
02001  01   02   00  1  T2                  I       01  INDX1_T2      Y   ----  ----

So, we have now got double index access (which is what we wanted!) What about trying to push T2 up to the first table to be used? Just add a second row in the CTE like this:

WITH DSN_INLINE_OPT_HINT
(TABLE_CREATOR
, TABLE_NAME
, ACCESS_TYPE
, ACCESS_CREATOR
, ACCESS_NAME
, JOIN_SEQ) AS
(VALUES (NULL
, NULL
, 'INDEX'
, NULL
, NULL
, NULL)
  ,(NULL
  , 'T2'
  , NULL
  , 'BOXWELL'
  , 'INDX1_T2'
  , 1 )       
)
SELECT T1.*        
 FROM T1            
    , T2            
 WHERE T1.C1 = T2.C1
 ;

And the output now changes to be this:

---------+---------+---------+---------+---------+---------+---------+---------+---------+--------
LINE   QNO  PNO  SQ  M  TABLE_NAME          A   PA  CS  INDEX         IO  UJOG  UJOGC  P
---------+---------+---------+---------+---------+---------+---------+---------+--------
02002  01   01   00  0  T2                  I       00  INDX1_T2      Y   ----  ----   S
02002  01   02   00  2  T1                  I       00  INDX1_T1      N   ----  ----

Isn’t that cool?

I think these are very, very handy items and are a crucial extra piece in the puzzle of “tipping point” SQLs. CTE Opthints work for both Dynamic and static SQL by the way.

One final bit of info about these CTE Opthints: If defined OK and accepted by EXPLAIN you will get:

 DSNT404I SQLCODE = 394, WARNING:  USER SPECIFIED OPTIMIZATION HINTS USED DURING ACCESS PATH SELECTION                                      
 DSNT418I SQLSTATE   = 01629 SQLSTATE RETURN CODE                           
 DSNT415I SQLERRP    = DSNXOPCO SQL PROCEDURE DETECTING ERROR               
 DSNT416I SQLERRD    = 20 0  4000000  1143356589  0  0 SQL DIAGNOSTIC INFORMATION                                                       
 DSNT416I SQLERRD    = X'00000014'  X'00000000'  X'003D0900'  X'44263CAD'   
          X'00000000'  X'00000000' SQL DIAGNOSTIC INFORMATION               
 DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0

If you get something wrong you will get *no* message SQLCODE +394 (This happens if your column names are invalid for example), and if the SQL Opthint is *not* used you get this SQLCODE output:

 DSNT404I SQLCODE = 395, WARNING:  USER SPECIFIED OPTIMIZATION HINTS ARE INVALID (REASON CODE = 'xx'). THE OPTIMIZATION HINTS ARE IGNORED
 DSNT418I SQLSTATE   = 01628 SQLSTATE RETURN CODE                         
 DSNT415I SQLERRP    = DSNXOOP SQL PROCEDURE DETECTING ERROR              
 DSNT416I SQLERRD    = 20 0  4000000  1142580885  0  0 SQL DIAGNOSTIC INFORMATION                                                     
 DSNT416I SQLERRD    = X'00000014'  X'00000000'  X'003D0900'  X'441A6695' 
          X'00000000'  X'00000000' SQL DIAGNOSTIC INFORMATION             
 DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0

Tipping points

We all have had SQLs that run fine for years and then, normally after a RUNSTATS, the order of the tables swaps… this is not really seen by anyone until the SQL starts performing really badly! If you have one of these “tipping point” SQLs you can now simply “lock it in” with a simple CTE at the start of the SQL.

Columns of interest

In my examples I only used a few of the allowable columns in a CTE Opthint. Here’s a list of as many as I have found:

Column NameRemarks
TABLE_CREATORNULL for all
TABLE_NAMENULL for all
CORRELATION_NAMENeeded if duplicate table names
ACCESS_TYPERSCAN, INDEX, INLIST, MULTI_INDEX
ACCESS_CREATORIndex Schema, NULL for all
ACCESS_NAMEIndex Name, NULL for all
JOIN_SEQJoin sequence number. 1 for first table.
JOIN_METHODNLJ, SMJ, HYBRID
PARALLELISM_MODECPU, IO, SYSPLEX
ACCESS_DEGREEDegree of required parallelism
JOIN_DEGREEDegree of required parallelism
TABNONormally never required. If used take the number from the PLAN_TABLE
QBLOCKNONormally never required. Must be used if duplicate table names and correlation Ids. Take the value from the PLAN_TABLE
PREFETCHS or L. Sequential or List Prefetch to be used. From this column on I have never used them!
QBLOCK_TYPE
MATCHING_PRED
HINT_TYPE
OPT_PARM

As usual, if you have any questions or ideas please drop me a line!

TTFN,

Roy Boxwell

Update: One of my readers pointed out that he uses these beasts and even had to open a PMR. Here I quote:

“I learned the hard way that CTE Hints were not honored when you did a rebind with APREUSE(WARN).  I reported this to IBM, they did fix it with UI68523, you want to be sure you have that.”

Another reader found that I had cut-and-pasted the DDL a bit too fast so all of the DDL got corrected where it was wrong (The table create and the second index create)

Yet another sent me an updated column list and so have done some changes to the list of columns used in the table.

It is Now Official!

IBM just released, on the 2022-06-24, an update on the AHA page DB24ZOS-I-717 that CTE inline hints are now supported with a pointer to the APAR PK04574 (Closed in 2015!!!). Now we must just wait for the docu update I guess!