Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2010
    Posts
    4

    Question Unanswered: DB2 Estimating REORG Times - DB2

    How can the Reorg times be estimated in DB2?
    Is there a formula or script or select that might help
    to give me an idea?
    Thanks,

    JMaria

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Unfortunately, there is no formula. It is much like trying to estimate the time you will run a marathon: you won't know your result until you actually run it for the first time.

  3. #3
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    You may not be able to estimate, but there are some things you can do to try and improve reorg time:
    DB2 Database for Linux, UNIX, and Windows


    Some additional info to what's documented above:

    - DB2 will use private sorts for index key sorting regardless of the value of intra_parallel. If intra_parallel is ON, DB2 can take advantage of multiple CPUs on the server (it will use multiple subagents). See the following link regarding intra_parallel if you're using v9.5:
    IBM DB2 9.5 Information Center for Linux, UNIX, and Windows

    - Increasing sortheap and making sure sheapthres is "at least sortheap x the number of processors used" can be helpful. DB2 may still need to spill the sort so increasing tempspace's bufferpool can help to minimize disk I/O. It's also recommended to have multiple containers for your tempspace, each container on a different disk.

    If this is a 64-bit instance on AIX, verify that ulimit(data) is set to unlimited or some high value (ulimit -d).

  4. #4
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Quote Originally Posted by n_i View Post
    run a marathon
    Have you ever done it?

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by db2girl View Post
    Have you ever done it?
    No, but how difficult could that be?

  6. #6
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    No idea but I think it would require a lot of training.

  7. #7
    Join Date
    Aug 2010
    Posts
    4
    There is a way to get it from the history prior past reorgs that gets save on some db2 tables!! i just got an average estimation base on that!! and it did work fine!! thanks for your responses!

  8. #8
    Join Date
    Oct 2014
    Posts
    1

    Smile Estimating REORG ETA

    Quote Originally Posted by noche67 View Post
    How can the Reorg times be estimated in DB2?
    Is there a formula or script or select that might help
    to give me an idea?
    Thanks,

    JMaria
    Estimating the time a REORG may take to execute is something that would depend on many things.
    However, although it would be very complicated to be extremely accurate, it would be possible to raise the ETA for a REORG.

    You could try this query bellow and it should provide you with the information you may be looking for.
    (I hope this could be helpful somehow)

    -------------------------------------------------------------------------------------------------------------------
    db2 "WITH tmp AS
    (
    SELECT

    RTRIM (SUBSTR (tabschema,1,10)) AS schema, RTRIM (SUBSTR (tabname,1,29)) AS table, reorg_start AS start_time, reorg_end AS end_time,
    TIMESTAMPDIFF (4, CAST ((COALESCE (reorg_end,current_timestamp) - reorg_start) AS CHAR(22))) AS min_running,
    RTRIM (SUBSTR (reorg_status,1,10)) AS status, reorg_phase AS current_or_last_phase,

    CASE reorg_max_counter
    WHEN 0 THEN 100
    ELSE ROUND ((reorg_current_counter / (reorg_max_counter * 100)),0)
    END AS progress_percentage

    FROM sysibmadm.snaptab_reorg
    WHERE DATE (reorg_start) > (current_date - 3 day)
    )
    SELECT
    tmp.*, ROUND ((tmp.min_running * 100) / NULLIF (tmp.progress_percentage,0),0)
    AS estimated_time_min
    FROM tmp ORDER BY tmp.start_time"
    -------------------------------------------------------------------------------------------------------------------

Posting Permissions

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