embedded sql performance over large files
We have many applications using embedded sql statements. We are discovering that as the files grow larger the performance diminshes significantly. We have tried using dbmon to ensure indices are not being built on the fly. There are either indices or logicals for all keyed access sequences. Any idea on how to improve performance without stripping out the sql and replacing it with native db2 files?
Software/Hardware used:
Software/Hardware used:
ASKED:
February 20, 2006 4:04 PM
UPDATED:
November 30, 2009 7:20 PM
Answer Wiki:
debug your sql with Sql Visual Explain, it's a tool provided with operation navigator. details ? look at http://jplamontre.free.fr/AS400/RTVSQL.htm and http://jplamontre.free.fr/AS400/SqlExplain.htm
============================================================
Ripping out the SQL and replacing with native RLA will almost certainly make things slower, so why even consider it? Of course, that assumes that SQL is being used appropriately in the first place. If it's not focused on "set at a time" operations, then the reason it's slow is because it's not being used correctly.
If it is being used correctly, then you're going to make things slower after wasting a lot of programming time and effort. Doesn't make much sense to me even to consider it in that case.
Tom
To see all answers submitted to the Answer Wiki: View Answer History.
To see what SQL is doing, run Your prog in debug-mode. Just type STRDBG on the command line, run the prog and do a DSPJOBLOG. Go through all the messages and look where time is wasted, if access paths are build and so on. Take care not to get too many joblog entries from SQL or set the job attribute parameter JOBMSGQFL to *WRAP. Else it can abnormal end Your interactive session.
Are you using static SQL or dynamic SQL ?
What is the value of IGNORE_DERIVED_INDEXES in file QAQQINI ?