Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2007
    Posts
    11

    Unanswered: Reorgchk values does not change after running reorg for tables, index and runstats

    We are able to run the reorg/runstats on the following tables and indexes in our environment successfully but even after the successful completion of re-org/runstats on the tables and indexs, when we try to check the same via REORGCHK command, again it shows that the tables needs to be reorged.

    Could you pl. provide if there is an alternate way to do the re-org/runstats to solve this issue?



    $ db2 get connection state



    Database Connection State



    Connection state = Connectable and Connected

    Connection mode = SHARE

    Local database alias = H02695I1

    Database name = H02695I1

    Hostname =

    Service name =



    The Following snapshot refers -- Doing the Reorgchk for Sony I1 database and Listed down the tables that requires reorg/runstats.



    $ db2 "CALL REORGCHK_TB_STATS('T','ALL')" | grep '*' | awk '{print "REORG TABLE " $1 "." $2 ";\n" "RUNSTATS ON TABLE " $1 "." $2 ";"}'

    REORG TABLE HREDADM.PS_H_DEPT1_TBL;

    RUNSTATS ON TABLE HREDADM.PS_H_DEPT1_TBL;

    REORG TABLE HREDADM.PS_H_DEPT_TMP;

    RUNSTATS ON TABLE HREDADM.PS_H_DEPT_TMP;

    REORG TABLE HREDADM.PS_H_DLT_EE_ATTRIB;

    RUNSTATS ON TABLE HREDADM.PS_H_DLT_EE_ATTRIB;

    REORG TABLE HREDADM.PS_H_DLT_ROLEUSER;

    RUNSTATS ON TABLE HREDADM.PS_H_DLT_ROLEUSER;

    REORG TABLE HREDADM.PS_H_EE_ATTRIB;

    RUNSTATS ON TABLE HREDADM.PS_H_EE_ATTRIB;

    REORG TABLE HREDADM.PS_H_EE_ATTRIB_ALL;

    RUNSTATS ON TABLE HREDADM.PS_H_EE_ATTRIB_ALL;

    REORG TABLE HREDADM.PS_H_MASSINSRT_TMP;

    RUNSTATS ON TABLE HREDADM.PS_H_MASSINSRT_TMP;

    REORG TABLE HREDADM.PS_H_STG_EE_ATTRIB;

    RUNSTATS ON TABLE HREDADM.PS_H_STG_EE_ATTRIB;

    REORG TABLE HREDADM.PS_H_TREE_FLAT;

    RUNSTATS ON TABLE HREDADM.PS_H_TREE_FLAT;





    Below snap shows the successful completion of reorg for table & indexes and runstats for the table HREDADM.PS_H_DEPT1_TBL





    $ db2 reorg table HREDADM.PS_H_DEPT1_TBL

    DB20000I The REORG command completed successfully.

    $ db2 reorg indexes all for table HREDADM.PS_H_DEPT1_TBL

    DB20000I The REORG command completed successfully.

    $ db2 runstats on table HREDADM.PS_H_DEPT1_TBL with distribution and detailed indexes all

    DB20000I The RUNSTATS command completed successfully.



    Below snap shows the reorgchk analysis after runnig the reorg / runstats for the table



    $ db2 reorgchk update statistics on table HREDADM.PS_H_DEPT1_TBL



    Doing RUNSTATS ....





    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: HREDADM.PS_H_DEPT1_TBL

    HREDADM PS_H_DEPT1_TBL 48295 0 9668 9668 - 27479856 0 70 100 -*-


    ----------------------------------------------------------------------------------------



    Index statistics:



    F4: CLUSTERRATIO or normalized CLUSTERFACTOR > 80

    F5: 100 * (KEYS * (ISIZE + 9) + (CARD - KEYS) * 5) / ((NLEAF - NUM EMPTY LEAFS -1) * (INDEXPAGESIZE - 96) > MIN(50, (100- PCTFREE))

    F6: (100 - PCTFREE) * ( FLOOR[ (100 - min(10, pctfree)) / 100 * (indexPageSize - 96) / (ISIZE + 12) ] ** (NLEVELS - 2) ) * (indexPageSize - 96) / (KEYS * (ISIZE + 9) + (CARD - KEYS) * 5) < 100

    F7: 100 * (NUMRIDS DELETED / (NUMRIDS DELETED + CARD)) < 20

    F8: 100 * (NUM EMPTY LEAFS / NLEAF) < 20



    SCHEMA NAME CARD LEAF ELEAF LVLS ISIZE NDEL KEYS F4 F5 F6 F7 F8 REORG

    -------------------------------------------------------------------------------------------------

    Table: HREDADM.PS_H_DEPT1_TBL

    HREDADM PS0H_DEPT1_TBL 48295 779 0 3 49 0 48295 68 90 7 0 0 *----

    HREDADM PS1H_DEPT1_TBL 48295 417 0 3 22 0 48295 96 89 25 0 0 -----

    HREDADM PS2H_DEPT1_TBL 48295 444 0 3 24 0 48295 100 89 22 0 0 -----

    HREDADM PS3H_DEPT1_TBL 48295 514 0 3 29 0 48295 88 89 17 0 0 -----

    HREDADM PS4H_DEPT1_TBL 48295 514 0 3 29 0 48295 98 89 17 0 0 -----

    HREDADM PS_H_DEPT1_TBL 48295 378 0 3 19 0 48295 100 89 30 0 0 -----

    -------------------------------------------------------------------------------------------------


    CLUSTERRATIO or normalized CLUSTERFACTOR (F4) will indicate REORG is necessary

    for indexes that are not in the same sequence as the base table. When multiple

    indexes are defined on a table, one or more indexes may be flagged as needing

    REORG. Specify the most important index for REORG sequencing.



    Tables defined using the ORGANIZE BY clause and the corresponding dimension

    indexes have a '*' suffix to their names. The cardinality of a dimension index

    is equal to the Active blocks statistic of the table.



    Thanks,

    Nambi

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    What is the PCTFREE for the table? Check the PCTFREE Column in syscat.tables
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Dec 2007
    Posts
    11
    Hi,



    'PS_H_DEPT1_TBL' 20
    'PS_H_DEPT_TMP' 20
    'PS_H_DLT_EE_ATTRIB' -1
    'PS_H_DLT_ROLEUSER' 20
    'PS_H_EE_ATTRIB' 10
    'PS_H_EE_ATTRIB_ALL' '-1'
    'PS_H_MASSINSRT_TMP' '-1'
    'PS_H_STG_EE_ATTRIB' '-1'
    'PS_H_TREE_FLAT' '-1'

  4. #4
    Join Date
    Dec 2007
    Posts
    11
    Hi,
    This is the PCTFRee values for the tables.

    'PS_H_DEPT1_TBL' 20
    'PS_H_DEPT_TMP' 20
    'PS_H_DLT_EE_ATTRIB' -1
    'PS_H_DLT_ROLEUSER' 20
    'PS_H_EE_ATTRIB' 10
    'PS_H_EE_ATTRIB_ALL' '-1'
    'PS_H_MASSINSRT_TMP' '-1'
    'PS_H_STG_EE_ATTRIB' '-1'
    'PS_H_TREE_FLAT' '-1'

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Another reason I have seen for the * in F2 is the row width. Say, you have a row with width of 2500 in a 4K tablespace, the utilization will be less than 70%. Obviously REORG cannot 'solve' this problem.

    For a * in F4, I wouldn't worry. You cannot have a table clustered on more than one index. In your case, it is PS_H_DEPT1_TBL and therefore PS0H_DEPT1_TBL can be ignored.


    HTH

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •