understanding and detecting sql performance situation with abnormal 400 shutdown

540 pts.
Tags:
DB2
RPGLE
SQL
SQL performance
Need help in understanding and detecting situation with logical files built for sql rpgle performance and abnormal shutdown. Our system went down due to ups failure. All of a sudden the rpgle sql program is a resource hog because it is not selecting the lf that was built specifically for performance issues. LF is intact and shows not damage; it's just not being used. Program does not reference this file in the rpg sql statement, but has always used it. I recompile the LF and everything is normal. My questions are 1. what's going on? and 2. how do I query the system for other lfs that are now being ignored? thanks

Answer Wiki

Thanks. We'll let you know when a new response is added.

Hi,

Have you IPL’d since the abnormal shutdown (with a normal IPL)?

This is pure speculation on my part, but maybe with an abnormal restart there are certain processes that don’t get started (to try to avoid another abnormal shutdown). One of these processes could be the query engine that gets used to find the most efficient access paths for SQL (and SQL RPGLE).

Unless anyone has any better suggestions or explanations I would try and plan a normal IPL as soon as you get a chance.

Regards,

Martin Gilbert.

————————————
Hi Modiyooch
Sounds like you need to build an index in SQL — I think you can use analyzer on Navigator to help identify this. The system might have built a temp index used it until the IPL wiped it. Indexes look like the keyed part of a LF but process in a much faster query environment.
Phil

Discuss This Question: 4  Replies

 
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
  • Modiyooch
    yes, we had a normal shutdown and restart after the failure. The only indication of a problem other than poor program performance was that a lf with heavy usage had not been used since the initial failure. I was just hoping to better identify this situation without finding out from the users when their programs become grossly inefficient. thanks for the input
    540 pointsBadges:
    report
  • Cwc
    You can also use the database monitor from a command line (STRDBMON), to see why the query engine is rejecting that index.
    4,290 pointsBadges:
    report
  • graybeard52
    Two things that will simply kill you on SQL performance: 1. Writing a query over a logical file. 2. Writing a query over a PF where ANY logical has select/omit or other derived keys, unless QAQQINI has been told to ignore derived indexes (default is to NOT ignore). Otherwise, try deleting the SQL packages and let the system rebuild them.
    3,115 pointsBadges:
    report
  • BigKat
    It is also possible that you have crossed some magic combination of number of records, etc, that now causes the query analyzer to build a different (and less efficient) plan
    7,845 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