How to Tell When an Index Reorg is Needed

Posted by

My earlier post on table reorgs covered Reorg’s place in a database maintenance cycle and running reorgchk. If you haven’t already read it, check it out: How to Tell When a Table Reorg is Needed

INDEXES

After the section on tables, REORGCHK output has a section with details on each index for each table that REORGCHK looked at.

The index section of reorgchk looks something like this:

Index statistics:

F4: CLUSTERRATIO or normalized CLUSTERFACTOR > 80
F5: 100 * (Space used on leaf pages / Space available on non-empty leaf pages) > MIN(50, (100 - PCTFREE))
F6: (100 - PCTFREE) * (Amount of space available in an index with one less level / Amount of space required for all keys) < 100
F7: 100 * (Number of pseudo-deleted RIDs / Total number of RIDs) < 20
F8: 100 * (Number of pseudo-empty leaf pages / Total number of leaf pages) < 20

SCHEMA.NAME                 INDCARD  LEAF ELEAF LVLS  NDEL    KEYS LEAF_RECSIZE NLEAF_RECSIZE LEAF_PAGE_OVERHEAD NLEAF_PAGE_OVERHEAD  PCT_PAGES_SAVED  F4  F5  F6  F7  F8 REORG
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Table: WSCOMUSR.ATTR
Index: SYSIBM.SQL120212212515070
                                170     1     0    1     0     170            8             8                566                 566                0  98   -   -   0   0 -----
Index: WSCOMUSR.I0001203
                                170     2     0    2     0     170           22            22                516                 516                0  62 147   -   0   0 *----
Index: WSCOMUSR.I0001204
                                170     1     0    1     0       6            4             4                710                 710                0  98   -   -   0   0 -----
Index: WSCOMUSR.I0001464
                                170     1     0    1     0       2            2             2                822                 822                0  96   -   -   0   0 -----
Index: WSCOMUSR.X_ATTR_IX1
                                170     4     0    2     0     170           66            66                236                 236                0  97 110   -   0   0 -----
Index: WSCOMUSR.X_ATTR_IX2
                                170     2     0    2     0     170           30            30                392                 392                0  95 178   -   0   0 -----
Index: WSCOMUSR.X_ATTR_IX3
                                170     2     0    2     0     170           25            25                442                 442                0  62 158   -   0   0 *----
Table: WSCOMUSR.ATTRIBUTE
Index: SYSIBM.SQL120212212515260
                                  0    16    16    2     0       0           12            12                476                 476                0 100 105   -   0 100 ----*
Index: WSCOMUSR.I0000019
                                  0    26    26    2     0       0           16            16                442                 442                0 100 105   -   0 100 ----*
Index: WSCOMUSR.I0000298
                                  0     5     5    2     0       0           11            11                516                 516                0 100 105   -   0 100 ----*
Table: WSCOMUSR.BUSEVENT
Index: SYSIBM.SQL120212212516210
                            1.1e+07 48067  1414    4 8e+05 1.1e+07           12            12                948                 948                0  68  62 266   7   2 *-*--
Index: WSCOMUSR.BUSEVENT_IX1
                            1.1e+07 25184     0    3     0 9048618           10            10               1048                1048                0  89  91   1   0   0 -----
Index: WSCOMUSR.I0001104
                            1.1e+07 10123     0    3    39       1            3             3               1894                1894                0 100  86   4   0   0 -----
Table: WSCOMUSR.DMEXPLOG
Index: SYSIBM.SQL120212212521010
                              34214  4717  4439    4  1406   34214            8             8                566                 566                0  93  59 +++   3  94 ----*
Index: WSCOMUSR.DMEXPLOG_IX1
                              34358  3252  3036    4    25   33629           10            10                516                 516                0 100  83 +++   0  93 ----*
Index: WSCOMUSR.I0001198
                              34292  9708  8957    5  4285   34267           36            36                416                 416                0  76  55 +++  11  92 *---*
Index: WSCOMUSR.I0001199
                              34332  8933  8377    4   163   33960           22            22                354                 354                0  90  50 +++   0  93 -*--*

It's nice to use a wide screen when viewing this output on a server - a 72-character wide window may drive you insane.

Note that the format is very similar to tables. There are numerical details about each index. There are the results of several forumulas - this time F4 through F8, and there is a final column on the right with flags when a reorg MAY be needed.

