Home > IT Answers > AS/400 > understanding and detecting sql performance s...
Modiyooch
370 pts.
0
Q:
understanding and detecting sql performance situation with abnormal 400 shutdown
RPGLE, SQL, DB2, 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
ASKED: Jun 3 2009  2:32 PM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
0
Philpl1jb
24105 pts.
0
A:
 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0
  • AddThis Social Bookmark Button
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
Last Answered: Jun 3 2009  3:35 PM GMT by Philpl1jb   24105 pts.
Latest Contributors: Gilly400   23625 pts.
  •   
0
0
RSS - Discussions Help
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

Modiyooch   370 pts.  |   Jun 3 2009  3:21PM GMT

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

 

Cwc   3825 pts.  |   Jun 4 2009  1:24PM GMT

You can also use the database monitor from a command line (STRDBMON), to see why the query engine is rejecting that index.

 

Graybeard52   2355 pts.  |   Jun 5 2009  2:24AM GMT

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.

 

BigKat   2480 pts.  |   Jun 5 2009  5:16PM GMT

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

 
0