INDEXREC set to access : Poor Performance

[BAS]

Hi ,

Can anyone throw some light on this,
We have test,dev and production environments with DB2 8.1 fix pack10
running on aix 5.3.

Ours is an ERP finance application module, The reports which we run on
the test and the dev environments are performing well and with the same
h/w configuration we are not able to get the same performance in PROD.

While analysing the diag logs, i could see the index rebuilds are
frequent and huge rebuilds.
In Prod INDEXREC was set to ACCESS and in Dev and Test env. it is set
to RESTART.

I feel this is the reason for the poor performance, a i am not able to
trace any other difference with dev,test and prod.

DB2 guru's can someone throw some light on this....

Regards,
Baskar

[santhosh.david]

Hi Baskar,

It all depends on how your application is designed. Setting this
parameter to "access" has its own merits and demertis.

Merits - There is no point of have a invalid index in your database.
You cannot improve the performance of a sql statement with invalid
indexes in hand. This will only rebuild invalid indexes and not all the
indexes.

Demerits - But, IBM prefers to set this parameter to restart, because
it will result in a degradation of the performance of the database
manager while the index is being re-created. Any user accessing that
specific index or table would have to wait until the index is
recreated.

I request Knut Stolze to throw some light on it.

Regards,
Santhosh David.