The output is a bit different in that there are sections for each table that the indexes are grouped in. This always made the output harder for parsing for scripting, though there are better options in more recent versions for scripting. It is not just the line formatting, but in some ways, the decisions to be made are a bit more complicated.

Let's work through the formulas one by one.

F4

Formula F4 is described in the index section header as:
F4: CLUSTERRATIO or normalized CLUSTERFACTOR > 80

This formula or flag is looking at how well clustered the table is over a particular index. The values of CLUSTERRATIO or CLUSTERFACTOR are pulled directly from the SYSCAT.INDEXES system catalog view. IF we want the table clustered over this particular index, the way to get rid of this flag is to reorganize the table ON the index. Think about this one. In many cases it is impossible to have a table clustered over all of the indexes at once. We cannot blindly reorg based on this flag. If we go that direction, we keep reorganizing the table over and over again on one index after another.

The important thing with F4 is to decide, on a table by table basis, whether we want the data clustered over an index. If we do, and if there is some reason you cannot or do not want to define that index as a clustering index, then and only then you should reorg the table on that index if the F4 formula is flagged. Reorging a table on an index can increase your reorg time.

F4 is the only flag in the index section that has the possibility to prompt a table reorg. The other formulas will indicate various kinds of index reorgs.

Index Reorgs

Unless you are using table partitioning, you may only reorg all indexes for a table. You may not reorg a specific index for a table without reorging the remaining indexes. There are multiple types of index reorgs that can be done. Index reorgs are kind of online (see the end of this article for how online), and do use resources. If you are lucky enough to do offline, classic table reorgs, indexes are rebuilt as a part of them. They are not rebuilt as a part of online table reorgs. If you are only doing online table reorgs, you will want to make sure you're also doing the appropriate index reorgs. I will talk about each of the remaining formulas and then revisit what kinds of reorgs are suggested by each flag.

F5

Formula F5 is described in the index section header as:
F5: 100 * (Space used on leaf pages / Space available on non-empty leaf pages) > MIN(50, (100 - PCTFREE))

This formula is trying to calculate how much of the space allocated to non-empty leaf pages is used, and checking to see if that is greater than either 50% or the space we expect to be used on pages given the value of PCTFREE for this index. If you need a reminder on what leaf pages are, see DB2 Basics: What is an index?

PCTFREE is a value that is set for each index and is respected in certain situations like reorg and load. It tells DB2 to leave a certain portion of each index leaf page open so that index keys can be inserted in proper place in the index without having to allocate new pages. It is set to 10% by default.

If we have a high percentage of space open on our non-empty leaf pages, it may indicate that our indexes can benefit from some consolidation and cleanup.

The following should work to calculate F5, if nleaf is greater than one and ((nleaf-num_empty_leafs-1) * (PAGESIZE-96)) does not return zero. Remember that any calculations provided are for reference and understanding only, and they are not guaranteed to be correct. The IBM Knowledge Center page on REORGCHK describes the calculations in detail, but does not provide SQL for performing them.

select (100 * ((fullkeycard * ((select SUM(CASE WHEN A.typename = 'VARCHAR' THEN A.AVGCOLLEN - 2 ELSE A.AVGCOLLEN END + CASE WHEN NULLS = 'Y' THEN 0 ELSE 0 END ) + 0 FROM SYSCAT.COLUMNS A, syscat.indexcoluse B WHERE A.colname=B.colname and B.indschema=i.indschema and B.indname=i.indname and A.tabschema=i.tabschema and A.tabname=i.tabname)+9)) + (card-fullkeycard) * 5) / ((nleaf-num_empty_leafs-1) * (PAGESIZE-96)))
from syscat.indexes i
        join syscat.tables t    on i.tabschema=t.tabschema
                                and i.tabname=t.tabname
        join syscat.tablespaces ts      on i.tbspaceid = ts.TBSPACEID
where i.indschema = 'WSCOMUSR' and i.indname='IPF00008' with ur

F6

Formula F6 is described in the index section header as:
F6: (100 - PCTFREE) * (Amount of space available in an index with one less level / Amount of space required for all keys) < 100

What this formula does is checks to see if an index with one less level would have enough space for all data, respecting PCTFREE. Usually that's a significant change, so this formula I don't find flagged as often as some of the others. It's another that is hard to get a query working to calculate, so I don't have that available. Check out the IBM Knowledge Center page on REORGCHK, and if you have SQL that works for it, post in the comments below so that others can enjoy your SQL prowess!

