Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256

    Unanswered: script for reorg / runstats

    I currently deploy a small database server with a number of datamarts (7.1 on Win NT). There is just one datamart that stacks really large tables and I am running into performance problems.
    The largest facttable has some 3.5 million records. I used to manually reorganize now and then , but I suppose there must be ways to start up a script every evening to reorganize / run statistics.

    What would be the most efficient way to perform such a run automatically?

    (P.s. Cannot update hardware / software, cause we are supposed to phase out our datamarts in the near future)
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

  2. #2
    Join Date
    Jan 2003
    Posts
    1,605
    1. Create a file run.bat and inside write:
    Code:
    db2cmd /c /w /i db2 -tvf reorg_runstats.sql
    2. Create a reorg_runstats.sql file and inside write SQL commands separated by ";" character:
    Code:
    reorg table <table_name> index <index_name>;
    runstats on table <table_name> with distribution and detailed indexes all shrlevel change;
    Hope this helps,
    Grofaty

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    You can use Grofaty's scripts ...

    But remember, unless there is a substantial difference in the statistics(rule of thumb is 10-20% data change), having a runstats run every night will make little difference.

    As for REORG, again, do it only when there is a need ... You can use REORGCHK Command to get recommendations for REORG. Generally, I would consider doing once a month. I understand that REORG in V7 is not very reliable ... So make sure you take a tablespace backup before the REORG and preferably, after as well. You will have to see if you have enough space to do the REORG. DB2 makes a copy of the data in the same tablespace or in a temp tablespace(when USE TABLESPACE) option is used.

    HTH

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  4. #4
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256
    Okay, message understood.

    Biggest effect on the datamart is after first day of the month activities, so I will probably be allright with running the script on the evening of the first day of the month.

    Thanks both for your input.....
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

Posting Permissions

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