Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

closed as unclear what you're asking by RolandoMySQLDBA, Michael - sqlbot, Kin, Phil, Aaron Bertrand May 8 at 21:57

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question.If this question can be reworded to fit the rules in the help center, please edit the question.

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.