I have 4 AS400s running JDEdwards.
Lately a performance issue occured as described here: AS400 Performace issue
Although i am not an expert in AS400 , i tried to find out the cause. I think it came down to this:
I run the following SQL in all my systems:
select sort_sequence,index_schema,count(*) from qsys2.sysindexstat group by sort_sequence,index_schema
In 3 of my 4 systems all of them have sort_sequence="BY HEX VALUE"
In my 4 problematic system many indexes have sort_sequence="*LANGIDSHR"
I start to believe that this can cause performance problems as the query optimizer may leave these indexes unused and create temp indexes.
I wish somebody has an idea on where to go on from now and how.
Is there any specific setting that make all the indexes have default sort_sequence?
All my indexes are created massively so this could not happen by mistake.