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: