Different SQL index used, dynamic SQL using STRSQL vs. SQLRPGLE in a job

10 pts.
Tags:
SQL SQE vs CQE

Hi,

We have a performance problem with SQLRPGLE. Using the SYSIXADV we have built a index.

When the query is executed from STRSQL that index is used and it takes no longer than 5 seconds. But when the same query is run from Embedded SQLRPGLE program in a  job the Index is not being used and it takes forever to get the result. Apparaently SQE is giving away and CQE is taking over.

I have seen the results of STDBMON and it indicated that Index is not being used for SQLRPGLE program but was used for STRSQL.

And when the same Index was built on another machine both SQLRPGLE and STRSQL used the same index. Both machines are on V6R1 and PTF's are the same. The differences between the two environments that I have noted are as follows

Fast performing AS400(which Chose Index):

1. Job CCSID/Language/Sort Seq: 37(US)/ENU/*HEX

2. Program CCSID/Lang/Sort Seq: 37(US)/ENU/*HEX

3. File & Index CCSID/Lang/Sort Seq: 37(US)/ENU/*HEX

 

Slow Performing AS400 (which didnt choose the index):

1. Job CCSID/Language/Sort Seq: 297(FR)/FRA/*HEX

2. Program CCSID/Lang/Sort Seq: 65535/ENG/*HEX

3. File & Index CCSID/Lang/Sort Seq: 37(US)/ENU/*HEX

And the dynamic query itself has a Order By clause with a field name which has '#' (i.e. APP#), which I know will be different for different CCSID's

My question is, if the CCSID is responsible for Job not picking the Index. Is there any work around to circumvent the problem without changing CCSID's? Is there any PTF's available from IBM. Please let me know if you need any other details.

 

Thanks in advance,

naren

 



Software/Hardware used:
iSeries V6R1M0

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TomLiotta
    What is the source file CCSID that the program source was stored in and compiled from? What is QCCSID for the system that compiled the program?   Using variant characters for names is a definite potential problem, and it's unlikely that anything like a PTF exists because the system is working the way it's defined to work. Applying such a PTF would likely mess up a lot of processes on the system and make CCSID unpredictable.   What does STRDBG indicate for index choices on the remote (slow) system? You show the *PGM CCSID, but what does the SQL *MODULE show for a CCSID? Does the remote system have QCCSID 297, or is the job just running under that CCSID? Maybe most useful, what is the query that runs in the program?   Tom
    125,585 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following