Recomended Setting for Max Number of Rows per ApplicationGroup Table

[Trambak]

All
  We are currently using 10M (10 million) as the max number of rows per application group table. When we plan to increase it to 40M for example, the arsmaint job is taking a long time to complete thereby increasing the maintenance window considerably. The runstat command used as part of the arsmaint job is taking the most time. I would like to understand if anyone is using a higher value than 10M and how is the system maintenance performing? Is there a good way of increasing the value and still keep the maintenance job to a minimum.

Thanks
Trambak

[Justin Derrick]

Runstats is an online operation, and doesn't require downtime -- so simply run it during a period of low activity.  If you cannot perform a 'runstats' without suffering from performance issues while serving users, you need a hardware upgrade.

-JD.

[demaya]

Hi,

I don't use runstats via arsmaint. I set the following db2 settings to on:
Automatic table maintenance          (AUTO_TBL_MAINT) = ON
Automatic runstats                  (AUTO_RUNSTATS) = ON
Automatic statement statistics  (AUTO_STMT_STATS) = ON

Everything works fine here. I schedule the online backup via cron.

Cheers

[Trambak]

Justin
  I know that runstat is an online operation. But it still has an impact if we load data to the tables on which runstat is running. I think its recommeded that we dont load data to the tables when we are running the runstat on the tables. Is that true?

Mayach
   We dont have DB2 as our backend database. We use oracle. Is there any similar settings that I can leverage for Oracle.

Thanks
Trambak

[Justin Derrick]

Hi Trambak.

You're really entering the fuzzy world of performance tuning here.

You need to identify a period of low activity, when you're not loading data.  It sounds like you're already creating one with a nightly 'maintenance window'.  If you have two conflicting requirements -- do all maintenance inside the maintenance window, and that window of time is too short to complete all the assigned maintenance work, you need to start looking at a hardware upgrade of some sort.

Now, a thing about runstats and CMOD...  Since CMOD is an archive, tables rarely (if ever) change, except for the addition of new data.  Because of this, access methods are generally the same -- find the table containing the relevant dates, use an index to eliminate the majority of the table, and search for your results inside that set.  In this scenario, runstats doesn't really help that much.

The ultimate solution for you might be to simply run 'runstats' less frequently -- perhaps on the weekend when usage is lower, or your maintenance window is longer.

Good luck!

[dr_te_z]

except for the addition of new data.

When old documents are deleted you will see "old" tables suddenly containing less rows than the specified value. This means rows have been deleted and a runstats is required again.
I do agree with the "fuzzy world"