How to Tell When a Table Reorg is Needed

Reorgs are one critical piece of both performance maintenance and maintaining how much disk space is used. In my first blog entry in this series, DB2 Basics: What is a Reorg, I talked about some purposes of reorgs. In this entry, I’m going to talk about how to determine what kind of reorg is needed on what tables. I will have a future blog entry to discuss index reorgs in detail. The advice I’m giving here is directed at single-partition databases with non-partitioned tables.

Reorg’s Place in the Database Maintenance Cycle

I run a standard maintenance cycle weekly. It consists of:

• RUNSTATS on all tables (yes, including system tables)
• REORGCHK on all tables
• REORGS of tables that indicate a need
• RUNSTATS on tables that have been REORGed
• rbind with the \all option
• flush package cache

REORGCHK

Reorgchk is a DB2 utility that calculates a number of formulas to determine what kinds of reorgs are needed on what tables. You can run reorgchk on a single table, on a schema, or on all tables in a database. While you can optionally have reorgchk update statistics, I do not recommend it. Reorgchk is supposed to detect the type of statistics that were last gathered and base what kind of statistics on that, but do you really want to take the chance with something as critical as RUNSTATS? Since I have scripts already from older versions, I collect runstats first, then run reorgchk.

The Reorgchk page in the Knowledge Center actually has a lot of good information on it. It tells you things like the recommended types of reorgs based on the output of reorgchk.

The simplest form of the reorgchk command is this:
db2 "reorgchk current statistics on table all" >reorgchk.out

Running that statement requires a database connection. The output may be extensive, especially if you have a significant number of tables.

When you run reorgchk, there are two sections – one for tables and one for the indexes on tables. To make things easier, those are covered separately below.

Tables

The table section of reorgchk looks something like this:

Table statistics:

F1: 100 * OVERFLOW / CARD < 5
F2: 100 * (Effective Space Utilization of Data Pages) > 70
F3: 100 * (Required Pages / Total Pages) > 80

SCHEMA.NAME                     CARD     OV     NP     FP ACTBLK    TSIZE  F1  F2  F3 REORG
----------------------------------------------------------------------------------------
Table: WSCOMUSR.ATTRIBUTE
                                   0      0      0     68      -        0   0   0   0 -**
Table: WSCOMUSR.ATTRTYPE
                                   7      0      1      1      -      448   0   - 100 ---
Table: WSCOMUSR.ATTRVAL
                               29097    653    623    624      -  2473245   2  99 100 ---
Table: WSCOMUSR.ATTRVALDESC
                               29013      6    874    874      -  3452547   0  99 100 ---
Table: WSCOMUSR.ATTRVALUE
                                   0      0      0   1386      -        0   0   0   0 -**
Table: WSCOMUSR.DMEXPLOG
                               34165      0    847   1056      -  3348170   0  79  80 --*
Table: DBAMON097.TABLE_HIST_CRON
                              256149      0   1652   2284      - 53535140   0  71  72 --*

The above output gives a lot of information including information on the size of the tables. There are three formulas that reorgchk calculates – F1, F2, and F3. They are described at the top of the reorgchk output, and calculated for each table. The column at the far right is what tells us that a table reorg is actually needed. Note that there are three values – either dashes - or asterisks *. Each of these represents one of the three formulas, in order. An asterisk indicates that a table reorg is needed for that table, based on the results of that specific query.

F1

F1 is described in the reorgchk header as:
F1: 100 * OVERFLOW / CARD < 5
What this formula is looking for is overflow records. In DB2 a row is stored contiguously on a single page. When actions like updates to VARCHAR columns cause a row to no longer fit on a page, DB2 will replace the original row on the page with a pointer to the new location for the row. If more than one relocation of the row is done, the original location is updated with the true location, so DB2 never has to do more than one hop to get from the pointer to the data. However, even that one hop means that the I/O (whether from bufferpool or from disk) is doubled, which can be detrimental to performance. A reorg will get rid of these pointers and ensure the data is in the location within the table where it is expected to be. This process involves updating the row’s rid, and thus indexes must be rebuilt or changed to match the new location.

Formula F1 looks to ensure that 5% or less of the total rows in the table are overflows. I’m going to share SQL I’ve used to calculate these values manually back in version 8.2 and 9.7. Use it at your own risk, and I have not verified it on 10.1 or 10.5.

For F1, this is a handy query to find tables that need reorgs:

select     substr(tabschema,1,18) as tabschema, 
        substr(tabname,1,30) as tabname, 
        card, 
        overflow, 
        case when card > 0 
            then decimal(100*(float(overflow)/float(card)),10,2) 
            else -1 
        end as F1 
    from syscat.tables 
    where overflow >0 with ur

I’m not suggesting you make use of the SQL above, but just providing it for information. It gives output that looks like this:

TABSCHEMA          TABNAME                        CARD                 OVERFLOW             F1
------------------ ------------------------------ -------------------- -------------------- ---------------
SYSIBM             SYSTABLES                                     11298                  141            1.24
SYSIBM             SYSCOLUMNS                                    36135                   87            0.24
SYSIBM             SYSINDEXES                                     6404                   22            0.34
SYSIBM             SYSPLANDEP                                     1666                    1            0.06
WSCOMUSR           CLEANCONF                                       163                    1            0.61
SYSTOOLS           HMON_ATM_INFO                                  1841                 1138           61.81
SYSTOOLS           ADMIN_MOVE_TABLE                              18947                   26            0.13

F2

F2 is described in the reorgchk header as:
100 * (Effective Space Utilization of Data Pages) > 70

