Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2010
    Posts
    206

    Unanswered: DB2 Auto_Reorg in V10.1 FP3

    Hello friends,

    does anyone have some experiences with the auto_reorg function of DB2?

    i would like to activate this on a customer environment but there is nothing to find about experiences in the internet.

    hope to get some answers on this. thanks in advance.

    kind regards,

    db_n00b

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    I would not recommend this
    be very careful that you define the maintenance windows...
    It is documented in infocenter, just have a look....
    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

  3. #3
    Join Date
    Jan 2010
    Posts
    206
    Thanks for your reply.

    But why being careful if its online reorging?

  4. #4
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    it can use additional resources on your machine and potentially slow down you server
    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

  5. #5
    Join Date
    Jan 2010
    Posts
    206
    I checked your documentation (link) and i saw that you can set up the time of the reorgs with the control center or with the data studio ...
    but what is you only use console?

    please let me know

  6. #6
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    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
    Sep 2013
    Location
    India
    Posts
    246
    Command line process is as below:





    First set up your auto reorg xml file as per your need


    EXAMPLE
    Autoreorg.xml


    <?xml version="1.0" encoding="UTF-8" ?>
    - <DB2AutoReorgPolicy xmlns="http://www.ibm.com/xmlns/prod/db2/autonomic/config">
    <ReorgOptions dictionaryOption="Keep" indexReorgMode="Offline" useSystemTempTableSpace="false" />
    - <ReorgTableScope>
    <FilterClause>TABSCHEMA NOT LIKE 'SYS%'</FilterClause>
    </ReorgTableScope>
    </DB2AutoReorgPolicy>

    Create a maintenance window xml file as below as per your need

    EXAMPLE
    maintwindow.xml

    ?xml version="1.0" encoding="UTF-8"?>
    <DB2MaintenanceWindows
    xmlns="http://www.ibm.com/xmlns/prod/db2/autonomic/config" >
    <!-- Online Maintenance Window -->
    <OnlineWindow Occurrence="During" startTime="14:00:00" duration="06" >
    <!-- <DaysOfWeek>Mon Tue Wed Thu Fri</DaysOfWeek -->
    <DaysOfWeek>All</DaysOfWeek>
    <DaysOfMonth>All</DaysOfMonth>
    <MonthsOfYear>All</MonthsOfYear>
    </OnlineWindow>

    <!-- Offline Maintenance Window -->
    <OfflineWindow Occurrence="During" startTime="21:00:00" duration="23" >
    <DaysOfWeek>All</DaysOfWeek>
    <DaysOfMonth>All</DaysOfMonth>
    <MonthsOfYear>All</MonthsOfYear>
    </OfflineWindow>

    </DB2MaintenanceWindows>

    Use below 2 command to deploy corresponding policyfiles:

    call sysproc.automaint_get_policyfile( 'MAINTENANCE_WINDOW', 'maintwindow.xml' )

    call sysproc.automaint_get_policyfile( 'AUTO_REORG', 'autoreorg.xml' )


    look for successful / error messages in diag log.
    ssumit

  8. #8
    Join Date
    Apr 2012
    Posts
    156
    What version of DB2 are you running? If you are on a version prior to DB2 V10.1 I would not turn on auto reorg, only auto stats. On my DB2 V10+ systems I have been using it for a little over 6 months without any issues, and it has done a really good job. I have windows defined, the only issue is they can run past the window if the reorg has already started. Keep that in mind when defining your windows.

Posting Permissions

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