Index Rebuild taking too long on DB2

[Former Member]

We are in a Project to move from Oracle (10.2) to DB2 (DB6 Ver 09.07?)  on our ECC/BW/APO systems..

We have Live still operating on Oracle, and QA system (copy of Live then converted) running on DB2.

We have an InfoCube in BW (holds versions of our Entire Demand Plan, one version per day!) which has a lot of records (too many really but thats another story!!!!), in our Live (Oracle) Overnight Process Chain this takes just short of 30 minutes to re-add Indexes after data is loaded, but in DB2 environment this is taking over 10 hours (even on a system with no other users but me it took 8.5 hours!). This is the same every day, so not just a first run type of issue.

Note: I am no expert at Db level !!!!!

The cube is marked as "Index Clustering", as opposed to Multi-Dimensional Clustering, and I see I can't change this as I have data loaded).

There are no specific messages showing in SM21 or ST22 that I can see, Nothing shows in Job Log.

There is 2 years worth of data in the cube (yes I know this should not really be the case but I am new to organization and it seems to historically be this way, I need to rework the entire data model, but that will have to be after this project!).

I have a High call with SAP open but it has been 4 days with no real response and they won'tr work on it over the weekend apparently, but I need to get this moving otherwise Project Go-Live is threatened!

Are there any gurus out there who can point me in the direction of what might be the issue, or what to check, and of course how you might go about getting this back to normal runtime? Our overnight processes would not complete anythwre near to the required time if they went for an extra 10 hours!!!!!

Regards

Simon

[Former Member]

Hello,

Delete your all existing indexes for info cube and rebuild it. 

Hope it will help.

Regards,

Sangita

[Former Member]

As part of the Process Chain I am deleting Indexes, then loading data and then recreating indexes. We do this for all Infocube loads. It is the recreation step which is taking a very long time.

Do you mean doing somethign different to this?

I have also tried deleting indexes within RSA1->Infocube->Manage->Performance, and then recreating the index on same tab, but this just gives me the same long time.

This is a very large cube (100Gb) with many, many millions of records.

Regards

Simon

[Former Member]

Hi,

   You can try to  rebuild the index and check whether it resolves your issue.

Thanks and Regards,

Vimal

[Former Member]

Hi,

Please go to SE38 and try to execute the report SAP_INFOCUBE_INDEXES_REPAIR

It will drop all the indexes and recreate it.

Recently we faced few index related issues like slow performance due to corrupted indexes.

We executed this report which drops all the indexes and will be recreated it correctly.

Based on the DB size, the report will run for some time.

Keep your database stats up to date and try to run the above report.

I hope it helps.

Thanks and Regards,

Vimal

[Former Member]

As part of the Process Chain I am deleting Indexes, then loading data and then recreating indexes. We do this for all Infocube loads. It is the recreation step which is taking a very long time.

Won't above just do the same thing?

Database Stats are updated in the step after Index recreate, but this is normal in Process Chains.

I have also tried deleting indexes within RSA1->Infocube->Manage->Performance, and then recreating the index on same tab, but this just gives me the same long time.

This is a very large cube (100Gb) with many, many millions of records.

Regards

Simon

[Former Member]

Hi Simon,

What does your migration consultant say?  As this is a heterogenous migration then a certified consultant should be helping - is that you?  He may not be a DB2 expert but may have a few contacts at SAP who can help too.

Regards,

Graham