While that’s a good human description of what reorgchk is looking for, it’s not very technical. This formula tells us how effectively DB2 is making use of space on pages. As a result, it is likely to be flagged for extremely small tables. I eliminate tables with a cardinality of 0 from reorgs, because they are sometimes flagged on this formula, and it wastes time.

I don’t have SQL that works for this formula. I played with it some, and don’t have it. Basically what DB2 does for this one is to calculate how many bytes would be used based on the cardinality and the average row size, and then calculates the size of the table based on the current number of pages, and looks for how close the value based on pages is to the value based on cardinality. Some table geometries will always cause this one to be flagged.

F3

F3 is described in the reogchk header as:
100 * (Required Pages / Total Pages) > 80

Basically DB2 is checking to make sure that the total pages in the table is not more than 20% higher than the required number of pages.

The sql calculation is much easier here, again use this SQL at your own risk:

select  substr(tabschema,1,18) as tabschema, 
        substr(tabname,1,30) as tabname, 
        npages, 
        fpages, 
        case 
            when fpages >0 then decimal(100 * (float(npages)/float(fpages)),5,2) 
            else -1 
        end as F3 
from syscat.tables 
where type='T' 
        and npages >1 
        and fpages >1 
with ur

The output looks like this:

TABSCHEMA          TABNAME                        NPAGES               FPAGES               F3
------------------ ------------------------------ -------------------- -------------------- ---------------
SYSIBM             SYSTABLES                                      1363                 1363          100.00
SYSIBM             SYSCOLUMNS                                     2112                 2114           99.90
SYSIBM             SYSINDEXES                                      658                  660           99.69
SYSIBM             SYSVIEWS                                         57                   57          100.00
SYSIBM             SYSVIEWDEP                                       53                   53          100.00
SYSIBM             SYSPLAN                                          68                   68          100.00
SYSIBM             SYSPLANDEP                                       51                   51          100.00
SYSIBM             SYSSECTION                                       85                   87           97.70

Table Reorg Decisions

One approach for scripters of reorgs is to parse out the table name for any row with a *. That actually works in this table reorg section, but does not in the index section I’ll talk about in a future post. There’s a boon in recent versions for people who want to script this. The REORGCHK_TB_STATS procedure can be used to return this information in a more friendly format for scripting. It is executed like this:

db2 "call REORGCHK_TB_STATS('T','SYSIBM.SYSCOLUMNS')"


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

  TABLE_SCHEMA                                                                                                                     TABLE_NAME                                                                                                                       DATAPARTITIONNAME                                                                                                                CARD                 OVERFLOW             NPAGES               FPAGES               ACTIVE_BLOCKS        TSIZE                F1          F2          F3          REORG
  -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- ----------- ----------- ----------- -----
  SYSIBM                                                                                                                           SYSCOLUMNS                                                                                                                                                                                                                                                                       36135                   87                 2112                 2114                   -1              8021970           0          94         100 ---

  1 record(s) selected.

But that’s not all. That alone is just a different format of data to parse through for the values. Running that populates a table called SESSION.TB_STATS that is session specific that you can now query to get particular values like this:

select  substr(table_schema,1,18) as tabschema, 
        substr(table_name,1,30) as tabname, 
        f1, 
        f2, 
        f3, 
        reorg 
    from SESSION.TB_STATS

TABSCHEMA          TABNAME                        F1          F2          F3          REORG
------------------ ------------------------------ ----------- ----------- ----------- -----
SYSIBM             SYSCOLUMNS                               0          94         100 ---

  1 record(s) selected.

I learned that nifty trick over at Phillip Carrington’s blog. I haven’t tried out the rest of his methodology, but thought that tip was good.

Calling REORGCHK_TB_STATS this way should collect data for all tables:
call REORGCHK_TB_STATS('T','ALL')

Selecting Table Reorg Syntax

Any syntax choices at this point are not determined by the reorgchk output but by other properties of the reorg. There are two basic options you have – classic or inplace. The former is also referred to as offline. Inplace can be referred to as online, but it is not fully online (see When is a Reorg Really Online?)

The Knowledge Center has a good page on reorg syntax.

Classic Table Reorgs

The most obvious disadvantage of classic reorgs is that the table is unavailable for significant portions of them. Because of this and the availability requirements of the e-commerce databases I work with, I don’t often do this kind of reorg. You can specify ALLOW READ ACCESS to allow some access to the table while the reorg is occurring. One of the “advantages” of a classic reorg is that you can specify a temporary tablespace for the reorg to use. Be careful, though – the reorg may require a good three times the table size if you specify a temporary tablespace, and should only require around two times the table size if you do not specify one. Classic reorgs can also be used to reorganize LOB data – which you should not be doing on a regular basis, but may have to do to free up space from deleted LOBs or to inline LOBs.

Inplace Table Reorgs

I run inplace table reorgs on a regular basis. They are largely online. They also have the advantage of only moving small amounts of data at time so you don’t have to have two to three times the size of your table in available space. The big disadvantage here is in transaction log space. Inplace reorgs eat transaction log space like nothing else, so be prepared. Usually, the ALLOW WRITE ACCESS keywords are specified for inplace reorgs. While it involves not releasing any empty pages at the end of the table at the end of the reorg, specifying NOTRUNCATE TABLE does make for the most online reorg possible.

Keep in mind that an inplace reorg is an asynchronous operation – the command will return right away, but the reorg will continue to run in the background. This means you can easily tank your server if you improperly script these and reorg every table in your database at the same time. It is critical to control for how many online reorgs are running at once. There are options on the reorg command that will allow you to stop or to pause running inplace reorgs.

Running online reorgs write data to the history file. You can also use SNAPTAB_REORG to query reorg statuses for running and recently completed reorgs. db2pd also provides data. I think that monitoring and querying reorg statuses will have to be a separate blog entry.