Options for Complex (slow) data source

[gwilburn]

In case someone else runs into this...

My performance issue is sporadic, and related to how the IBM i SQE is building the plan cache.

I have witnessed consecutive days where the SQE for these data sources, in Run SQL Scripts (RSS), uses indexes I've created. When used, the response time is 1-3 seconds.
Other times the SQE decides to build Temporary Hash Tables over very large files instead of using the corresponding indexes. When this occurs, the response time is 20+ seconds.

The IBM database group is looking at the issue.

[robert.swanson]

Interesting. We'll be curious to hear what IBM reports.

[gwilburn]

Update:

So #1 IBM says I should not IPL the system each morning. It's clearing my SQL Plan Cache. I responded by telling them they shouldn't be issuing daily PTFs and I wouldn't have to :). I may rethink this.

#2 To get the SQE to use the index I created, I did CHGLF KEEPINMEM(*YES). This seems to have corrected my issue. I used this from the command line even though my index was created using SQL CREATE INDEX. But the KEEPINMEM can be done on the CREATE INDEX or with a separate SQL command.

https://www.ibm.com/docs/en/i/7.3?to...rence-controls

Hope this helps someone else.

[sean.lanktree]

Thank you for the update.