practical advice for large index generation in JDE

E1 or JDE is not that smart when it comes to generating indexes…  In fact, it’s pretty bad.

Developers think that it’s easy, until they realise that the process is going to create all indexes on the F0911 without any parallelism and synchronously.  When you have 21 million records, you might be waiting 48 hours.  And you might be causing some pretty bad locking too.

So how do you get around this conundrum, developers are not too bright and that big “generate indexes” button is pretty damn attractive!!!

Firstly, secure all standard passwords – PRODDTA etc etc.  Developers are also well know for pressing “generate table” – classic.  Oh, how I love flashback for that one!

Secondly don’t let developers generate indexes, sure they can create them…  not generate.  Make it policy.  Secure that data source selection screen (W9866J)!

If you need to generate the indexes, do the following:

• turn client logging on
• Generate the table to an alternate data source
• use findstr to extract the CREATE statements
• find the index you want to generate
• Change owner and tablespace information
• paste query into SQL Plus

Now that is a set of steps that even a developer could understand!

I’m also a fan of having database only indexes, JDE does not need to know about them if you want them for performance only.  The only reason developers love to put them in is because of the poor API’s that are exposed to them in the FDA and RDA for fetch and fetch all… (or one of them)