Results 1 to 12 of 12
  1. #1
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399

    Unanswered: reorg ALL tables and indexes in a db

    Is there a way to reorg ALL tables and indexes in a db besides running reorg on each table one by one and then tables indexes? Our db has more than 700 tables so running reorgs on all of them one by one would be time consuming (not to say ridiculous ). Same with runstats...

    May be there is a script doing it (reading table names of the db from system catalog and than doing reorgs and runstats on them)? I checked the library (searched on reorg) and did not find anything.

    Thanks in advance
    Last edited by MarkhamDBA; 01-11-09 at 22:38.
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by MarkhamDBA
    Our db has more than 700 tables so running reorgs on all of them one by one would be time consuming (not to say ridiculous ).
    In my opinion, it will indeed be ridiculous, but for a different reason. It is highly unlikely that you need to reorg all 700 tables at once. Besides, only you can decide which will be the best clustering index, if any, for each table - you cannot automate that decision.

    You would have to analyze the results of REORGCHK and make appropriate decisions. Nobody said being a DBA is easy...

    As for statistics, you could use REORGCHK UPDATE STATISTICS FOR SCHEMA ... to, well, update statistics for all tables in a schema.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Oct 2004
    Posts
    268
    See if this hepls............

    Help - WebSphere Commerce version 5.6.1.4

  4. #4
    Join Date
    Aug 2004
    Posts
    24
    try this sql and save the result as a file and then "db2 -tf filename"

    select 'REORG INDEXES ALL FOR TABLE '||tabschema||'.'||tabname||';' from syscat.tables where tabschema='xxx' and type='T'
    union all
    select 'REORG TABLE '||tabschema||'.'||tabname||';' from syscat.tables where tabschema='xxx' and type='T';
    I am a java and database developer.

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    I would not run the script as mentioned, yes use the SQL to generate the list of objects to reorg. Reorg is an asynchronous command, so you need to add logic to ensure the prior reorg is complete prior to submitting the next one. Otherwise you are reorging your entire db at the same time, if your machine can handle that workload, then by all means go for it.

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Only inplace reorg is asynchronos, AFAIK

    Reorg is an asynchronous
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  7. #7
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    SCHEMA NAME CARD LEAF ELEAF LVLS ISIZE NDEL KEYS F4 F5 F6 F7 F8 REORG
    Table: DBA.XXS_XXXXXXXXX
    DBA xxxXNTYPEIX 7560 13 0 2 4 0 64 100 79 9 0 0 -----
    IPLF xxxXNIX 7560 92 0 3 34 0 7560 75 89 86 0 0 *----
    IPLF TXNPOSTIX 7560 12 0 2 12 0 41 95 87 9 0 0 -----
    SYSIBM SQL050621141422620 7560 97 0 3 37 0 7560 75 90 75 0 0 *----

    Table: DBA.XX_XXXXXX
    DBA XXXTXNIX11 235 6 0 2 72 0 235 95 95 18 0 0 -----
    DBA XXXTXNIX22 235 1 0 1 42 0 25 100 - - 0 0 -----
    DBA XXXTXNIX33 235 2 0 2 42 0 105 50 150 59 0 0 *----

    So in the above example should I do anything with IPLF.xxxXNIX, SYSIBM.SQL050621141422620, DBA.XXXTXNIX33 indexes if there is a * in the REORG col? What is SYSIBM.SQL050621141422620 anyway?
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  8. #8
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    it is a key/index that has been created implicitely for you
    eg alter table .. primary key ... will create an index for you..if not exist
    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

  9. #9
    Join Date
    Dec 2002
    Location
    Madrid - Spain
    Posts
    422
    Provided Answers: 1
    Hi Colleagues,
    When I executing db2 "REORG INDEXES ALL FOR TABLE schema.table", this process lock the table where is executing the reorg indexes?

    Thank you fpor you help, Regards.
    DBA DB2 for LUW

  10. #10
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    db2 "REORG INDEXES ALL FOR TABLE schema.table allow write access"
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  11. #11
    Join Date
    Dec 2002
    Location
    Madrid - Spain
    Posts
    422
    Provided Answers: 1
    Thanks for your help,

    This command locks the table or not?
    db2 "REORG INDEXES ALL FOR TABLE schema.table allow write access"

    Greetings.
    DBA DB2 for LUW

  12. #12
    Join Date
    Dec 2002
    Location
    Madrid - Spain
    Posts
    422
    Provided Answers: 1
    Hi colleagues,
    When I run reorg table, this command lock de table?

    Greetings.
    DBA DB2 for LUW

Posting Permissions

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