Results 1 to 10 of 10

Thread: reorgchk report

  1. #1
    Join Date
    Jun 2008
    Posts
    40

    Unanswered: reorgchk report

    Hi Guys,

    I am working on db2 luw version 8.1 on aix .


    I have generated a reorgchk report for the tables in my database , For a few of them i could see some "*" for some of the tables .

    To my understanding if for any formula for that table u see a "*" u can blindly do a reorg for it .

    Am I right ? or is it that even if you find a "*" its not compulsory that reorg is required for that table , is there some additional criteria to check that .

    Thanks
    Sandeep

  2. #2
    Join Date
    Jan 2010
    Posts
    335
    Hi,

    http://public.dhe.ibm.com/software/d...ages_0508I.pdf
    see section "Improving availability during table and index maintenance"

    Cheers

  3. #3
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    I would suggest some reading about the command itself before acting on any message
    try to understand what is going on
    REORGCHK
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  4. #4
    Join Date
    Jun 2008
    Posts
    40

    reorgchk report

    Hi ,

    Thanks for the link .

    But it doesn't answer my question .

    I still want to know if you see a "*" ,then should u chk any additional info or you can blindly do a reorg or not .

    Right now my concern is not with the position of the "*" and which option to use with reorg (clean up etc ) .

  5. #5
    Join Date
    Jan 2010
    Posts
    335
    If you want to, you can blindly reorg each table every day.

    Or you can spend a few second and check if a reorg is necessary or not. That's why przytula_guy and i provided you the links.

  6. #6
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    you should check what the * stands for and analyze if the cause is important for you
    in case it is for space reclamation - it might be you have space enough and this is a warning for you
    if clustering is low - it might be important and I decide todo a reorg..
    as indicated before : try to understand what is going on and how to act on this
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  7. #7
    Join Date
    Jun 2008
    Posts
    40

    reorgchk report

    Got it !!

    Thanks
    Sandeep

  8. #8
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Depending on your table/index definitions, you may not be able to get rid of all '*'. For example, for the index cluster ratio. So always reorganizing a table if some '*' occurs somewhere is most likely the wrong approach.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  9. #9
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by stolze View Post
    Depending on your table/index definitions, you may not be able to get rid of all '*'. For example, for the index cluster ratio. So always reorganizing a table if some '*' occurs somewhere is most likely the wrong approach.
    This is my experience also (and other issues besides index cluster ratio). IMO the reorgchk program is a piece of junk and cannot be relied upon to automatically initiate reorgs. This is not a big concern if there is a defined maintenance window and one can do classic reorgs (offline), but is a problem for inplace (online) reorgs, especially since they run asynchronously, and one does not want to have more than few running at any given time (which is hard to control with asynchronous background processes).

    The two main things you need to worry about for tables are clustering (if you have a clustering index and there have been a lot of inserts and deletes), and especially overflow pages. For indexes, badly fragmented indexes should be reorged if practical, although for an OLTP application you may not see a huge benefit.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  10. #10
    Join Date
    Dec 2011
    Posts
    27
    am not sure this will help you
    you can know the need of "reorg table" by checking reorg pending state of the table. if the value is greater than 3 execute reorg cmnd

    reorgpensql = "SELECT * FROM SYSIBMADM.ADMINTABINFO WHERE TABSCHEMA = 'schema_name' AND TABNAME='" & Tab_Name & "' AND REORG_PENDING = 'Y'"
    Set reorgpenrs = Conn.Execute(reorgpensql)
    if not reorgpenrs.eof then
    maxno = reorgpenrs("NUM_REORG_REC_ALTERS")
    if maxno > 2 Then
    Conn.Execute("call SYSPROC.ADMIN_CMD ('REORG TABLE " & Tab_Name & "')")
    End If
    End If

Posting Permissions

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