Way to speed up first invocation of stored procedure?

330 pts.
Tags:
AS/400
DB2 Universal Database
Can you help? An iSeries user recently wrote the editors of Search400.com with this question: "One of our stored procedure takes approximately 1 minute for the first invocation when I do a call using RUNSQLSTM. If the same procedure is called repeatedly with different parameters, the results come back within one or two seconds. Is there a way we can reduce the warm up time for the stored procedure?" -- Michelle Davidson, editor, Search400.com
ASKED: September 13, 2005  1:21 PM
UPDATED: November 10, 2009  3:55 AM

Answer Wiki

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

John Paul Lamontre said this is not an easy question. He writes:

First, run SQL under
STRDBG
(without any parameter).

You will have a complete description of the SQL optimiser process. Inspect in detail each message, checking for a missing index or other requirements of SQL.

If second runs are quicker, SQL has probably delayed the destruction of a temporary index.

I have encountered thie problem sometimes, but was unable to find an explanation.

Discuss This Question: 1  Reply

 
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
  • TomLiotta
    Without knowing a lot about the environment and the stored proc, it's almost random guesswork. Are the files SQL tables or DDS? What kind of stored proc is it? SQL? (REXX?) What does it do? What does debug show? Are proper indexes defined? What do the tables/files look like? What columns/fields are accessed? What kind (and how much) of performance tuning has been done to the system overall? Have memory pools been properly defined and categorized? Have sufficient disk arms been given to the system? Does sufficient memory exist? Tom
    125,585 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