SQL statement slower on as400 console than from remote odbc I am having a strange problem on one of my i systems running 7.1

I am using jdedwards 8.0 as erp but the issue is not related as i just found out.

After some research it came down to this. I run the same simple select in a specific table. First time i run it from one fat client which connects through odbc and the second time i run it through STRSQL.

first time it takes 1 sec maximum and second time it takes 4-5 seconds.

Debug (STRDBG) shows that when running on server it has an index suggestion that already exists! and odbc machine uses it.

I tried droping and adding the index from both erp and from navigator but results are the same.

if i run the same sql from STRSQL for the same table but on our production library it runs fast like odbc case.

sorry for my ignorance but iam not even close to being a guy that knows as400...

P.S. some more info:

Its a simple SQL that i am running:

SELECT  *  FROM testDTA/F74G0904  WHERE  ( WFDCT = 'JE' AND WFDOC = 
166823.000000 AND WFKCO = '00003' AND WFDGJ = 114090 AND WFJELN =   
1.000000 AND WFLT = 'AA' AND WFEXTL = ' ')  
The table has around 10.000.000 in both libraries.



  GREEN SCREEN - LIB1 - 4 SECS
  
  GREEN SCREEN - LIB2 - 1 SEC
  
  ODBC CLIENT - LIB1 - 1 SEC
  
  ODBC CLIENT LIB2 - 1 SEC


It seems to be library specific and connection specific at the same time.

DSPJOBLOG in the case of "GREEN SCREEN - LIB1 - 4 SECS" suggests an index that already exists. Also as i said tried to drop and create the index.

Take a look on the sam eSQL in different libraries, optimizer and execution plan:

Comparison images

Thanks
The problem was something simple yet hard to find.

The indexes' sort sequence was wrong.

The clients were wrongly configured to use language preference for sort sequence. 

So when the indexes were created they had language specification for sorting.

The server when a job was submitted, was looking for indexes with *HEX specification so he was ignoring the existing indexes.