F7

Formula F7 is described in the index section header as:
100 * (Number of pseudo-deleted RIDs / Total number of RIDs) < 20

With type 2 indexes, index keys are essentially marked as deleted rather than actually deleted. These are called pseudo-deleted RIDs. F7 checks to make sure that less than 20% of the RIDS in the table are marked for deletion.

Here is the query for calculating this one - it's pretty easy:

select  substr(indname,1,30) as indname, 
        decimal(100*(FLOAT(NUMRIDS_DELETED)/FLOAT(NUMRIDS_DELETED+INDCARD)),10,5) as F7 
    from syscat.indexes 
    where tabschema='WSCOMUSR' 
        and tabname='ATTRVALDESC' 
    with ur

INDNAME                        F7
------------------------------ ------------
SQL120212212515340                  0.06914
I_ATTRVALDESC01                     0.06255
X_ATTRVALDESC_IX1                   0.18416
I0001468                            0.03293
I0001470                            0.03293

  5 record(s) selected.

This one is likely to be flagged if a lot of data is deleted from the table.

F8

Formula F8 is described in the index section header as:
F8: 100 * (Number of pseudo-empty leaf pages / Total number of leaf pages) < 20

This one is looking for entire pages where all of the RIDs have been marked as deleted. It checks to make sure that no more than 20% of the pages in the index are fully marked as deleted. Again, writing the SQL to calculate this yourself is not hard:

select  substr(indname,1,30) as indname, 
        decimal(100*(FLOAT(NUM_EMPTY_LEAFS)/FLOAT(NLEAF)),10,5) 
    from syscat.indexes 
    where tabschema='WSCOMUSR' 
        and tabname='CATENTDESC' 
    with ur

INDNAME                        F8
------------------------------ ------------
SQL120212212517170                  0.00000
I0000304                            0.00000
I0001196                            7.88177
I0001248                            0.00000
IPF00002                            9.30232

  5 record(s) selected.

Index Reorg Decisions

The cheat of just looking for lines in reorgchk with a * simply does not work for indexes. There are too many complicated decisions to be made. Luckily for scripting, we have similar constructs as we did for table reorgs.

Start with REORGCHK_IX_STATS like this:

$ db2 "call REORGCHK_IX_STATS('T','SYSIBM.SYSCOLUMNS')"


  Result set 1
  --------------

  TABLE_SCHEMA                                                                                                                     TABLE_NAME                                                                                                                       INDEX_SCHEMA                                                                                                                     INDEX_NAME                                                                                                                       DATAPARTITIONNAME                                                                                                                INDCARD              NLEAF                NUM_EMPTY_LEAFS      NLEVELS     NUMRIDS_DELETED      FULLKEYCARD          LEAF_RECSIZE         NONLEAF_RECSIZE      LEAF_PAGE_OVERHEAD   NONLEAF_PAGE_OVERHEAD PCT_PAGES_SAVED F4          F5          F6          F7          F8          REORG
  -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------- -------------------- -------------------- ----------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------- --------------- ----------- ----------- ----------- ----------- ----------- -----
  SYSIBM                                                                                                                           SYSCOLUMNS                                                                                                                       SYSIBM                                                                                                                           INDCOLUMNS01                                                                                                                                                                                                                                                                     18841                  289                    0           3                    0                18841                   40                   40                  710                   710               0          95          90          19           0           0 -----
  SYSIBM                                                                                                                           SYSCOLUMNS                                                                                                                       SYSIBM                                                                                                                           INDCOLUMNS02                                                                                                                                                                                                                                                                     18841                   32                    0           2                    0                   14                   18                   18                  822                   822               0          79          93          -1           0           0 *----
  SYSIBM                                                                                                                           SYSCOLUMNS                                                                                                                       SYSIBM                                                                                                                           INDCOLUMNS03                                                                                                                                                                                                                                                                     18841                   32                    0           2                    0                    1                    4                    4                  822                   822               0         100          92          -1           0           0 -----

  3 record(s) selected.

  Return Status = 0

Again this first part returns good data, but not in an easily scriptable format. But the real power is in the population of the SESSION.IX_STATS table, which can be queried for each element like this:

