0

I did a reorgchk and it showed a few indexes needed reorg. So i ran this script:

FORCE APPLICATIONS ALL;
CONNECT TO DOMINOS;
-- [SYSIBM].[SYSTABLES].[INDTABLES01]: [*----];
REORG INDEXES ALL FOR TABLE SYSIBM.SYSTABLES REBUILD;
-- [SYSIBM].[SYSPLAN].[INDPLAN03]: [*----];
REORG INDEXES ALL FOR TABLE SYSIBM.SYSPLAN REBUILD;
-- [SYSIBM].[SYSVIEWDEP].[INDVIEWDEP01]: [*----];
REORG INDEXES ALL FOR TABLE SYSIBM.SYSVIEWDEP REBUILD;
-- [SYSIBM].[SYSPLANAUTH].[INDPLANAUTH02]: [*----];
REORG INDEXES ALL FOR TABLE SYSIBM.SYSPLANAUTH REBUILD;
-- [SYSIBM].[SYSVARIABLES].[INDVARIABLES03]: [*----];
REORG INDEXES ALL FOR TABLE SYSIBM.SYSVARIABLES REBUILD;
-- [SYSIBM].[SYSDATAPARTITIONS].[INDDATAPARTITIONS03]: [*----];
REORG INDEXES ALL FOR TABLE SYSIBM.SYSDATAPARTITIONS REBUILD;
-- [SYSIBM].[SYSROUTINES].[INDROUTINES10]: [*----];
REORG INDEXES ALL FOR TABLE SYSIBM.SYSROUTINES REBUILD;
-- [SYSIBM].[SYSDEPENDENCIES].[INDDEPENDENCIES03]: [*----];
REORG INDEXES ALL FOR TABLE SYSIBM.SYSDEPENDENCIES REBUILD;
-- [SYSIBM].[SYSCOLUMNS].[INDCOLUMNS02]: [*----];
REORG INDEXES ALL FOR TABLE SYSIBM.SYSCOLUMNS REBUILD;
-- [SYSIBM].[SYSPLANDEP].[INDPLANDEP01]: [*----];
REORG INDEXES ALL FOR TABLE SYSIBM.SYSPLANDEP REBUILD;
RUNSTATS ON TABLE SYSIBM.SYSTABLES WITH DISTRIBUTION AND DETAILED INDEXES ALL;
RUNSTATS ON TABLE SYSIBM.SYSPLAN WITH DISTRIBUTION AND DETAILED INDEXES ALL;
RUNSTATS ON TABLE SYSIBM.SYSVIEWDEP WITH DISTRIBUTION AND DETAILED INDEXES ALL;
RUNSTATS ON TABLE SYSIBM.SYSPLANAUTH WITH DISTRIBUTION AND DETAILED INDEXES ALL;
RUNSTATS ON TABLE SYSIBM.SYSVARIABLES WITH DISTRIBUTION AND DETAILED INDEXES ALL;
RUNSTATS ON TABLE SYSIBM.SYSDATAPARTITIONS WITH DISTRIBUTION AND DETAILED INDEXES ALL;
RUNSTATS ON TABLE SYSIBM.SYSROUTINES WITH DISTRIBUTION AND DETAILED INDEXES ALL;
RUNSTATS ON TABLE SYSIBM.SYSDEPENDENCIES WITH DISTRIBUTION AND DETAILED INDEXES ALL;
RUNSTATS ON TABLE SYSIBM.SYSCOLUMNS WITH DISTRIBUTION AND DETAILED INDEXES ALL;
RUNSTATS ON TABLE SYSIBM.SYSPLANDEP WITH DISTRIBUTION AND DETAILED INDEXES ALL;

Then ran reorgchk again, only to find out they still need a reorg (scroll to the right):

Index: SYSIBM.INDCOLUMNS02
                               8212    14     0    2     0      15           19            19                822                 822                0  79  97   -   0   0 *----
