Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Aug 2010
    Posts
    43

    Unanswered: How to I see tables that need reorg?

    Hi,

    I need your help.

    Where I see that my db need reorg on tables? or runstats?

    The db2diag.log say me that need reorg and runstat!!! but I don't know wich ones.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Which DB2 version and OS are you using?

    For DB2 LUW:

    To check the last runstats:

    Select tabschema,tabname,stats_time from syscat.tables where type = 'T'

    To check if reorg is necessary, look up REORGCHK in the manual.

    Andy

  3. #3
    Join Date
    Aug 2010
    Posts
    43
    Thanks.
    But de reorgchk I can run on line? I will not get problems such as Locks?


    I read and found that "you can find table need reorg if clasterratio in sysibm.sysindexe is < 90 row overflow in sysibm.systables is > 5 need to run reorg"

    What do you thing?

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    If your runstats are up to date, the running REORGCHK will not be a problem and will actually give you valid results. It does those checks for you.

    Andy

  5. #5
    Join Date
    Jan 2010
    Posts
    335
    The reorgchk command works with the statistics information. So if you use 'current statistics' with the reorgchk, then you don't have any issue with locks.

    Your quotation shows indicators for reorg. DB2 lists the indicators with the output of the reorgchk command.

  6. #6
    Join Date
    Aug 2010
    Posts
    43
    Ok thanks, this forum is great.

    and what about reorg command? I can run on line? maybe I will get locks? or the command is exclusive

  7. #7
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I suggest you read the manual on the REORG command and in doing reorgs in general.

    Andy

  8. #8
    Join Date
    Aug 2010
    Posts
    43
    Ok thanks, what does the result of reorgchk means??

  9. #9
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Read the manual for REORGCHK and it will tell you what everything means.

    Andy

  10. #10
    Join Date
    May 2009
    Posts
    4
    If its v9.1 you can use following cmd to list the tables needed reorg.

    db2 "select TABSCHEMA, TABNAME from SYSIBMADM.ADMINTABINFO where REORG_PENDING = 'Y'"

  11. #11
    Join Date
    Aug 2010
    Posts
    43
    Thak you very much, I will try.

  12. #12
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by sabari321 View Post
    If its v9.1 you can use following cmd to list the tables needed reorg.

    db2 "select TABSCHEMA, TABNAME from SYSIBMADM.ADMINTABINFO where REORG_PENDING = 'Y'"
    I have some tables where reorgchk determines that a reorg is needed, but this say nothing. I am not sure where this will be useful.

    Andy

  13. #13
    Join Date
    Aug 2010
    Posts
    43
    Quote Originally Posted by ARWinner View Post
    I have some tables where reorgchk determines that a reorg is needed, but this say nothing. I am not sure where this will be useful.

    Andy


    Yes, I tried and this query say nothing, but I need a script for get all tables that need reorg because I have more than 1000 tables and It`s very hard get all tables with this command.

    You Hava any idea?

  14. #14
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    This is what I do:

    Code:
    call sysproc.reorgchk_tb_stats('T','ALL')
    
    
    select card,tsize,f1,reorg,table_schema,table_name from session.tb_stats where reorg <> '---' order by tsize
    Andy

  15. #15
    Join Date
    Aug 2010
    Posts
    43
    Quote Originally Posted by ARWinner View Post
    This is what I do:

    Code:
    call sysproc.reorgchk_tb_stats('T','ALL')
    
    
    select card,tsize,f1,reorg,table_schema,table_name from session.tb_stats where reorg <> '---' order by tsize
    Andy
    Thanks, but this sp work with "current statistics"?...I will try

Posting Permissions

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