Results 1 to 15 of 15
  1. #1
    Join Date
    Dec 2008
    Location
    Chennai
    Posts
    111

    Unanswered: db2 9.7 Reorg Table

    Hi
    DB2 9.7, FP 3a/ Win 2008 R2

    I have a LARGE type tablespace and contain couple of big tables with around 80 million rows. Each has around 6 indexes in different tablespace (LARGE type).
    I am going to do the offline REORG for this tables and want to use the temporary tablespace (USERTEMP).

    ### REORG TABLE XYZ USE USERTEMP;

    Also I am lookig to recover some space back as the current HWM is too high.

    My question is,
    1. Does the USERTEMP tablespace must be LARGE type TBS?
    2. Will this rebuild the indexes too?

    Please advise
    Thanks, valan.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    From the syntax diagram for CREATE TABLESPACE, you cannot create a "LARGE" SYSTEM TEMPORARY tablespace. You do need one that is the same pagesize of the tablespace that the table resides in. It will not reorg your indexes at the same time. You need to reorg those separately. It does rebuild the indexes in the sense that the pointers to the rows get changed to the new layout of the table data.

    Andy

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I believe that an offline reorg of a table will automatically rebuild (reorg) all the indexes also.
    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
    Dec 2008
    Location
    Chennai
    Posts
    111

    reorg

    My LARGE TBS page size is 4K. The table size is around 40GB, Indexes also smilar size.
    If I use the TEMPSPACE (4k page), will the table and indexes will fit in this TBS? (4K page TSB size limit is 64GB)


    Thanks.

  5. #5
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by Marcus_A View Post
    I believe that an offline reorg of a table will automatically rebuild (reorg) all the indexes also.
    You believe, I'm sure

    What I am not sure of, but would be nice to test in this case:
    drop the 4k usertemp
    create a 32k usertemp (I assume there are NO 8 & 16k usertemps).
    Now when you do your REORG it will use the 32K tempspace which can be much larger.

    Hmmmmm... suppose this works... Why the *beeb* do we need 4, 8 & 16k temp-tablespaces???? Maybe we never "needed" them but just created them out of a habit

  6. #6
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    the problem is that if this 4K_temp is the only with 4K size pages - you can not drop it
    if both exist (4K-32K)- you can not force usage of one or another
    we have the same problem with a table in 32K ts - and join/order/group is using 4K temp and hitting max limit - (see older post) - not easy to force the usage of this 32K temp
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  7. #7
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by przytula_guy View Post
    the problem is that if this 4K_temp is the only with 4K size pages - you can not drop it
    if both exist (4K-32K)- you can not force usage of one or another
    we have the same problem with a table in 32K ts - and join/order/group is using 4K temp and hitting max limit - (see older post) - not easy to force the usage of this 32K temp
    This is true for a query, but not for a reorg--which is what the OP is asking about. If you specify a System Temporary Tablespace on the reorg command, it will use the one you requested (if it is the same pagesize as that of the table).


    My LARGE TBS page size is 4K. The table size is around 40GB, Indexes also smilar size.
    If I use the TEMPSPACE (4k page), will the table and indexes will fit in this TBS? (4K page TSB size limit is 64GB)
    The limits for the table and indexes are separate. You can have table and indexes that are bigger than 64GB in a 4K tablespace. The table alone cannot exceed 64GB. The index size is either 64GB or 2048GB depending on the tablespace type (DMS/SMS).

    Andy

  8. #8
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    tables as large as these should be partitioned.
    it will improve your query performance and maintenance time (reorg, runstats).

    might be a good idea to place them in separate tablespaces too but it depends...
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  9. #9
    Join Date
    Dec 2008
    Location
    Chennai
    Posts
    111

    thanks

    Thanks for all your suggestions, I will try this on our test system first.

    Regards, valan.

  10. #10
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Quote Originally Posted by ARWinner View Post
    This is true for a query, but not for a reorg--which is what the OP is asking about. If you specify a System Temporary Tablespace on the reorg command, it will use the one you requested (if it is the same pagesize as that of the table).

    Just to add:
    Table reorg will use a tempspace specified on the reorg command. If you happen to have two tempspaces of the same page size (not sure why someone would do that but I've seen it) and rebuilding of the indexes require a tempspace due to the spilling of sorts, then it will be chosen in a round robin (just for the index rebuild phase).

  11. #11
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by MarkhamDBA View Post
    tables as large as these should be partitioned.
    it will improve your maintenance time (reorg, runstats).
    working/testing with that right now. Not as simple as "presented on the whiteboard".
    To really gain REORG time you must define ALL your indexes as partitioned.
    When your partitioning column is not part of your PK definition you'll have to make choices:

    - The unique PK index remains global. bye bye performance gain because that index has to be rebuilt completely when you do a reorg (on data partition)
    - drop the PK constraint and drop the unique-ness of your index. bye bye FK constraints because your table does not have a PK anymore. Works, but not as designed.

    But if you manage to convert all your indexes to partitioned the performance gain during "reorg data partition" ís impressive.

  12. #12
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by db2girl View Post
    If you happen to have two tempspaces of the same page size (not sure why someone would do that but I've seen it)
    A good reason would be to place them on different file systems so that when you reorg a table on one filesystem you can use the temp space on an other filesyatem to speed thing up.

    Andy

  13. #13
    Join Date
    Oct 2007
    Posts
    246
    Quote Originally Posted by db2girl View Post
    Just to add:
    Table reorg will use a tempspace specified on the reorg command. If you happen to have two tempspaces of the same page size (not sure why someone would do that but I've seen it) and rebuilding of the indexes require a tempspace due to the spilling of sorts, then it will be chosen in a round robin (just for the index rebuild phase).
    hi

    db2 9.7 FP5 aix , as you said its true we have 4 Temp tbspace with same page size and its switching to next tempspace as you said round robin,

    1) We monitor its switching the temp tablespac at BUILT phase: it was offline reorg on table
    2) Round robin means after which part its switching i mean as i said 4 temp tbspace of 4 k : first it started for temp space was monitor the FS there was lot of space then it switched to next tht FS was also having lot of space then on third it switched with was having less space and it broke i want to on wht bases its switching

    the table is having 2 to 3 indexes not sure need to chk

    regds
    Paul

  14. #14
    Join Date
    Mar 2003
    Posts
    280
    A bit off topic, but it is my impression that reorg will run faster if performed in the same tablespace as the table. Starting with 9.7 HWM is no longer as important as before, so one might concider doing the reorg in the tablespace. Just a thought
    --
    Lennart

  15. #15
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by lelle12 View Post
    A bit off topic, but it is my impression that reorg will run faster if performed in the same tablespace as the table. Starting with 9.7 HWM is no longer as important as before, so one might concider doing the reorg in the tablespace. Just a thought
    That assumes that the tablespace was first created in 9.7. If created in an earlier release and upgraded, HWM is still a problem.
    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
  •