I am having a problem as i outline here: https://www.ibm.com/developerworks/community/forums/html/topic?id=626500cb-d6e3-459b-acb2-c4bfd1ab8d79&ps=25
I have 4 AS400s running JDEdwards.
Lately a performance issue occured as described above
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 4th 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.