select  substr(table_schema,1,18) as tabschema,
        substr(table_name,1,30) as tabname,
    substr(index_schema,1,18) as indschema,
    substr(index_name,1,30) as indname,
        f4,
        f5,
        f6,
    f7,
    f8,
        reorg
    from SESSION.IX_STATS;

TABSCHEMA          TABNAME                        INDSCHEMA          INDNAME                        F4          F5          F6          F7          F8          REORG
------------------ ------------------------------ ------------------ ------------------------------ ----------- ----------- ----------- ----------- ----------- -----
SYSIBM             SYSCOLUMNS                     SYSIBM             INDCOLUMNS01                            95          90          19           0           0 -----
SYSIBM             SYSCOLUMNS                     SYSIBM             INDCOLUMNS02                            79          93          -1           0           0 *----
SYSIBM             SYSCOLUMNS                     SYSIBM             INDCOLUMNS03                           100          92          -1           0           0 -----

  3 record(s) selected.

Now you should have the data you need in the format you need it to make decisions. These recommendations come straight from the IBM Knowledge Center page on REORGCHK - a very useful page.

  • If the results of the calculations for Formula 1, 2 and 3 do not exceed the bounds set by the formula and the results of the calculations for Formula 4, 5 or 6 do exceed the bounds set, then index reorganization is recommended.
  • If only the results of the calculations Formula 7 exceed the bounds set, but the results of Formula 1, 2, 3, 4, 5 and 6 are within the set bounds, then cleanup of the indexes using the CLEANUP option of index reorganization is recommended.
  • If the only calculation result to exceed the set bounds is the that of Formula 8, then a cleanup of the pseudo empty pages of the indexes using the CLEANUP PAGES option of index reorganization is recommended.

Your reorg command for indexes will always start with REORG INDEXES ALL FOR TABLE and include the table name and one of the syntax options outlined above. There aren't online or offline choices or other options for indexes. Unless you're using the CLEANUP ONLY or CLEANUP ONLY PAGES options, what DB2 is doing is building new indexes side-by-side with the old it indexes. It will then acquire a Z lock on the whole table to make the switch. The switch may be fairly quick. But that's also a super-exclusive lock, which blocks all table access, including shared and read-only locks. I believe this even includes application using the UR or Uncommitted Read isolation level.

Share your own thoughts and experiences with index reorgs!

Lead Db2 Database Engineer and Service Delivery Manager , XTIVIA
Ember is always curious and thrives on change. Working in IT provides a lot of that change, but after 17 years developing a top-level expertise on Db2 for mid-range servers and more than 7 years blogging about it, Ember is hungry for new challenges and looks to expand her skill set to the Data Engineering role for Data Science. With in-depth SQL and RDBMS knowledge, Ember shares both posts about her core skill set and her journey into Data Science. Ember lives in Denver and work from home for XTIVIA, leading a team of Db2 DBAs.

8 comments

  1. This is another wonderful write up by you. Thanks for sharing. Is there any way to find directly the size of an index ?

    1. In my mind, the easiest, quickest way is still to use reorgchk or one of the related functions. Using DMS or AST tablespaces, you cannot directly look at a file on disk to see the size if that’s what you’re asking.

  2. How can I calculate the nleaf (number of active leaf pages in the index)?
    I really need this information to create virtual indexes.
    Thank you.

    1. SYSCAT.INDEXES shows nleaf. It is populated when you do runstats on indexes. You can query it using something like this:

      db2 “select substr(indschema,1,10) as indschema, substr(indname,1,30) as indname, nleaf from syscat.indexes”

  3. Here is some sample sql which will provide a list of top 10 indexes based on size which are greater than 100 mb –

    SELECT Substr(indschema,1,6) AS indschema,
    Substr(indname,1,18) AS indname,
    indextype,
    Substr(tabschema,1,6) AS tabschema,
    Substr(tabname,1,18) AS tabname,
    avgleafkeysize,
    avgnleafkeysize,
    indcard,
    and int((avgleafkeysize + 11)*indcard*2/(1024*1024)) AS space_mb,
    compression,
    lastused
    FROM syscat.indexes
    WHERE tabschema NOT LIKE ‘SYS%’
    AND int((avgleafkeysize + 11)*indcard*2/(1024*1024)) > 102400
    ORDER BY 9 desc
    FETCH first 10 ROWS only WITH UR;

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.