embedded sql performance over large files

0 pts.
Tags:
AS/400
DB2 Universal Database
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?
ASKED: February 20, 2006  4:04 PM
UPDATED: November 30, 2009  7:20 PM

Answer Wiki

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

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

Discuss This Question: 2  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
  • Jgsnsbt
    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.
    30 pointsBadges:
    report
  • graybeard52
    Are you using static SQL or dynamic SQL ? What is the value of IGNORE_DERIVED_INDEXES in file QAQQINI ?
    3,115 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