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)

1. Create a file run.bat and inside write:
2. Create a reorg_runstats.sql file and inside write SQL commands separated by ";" character:
Hope this helps,
Grofaty

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

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.....