Bad response time for first invocation of stored procedures in DB2400

Tags:
DB2 Universal Database
Platform: V5R3 Problem: A stored procedure takes about 1 to 2 minutes for the first invocation (No difference in this timing for green screen or i-series navigator or Java call through QZDASOINIT jobs). Subsequent invocations are wicked fast with subsecond response times Other info: Procedure takes 1 input parameter and results a return set by opening a cursor. Please let me know if this is because of plan or access paths being built every time for a new job? If yes, how can this be moved to the DB2 side so that the access path is shared across the jobs? Regards, Shivaprakash Rao

Answer Wiki

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

iSeries DB2 (SQL) is very sensitive to access paths. With response times like you describe that’s the first place I look. Try creating an index over the file with the same columns as either your ORDER BY or your WITH clause (in the same order).

Discuss This Question:  

 
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

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