--
Index: SYSIBM.INDDATAPARTITIONS01
                                214     3     0    2     0     214           34            34                710                 710                0  75 132   -   0   0 *----
--
Index: SYSIBM.INDDATAPARTITIONS03
                                214     3     0    2     0     214           31            31                628                 628                0  75 120   -   0   0 *----
--
Index: SYSIBM.INDDATAPARTITIONS05
                                214     3     0    2     0     214           31            31                628                 628                0  75 120   -   0   0 *----
--
Index: SYSIBM.INDDEPENDENCIES01
                               1691    46     0    3     0    1691           82            82                318                 318                0  75  89  72   0   0 *----
--
Index: SYSIBM.INDDEPENDENCIES03
                               1691    12     0    2     0     601           45            45                476                 476                0  78  93   -   0   0 *----
--
Index: SYSIBM.INDPLAN01
                                373     5     0    2     0     373           30            30                516                 516                0  56  98   -   0   0 *----
--
Index: SYSIBM.INDPLAN03
                                373     4     0    2     0     373           24            24                710                 710                0  56 117   -   0   0 *----
--
Index: SYSIBM.INDPLANAUTH01
                                499     8     0    2     0     499           43            43                566                 566                0  67 102   -   0   0 *----
Index: SYSIBM.INDPLANAUTH02
                                499     4     0    2     0     373           22            22                822                 822                0  70 120   -   0   0 *----
--
Index: SYSIBM.INDROUTINES01
                               1079    15     0    2     0    1079           36            36                566                 566                0  80  96   -   0   0 *----
Index: SYSIBM.INDROUTINES02
                               1079    17     0    2     0    1079           42            42                476                 476                0  67  93   -   0   0 *----
Index: SYSIBM.INDROUTINES03
                               1079    13     0    2     0     957           35            35                516                 516                0  67  97   -   0   0 *----
--
Index: SYSIBM.INDROUTINES06
                               1079    25     0    2     0    1079           69            69                262                 262                0  67  90   -   0   0 *----
--
Index: SYSIBM.INDROUTINES08
                               1079     7     0    2     0    1079           13            13                496                 496                0  59 104   -   0   0 *----
Index: SYSIBM.INDROUTINES09
                               1079     3     0    2     0     241            6             6                710                 710                0  80 111   -   0   0 *----
Index: SYSIBM.INDROUTINES10
                               1079    13     0    2     0     957           35            35                516                 516                0  67  97   -   0   0 *----
--
Index: SYSIBM.INDTABLES01
                                709     8     0    2     0     709           26            26                822                 822                0  69 105   -   0   0 *----
--
Index: SYSIBM.INDTABLES05
                                709     9     0    2     0     709           32            32                566                 566                0  69 100   -   0   0 *----
--
Index: SYSIBM.INDVARIABLES03
                                122     2     0    2     0     122           21            21                666                 666                0  58 103   -   0   0 *----
--
Index: SYSIBM.INDVIEWDEP01
                                934    19     0    2     0     934           56            56                476                 476                0  63  91   -   0   0 *----
--
Index: SYSIBM.INDVIEWDEP03
                                934     6     0    2     0     306           38            38                666                 666                0  65 100   -   0   0 *----
--

As you can see, they all show an exceed (*) in the F4 formula, and according to the documentation:

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.

|improve this question
1

This can happen as Formula 4 is about the cluster ratio. This is the correlation of the index sequence to the sequence of the data. The documentation also states (same page)

The clustering ratio of an index should be greater than 80 percent. When multiple indexes are defined on one table, some of these indexes have a low cluster ratio. (The index sequence is not the same as the table sequence.) This cannot be avoided. Be sure to specify the most important index when reorganizing the table.

This is why you have multiple formulas to determine the need (and urgency) of a reorg.

|improve this answer
  • Wow, completely missed the "When multiple indexes are defined..." part... Thank you! – Lucas Sep 25 '16 at 21:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.