Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2004
    Posts
    55

    Unanswered: Question on reorgs

    I have a question concerning reorgs.

    We have a relatively new DB2 system (8.1) on Aix 5.1 and we are trying to institute regular reorgs via scripts.

    Does it matter if the index for a table or the table itself is reorganized first?

    If the script reorganizes a table then follows that with a reorganize indexes all for that table is that a problem?

    Right now the script issues in a loop

    db2 -v "reorg table SIEBEL.$i"

    followed by

    db2 -v "reorg indexes all for table SIEBEL.$i"


    Im wondering if that is a problem.

    Thanks in advance.

  2. #2
    Join Date
    Mar 2004
    Posts
    448
    When an index is organised first, you will read less pages, if you want
    to cluster the table data based on that index.
    There are also other advantage of doing the index reorg first, based on that you have a clean and concise index structure.

    regards.

    Mujeeb

  3. #3
    Join Date
    Sep 2003
    Posts
    237
    Way I see it is if you reorg a table, page#,slot# are changed for some records, rendering all indexes invalid; that's why in a classic reorg, when you do a table reorg, inexes are also reorganized afterwards automatically.
    mota

  4. #4
    Join Date
    Mar 2004
    Posts
    55
    Mota


    I am testing using the syntax

    db2 reorg table tablename allow no access

    is that considered classic?

    I then do a runstst with detailed and indexes all on the table and an index still shows as needing to be reorged.

  5. #5
    Join Date
    Sep 2003
    Posts
    237
    according to the first example under REORG INDEXES/TABLE in the COMMAND REFERENCE yours is a classic reorg. Why do you say your index needs a reorg; could you please post the snippet of your indexes indicating a reorg, from the reorgcheck report.
    mota

  6. #6
    Join Date
    Mar 2004
    Posts
    55
    Apparently the indexes would show an out of cluster situation iunless they were designated in the reorg command to be used to order the table data.
    thanks

  7. #7
    Join Date
    Mar 2004
    Posts
    448
    What I usually did , is that

    1. run the reorgchk command, it will automatically update the statistics.

    2. Reorg, if need arise.

    2. run the reorgchk command without updating the statistics.

    regards

    Mujeeb

  8. #8
    Join Date
    Sep 2003
    Posts
    237
    When you update stats, tables are locked out. My preference is RUNSTATS ON TABLE tab1 ALLOW WRTE ACCESS; REORGCHK CURRENT STATISTICS ON TABLE ......; then a classic reorg; and again RUNSTATS ON TABLE tab1 ALLOW WRTE ACCESS; because the table is reorged and indexes altered, particularly the TYPE2 indexes in V8, a final runstats is necessary.
    mota

  9. #9
    Join Date
    Mar 2004
    Posts
    55
    When doing an offline reorg, and you have a clustering index

    is it best or even necessary to speciify the clustering index when you do the reorg
    'reorg table using index'

  10. #10
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    "For an inplace table reorg, if a clustering index is defined on the table and an index is specified, it must be clustering index. If the inplace option is not specified, any index specified will be used. If you do not specify the name of an index, the records are reorganized without regard to order. If the table has a clustering index defined, however, and no index is specified, then the clustering index is used to cluster the table. You cannot specify an index if you are reorganizing an MDC table."

    Generally speaking, if you defined a clustering index, then that is the order you want to rows after a reorg and during insert operations, so I don't know why you would need to specify an index in the reorg statement if you have already defined a clustering index.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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