I couldn't see the first part of your post, but from what I can see of
your message, I think you are somewhat stuck if you absolutely MUST use
dynamic SQL. The first access requires the SQL statement to be
prepared, and if any access paths must be built, that's when it
happens.... Depending on your OS release, your access plan may be
stored in the "plan cache", which may or may not be helping you with the
most common access plans used by your app.
You should first attempt to see if access paths are being built, and if
so, perhaps you should create the suggested index... If you are on V5R4
or later, I might suggest that you use Navigator to check the Index
Advisor. Prior releases will give you the needed diagnostic details in
the joblog, when running under debug. Just use STRDBG UPDPROD(*YES) in
your CL wrapper.
Are you sure that you must use dynamic sql for this? Static SQL will
outperform dynamic SQL in almost every way. Can you post a sample of
your SQL?
I'm sorry, but I won't hazard a guess as to your question #2.
Eric DeLong
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of profound
Sent: Tuesday, October 21, 2008 9:49 AM
To: midrange-l@xxxxxxxxxxxx
Subject: RPG CGI application
This is a multi-part message in MIME format.
in persistent mode, some
using dynamic SQL. I have noticed 2 problems
1. When a program containing SQL is first called, it takes a
significant time to run (sometimes up to 14 seconds). Any subsequent
call to the same program will run normally in less than a second.
2. When running non-SQL programs, and the user stops using the system
for approx 30 minutes, the first program takes a very long time to run
when the user resumes using the system again ( sometime up to 20
seconds).
Any suggestions?
Thanks,
Rob Ferguson
Sr. Developer/Analyst
Profound Logic Software, Inc.
Phone: 937-439-7925
Toll-Free: 877-224-7768
Fax: 603-849-7757
rferguson@xxxxxxxxxxxxxxxxx <mailto:rferguson@xxxxxxxxxxxxxxxxx>
www.profoundlogic.com <
http://www.profoundlogic.com>
Modernization Made Easy
midrange.com
