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