Results 1 to 7 of 7
  1. #1
    Join Date
    May 2003
    Posts
    369

    Unanswered: how often do you run reorgs in your db2 environments?

    Hello fellow DBAs and DB2 UDB gurus:

    Just out of curiosity how often are you faced with reorg tables in your production data center DB2 UDB environment? We get nitely loads that fill up tablespaces quite a bit and I have to run reorg tables on a constant basis. Any way around this?

    Scott
    DB2 UDB DBA

  2. #2
    Join Date
    Apr 2003
    Location
    Florida
    Posts
    79
    You may be able to sort input data before LOAD into clustering index order - I think that would cut-down on needing to reorg....As long as you maintain enough space.

    May want to try taking image copies of the indexes and reorging them only as needed instead of rebuilding after each load (expensive - if you are even doing this.... lol@me! )

    If this is mainframe DB2 - I have some queries I can give you that report any table or index spaces that need reorg....

    Rick

  3. #3
    Join Date
    Sep 2002
    Posts
    456

    Re: how often do you run reorgs in your db2 environments?

    Use the reorgchk utility to see which tables need reorg? I am not sure if the nightly data is same for each table every day but running this utility might give you an idea.

    dollar

    Originally posted by mixxalot
    Hello fellow DBAs and DB2 UDB gurus:

    Just out of curiosity how often are you faced with reorg tables in your production data center DB2 UDB environment? We get nitely loads that fill up tablespaces quite a bit and I have to run reorg tables on a constant basis. Any way around this?

    Scott
    DB2 UDB DBA

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I assume you are using load insert and the rows are added rather than being replaced. You could allocate larger TS containers so that they don't run out of space every night.

    But the need to reorg also depends on the clustering sequence as defined by your clustering index (this is the physical order of the rows that you desire in the TS). If you are always inserting new rows at the end of table, and that is the correct physical sequence desired, then maybe you don't need to reorg every night (if you have enough space).

    But if you have any non-clustering indexes, these indexes may be badly in need of reorganization with load insert. One way around this is to drop any non-clustering indexes before the load, then recreate the indexes after the load. This not only "reorgs the index" but it also improves the efficiency of the data load.

    You can run REORGCHK to help you decide if nightly reorgs are needed.

    In version 8, you can reorg just the indexes (all or none) but the drop/create method might work better with large loads. In DB2 UDB for OS/390 (any version) you can reorg any one index.
    --------------------------------
    Oops! sorry for repititive info. I didn't see the other posts while composing mine.
    Last edited by Marcus_A; 05-20-03 at 15:51.

  5. #5
    Join Date
    Apr 2003
    Posts
    191

    Re: how often do you run reorgs in your db2 environments?

    Hi Scott,

    you can create indexes with pctfree, and in vs. 8 you can use multidimensional clustering. Either will help to curb the need for reorgs.

    At my place we do reorgs on nightly and weekly schedules. Besides of the annoying work there are some points about that as well. First of all reorg on a completely unsorted table may take really long, in one instance, we had some 6 hours instead of 30 minutes after introducing a clustered index. Second, we had a page header error someplace in a table, obviously on historic data nobody else would normally use. Reorg uncovered the problem (well, this is not guaranteed to uncover any data problem).

    Johann

    Originally posted by mixxalot
    Hello fellow DBAs and DB2 UDB gurus:

    Just out of curiosity how often are you faced with reorg tables in your production data center DB2 UDB environment? We get nitely loads that fill up tablespaces quite a bit and I have to run reorg tables on a constant basis. Any way around this?

    Scott
    DB2 UDB DBA

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: how often do you run reorgs in your db2 environments?

    Johann ,.... IMHO MDCs should be used to support your query processing rather than it being used to avoid REORGs ....

    Cheers

    Sathyaram

    Originally posted by jsander
    Hi Scott,

    you can create indexes with pctfree, and in vs. 8 you can use multidimensional clustering. Either will help to curb the need for reorgs.

    At my place we do reorgs on nightly and weekly schedules. Besides of the annoying work there are some points about that as well. First of all reorg on a completely unsorted table may take really long, in one instance, we had some 6 hours instead of 30 minutes after introducing a clustered index. Second, we had a page header error someplace in a table, obviously on historic data nobody else would normally use. Reorg uncovered the problem (well, this is not guaranteed to uncover any data problem).

    Johann

  7. #7
    Join Date
    Apr 2003
    Posts
    191

    Re: how often do you run reorgs in your db2 environments?

    Hi Sathyaram,

    very true, but sometimes you can actually have it both ways.

    Johann

    Btw now (with version 8) that online reorg is in perfect reach, there is even less reason not to reorg.

    Originally posted by sathyaram_s
    Johann ,.... IMHO MDCs should be used to support your query processing rather than it being used to avoid REORGs ....

    Cheers

    Sathyaram

Posting Permissions

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