Results 1 to 8 of 8

Thread: reorg

  1. #1
    Join Date
    Mar 2014
    Posts
    14

    Unanswered: reorg

    Difference between reorg indexes all....and reorg table tabname.

    I was trying to understand if the reorg indexes all just does the reorg of indexes or also the table and vice versa.

  2. #2
    Join Date
    Mar 2014
    Posts
    14

    reorg

    We have this table which has about 86M rows, range by partition on single node DB. DB 10.5. The reorg tables about 6 hours to finish, any way we can improve the performance.
    we use
    reorg table tabname.

  3. #3
    Join Date
    Mar 2014
    Posts
    14

    reorg

    Are reorg indexes all for table and reorg table tabname different ?

    I mean does one do only reorg of indexes and not table and the later both table and indexes.

    The reason being the table with 80 M rows takes around 6 hrs to do a reorg if given reorg table where as reorg indexes all for table tabname finishes in 5 mins.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by db2_nube View Post
    I mean does one do only reorg of indexes and not table and the later both table and indexes.
    Yes, offline (classic) reorg of table will also reorg all the indexes.

    A reorg of a 80 million row table should not take that long. You might want to specify a temporary tablespace to do the reorg, especially if you have a clustered index defined:

    USE tbspace-name
    Specifies the name of a system temporary table space in which to store a temporary copy of the table being reorganized. If you do not provide a table space name, the database manager stores a working copy of the table in the table spaces that contain the table being reorganized.

    Make sure the system temporary tablespace specified has a decent sized bufferpool associated with it.
    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
    Mar 2014
    Posts
    14
    And the system temp tbsp needs to be the same bufferpool as the table tbsp and index tbsp ?

    Does it make a difference if I change the table to be built in higher bufferpool, I'm going to try and test it out anyways though.

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by db2_nube View Post
    And the system temp tbsp needs to be the same bufferpool as the table tbsp and index tbsp ?

    Does it make a difference if I change the table to be built in higher bufferpool, I'm going to try and test it out anyways though.
    No, it doesn't have to be same bufferpool. I don't know what you mean by "higher" bufferpool.
    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
    Mar 2014
    Posts
    14
    My bad I said it out wrong, I meant the page size for the tempspace.

    Also does it make a difference if the table resides in 4K tbsp and 32K tbsp while doing reorg and running queries ?

    I created a temp table in 32K tablespace and reran the reorg 16 partitions in parallel and finished in 40 mins, earlier it was taking 2 hrs.
    Last edited by db2_nube; 03-24-14 at 21:57. Reason: update

  8. #8
    Join Date
    Jul 2011
    Location
    USA
    Posts
    80
    PAGE SIZE of tablespace to which reorg required table belongs and temporary tablespace PAGE SIZE need to be same.

Tags for this Thread

Posting Permissions

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