MRP taking so long after Monthly reorganization

[Former Member]

Hi Guys,

We perform monthly reorg of all SAP tables. Since the weekend of June 16th, I noticed that MRP started taking lot longer. We run parallel MRP where we use 6 DIA work processes. On the weekend we run REGEN MRP which means that there are lots of materials to be planned. In the past the job will finish in 5 hours. This past weekend it ran for 18 hours and still didn't finished. on the SM50 I could see all the 6 WP's doing a sequential read of MDFA. MDFA is a view based on AFPO table. Also last week one of user reported that MD07 takes a lot longer and again in that case I saw in SM50 that the system was doing a sequential read of MDFA.

Yesterday I cancelled the MRP. Took the system down, deleted old sql packages and then reactivated MDFA using SE11. I again submitted MRP but unfortunately it did it still didn't finish.

I have opened note with SAP in the PP-MRP group and they are looking at the issue but my gut feeling says that it is more a Basis issue rather than MRP issue.

If I don't find any solution from SAP then I am going to take system down tonight. and perform Reorg on AFPO.

Please let me know if there are any other pointers.

We are on V5R4.

Regards

Srikant

[Former Member]

Hi Srikant,

I'm sorry to say, but this sounds very likely like an optimizer bug in DB2/400 ;-(

Are you on a "pretty new" V5R4 PTF level?

The described views are "pretty easy" views and therefore the SQL should return fast. I'm pretty sure, that an SQL-Trace (ST05) would show enormous times on these views which could happen in case of e.g. wrong join order.

You should analyze this and should move your OSS ticket to componente BC-DB-DB4 asap and then SAP & IBM will have a look into it.

You could try rgzpfm on that one table, but the chances are low and it would just be random ...

Regards

Volker Gueldenpfennig, consolut.gmbh

http://www.consolut.de - http://www.4soi.de - http://www.easymarketplace.de

[Former Member]

Hi Volker,

Thanks a lot for the helpful hint. The problem is now fixed and here is how I did it. I did reorg on AFPO. Brought SAP up and then using SE14 activated and adjusted MDFA and AFPO. The problem was still not fixed. Using the trace I found that the system was not accessing AFPO + 1 index rather it was reading the entire table. Using SE14 I activated and adjusted the index and the problem was fixed.

MRP is back down to 20 min.

Life is good now.

Thanks a lot for help.

Regards

Srikant