Results 1 to 15 of 15
  1. #1
    Join Date
    Aug 2012
    Posts
    14

    Unanswered: Large Tables Reorg Issue

    Hi All,

    I need some recommendation on large table reorg issue. We have db2 9.7 and fixpack 8 version with DPF enviornment on AIX server.

    We have some tables with billon of rows and max table size is 375GB.
    It is going to be online reorg with indexes.
    The process we normally use is :
    1. REORG TABLE <schema.table> INPLACE ALLOW READ ACCESS;
    2. REORG INDEXES ALL FOR TABLE <schema.table> ALLOW READ ACCESS;
    3. RUNSTATS ON TABLE <schema.table> WITH DISTRIBUTION AND DETAILED INDEXES ALL;

    Question:
    Is use of a system temporary table space recommended?

    Your immediate attention will be appreciated !!

  2. #2
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    No. You code "INPLACE" that means no temp space will be used. The only time you specify TEMP space in a reorg is when you have more than 1 TEMP tablespace defined for a pagesize and you want to control which 1 to use. In general: TEMP tablespaces must be there and big enough. DB2 will decide when to use it or not.
    Somewhere between " too small" and " too large" lies the size that is just right.
    - Scott Hayes

  3. #3
    Join Date
    Aug 2012
    Posts
    14
    Thank you for your response.

  4. #4
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    Quote Originally Posted by dr_te_z View Post
    The only time you specify TEMP space in a reorg is when you have more than 1 TEMP tablespace defined for a pagesize and you want to control which 1 to use...... DB2 will decide when to use it or not.
    Are you sure?

  5. #5
    Join Date
    Aug 2012
    Posts
    14
    Our Two tables online reorg completed successfully without using temp tbs. The row count was 120934683 and 108298538.

    Now we are facing another issue with range partititon table, online reorg fails and now it is decieded to take an offline reorg. Row count is 198725843

    Can someone please expalin pros and cons of this offline reorg ?

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by Tarn View Post
    Our Two tables online reorg completed successfully without using temp tbs. The row count was 120934683 and 108298538.

    Now we are facing another issue with range partititon table, online reorg fails and now it is decieded to take an offline reorg. Row count is 198725843

    Can someone please expalin pros and cons of this offline reorg ?
    An online reorg will take a long time, and do a lot of DB2 recovery logging. I would do an offline reorg of one partition at a time and see if that works. See the REORG command for details on how to do this.
    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
    Aug 2012
    Posts
    14
    Thanks for your response.

    Just an update that our Offline reorg completed successfully on range partitions table.
    Table size was 84GB.

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by Tarn View Post
    Thanks for your response.

    Just an update that our Offline reorg completed successfully on range partitions table.
    Table size was 84GB.
    Did you also reorg the indexes? With offline reorg, that is a separate process.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  9. #9
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by db2girl View Post
    Are you sure?
    Please share your doubts. I was sure but you make me feel insecure.....
    Somewhere between " too small" and " too large" lies the size that is just right.
    - Scott Hayes

  10. #10
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    Quote Originally Posted by dr_te_z View Post
    Please share your doubts. I was sure but you make me feel insecure.....

    As far as I know, for an offline (classic) table reorg, tempspace is optional (you have to specify it using USE parameter). So, db2 will not use tempspace during an offline table reorg unless you tell it to. DB2 may use tempspace during an index rebuild phase (even if USE is not specified) and there is no option to tell db2 which one to use. Not sure if anything changed in v10.x.

  11. #11
    Join Date
    Jul 2011
    Posts
    6
    Quote Originally Posted by db2girl View Post
    As far as I know, for an offline (classic) table reorg, tempspace is optional (you have to specify it using USE parameter). So, db2 will not use tempspace during an offline table reorg unless you tell it to. DB2 may use tempspace during an index rebuild phase (even if USE is not specified) and there is no option to tell db2 which one to use. Not sure if anything changed in v10.x.
    I can attest to this under 9.7.0.5. I did a classic reorg without specifying a tempspace and the system tempspace used for rebuilding indexes filled.

    Now I have an outstanding question to IBM support asking why a container on a second storage path was not used. When later reducing the tablespaces I also noticed that space was only ever returned to one of two storage paths.

    I'm trying to find a clear and detailed description of how automatic storage with multiple paths works. Something like Oracle's dba_extents identifying storage containers (fileid in Oracle-speak) would be helpful.

    D

  12. #12
    Join Date
    Nov 2011
    Posts
    334
    Quote Originally Posted by Marcus_A View Post
    Did you also reorg the indexes? With offline reorg, that is a separate process.
    Hi ,Mr Marcus_A,
    I dont understand what does this mean exactly ("With offline reorg, that is a separate process").
    but i just want to verrify "if OP use offline reorg instead of online reorg ,then the "reorg indexes" command is not neccessary, because the offline reorg will rebuild all the indexess of the table that to be reorged ?
    Is it right ?
    thx

  13. #13
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by fengsun2 View Post
    Hi ,Mr Marcus_A,
    I dont understand what does this mean exactly ("With offline reorg, that is a separate process").
    but i just want to verrify "if OP use offline reorg instead of online reorg ,then the "reorg indexes" command is not neccessary, because the offline reorg will rebuild all the indexess of the table that to be reorged ?
    Is it right ?
    thx
    An offline reorg will automatically rebuild all the indexes. That is not true of an online (in place) reorg. See the manual for more information.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  14. #14
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by db2girl View Post
    As far as I know, for an offline (classic) table reorg, tempspace is optional (you have to specify it using USE parameter). So, db2 will not use tempspace during an offline table reorg unless you tell it to. DB2 may use tempspace during an index rebuild phase (even if USE is not specified) and there is no option to tell db2 which one to use. Not sure if anything changed in v10.x.
    As I always understoot:
    - use the shortest command i.e. "db2 reorg table sample".
    - an offline reorg will take place (so all indexes will be rebuilt as well)
    - db2 will examine the tablespace to see if there is enough free space for a full copy of the table. (just free space or continuous free space I do not know)

    If so, the whole table is moved inside the tablespace and no temp space is used. If NOT, then db2 will stream the whole table from regular table space to temp, free-up the space in the regular tablespace and stream it back again. This could ask a lot from your temp-space definitions and therefore you can specify which temp space to use.

    This is what I remember of the manuals/classes/presentations I consumed in the past years. Please correct me when I am wrong. Anybody out there with a nice lab-environment to test it all out?
    Somewhere between " too small" and " too large" lies the size that is just right.
    - Scott Hayes

  15. #15
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    My high-level understanding of an offline (classic) reorg as of v9.7:

    Let's say table sample is defined in userspace1.


    Reorg command: "db2 reorg table sample"
    If table sample doesn't have any indexes, db2 will not use any tempspace at all. db2 will create a copy of sample in userspace1 during the build phase.
    If table sample have indexes, db2 may use tempspace if sort needs to be spilled during the sort/index rebuid phase.


    Reorg command: "db2 reorg table sample use tempspace2"
    db2 will create a copy of sample in tempspace2 during the build phase and may use any tempspace if sort needs to be spilled during the sort/index rebuid phase.

Posting Permissions

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