Hello all

We are using Ingres 2.6 on Solaris (su9 64 bit version)
and have been warned by our developers that the next release
of our application software will dramatically increase the use of
database procedures.

Any thoughts on config.dat setting changes that might make the
process of execution of these rpocedures less painful?

Fro example, are there any settings more specific to procedures than
database caching, logging or security?

Thanks

Fred Myers

"Fred Myers" <Fred.Myers (AT) det (DOT) wa.edu.au> wrote

[snip]
*Less* painful? Do you expect some necessary minimum amount of pain from
executing DBPs?

DBPs live in the QSF pool, so you may find you need to significantly enlarge
it to prevent churning. The rule of thumb is that if it's more than 80%
used, you should double it and measure again. Trace point QS501 will tell
you how much is in use. Other trace points give more information on
churning. You can also use DBAnalyzer to monitor what's going on in the QSF
pool.

You may find you also need to increase the stack size, because it's used to
pass parameters to DBPs.

In my experience the pain of DBPs is not in executing them but in managing
them. The tools for that are non-existent. Because they are easily rolled
out and replaced (more or less on-the-fly), you quickly lose confidence that
you know the version of each DBP that you are using. The biggest favour you
could do yourself is to impose rigorous discipline on the rollout procedure,
and insist on a distinctive, automatically updated version string in each.
Be prepared to drop and replace all procedures at short notice, just so that
you can get back to a known state. That will mean understanding
dependencies because there may be a critical order in which they need to be
redefined. (I say this based on my experience with a system that had almost
4,000 DBPs.)

Roy Hann (rhann at rationalcommerce dot com)
Rational Commerce Ltd.
www.rationalcommerce.com
"Ingres development, tuning, and training experts"

A couple of other things to note.
If any of these procedures are large (undefined) then you may need to
increase opf_memory and/or psf_memory just to load them. Watch out for 'Out
of memory' errors and note the facility code.
Also beware of the pitfalls of recreating DBprocs in a multi DBMS server
environment. Each server has its on QSF pool so there is a copy of the DB
proc in each one. It will only be replaced in the catalogues and the QSF
pool of the DBMS server that did the CREATE PROCEDURE. Thus you can end up
with the situation where some sessions are using one version of the proc and
some are using another. There are a number of options:
a. Only recreate the procedure in a controlled environment where no
users are connected just after starting ingres or something similar.
b. Restart Ingres straight after loading the proc.
c. Make sure you run the CREATE in every DBMS server by using
II_DBMS_SERVER to connect to each one
d. Flush the qsf pool in every DBMS server straight after CREATING the
proc by using TRACE POINT QS506. Note that this will flush all procs and
repeated queries so it will have a short term impact on performance. Trace
points are not supported.
Having said all that you are hopefully only using one DBMS server, but no
harm in mentioning it.


Peter Gale

Peter Gale wrote:

Very true, but -- watch out for messages that claim that PSF is out of
memory. If you increase PSF memory, and it doesn't help, try increasing
QSF memory instead. The parser facility uses both, and does not
always distinguish between them when issuing messages.


Karl

We have a slight issue with updating procedures in development databases
where not all servers get the updated version of the procedure, and we have
to use a script (trace point?) to force an update on all servers.

Andrew


"Fred Myers" <Fred.Myers (AT) det (DOT) wa.edu.au> wrote

On Sun, 18 Jan 2004 22:38:13 +0100, "Andrew Wilson"
<bluemalov_NOSPAM_ (AT) hotmail (DOT) com> wrote:


"Also beware of the pitfalls of recreating DBprocs in a multi DBMS
server environment. Each server has its on QSF pool so there is a copy
of the DB proc in each one. It will only be replaced in the catalogues
and the QSF pool of the DBMS server that did the CREATE PROCEDURE.
Thus you can end up with the situation where some sessions are using
one version of the proc and some are using another. There are a number
of options:
a. Only recreate the procedure in a controlled environment where no
users are connected just after starting ingres or something similar.
b. Restart Ingres straight after loading the proc.
c. Make sure you run the CREATE in every DBMS server by using II DBMS
SERVER to connect to each one
d. Flush the qsf pool in every DBMS server straight after CREATING the
proc by using TRACE POINT QS506. Note that this will flush all procs
and repeated queries so it will have a short term impact on
performance. Trace points are not supported."

http://groups.google.com/groups?q=cr...net.com&rnum=1

--Kent



=================================
Kent Smith * IPSO Incorporated
Business * Technology * Solutions
Financial Services and Accounting Systems Consulting

http://www.ipsoinc.com

Kent, Lighten up.

Kent Smith <ksmith (AT) ipsoinc (DOT) com> wrote

On 21 Jan 2004 09:59:34 -0800, stevem (AT) 141 (DOT) com (Steve McElhinney)
wrote:

I have put on a few pounds lately. Part of getting older, I suppose.
Perhaps a gym membership would help.

--Kent


=================================
Kent Smith * IPSO Incorporated
Business * Technology * Solutions
Financial Services and Accounting Systems Consulting

http://www.ipsoinc.com