Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2009
    Posts
    24

    Unanswered: DB2 reorg question

    I am trying to determine which tables need to be reorged. Using REORGCHK, I have a few tables that shows a * in F2. So, I ran reorg and runstats on the first one. REORGCHK still showed the same thing so I started looking that the formulas. The column for F2 has a 0 in it. Obviously, that is below the the 70 threshold. When I solve the formula by hand, I get 73.54. Now, I could be calculating that with the wrong values, but it seems to match on tables that have values so I think I am doing it correctly. So, what is causing DB2 to resolve that to a 0. Out of our 50 or so tables, I have about 5 that are showing this. Any help would be very much appreciated. REORGCHK results for this table are below:


    SCHEMA.NAME CARD OV NP FP ACTBLK TSIZE F1 F2 F3 REORG

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

    Table: SHADOW.COUNTS

    36576 0 531 531 - 1572768 0 0 100 -*-

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

    What is the output of this query:

    Code:
    SELECT t.stats_time,t.pctfree,s.pagesize 
    from syscat.tables as t 
    inner join syscat.tablespaces as s on (t.tbspaceid = s.tbspaceid)
    where tabschema = 'SHADOW' and tabname = 'COUNTS'
    Andy

  3. #3
    Join Date
    Dec 2009
    Posts
    24
    Oh, sorry. Thanks for your reply. I meant to add that and forgot to type it. We are using 9.5 Express-C on windows.

    The output of the command is:

    STATS_TIME PCTFREE PAGESIZE
    -------------------------- ------- -----------
    2012-08-14-18.36.17.984000 25 4096

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    REORGCHK has some bugs. I have noticed problems with tables that have a small number of rows. Sometimes you must use the following clause on RUNSTATS to elimiate all flags:
    ... AND DETAILED INDEXES ALL"
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Dec 2009
    Posts
    24
    Thanks Marcus. We do use that clause on our runstats.

    Typically, how do you handle the reorgs when you can't trust REORGCHK completely? Do you usually just ignore those tables and do them every once in a while? Just trying to understand the best way to handle table/index reorgs.

    Thanks for all the help guys

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by Brandon765 View Post
    Thanks Marcus. We do use that clause on our runstats.

    Typically, how do you handle the reorgs when you can't trust REORGCHK completely? Do you usually just ignore those tables and do them every once in a while? Just trying to understand the best way to handle table/index reorgs.

    Thanks for all the help guys
    If the tables are relatively small, and you have a maintenance window, do an offline reorg on a scheduled interval regardless of whether you think they need it or not. You can do some testing to see how long the reorgs take, but typically one can reorg a table with 10,000 rows in about 1-2 seconds.

    For very large tables, where there may not be enough time to reorg all of them every time, one can do a them on a rotating basis or even not at all if don't think there is a problem (based on your knowledge of the application).

    One of the key factors for table reorg is the number (and percent) of overflow rows. If there is an index defined as "Clustered," then the cluster ratio of the (one and only one) clustered index can determine whether the table needs to be reorged (if cluster ratio is < 90% for example).

    Or you can just reorg the indexes only, based on your knowledge of the application and whether significant index splits are likely (an index with an identity column that is always increasing, or a timestamp index that is always increasing may not need a reorg, since page splits are less likely.

    Good idea to also reorg the catalog tables (sysibm schema, and not the syscat views).

    Online (inplace) reorgs are more complex, since they are asynchronous and you need to make sure that not too many are running at the same time (as soon as you submit an online reorg, control returns to your script even though the online reorg is not completed (runs as background task).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Dec 2009
    Posts
    24
    Thanks a ton Marcus. I already have a script that does the major tables that I need to reorg. I was just trying to upgrade the script to be smarter about which it chose to do and the REORGCHK results weren't making sense. Unfortunately, an offline maintenance window isn't really an option for us as we have pretty heavy traffic 24 hours a day. Thanks again for your help and ARWinner.

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    It may sound strange, but I would not bother with reorgs at all unless there were an evident performance problem that would be known to be solved by a reorg. From my experience I can say there are very few extreme cases where regular reorgs are truly necessary.
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

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