aritting
Active Member
We had very slow performance with the inventory as of regeneration program R41542. R41542 reads F4111 for records that do not have an X,S,or Y in F4111.ILIPCD and then uses a business function to insert or update records into F41112.
This job was taking 2 - 3 hours to process only 2000 - 3000 records a day. I was able to improve the processing of this job dramatically by applying two indexes.
My platform is DB2 on AS400 but these indexes should apply to all other platforms.
On F4111 I created a binary radix index on ILIPCD:
CREATE INDEX PRODDTA.F4111_DBA01 ON PRODDTA.F4111 ( ILIPCD ASC )
This index improved the initial business view data selection.
The second index more dramatically improved performance.
F41112 comes from JDE with only the primary key index on it. I ran R41542 in debug mode to get the sql statement it was sending to the server: SELECT * FROM PRODDTA.F41112 WHERE ( INDCT = ? AND INFY = ? AND INCTRY = ? AND INITM = ? AND INMCU = ? AND INLOCN = ? AND INLOTN = ? AND INGLPT = ? ) ORDER BY INCTRY DESC, INFY DESC
To F41112 I applied this index:
CREATE INDEX PRODDTA.F41112_DBA01
ON PRODDTA.F41112 ( INDCT ASC , INMCU ASC , INGLPT ASC , INLOCN ASC , INITM ASC , INLOTN ASC , INCTRY DESC , INFY DESC ) ;
With these two indexes, R41542 can now processes a few thousand records in under 10 minutes
This job was taking 2 - 3 hours to process only 2000 - 3000 records a day. I was able to improve the processing of this job dramatically by applying two indexes.
My platform is DB2 on AS400 but these indexes should apply to all other platforms.
On F4111 I created a binary radix index on ILIPCD:
CREATE INDEX PRODDTA.F4111_DBA01 ON PRODDTA.F4111 ( ILIPCD ASC )
This index improved the initial business view data selection.
The second index more dramatically improved performance.
F41112 comes from JDE with only the primary key index on it. I ran R41542 in debug mode to get the sql statement it was sending to the server: SELECT * FROM PRODDTA.F41112 WHERE ( INDCT = ? AND INFY = ? AND INCTRY = ? AND INITM = ? AND INMCU = ? AND INLOCN = ? AND INLOTN = ? AND INGLPT = ? ) ORDER BY INCTRY DESC, INFY DESC
To F41112 I applied this index:
CREATE INDEX PRODDTA.F41112_DBA01
ON PRODDTA.F41112 ( INDCT ASC , INMCU ASC , INGLPT ASC , INLOCN ASC , INITM ASC , INLOTN ASC , INCTRY DESC , INFY DESC ) ;
With these two indexes, R41542 can now processes a few thousand records in under 10 minutes
