No announcement yet.

Options for Complex (slow) data source

  • Filter
  • Time
  • Show
Clear All
new posts

  • #16
    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.


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


      • #18

        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.

        Hope this helps someone else.


        • #19
          Thank you for the update.