Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399

    Unanswered: reorg is too slow

    DB2 v9.5.1 ESE on AIX v6.1
    it is taking forever to reorganize one of the tables in our prod. database. Are there any ways to make it faster?

    We do it in the maintenance window and use the following commands:

    REORG INDEXES all for table <table_name> allow no access
    and then
    REORG TABLE <table_name> allow no access

    Table has 39 cols (incl. 5 varchar cols up to 1024b length), 73,990,776 rows, 7 indexes (not cluster), no LOBs and it’s in the 16K-page size tablespace.

    We are in v9.5 so making it partitioned will not help (as far as I know in v9.7 we can reorg table’s partitions separately).

    Thanks in advance
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  2. #2
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Just "reorg table schema.table". The table will be reorganized offline AND all the indexes go along. The separate "reorg indexes" is superfluous.

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by MarkhamDBA View Post
    DB2 v9.5.1 ESE on AIX v6.1
    Are you really still on fixpack 1 of Version 9.5? You certainly have a lot of guts.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Oct 2007
    Posts
    246
    hi
    continue with above topic
    wht is the major difference between
    cleanup only all and cleanup only pages -- it relates to NUM_EMPTY_LEAF and NUMRIDS_DELETED
    kindly suggest
    regds
    Paul

  5. #5
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    As per the information in the manual, the main difference is:

    The CLEANUP ONLY PAGES option will search for and free committed pseudo empty pages. A committed pseudo empty page is one where all the keys on the page are marked as deleted and all these deletions are known to be committed. The number of pseudo empty pages in an indexes can be determined by running RUNSTATS and looking at the NUM EMPTY LEAFS column in SYSCAT.INDEXES. The PAGES option will clean the NUM EMPTY LEAFS if they are determined to be committed.

    The CLEANUP ONLY ALL option will free committed pseudo empty pages, as well as remove committed pseudo deleted keys from pages that are not pseudo empty. < removed some info about merging pages > The number of pseudo deleted keys in an index , excluding those on pseudo empty pages, can be determined by running RUNSTATS and then selecting the NUMRIDS DELETED from SYSCAT.INDEXES. The ALL option will clean the NUMRIDS DELETED and the NUM EMPTY LEAFS if they are determined to be committed.

    IBM DB2 9.5 Information Center for Linux, UNIX, and Windows



    CLEANUP ONLY
    - PAGES will cleanup committed pseudo empty pages (pages on which all keys are marked deleted and committed) -> NUM_EMPTY_LEAFS (if committed)
    - ALL will do what PAGES option does plus cleanup committed pseudo deleted keys -> NUM_EMPTY_LEAFS + NUMRIDS_DELETED (if committed)

  6. #6
    Join Date
    Oct 2007
    Posts
    246
    thks for the reply
    i m sorry i still have a question i got abt pseudo empty pages
    but not able to get pseudo deleted keys --
    manual says that -- the total number of logically deleted RIDs stored in NUMRIDS_DELETED
    kindly suggest
    regds
    Paul

  7. #7
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    Quote Originally Posted by Marcus_A View Post
    Are you really still on fixpack 1 of Version 9.5? You certainly have a lot of guts.
    fixpack 3a. My mistake
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  8. #8
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Quote Originally Posted by Mathew_paul View Post
    but not able to get pseudo deleted keys --
    manual says that -- the total number of logically deleted RIDs stored in NUMRIDS_DELETED

    From the manual:
    NUMRIDS_DELETED is number of pseudo-deleted RIDs


    Regarding REORG with CLEANUP ONLY ALL:
    Some sections of the manual mention that it does the cleanup of pseudo-deleted keys and some others mention that it does the cleanup of pseudo-deleted RIDs. I'm not sure why... Keys and RIDs are not the same thing. For non-unique index, a RID can be marked as pseudo-deleted but not the key. I think pseudo-deleted key is the key for which all RIDs are marked deleted, but I'll need to double check this info.

  9. #9
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    I verified that:

    We don't keep a count of pseudo-deleted keys (keys in the index in which all the RIDs have been pseudo-deleted). We only have a count of pseudo-deleted RIDs (NUMRIDS_DELETED). If all the RIDs for a given key (assuming non-unique index) are deleted, then the key is removed from the index when the RIDs are all removed.

Posting Permissions

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