Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Dec 2002
    Location
    Madrid - Spain
    Posts
    422
    Provided Answers: 1

    Unanswered: tables that need reorg or runstat in DB2 V8.

    Hi Colleagues,
    I have DB2 V8 on AIX 5.3, I need to know which are the tables that need reorg or runstat.

    It is very helpful if you can send an example.

    thanks and greetings.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Check the manual for RUNSTATS and REORGCHK.

    Andy

  3. #3
    Join Date
    Dec 2002
    Location
    Madrid - Spain
    Posts
    422
    Provided Answers: 1
    somebody can send me an example where indicated the syntax for execute and show me the tables that need the reorg or runstats?

    Thank you for advanced.

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    The examples are in the manual. It is provided free of charge and is a great resource. Try using it.


    Here, I will even get you started: DB2 Universal Database


    Andy

  5. #5
    Join Date
    Dec 2002
    Location
    Madrid - Spain
    Posts
    422
    Provided Answers: 1
    Hi colleagues,

    can someone tell me how to detect which tables need runstats in db2 reorg or V8?

    Thank you very much and greetings.
    DBA DB2 for LUW

  6. #6
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    maybe we should write it in spanish ..
    have a look at reorgchk command
    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
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by georgipa View Post
    detect which tables need runstats
    You cannot detect which tables need runstats. You should know your data lifecycle or rely on automatic collection of statistics.

  8. #8
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Quote Originally Posted by n_i View Post
    automatic collection of statistics.
    Do you really trust auto-maintenance...

  9. #9
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by db2girl View Post
    Do you really trust auto-maintenance...
    Actually, for statistics I do. I've had a couple of projects recently where performance was critical (one was a credit card payment processing application, for example), and found no reasons to complain. You do need to execute RUNSTATS manually once in a while where data are massively modified by a batch process, because automatic runstats may not recognize the changes have been made for a few minutes, but otherwise it does the job without the performance penalty.

  10. #10
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    I can't trust any auto-maintenance and would turn it off if this was my choice. I was testing reorg/runstats/rbind and ran it on the server where I was not supposed to do it (didn't know it's fully controlled by the TSM product that DB2 is bundled with) and to my surprise db2 auto-maintenance (I think that's what this TSM product is using) performed reorg on some tables again a few days later (with no data change) - found "ANR0294I Reorganization for table xxxxxx ended" in some log.

  11. #11
    Join Date
    Mar 2012
    Location
    Canberra, Australia
    Posts
    38
    We turn it on for the non-prod environments and have auto runstats on the prod environment and haven't had any difficulty

  12. #12
    Join Date
    Jan 2009
    Posts
    33
    FYI,
    In 9.7 fp5 we faced some issues of performance in batch runs when the auto maintenance was turned on...and raised a pmr and IBM identified auto maintenance as the culprit and asked us to turn it off.after which we have no performance issues.

    Regards,
    sharath

  13. #13
    Join Date
    May 2012
    Location
    Chennai
    Posts
    45
    FYI,

    reorg needs when you alter the table one or more times then you insert or update the data into that table, It does nt insert or update.At that time, we have to arrange the alter commands into one page. For that we are using
    > reorg table <tablename>

    runstats means have to calculate the cards, npages,xml_objects,lobs that time we are using runstats else it shows -1.check syscat.tables then you can understand why we are using runstats.
    >runstats on table <schema_name>.<table_name>

  14. #14
    Join Date
    Dec 2002
    Location
    Madrid - Spain
    Posts
    422
    Provided Answers: 1
    can someone explain to me how to know which tables need reorganization, some tips to go more direct.
    Thank you for advanced.
    DBA DB2 for LUW

  15. #15
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by georgipa View Post
    can someone explain to me how to know which tables need reorganization, some tips to go more direct.
    Thank you for advanced.
    Reorg is never "necessary", but it may improve performance.
    So, one answer could be "a table needs reorg when applications accessing that table start becoming (too) slow".

    An other answer could be "a table needs reorg when you know that (lots of) non-clustered inserts have happened since the last reorg".
    Or, similarly, "when (lots of) updates have happened to the values in the cluster column".

    Older versions of DB2 don't store that information in the catalog, so you'll either have to rely on information from outside DB2, or else execute Runstats on that table to collect that sort of info ;-)
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

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